Re: [ADMIN] Is regular vacuuming with autovacuum needed?
On Mon, Aug 16, 2010 at 4:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Smith g...@2ndquadrant.com writes: The other alternative here is to just tune autovacuum so it runs really slowly, so it won't kill responsiveness during any peak period. While in theory that's the right thing to do, this is much harder to get working well than what I just described. But you really have to do that *anyway*, if you're not going to turn autovac off. I think the case where you want to combine cron-driven vacuum with autovac is where, having made sure autovac is dialed down enough to not present performance issues, you find that it can't keep up with the required vacuuming. Then you need to do some not-hobbled vacuuming during your maintenance windows. Eventually probably autovac will have some understanding of maintenance windows built-in, but it doesn't yet. If the global values aren't vacuuming that table enough, then it seems apropos to change the autovacuum_vacuum_threshold value to some lower value for that table. (And it seems to me that if autovac never kicks in until 10% of a table's dead, that's not nearly aggressive enough, possibly even with the global value! Given the 8.4 visibility map changes, shouldn't autovac be a tad more aggressive, when it should be spending little time on non-dead material? If the visibility map is doing its job, then the global threshold can be set pretty low, no?) I'm not quite sure what to think maintenance windows would look like... I see them having at least two distinct forms: a) A maintenance that is particularly for vacuuming, where factors would get dialed down to encourage vacuuming tables that mayn't have been hit lately; b) A maintenance where it is expected that Things Are Being Changed, where it might be pretty well necessary to shut off autovac so it doesn't interfere with DDL work. -- http://linuxfinances.info/info/postgresql.html -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] postgresql and xfs filesystrem
On Sun, Jan 25, 2009 at 1:47 PM, Ezra Taylor ezra.tay...@gmail.com wrote: All: Do any of you have gripes about using XFS with the latest version of postgres? I'd not expect there to be much specific benefit to it... I did some benchmarking, now quite a while ago, which showed XFS to be, for a totally write-bound workload, a *few* percent better than ext3/JFS, but note that this is only a minor difference. The fact that XFS isn't one of the more highly supported filesystems on Linux is something I'd consider a *way* more important factor. When balancing oh, maybe a tiny percentage faster against oh, nobody will be in a position to offer much support if anything goes wrong, I'll take easier to support any day. -- http://linuxfinances.info/info/linuxdistributions.html Robert Benchley - Drawing on my fine command of the English language, I said nothing. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Need Scheduling Support for Your Databases?
carl [EMAIL PROTECTED] writes: Greetings All, I'm working on a specification for an Enterprise-class Open Source batch scheduler, and I really would like some expert commentary from experienced database people. Please have a look at: http://openjcs.sourceforge.net and the specification to date at: http://openjcs.sourceforge.net/doc/OpenJCS-arch.pdf Frankly, I'm of two minds about it. Half of me thinks it's massive overkill, and the other half thinks I must be missing something really important. Let me know what you think. I really do want your honest opinion, good, bad or indifferent. Also, it has been suggested that the specification might be useful if ported to Windows. For those of you who administer databases on both Windows and *nix, how useful would a tool like this be to you in your day-to-day operations? Do you think there is a big enough market to warrant cross-platform support? It's not so much a DBMS issue as it is one for people running a lot of heterogeneous applications (e.g. - DBMS bits, systems administration bits, application bits, ...) across a bunch of hosts. The best overview document I have seen about enterprise job scheduling is the following one which is actually a Microsoft white paper. (I found it when Googling for vms job scheduling.) http://download.microsoft.com/download/2/7/2/272DB1B6-209A-4AEC-A231-105B35DC0271/Job_Sch_Final.doc I don't know that I necessarily would want *all* the features that they mention; they describe 3 'generations' of batch scheduling features, and the items in the 3rd generation seem rather abstruse. But plenty of the features in the first couple of generation lists seem pretty useful. You may well find features in that document that you'll want to add. I'll note that you almost certainly want to use a full scale DBMS (probably PostgreSQL!) to robustly store and help validate the rather large amount of metadata that you will have around. That is the usual way that enterprise job scheduler systems are implemented. For instance, one feature you almost certainly want (that a proper DBMS would help support) is the notion of supporting work calendars. There are jobs to run every workday, based on some calendar. And there may be multiple of those, as sets of holidays vary from place to place. Indeed, this week contains the (different) national holidays for Canada and the US. My employer has offices in both countries, so that there is the potential to need to schedule things to evade some *or both* sets of holidays. Monday and Wednesday are crummy days to plan meetings (even ignoring the factor that a lot of staff are taking extra time off). -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://linuxfinances.info/info/sgml.html Rules of the Evil Overlord #151. I will not set myself up as a god. That perilous position is reserved for my trusted lieutenant. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Password Policy
In the last exciting episode, [EMAIL PROTECTED] (Ezequias Rodrigues da Rocha) wrote: Hi list (my first post), Is there any password polity that postgresql implement ? No, that would be a serious mistake, as it would prevent people from having local policies that differed from the PostgreSQL policy. It is possible to put a set all no administrators passwords to = '123456' from times and times ? I'm not quite certain what you mean by that... Do you mean you want to be able to set all non-administrators' passwords to some particular value? You could do that by setting one user's password to a particular plaintext form, and then alter the others to match it. Thus: alter user stalking_horse password to '123456'; update pg_shadow set passwd = (select passwd from pg_shadow where usename = 'stalking_horse') and not usesuper; I'm not sure that's an entirely wonderful policy, though. Has anyone implement a dinamic password autentication (the password changes according the date/month etc of a day ) ? Well, you could use ident-based authentication, and then use some wacky method to authenticate the user at the Unix level via PAM... Changing passwords that often strikes me as being the wrong kind of approach to this. Having a flurry of passwords that users can't possibly remember is a clear route to having passwords sitting on Post-It notes on cubicles. I'd be much more inclined to use an unrememberable random password, stowed in ~/.pgpass, which doesn't need to expire terribly often. The other option would be to use ssh style keys for authentication; that isn't possible now, but would be an attractive feature. Is there any function to encript, decript plain text in PostgreSQL and if it is aplicable ? There is a contrib module, pgcrypto, which is quite suitable for such purposes... -- output = (cbbrowne @ gmail.com) http://linuxfinances.info/info/emacs.html The purpose of an undergraduate education at MIT is to give you a case of post-traumatic stress syndrome that won't wear off for forty years. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] Backup Strategies?
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Joshua Kramer) belched out: Hello All, What strategies are people using for automated, script-based backup of databases? There are a few I can think of: 1. Create a db_backup unix user and a db_backup pgsql user. Grant READ access to all objects on all databases for the db_backup pgsql user. Create a .pgpass file in the home directory of the db_backup unix user. Backup as needed with a script run as the db_backup unix user. 2. Create a db_backup unix user and repeat above, except using the postgres db user. My department took the approach of having a set of admin-specific users, much in the spirit of 1. For backups, vacuuming, and replication, the respective clever names were dumpy, molly, and slony. (When auditors asked about the new users, there was much snickering...) We didn't create a special Unix account for it; that seemed unnecessary. -- (format nil [EMAIL PROTECTED] cbbrowne linuxfinances.info) http://cbbrowne.com/info/rdbms.html Who wants to remember that escape-x-alt-control-left shift-b puts you into super-edit-debug-compile mode? (Discussion in comp.os.linux.misc on the intuitiveness of commands, especially Emacs.) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] pgpool
After a long battle with technology, [EMAIL PROTECTED] (adey), an earthling, wrote: Please could someone give me an idea of what pgpool is, and where I can research it? I have run a search on postgresql.org and found many references, but they don't explain what it is, and it doesn't appear in the documentation index of the v8.1 manuals. It's not officially part of the database, so no, it's not in the database documentation. http://pgpool.projects.postgresql.org/ It's a connection pool server implemented in C... The notion is that you run your database on (say) port 5432, and then put pgpool in between, on port 4432. pgpool opens up some fixed number of connections to the database, keeping them open, and your application connects to port 4432, as if it were a direct connection to the database. pgpool then manages the connections, basically keeping them open irrespective of how your application handles connections. This can be a big help to performance if your application is pretty loose about opening and closing connections whenever it likes. -- (reverse (concatenate 'string moc.liamg @ enworbbc)) http://linuxdatabases.info/info/lsf.html The real problem with the the year 2000 is that there are too many zero bits and that adversely affects the global bit density. -- Boyd Roberts [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] problem with installing postgresql
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Praveen Kumar N) transmitted: hai... i have some problem with installing postgresql. Following is the output of installation: -- [EMAIL PROTECTED] pgsql]$ initdb -D /usr/local/pgsql/Data/ The files belonging to this database system will be owned by user praveen. This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. fixing permissions on existing directory /usr/local/pgsql/Data ... ok creating directory /usr/local/pgsql/Data/global ... ok creating directory /usr/local/pgsql/Data/pg_xlog ... ok creating directory /usr/local/pgsql/Data/pg_xlog/archive_status ... ok creating directory /usr/local/pgsql/Data/pg_clog ... ok creating directory /usr/local/pgsql/Data/pg_subtrans ... ok creating directory /usr/local/pgsql/Data/pg_twophase ... ok creating directory /usr/local/pgsql/Data/pg_multixact/members ... ok creating directory /usr/local/pgsql/Data/pg_multixact/offsets ... ok creating directory /usr/local/pgsql/Data/base ... ok creating directory /usr/local/pgsql/Data/base/1 ... ok creating directory /usr/local/pgsql/Data/pg_tblspc ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 3500/175000 creating configuration files ... ok creating template1 database in /usr/local/pgsql/Data/base/1 ... child process was terminated by signal 11 initdb: removing contents of data directory /usr/local/pgsql/Data - I have installed it with user name praveen.Earlier i installed succesfully.but this time I am getting the above output and didnt get any solution for this problem on net.so could you please tell me the solution for it. thanks in anticipation. regards, The last time I encountered that problem, it related to readline not having been compiled properly for AIX. You're not running on AIX, are you? -- let name=cbbrowne and tld=gmail.com in name ^ @ ^ tld;; http://linuxdatabases.info/info/lisp.html If there are aliens, they use Lisp. --Edward Lasker, mutatis mutandi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] How are ppl monitoring PostgreSQL ... ? What is being
[EMAIL PROTECTED] (Michael Fuhr) wrote: On Sat, Jun 10, 2006 at 07:29:52PM +0100, Andy Shellam wrote: I'm using a great little Linux program called monit to check that there's something listening on the 5432 port. It also monitors individual process memory and CPU usage etc. Quite good. A server can be quite broken yet still respond to transport- and network-layer probes like TCP connections and pings. Some NMSs support custom monitors, so whenever possible I like to do application-layer tests to ensure that the server not only accepts connections but is indeed behaving as expected. I'd generally agree with this; it points to having tests that aren't so much about PostgreSQL as they are about the applications using PostgreSQL... Marc G. Fournier wrote: The subject kinda says it all ... I know there are SNMP patches available out there now, but without those ... ? MRTG can generate graphs of anything you can write a script to measure, as long as the script returns output in a certain format. Other packages of that ilk probably have similar capabilities. Our NOC group runs replication tests against various servers that feed MRTG; they point at a view that is normally frequently updated, and check to see how elderly the latest value is. On replicated nodes, this checks the health of replication. On master nodes, this checks the health of the application itself. -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxdatabases.info/info/slony.html Rules of the Evil Overlord #66. My security keypad will actually be a fingerprint scanner. Anyone who watches someone press a sequence of buttons or dusts the pad for fingerprints then subsequently tries to enter by repeating that sequence will trigger the alarm system. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] postgres ECCN
[EMAIL PROTECTED] (Rosario Colica \(XX/MCI\)) wrote: All, I am wondering if you can help. I need to find the ECCN coding for the DB in order to be able to export of our products with the dB included. People have asked this about Mozilla, with the following answer: http://www.hecker.org/mozilla/eccn See also: http://www.bis.doc.gov/encryption/PubAvailEncSourceCodeNofify.html http://www.bis.doc.gov/Encryption/EncFAQs6_17_02.html It would appear that PostgreSQL would be likely to fit into either ECCN 5A992 or 5D992, as a mass market encryption product. Of course, IANAL, so your milage may vary. -- (format nil [EMAIL PROTECTED] cbbrowne gmail.com) http://linuxfinances.info/info/ I visited a company that was doing programming in BASIC in Panama City and I asked them if they resented that the BASIC keywords were in English. The answer was: ``Do you resent that the keywords for control of actions in music are in Italian?'' -- Kent M Pitman ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Show tables query
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Andy Shellam) belched out: Is there an SQL command supported by Postgres to return a list of tables in a database? Yes, it's called SELECT. There is a standard schema called INFORMATION_SCHEMA, which contains a variety of relevant views. Notably, you could request: SELECT * FROM INFORMATION_SCHEMA.TABLES; That has the merit of actually conforming to SQL standards... -- output = reverse(moc.liamg @ enworbbc) http://cbbrowne.com/info/ They have finally found the most ultimately useless thing on the web... Found at the Victoria's Secret website: The online shop: Text Only Listing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] postgresql-R
Clinging to sanity, [EMAIL PROTECTED] (Louis Gonzales) mumbled into her beard:
Hey Jim, Thanks again for the pointer to this. I've already
compiled and installed on one of the two Solaris nodes, that I
needed to. Yeah upon further reading, I can't wait for Slony-II to
come out - is there truth behind the anyday rumor? - is it also
true that it's going to implement the true multi-master scenario,
where updates can be made at any of the clustered nodes.
I'm not holding my breath, at this point.
Performance results have indicated the Slony-II approach wouldn't work
out well for systems where there is heavy competition for locks on
some objects. The trouble is that you wind up having to distribute
locks, and if the application has common heavily-updated objects, the
cost winds up prohibitive...
I'm going to deploy the slon worker process locally on every
participating node, rather than letting the master host the slon
processes for every cluster participant, for performance reasons.
I run quite a lot of Slony-I nodes, and let me be pointed...
Performance is NOT a good reason (or a reason at all) to spread slon
processes across a bunch of hosts.
Performance of replication is based on the I/O taking place in the
databases; the costs of distributing some of the slon network traffic
will be fairly immaterial.
The savings from hosting slons on specific DB nodes is an illusion.
The data will all have to cross the network to get from sources to
providers, so whether the slon is on one host or another, you'll have
the same traffic transmitted around.
Having slons centralized makes it way simpler to manage them; I can't
see there being anywhere near enough benefit from any savings of
network traffic to actually represent a material performance
improvement.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','cbbrowne.com').
http://linuxdatabases.info/info/slony.html
Never criticize anybody until you have walked a mile in their shoes,
because by that time you will be a mile away and have their shoes.
-- email sig, Brian Servis
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [ADMIN] Best filesystem
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Arnau) wrote: Which is the best filesystem in linux for postgresql? nowadays I'm using ext3, I don't know if other filesystems like XFS, reiser... would be better from the performance point of view. Do you care more about performance? Or reliability? I think I'd rather see things 3% slower than they could perhaps be if that diminishes the risk of suddenly losing a filesystem... The last time I did a benchmark, I found JFS *slightly* faster than XFS, and XFS slightly faster than ext3. But then lost several JFS filesystems to weird interactions between JFS and disk controller. That was about 2 years ago; there have been new releases of both filesystems, SCSI device drivers, as well as major kernel revisions. It's not evident how much things have really changed. At any rate, I would NOT put filesystem performance at the top of the list; ability of vendors to provide support is something I would put much higher. That factor alone usually rules out both JFS and XFS... -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxfinances.info/info/lsf.html Rules of the Evil Overlord #43. I will maintain a healthy amount of skepticism when I capture the beautiful rebel and she claims she is attracted to my power and good looks and will gladly betray her companions if I just let her in on my plans. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Change port number
In the last exciting episode, [EMAIL PROTECTED] (Rodrigo Sakai) wrote: I have a question about changing the port number of Databases server. It was told to me that is a good administrative practice to change the port number of the services, like change the 5432 to 6985 or any other number. This is for security reasons. Is really a good practice? Well, it would presumably protect against certain kinds of script kiddie attacks which assume that PostgreSQL can only ever run on port 5432. It won't protect terribly much against a concerted attack. We use variant port numbers a lot because we have multiple database instances in our environments. The measure isn't protective so much as it is necessary, since at most one instance can use port 5432... -- let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];; http://linuxdatabases.info/info/internet.html I heard that if you play the Windows CD backward, you get a satanic message. But that's nothing compared to when you play it forward: It installs Windows -- G. R. Gaudreau ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] autovacuum
i have PostgreSQL 8.1.2 i want to enable autovacuum at my PostgreSQL 8.1.2 from postgresql.conf i've got 50-60 insert and/or update queries in a second in that case tables shouldn't be locked does autovacuum locks tables while vacuuming? Of course it does; any request to access a relation will issue one or more locks on the relation. VACUUM issues an AccessShareLock request against each relation that is vacuumed, which is probably nearly the same lock request your applications will be requesting, save for the fact that they'll also be submitting some RowExclusiveLock requests for individual rows of relations. http://www.postgresql.org/docs/8.1/interactive/mvcc.html -- let name=cbbrowne and tld=gmail.com in name ^ @ ^ tld;; http://linuxdatabases.info/info/nonrdbms.html Bother, said Pooh, as he deleted his root directory. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] autovacuum
This seems maybe a bit overkill to me. I think what would be more useful is if autovacuum could execute more than one vacuum at a time, and you could specify tables that are high priority (or possibly just say that all tables with less than X live tuples in them are high priority). That way a longer-running vacuum on a large table wouldn't prevent more vacuum-sensative tables (such as queues) from being vacuumed frequently enough. Actually, I can think of a case for much the opposite, namely to want to concurrently vacuum some LARGE tables... Suppose you have 2 rather big tables that get updates on similar schedules such that both will have a lot of dead tuples at similar times. And suppose both of these tables are Way Large, so that they take six hours to vacuum. I could argue for kicking off vacuums on both, at the same moment; they'll both be occupying transactions for 1/4 of a day, and, with possibly related patterns of updates, doing them one after the other *wouldn't* forcibly get you more tuples cleaned than doing them concurrently. I'm not sure that's a case to push for, either, as something pg_autovacuum is smart enough to handle; I'm just putting out some ideas that got enough internal discussion to suggest they were interesting enough to let others consider... -- cbbrowne,@,gmail.com http://cbbrowne.com/info/linuxdistributions.html Transported to a surreal landscape, a young girl kills the first woman she meets and then teams up with three complete strangers to kill again. -- Unknown, Marin County newspaper's TV listing for _The Wizard of Oz_ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [ADMIN] Unable to connect to a specific database
I had to kill a vacuum in the middle with -9. I shut down and
restarted the postgres server several times after that but I am unable
to connect to the db that I was initially running vacuum on
I'm doing psql dbname and it hangs for a while. I'm still
waiting. Any ideas?
Kill -9 is distinctly not recommended.
It is possible that you have corrupted the database by issuing kill
-9.
You might want to look into what backups you have...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/nonrdbms.html
Very funny, Scotty. Now beam down my clothes.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Moving a database
What are the correct steps to move an database and from an server running postgreslq 7.4.2 to another running 8.0.3? I'll assume there are two hosts: - db7, running 7.4.2, on port 5432, and - db8, running 8.0.3 on port 5432. The simplest method would be thus: - Stop the applications accessing db7 - On db8, run the command: pg_dumpall -h db7 -p 5432 | psql -h localhost -p 5432 template1 That is likely to provide the most nearly perfect fidelity copy of the database on db8. You may discover that this takes longer than you want it to. If that proves to be the case, you can shorten the cutover time by using a replication system such as Slony-I to copy data from the one host to the other. That definitely involves more moving parts and more complexity. But it could turn an 8 hour outage into a 2 minute one... You should certainly do test runs of whatever approach you try so as to minimize the number of surprises. -- output = reverse(moc.liamg @ enworbbc) http://linuxdatabases.info/info/slony.html ...Yet terrible as Unix addiction is, there are worse fates. If Unix is the heroin of operating systems, then VMS is barbiturate addiction, the Mac is MDMA, and MS-DOS is sniffing glue. (Windows is filling your sinuses with lucite and letting it set.) You owe the Oracle a twelve-step program. --The Usenet Oracle ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] how to protect root access database
i have database with critical data (such patient information) how can i protect my database from root access because this host in company can access with root from many person (person who manage some service application on host but must not access this patient information) The only way to be certain that system administrators cannot access patient data is to encrypt the data before storing it in the database. The book that most directly describes this approach to application design is Peter Wayner's _Translucent Databases_; see also his web site... http://www.wayner.org/books/td/ -- (reverse (concatenate 'string moc.liamg @ enworbbc)) http://cbbrowne.com/info/ If we were meant to fly, we wouldn't keep losing our luggage. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] some databases have not been vacuumed ...
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Joshua D. Drake) transmitted: FM wrote: Hello after a vacuum full analyse I received this : WARNING: some databases have not been vacuumed in 1805294030 transactions HINT: Better vacuum them within 342189617 transactions, or you may have a wraparound failure. I read that it's because i do not vacuum DB very often. Now I vacuum every night but the warning remains. How can I get rid of this ? Run a vacuum full on all the database. That seems overkill. The problem is likely that the old tuples are in some database not being vacuumed. template1 is a likely candidate; do a plain VACUUM on that and the message may go away. It can be *possible* for the issue to be with template0, if it had at some point been open for modification. If that is the case, it is a good idea to: - Unlock template0 (via table pg_database) - Run VACUUM FREEZE on template0 - Relock it -- (format nil [EMAIL PROTECTED] cbbrowne gmail.com) http://linuxdatabases.info/info/slony.html You think you know when you can learn, are more sure when you can write, even more when you can teach, but certain when you can program. -- Alan Perlis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] real time replication of pgsql database
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (hellz waren) would
write:
we are designing a heavy duty database in pgsql that
expects to grow at an average of 100 MB spread over
tables of 250 tables.
We require always on database (24 X 7) database. And
if one fails, the other one needs to start up.
Could you someone tell me if there's any open source
project that enables real time replication of data
back up.
I can use heartbeat, mon etc then to awake the other
server if the main server is down.
You might want to look into Slony-I; it can do this sort of thing.
Version 1.1.0 was released on Thursday...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://slony.info
Who's afraid of ARPA?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] DB replicators comparison; (vs. DB upgrade via pg_dump)
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] wrote: I am about to upgrade from 7.3.4 to 8.0.3, and I read that using a DB replication tool is a good way to go about it. Replication is useful for this purpose if it helps you cut down on the time the transition takes. If the database is relatively small, and it only takes a few minutes to load the data in, then it's far simpler to do a dump/reload. But if you discover that the dump/reload takes 4 hours, and you're only permitted 20 minutes of downtime, the complexities of replication may get forced on you... -- output = (cbbrowne @ ntlug.org) http://linuxdatabases.info/info/slony.html Sheridan:So how did you find out all of this? Bester:I'm a telepath. Work it out. * ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Incremental backup
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Michael Kichanov) would write: I wish to realize incremental backup, i.e. to dump only those tables which have changed from last backup. 1q. How i can solve this task with internal postgres tools? 2q. How I can to find out timestamp of last change of the table? Does anyone resolve this things? Ps: postgres 7.3 PostgreSQL version 8 offers a Point In Time Recovery mechanism whereby you archive the WAL files; the WAL files represent incremental backups. Slony-I 1.1 provides a new log shipping feature where you can archive incremental changes, too; it tries to support versions as far back as 7.3.3, so it might be compatible with your needs... -- let name=cbbrowne and tld=gmail.com in name ^ @ ^ tld;; http://linuxdatabases.info/info/slony.html Programming is one of the most difficult branches of applied mathematics; the poorer mathematicians hadbetter remain pure mathematicians. -- Edsger W. Dijkstra ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] REMOVE
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Alvaro Herrera) wrote: On Tue, May 03, 2005 at 06:17:31PM +0200, Stephen Kennedy wrote: An ex-workmate had forwarded his mail to our group so we could deal with work-related stuff, and so we received all of his mailing list stuff as well. This option had not occurred to me. That is so annoying :-( Specially when the receiving account is no longer available -- the list owner starts getting bounces from an account he can't unsubscribe, and it's hard to track down who of the whole list is forwarding to the bogus account. I wouldn't be shocked to see this happen with my @acm.org address at some point. I wind up forwarding any messages I want to send via the NNTP gateway, as it appears that mail that I send with the @acm.org address as email just plain disappears somewhere between my machine at home and the mail server at postgresql.org. Evidently the mismatch between that and the domain it then comes out of (sympatico.ca) causes someone to drop it on the floor. I have correspondingly little confidence that an attempt to send email to shut off messages would actually work. So while I can chuckle at the idiots, it may not require that for unsubscribe attempts not to work... -- let name=cbbrowne and tld=gmail.com in name ^ @ ^ tld;; http://linuxdatabases.info/info/slony.html Rules of the Evil Overlord #186. I will not devise any scheme in which Part A consists of tricking the hero into unwittingly helping me and Part B consists of laughing at him then leaving him to his own devices. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] PL/SQL
Quoth [EMAIL PROTECTED] (dedy): Hiii alll, I would like to ask, does pl/sql in oracle is same with the pl/sql in postgreSQL They are similar, though are certainly not identical. -- cbbrowne,@,gmail.com http://linuxdatabases.info/info/languages.html FLORIDA: We've been Gored by the bull of politics and we're Bushed. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Killing Processes
In the last exciting episode, [EMAIL PROTECTED] (Michael Fuhr) wrote: On Sat, Apr 23, 2005 at 10:58:46AM -0500, Thomas F.O'Connell wrote: It is generally not recommended that you kill processes with anything stronger than HUP, which is (I believe) what kill sends by default. kill usually sends TERM by default, not HUP. (I say usually only because I haven't personally examined every implementation of kill in existence. Every one I've used sends TERM by default and I haven't heard of any that work otherwise.) The only normative standard that would seem meaningful would be POSIX. A quick google doesn't find anything relevant there. The most relevant thing is pointers to the GNU utilities, which is where the kill used on Linux comes from. The man page for that indicates TERM as the default, and later has pointers to POSIX.1 as its 'source of authority.' I'd hazard the guess that perhaps that default was declared in POSIX, which is at least a form of standard. It may not be normative for BSD, but it is still instructive... Have you ever had a bad day when you issued kill -3 rather than kill 2? I have also had the day when part of me wanted kill it now; kill -3, another part knew No, you should just use kill -2, and wait..., and my treacherous fingers wound up taking the wrong side in the conflict ;-). -- cbbrowne,@,ntlug.org http://cbbrowne.com/info/internet.html Rules of the Evil Overlord #144. I will order my guards to stand in a line when they shoot at the hero so he cannot duck and have them accidentally shoot each other. Also, I will order some to aim above, below, and to the sides so he cannot jump out of the way. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] High Available Software Technologies Support About Postgres
Oops! [EMAIL PROTECTED] was seen spray-painting on a wall: I want to kown if the Postgres can support High Available(HA) in verson 8. We're running some PostgreSQL 7.4 instances on the IBM HACMP system for AIX. That being readily supportible, I can't readily conceive of a reason why PostgreSQL version 8 would not also work perfectly well. -- output = reverse(gro.gultn @ enworbbc) http://linuxdatabases.info/info/lsf.html Rules of the Evil Overlord #77. If I have a fit of temporary insanity and decide to give the hero the chance to reject a job as my trusted lieutentant, I will retain enough sanity to wait until my current trusted lieutenant is out of earshot before making the offer. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Disk Encryption - Postgresql vs. Oracle
Martha Stewart called it a Good Thing when pgman@candle.pha.pa.us (Bruce Momjian) wrote: Wow, nice analysis. Should this be in our documentation somewhere? Suggest a suitable section and I'd be more than happy to send in a patch adding this in. The only place I see pgcrypto referred to (which ought to be a pointer to right place) is in the release notes. Or perhaps this is an item for FAQ.html? -- let name=cbbrowne and tld=gmail.com in name ^ @ ^ tld;; http://linuxfinances.info/info/slony.html REALITY is an illusion that stays put. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Disk Encryption - Postgresql vs. Oracle
In the last exciting episode, [EMAIL PROTECTED] (Joseph Brenner) wrote: I was talking to someone just recently who was saying that they were thinking about going with Oracle rather than Postgresql because Oracle has a their story in place about how to do disk encryption. So I am of course, looking into how to do it with Postgresql... (As to why you would *care* about disk encryption, I would guess the scenario is you've got a bunch of guys in the back room hot-swapping RAID drives, and you'd rather not post armed guards there to watch what happens to the older units.) contrib/pgcrypto looks pretty interesting, but I gather it's intended to let you encrypt particular fields inside a database, rather than the whole ball of wax. Maybe the right way to do it is to just get the OS to encrypt everything, and not make postgresql jump through any extra hoops? I see there's a general Linux disk encryption FAQ out there: http://www.telenovela-world.com/~spade/linux/howto/Disk-Encryption-HOWTO/index.html Doing some searches of the archives, I haven't turned up much discussion more recent than about a year ago, e.g. http://archives.postgresql.org/pgsql-admin/2004-03/msg00049.php Is there anything new on this front? If your threat model indicates that encrypting data at the disk level represents protection against some attack involving theft of disk drives, you would presumably find that using some form of OS loopback device with a crypto layer to be useful, and that would not require any particular support from PostgreSQL. Note that this model cannot protect against threats from system administrators as, in order for them to mount the filesystems, they must have access to the crypto keys. Furthermore, it cannot protect _at all_ against attacks that can take place while the database is up and running. A second approach, using pgcrypto, requires that you entrust the database process, and hence anyone with access to the relevant Unix user, with the cryptographic keys. That can allow some portions of the data to be encrypted, and others to remain plain text, and may again be suitable if you trust the system administrators with the keys. It has the merit that the sensitive data stays encrypted on disk at all times; it is only in plain text form in memory and possibly as it is being transmitted between server and client (protect against that using SSL connections). A third approach is for the cryptographic layer to stay purely on the application/client side. Encrypted data is encrypted on the client side, and is only ever decrypted there. If you have any reason to be concerned about threats that target the server, then you must not trust either of the first two approaches, but must look to client-side processing. Google for _Translucent Databases_ for more on this approach... -- output = (cbbrowne @ gmail.com) http://linuxdatabases.info/info/slony.html They are called computers simply because computation is the only significant job that has so far been given to them. -- Louis Ridenour ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] is postgresql 8 is realy mature
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Tsirkin Evgeny) wrote: Hi list! We are using currently postgresql 7.3.4 havily and planning an upgrade. our choices are either 7.4.x which comes with distribution (suse) or 8.0.1 . We can allow our selfs some testing time and minor changes in applications if needed but of course this is good to avoid . So the question is if the 8.0.1 is really well tested ,or maybe we should move to 7.4 or just leave 7.3 where it is? We wil be thankfull for any sharing of experience . Thanks. I would certainly seriously consider a move to 7.4.x; that should be a useful improvement in terms of both functionality as well as bug fixes. Version 8.0.1 is a bit less widely used, just yet. Two things about it cause me a bit of pause: 1. Introduction of native Win32 support has touched quite a lot of code, with some corresponding potential for diminishing the ability to consider past testing to remain legitimate; 2. The TWO changes in cacheing are pretty major, namely the implementation of ARC, with its forthcoming replacement with 2Q. Tom Lane has observed that there have not been many new defects found, and that of those found, many actually do apply to earlier versions. Which is eminently encouraging. For systems not expected to go into production for a while yet, I'd personally be fairly game to use 8.0.2, once released. For things to go to production immediately, I would be more than happy to use a 7.4.6 or 7.4.7; you may fairly reasonably expect to see material improvements with a 7.3-7.4 transition. -- (format nil [EMAIL PROTECTED] cbbrowne gmail.com) http://linuxdatabases.info/info/slony.html Rules of the Evil Overlord #173. Although it would provide amusement, I will not confess to the hero's rival that I was the one who committed the heinous act for which he blames the hero. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Replication with PostgreSQL
After a long battle with technology, [EMAIL PROTECTED] (prakash sompura), an
earthling, wrote:
Can any one tell me how do I replicate my PostgreSql database from
one server to another server?
One method involves using pg_dump to dump the state out, and load it
onto the other server. Certainly this is the simplest way to do a one
point in time replication.
Another involves using the PG 8.0 PITR (Point In Time Recovery)
facility.
If you want the replica to be relatively up to date and usable more or
less continuously as a queryable replica, then you should look into
one or another of the replication systems. Slony-I is the hot name
at this point...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/slony.html
If a word in the dictionary were misspelled, how would we know?
-- Steven Wright
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Best practice - Vacuum. Replication suggestions and pg vs mysql question
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (David B) wrote:
Environment. PG v8. (Opteron 2CPU. Raid 5 disks 1TB. 12GB Ram)
Environment would be one master feeding 3 slaves. Similar configs.
New transactions coming into master. Cust Service Reps using that box.
Analysis being done on slave boxes.
Hi Folks,
I'm looking at PG vs MySql for a high volume site.
Few 10's of millions inserts per day.
Also some deletes (1% - say 250,000 per day).
Also updates (perhaps 10% say 2.5m per day)
Lots of indexes on master table.
When I test vacuum it seems very slow.
I understand I need to do vacuums on a regular basis.
Two reasons right...1. space and 2. better performance.
#1
I'm thinking with that much activity I should do it nightly and not
wait for Saturday nights since the #rows affected will be very large
by that time. Any thoughts?
We have cron jobs that run every few minutes to vacuum certain tables
that are highly updated.
I'd certainly suggest vacuuming more than once a week, particularly in
view that this doesn't lock usual operations.
#2
How does vacuum work in a replication environment.
If I implement replication so that users can get to 'copy' for their
analysis I presume the same overhead will apply to replicated box?
Does the same lock on table on slave box apply?
Yes, slave servers will need similar vacuum regimens.
Will delay hit other tables not being vacuumed...I'd suspect it would.
E.g. I vacuum main table and it takes 15minutes. Are inserts to other
tables delayed by the 15minutes the vacuum takes on slave?
That's only the case if you're abusively using VACUUM FULL all the
time, which is unnecessary.
You normally only need to use VACUUM, which doesn't block normal
database operations.
#3
15minute lock is a long time.
I don't want to tell the CSR's they will have to put customers on hold
each night for 15mins if customer updates are the basis of their call.
Any typical workarounds for such a problem?
Um, we run systems 24x7, and VACUUMs _don't_ lead to any such
problems.
If you are having problems of everything hanging up for 15 minutes,
you're presumably doing a VACUUM FULL which is the WRONG thing to do.
#4
Does Mysql have a similar issue. Does it use something like 'vacuum'.
And if so does it suffer the same delays. Any thoughts?
MySQL(tm) doesn't have similar functionality, so it doesn't have
similar issues. It tends to suffer much worse if you hit it with a
lot of concurrent load, but that's a long story...
#5
Speaking of replication...I see lots of technologies I can use.
Slony appears very active group. Which can be a good thing or mean its
still a work in progress!!
Any pro's con's or suggestions on the most popular.
Slony-I is certainly seeing active efforts; we're preparing to get a
1.1 release ready, which will be, in some ways, a fair bit better than
1.0.5, which people were already pretty happy with.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/linuxdistributions.html
Rules of the Evil Overlord #142. If I have children and subsequently
grandchildren, I will keep my three-year-old granddaughter near me at
all times. When the hero enters to kill me, I will ask him to first
explain to her why it is necessary to kill her beloved grandpa. When
the hero launches into an explanation of morality way over her head,
that will be her cue to pull the lever and send him into the pit of
crocodiles. After all, small children like crocodiles almost as much
as Evil Overlords and it's important to spend quality time with your
grandkids. http://www.eviloverlord.com/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Excessive growth of pg_attribute and other system tables
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Steve Crawford) belched out: On Thursday 17 March 2005 3:51 pm, Tom Lane wrote: Steve Crawford [EMAIL PROTECTED] writes: My autovacuum config is running and I do see regular periodic vacuums of these pg_ tables but still they grow. Do you have the FSM settings set large enough to account for all the free space? max_fsm_pages = 2 max_fsm_relations = 1000 2 is definitely way too low. It's not enough to track the dead pages in pg_attribute alone, which looks to have the better part of 80K dead pages. I'd increase that to about 20, straight off. It seems curious that you have so many tuples getting killed off in this table; are you generating a lot of temp tables continually? By the way, you should be vacuuming pg_attribute _way_ more often, as it shouldn't have gotten as big if you did so... Doing a reindex and doing (once!) a VACUUM FULL should help bring the size down; vacuuming the table more often should keep size down... -- output = reverse(moc.liamg @ enworbbc) http://cbbrowne.com/info/postgresql.html To do is to be. -- Aristotle To be is to do. -- Socrates Do be do be do. -- Sinatra Do be a do bee. -- Miss Sally of Romper Room fame. Yabba dabba do. -- Fred Flintstone DO...BEGIN..END -- Niklaus Wirth ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Scheduling/Automated Jobs in Postgre
You could add this into whatever script starts up pg_ctl, but only if you decide to use PostgreSQL rather than Postgre. Could any one tell me how can I implement this in Postgre. Is there any concept like Scheduled Jobs in Postgre. If so pls. provide me with related informations or the links from where I could get them. There surely is. Use cron(8). But that's only true with PostgreSQL, not with Postgre. This mailing list addresses PostgreSQL, not Postgre. -- let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];; http://linuxdatabases.info/info/slony.html Some people are only alive because it is illegal to kill. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Scheduling/Automated Jobs in Postgre
[EMAIL PROTECTED] (Aravindhan G.D) wrote: I need to do some user defined jobs when ever the Postgre server gets restarted. These tasks should be automated one i.e) When ever I restart the server the PL/SQL functions I have defined should be executed automatically without my intervention. This is to record some information like Audit Trails and some more. You could add this into whatever script starts up pg_ctl, but only if you decide to use PostgreSQL rather than Postgre. Could any one tell me how can I implement this in Postgre. Is there any concept like Scheduled Jobs in Postgre. If so pls. provide me with related informations or the links from where I could get them. There surely is. Use cron(8). But that's only true with PostgreSQL, not with Postgre. This mailing list addresses PostgreSQL, not Postgre. -- let name=cbbrowne and tld=gmail.com in String.concat @ [name;tld];; http://linuxdatabases.info/info/slony.html Some people are only alive because it is illegal to kill. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Postgresql on Solaris versus Linux
Solaris is highly attuned to running threaded applications, and PostgreSQL is not that sort of application. So you're definitely not playing to Solaris' strengths. Solaris is one of the platforms that has been noted for suffering from context switch slowdowns when hit with a lot of concurrent traffic, similar to Intel multiprocessor Xeons. I'd not go so far as to call it a _bad_ idea. But if I was paying the premium prices for that class of hardware, I think I'd rather have a PowerPC system... -- (reverse (concatenate 'string gro.gultn @ enworbbc)) http://linuxfinances.info/info/slony.html Optimization hinders evolution. -- Alan Perlis ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] empty a database
Clinging to sanity, [EMAIL PROTECTED] (Dick Davies) mumbled into her beard: Is there a neat way to clean out a database via SQL commands? i.e. get rid of tables, sequences, integers, etc. At present I'm using dropdb/createdb, but thats' far from ideal and I think it's causing postgres to do more mork than it needs to... If you truly need for all of the objects to go away, dropping the database seems like a reasonable way to do this. I'm not sure what work you are imagining is too much or unnecessary. If you're regularly recreating a not-totally-empty database, containing some set of fresh tables/sequences/views/such, then I'd think you're doing the right thing, but need to take a further step... If you're recreating a database that has some non-zero initial configuration, then what you might do is to set up a 'template' database, let's call it mytemplate that contains that configuration. Then you can do the following: $ createdb --template=mytemplate mydatabase CREATE DATABASE $ do_some_work_with mydatabase $ dropdb mydatabase DROP DATABASE $ createdb --template=mytemplate mydatabase CREATE DATABASE $ do_some_work_with mydatabase $ dropdb mydatabase DROP DATABASE $ createdb --template=mytemplate mydatabase CREATE DATABASE $ do_some_work_with mydatabase $ dropdb mydatabase DROP DATABASE $ createdb --template=mytemplate mydatabase CREATE DATABASE $ do_some_work_with mydatabase $ dropdb mydatabase DROP DATABASE $ createdb --template=mytemplate mydatabase CREATE DATABASE $ do_some_work_with mydatabase $ dropdb mydatabase DROP DATABASE -- If this was helpful, http://svcs.affero.net/rm.php?r=cbbrowne rate me http://linuxdatabases.info/~cbbrowne/slony.html Wintel: A Wasteland of Useless Software - If the bazillions of programs out there actually amount to something, why is everyone using MICROS~1 Office, Word, Excel, PowerPoint, Access, ... -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] replication
The world rejoiced as [EMAIL PROTECTED] (Fred Blaise) wrote: Hello I am running postgres 7.4.6 on 2 BSDi boxes. One is live, the other one is a failover. I would like to implement a master-slave replication process. I believe replication has been included in the base package since 7.3.x. Do you have any good pointers/how-to to direct me to? For such cases, I believe that Slony-I is the preferred solution these days. The comprehensive documentation may be found in CVS at gborg; it may be found online at the URL below. -- output = reverse(moc.liamg @ enworbbc) http://linuxdatabases.info/~cbbrowne/slony.html Show me... show me... show me... COMPUTERS! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Database Scalability
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (M.V.
Jaga Mohan) transmitted:
I am using PgSQL 7.3 for my Application. My problem how can I scale
my database ? I mean how many records I can have in my database.
That depends mainly on how much disk space you have. If you haven't
got much disk, you won't be able to store many records. If you have
8TB of free space, you should be able to store rather a lot, depending
on how large they might be.
and what will be the data retreival speed when multiple clients
access the database. Is there any software or mechanism to find out
this ?
Retrieval speed will depend on various things, including:
- How fast your disk subsystem is;
- How much data you can cache in memory;
- How effectively you have tuned PostgreSQL's use of caches
- How useful the set of indices are on your tables
- How efficient the queries you have written for data retrieval
are at utilizing all of the above.
You would likely find that 7.4 releases would be quite a bit faster
and more efficient than the 7.3 series, due to various improvements to
the query planner, and possibly that the new version 8.0 is even
faster still. I find it curious that you want to use so old a version
of PostgreSQL.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://cbbrowne.com/info/linuxxian.html
Did you ever walk in a room and forget why you walked in? I think
that's how dogs spend their lives. -- Sue Murphy
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [ADMIN] Installing PostgreSQL as postgress versus root Debate!
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Goulet, Dick) wrote: You may well be on the development team, but you are wrong for one very important reason. If the Postgresql executables are owned by root they execute with the priviledges of root. Methinks you may not understand Unix permissions as well as you need to. Binaries only execute with the privileges of root if: a) They are being run by the root user, or b) They are owned by root and have the setuid bit set. -- cbbrowne,@,gmail.com http://cbbrowne.com/info/linux.html Rules of the Evil Overlord #75. I will instruct my Legions of Terror to attack the hero en masse, instead of standing around waiting while members break off and attack one or two at a time. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Installing PostgreSQL as postgress versus root Debate!
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED]
(Tomeh, Husam) transmitted:
I've seen book that prefer installing PostgreSQL as root and another one
recommends otherwise by first creating a postgres account and then
installing it as postgres. In the Oracle world, you don't use root to
install the software. What is the best practice as far as PostgreSQL
goes?
Best practices tend to vary.
If you are installing software using package management tools, then
it is mandatory that you install it as some form of administrative
user (e.g. - root) because typical package management tools require
root access.
(That's true whether we're talking about the RPM package manager that
Caldera paid RHAT to develop, dpkg used by Debian, BSD Ports, or
pkgadd used by sundry systems.)
I happen to do much of my work in managed hosting environments where
I may not even have root access. In those environments, the best
practice is the mandatory practice and is to get a postgres user
set up and to have that user install the software.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www.ntlug.org/~cbbrowne/emacs.html
Rules of the Evil Overlord #94. When arresting prisoners, my guards
will not allow them to stop and grab a useless trinket of purely
sentimental value. http://www.eviloverlord.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Install Slony-I on Postgresql 7.3.2
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Devi Munandar) transmitted: I want to Install Slony-I 1.0.5 on my machine i386, but my postgresql version is 7.3.2 running on OpenBSD3.4 Operating System, so does Slony-I 1.0.5 support to postgresql 7.3.2?, because i read in Installation documentation Slony-I.1.0.5 just support for OpenBSD3.5. Slony-I does not support PostgreSQL version 7.3.2; there is some of the namespace support that wasn't there yet until 7.3.3. You will need to upgrade from 7.3.2 to some newer 7.3.x version before attempting the Slony-I install. -- (format nil [EMAIL PROTECTED] cbbrowne gmail.com) http://linuxfinances.info/info/slony.html Sturgeon's Law: 90% of *EVERYTHING* is crud. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Need Help in Taking Backups
In the last exciting episode, [EMAIL PROTECTED] (Gourish Singbal) wrote: Whats the Best plan to take Backups for Postgres 7.4.5 database considering the database is 24/7 and transaction based. Well, you can take a pg_dump to get the state of the system at a point in time. That's very easy, although it's anyone's guess whether that's an acceptable answer or not. But you really need to specify your 'disaster modelling' and recovery requirements in _much_ greater detail. It may be that in order to keep recovery time down, you'll need to use a replication system such as Slony-I to give you a hot backup that's sitting just a few transactions behind. Or perhaps the answer will be to wait for sophistication for 8.0 and record transaction logs in order to use PITR. -- let name=cbbrowne and tld=gmail.com in name ^ @ ^ tld;; http://linuxfinances.info/info/postgresql.html We defeated the enemy with teamwork and the hammer of not bickering. -- The Shoveller, Mystery Men ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Very busy 24x7 databases and VACUUM
In the last exciting episode, [EMAIL PROTECTED] (David F. Skoll) wrote: Does anyone run a very busy PostgreSQL datatabase, with lots of read and write operations that run 24x7? (We're talking on the neighbourhood of 40 to 60 queries/second, with probably 5% to 10% of them being INSERT or UPDATE.) Yup... [Hand goes up...] Some of our clients run such a DB, and the nightly VACUUM slows things down tremendously while it is running. I see that in 8.0, you can reduce the VACUUM's I/O impact, but from reading the code, it also looks like that means the VACUUM will hold locks for longer, which is probably bad news. Yes, there's a trade-off there. The lazier vacuum will indeed hold its locks longer. Doing VACUUM more often than nightly is not a good idea; the tables tend to be pretty large and it looks like VACUUM has to scan all the tuples each time. (The nightly VACUUM is already taking several hours in some cases.) Yes, vacuum does need to scan all the tuples. There's no shortcut at this point. How do we handle this situation? Are there any plans for some kind of incremental vacuum that recovers a few pages here and there in the background? Is such a thing even possible? There has been some talk of a VACUUM CACHE idea, where the idea would be to walk through the shared buffer cache and vacuum just those pages. Recently updated pages ought to be in the cache, so we might expect this to be reasonably fruitful, as well as being rather quick. If that were to work out well, I would think it potentially fruitful to have a perhaps longer list of pages of interest whereby UPDATE/DELETE operations might throw pages that they touch into a queue for later re-examination. In a table that contains both active regions and large, seldom-updated inactive regions, it would be nice to have a way to focus on the active bits. If we defer some write operations until after the VACUUM has finished, will that speed up the VACUUM? There are some things we can save up until after VACUUM is finished. One thing that would be somewhat helpful would be to be sure that each of the tables that you are vacuuming is handled in a separate transaction. Supposing it's six tables that each take 1/2h to vacuum, if you do each in a separate transaction, some locks may get established over and over, but the lease will be dropped and renewed each half hour, which ought to be helpful. Any MVCC-relevant logic will just have 1/2h periods for which tuples are held onto instead of there being a big 3h lock put on them. That's not an explicit lock, but rather an inability to purge entries out... -- (format nil [EMAIL PROTECTED] cbbrowne gmail.com) http://www.ntlug.org/~cbbrowne/rdbms.html If two people love each other, there can be no happy end to it. -- Hemingway ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] pgsql database .1 .2 .3 file names
The world rejoiced as [EMAIL PROTECTED] (Bryan Biggers) wrote: Can anyone tell me what the data file names with the .1 .2 .3 etc. extensions are? Are these undo versions of my tables or something? I need to recover some disk space and I'm wondering if I need them. Here is an example... -rw---1 postgres postgres 1073741824 Oct 18 20:01 19523580 -rw---1 postgres postgres 1073741824 Sep 6 20:11 19523580.1 -rw---1 postgres postgres 1073741824 Sep 6 20:11 19523580.2 -rw---1 postgres postgres 1073741824 Sep 6 20:11 19523580.3 -rw---1 postgres postgres 1073741824 Sep 6 20:11 19523580.4 -rw---1 postgres postgres 1073741824 Oct 6 20:19 19523580.5 -rw---1 postgres postgres 629948416 Oct 19 12:33 19523580.6 You might just want to keep those files. They're the data for some table that evidently occupies about 6.5GB of space. It's _possible_ that the table needs vacuuming, in which case some of that space might be reclaimed. But you'd best do that using VACUUM. Deleting files would be expected to amount to destroying about a GB apiece of the data in the table... -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://www.ntlug.org/~cbbrowne/internet.html As of next Monday, TRIX will be flushed in favor of VISI-CALC. Please update your programs. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] High Availability for PostgreSQL
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Sharon Schooley) would write: We are looking for a 24/7 PostgreSQL solution. I've read some postings and information on various solutions including Taygeta, pgpool, and Mammoth PostgreSQL with Heartbeat. If there are any users of these or other PostgreSQL high availability solutions out there, can you respond and let me know what is working for you and how long you have been up and running? Our project is a high profile but simple application that must run 24/7. We are a county government entity. We are not currently PostgreSQL users. Our o/s is Suse 9.0 Pro Server. The issues likely have more to do with what kind of hardware you are running than the software. To build an HA system generally requires looking at _all_ the pieces, to make sure they fit together well, including: - The database itself - Middleware - Hardware, including such things as: - Redundant servers - Servers containing redundant hardware (e.g. - extra CPUs, memory boards) - Network appliances - Applications using the stuff in the lower layers Sometimes the middleware can hide hardware outages while backup hardware spins up; if the applications are designed to be either more or less forgiving of outages, that can either help or hurt. Running a demanding application 168h/week on a set of hardware infrastructure not designed for that will leave considerable risk of embarrassing failures. We've got people looking into AIX+HACMP for some applications; one thing we discovered is that this (expensive) technology is likely to make system reliability MUCH WORSE if it is not used properly. What you'll need (similar to the HACMP efforts) is to have the time to test your systems well under considerable load in order to figure out what are the sharp edges so that the system 'bleeds' a little in QA, but runs well in production. There will always be some of this, whatever set of technologies you pick for any complex project. There's always some unexpected local lessons to learn. After we ran the Slony-I replication system for about a week, we determined that it was _vital_ to do regular maintenance (vacuuming) on the internal table pg_listener otherwise system performance would steadily get pretty bad. Your application will have different patterns, but there will be some [likely small] set of vital bottlenecks, hard to discover until it is under load. Changing technologies will merely change which bottlenecks you hit :-). -- (reverse (concatenate 'string moc.liamg @ enworbbc)) http://www.ntlug.org/~cbbrowne/unix.html There's a new language called C+++. The only problem is every time you try to compile your modem disconnects. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Does PostgreSQL Stores its database in multiple disks?
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Igor Maciel Macaubas) would write: Heather, I might be able to do it. Let me check .. I can buy a cheap RAID IDE controller and try to mount everything as one. You'd be about as well off, if you're running Linux, to use the md RAID driver that simulates RAID in software. I was looking at the change log of postgresql 8, and figured out that it supports tablespace now, giving me the flexibility of storing my tables in different disks and even different directories if I want. So, I'm thinking in, instead of migrating it from oracle to 7.4.2, migrate it directly to 8.0 and be a beta tester for 8.0. that would be a great experiment, don't you think? Is anyone here using pgsql 8.0 in production now? I'm already using it in my development environment, around 80 tables but just a few megabytes of data (it's development). Is anyone here using it in a real world application by now? Nobody should be considering using 8.0 for anything called production use at this point in time, because it is NOT guaranteed that there will be an upgrade path to the official release. Now, I'm possibly 'somewhat conservative,' as someone that wouldn't want to call a system 'production' if it's using cheapo IDE drives. But what you're proposing isn't merely a matter of 'trying to jump the learning curve;' you're tempting, instead, the learning cliff, and the results of problems, there, are that instead of scuffing your knees, you wind up falling 8 stories and going splat. -- output = (cbbrowne @ ntlug.org) http://www.ntlug.org/~cbbrowne/spiritual.html Rules of the Evil Overlord #33. I won't require high-ranking female members of my organization to wear a stainless-steel bustier. Morale is better with a more casual dress-code. Similarly, outfits made entirely from black leather will be reserved for formal occasions. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] replication between mysql 3.23 and postgresql 7.2.4?
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (k b) wrote: Hello. I have a slightly off topic question. Is it possible to set up replication between a mysql 3.23.56 server and a postgresql 7.2.4 server without any extra software in between? i am primarily interested in replication from the mysql server to the postgresql server, but two way replication might be needed later on. is it possible? where can i find more information? You might look at how MySQL's native replication schemes are implemented, and see if there is some way to adapt them to generate SQL statements that could be injected into a PostgreSQL instance. But what this amounts to is rolling your own solution. It also strikes me as a good instance of a fool's errand, as the semantics of the systems are so strikingly different that you'll find that if you _do_ implement some form of replication, it won't work nearly as well as you would hope or need. The comments comparing MySQL with Oracle seem pretty appropriate http://sql-info.de/mysql/vs/mysql-oracle.html Notably: If you find your application works as equally well on Oracle as it does on MySQL, then you are vastly underutilising Oracle and probably paying for it as well. In the case of PostgreSQL, you may not be paying anything for it, but you certainly won't be getting any benefit out of it if you use it exactly the same as you use MySQL. And why PostgreSQL 7.2.4? That's a fairly ancient version, now. If you're prepared to do something as experimental as building a two-way replication system between MySQL and PostgreSQL, jumping straight to 8.0 won't introduce any risks that would be noticeable in comparison to the ones you already face. The 'conservative' approach would be to use 7.4.3, anyways. You might as well not bother; that will involve a whole lot less effort, and leave you less disgruntled at the end of the process. -- cbbrowne,@,cbbrowne.com http://cbbrowne.com/info/sgml.html I just got skylights put in my place. The people who live above me are furious. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] When will my database crash?
[EMAIL PROTECTED] (Simon Riggs) wrote: Proactive, planned maintenance is more manageable than sudden, grinding failure when you're at home in bed. Make sure your manager is part of the call-out plan, thats usually a good way to make them care about maintenance. And regular maintenance allows maintenance sessions to become individually _much_ less costly. If the heavily-updated tables were vacuumed daily or even hourly, it is quite likely that the issue would go away, from whence comes the pg_autovacuum strategy. For most applications, you should be running VACUUM FULL at least monthly, since any more than that is effectively the same thing as never, as your case shows. So long as you vacuum heavily-updated tables often enough, run 'plain VACUUM ANALYZE' once in a while, to catch the transaction ID rollover issue, and have enough space in the free space map, VACUUM FULL shouldn't be necessary. At Afilias, we _never_ run VACUUM FULL in the production transactional environment, or at least we haven't needed to since migrating to 7.4. (On 7.2, we needed to do so periodically, as well as periodically reindexing some tables.) -- select 'cbbrowne' || '@' || 'ntlug.org'; http://www3.sympatico.ca/cbbrowne/sap.html A good system can't have a weak command language. -- Alan Perlis [This explains why MS-DOS and Windows can't possibly be good systems...] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] When will my database crash?
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (S. C.) belched out:
We have a postgres 7.4 database which never vacuum for 4 months. I try to
vacuum one time. But my manager can't bear the low performance of website. So
I had to kill the vacuum before it finished. Is it ok for a postgres 7.4
database never do vacuuming?
We have 10,000 trans every day. That would be 10,000 inserts and 10,000
updates each day.
No, what you need to do is to _regularly_ vacuum it. That is, _ALL
THE TIME_.
Not vacuuming for four months was a severe error, which means that you
now need to run a really full maintenance cycle. At the next possible
opportunity, you really need to do a VACUUM FULL on the whole
database.
It's a little like running an automobile and not bothering to change
the oil or to do any other maintenance for several years. You'd
discover that you need to take the automobile entirely out of service
for an extended period in order to do _major_ repair work, and the
fact that this inconveniences someone is irrelevant.
_Perhaps_ you can do the maintenance a table at a time, here and
there.
But whenever you kill a vacuum, you are discarding any work it has
done. Don't waste everyone's time by watching it for a while, and
then stopping it; keep vacuuming until the tables are DONE.
And then put in a proper maintenance regimen, perhaps using
pg_autovacuum, or at least doing a vacuum of the whole database at low
periods either daily, or at worst, weekly.
In some of our systems, we have tables that need to get vacuumed every
five minutes so that the cost of vacuuming never gets unbearable.
Vacuuming every five minutes goes in a flash because there are
normally only a few hundred tuples, with only a couple hundred of them
live. For those tables, vacuuming once an hour would be
unacceptable, and would destroy performance of the systems.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www3.sympatico.ca/cbbrowne/wp.html
'Mounten' wird fuer drei Dinge benutzt: 'Aufsitzen' auf Pferde,
'einklinken' von Festplatten in Dateisysteme, und, nun, 'besteigen'
beim Sex.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Create Linux Script for PostgreSQL database backup
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Naomi Walker) would write: Anything would plain text would be a problem. Isnt .pgpass plain text? Yes, it's plain text. How do you propose to improve on that? At _some_ point, there has GOT to be a password in plain text form that libpq has access to, and any attempt to obfuscate it cannot provide anything better than illusory security. Suppose we decide we will store it in some encrypted form; libpq (or some equivalent) necessarily has _got_ to contain the decryption system, which means that anyone that can read the library and therefore read that decryption key, allowing them to decrypt the file containing the encrypted password. In effect, we could _pretend_ to encrypt the passwords in .pgpass, but it can't possibly provide any more security than we get storing them unencrypted. Suppose we characterize this in a sort of mathematical notation... P - plaintext password E: p == p' is an function mapping text into an encrypted form E':p' == p is the inverse function of E, mapping the encrypted form back into plaintext You propose that we store an encrypted form in the password file. That means that we have some tool that takes P, transforms it using function E to E(P), and puts it in the encrypted password file. But then there must be an instance of function E' either in libpq or within the postmaster. If I have access to the computer system, I therefore have access to libpq+postmaster, and thus can take that encrypted password, E(P), and use E' to find E'(E(P)) = P. That's the plaintext password; you imagined it hidden, but it wasn't, really. Public key encryption, while seemingly magical for many purposes, doesn't help with this. Functions E and E' could both be PK-related; the fact that E' MUST exist on the system means that E/E' can provide no meaningful security. This is a fundamental flaw that strikes just about any such sort of automated process that cannot resort to asking an operator for a key. (There's an exception, bt it requires having a tamper-resistant cryptographic device connected to the computer system, and those are really expensive to manage.) I do prefer secure systems to those that aren't, but I also engage a healthy distrust in people that tell me things that I know aren't true. If someone were to claim that encrypting these passwords provided material improvements to security, they would either be lying to me, or demonstrating that they don't understand what security this would(n't) provide. If PostgreSQL Core folk claimed that encrypting the passwords provided improved security, I'd have to think uncomplimentary thoughts about them... -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://www3.sympatico.ca/cbbrowne/advocacy.html Science is like sex: sometimes something useful comes out, but that is not the reason we are doing it. -- Richard Feynman ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Create Linux Script for PostgreSQL database backup
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Sandro Garoffolo) wrote: One question , if you set access to server with password in pg_hba.conf how can you pass the apssword in the script? You don't have to if you put it in $HOME/.pgpass; see the documentation for the format of that file... -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://cbbrowne.com/info/sap.html What would the world be like with no hypothetical situations? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] PostgreSQL Migration of databases 7.2 to 7.4
Quoth Mario Thomas [EMAIL PROTECTED]: We have a server in our datacentre running PostgreSQL 7.2 with about 50 databases set up (for different customers). We have just commissioned a much better specified server which is running PostgreSQL 7.4. We need to migrate the databases from 7.2 to 7.4. The smallest database contains about 200K of data whilst the largest database weighs in at about 100Mb. What's the best way to migrate the data? Would you suggest using the pgdump tools? We've found them to be quite problematic in use and are not entirely sure of the best command line switches to use. We need to make sure the data and permissions get migrated, as the data is being used for live web sites. So obviously speed of migration is important as we will have a period of downtime to complete the migrations. The way to minimize the downtime is to use one of the replication systems to replicate data between two database instances. It may take a while to set up the replica, but once it is in place, if you keep it fairly up to date, the switchover between versions should just take the few minutes needed to: a) Stop new data from going into the old database; b) Allowing all updates to get to the new database; c) Switching access over to the new database. Unfortunately, the latest and greatest option, Slony-I, does not support versions younger than 7.3.3, so you'd have to look at other solutions such as ERServer. Earlier in the year, this approach was used at Afilias, in conjunction with ERServer, to migrate systems from 7.2.4 to 7.4.2. -- output = (cbbrowne @ cbbrowne.com) http://www3.sympatico.ca/cbbrowne/internet.html Epistemology in One Lesson Reality ruthlessly selects out creatures that embody hypotheses too inconsistent with reality. Our only choice is whether we participate by being selected out, or (in Popper's great phrase) by letting our ideas die in our stead. -- Mark Miller ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Pseudo-Off-topic-survey: Opinions about future of Postgresql(MySQL)?
[EMAIL PROTECTED] (Enrique Arizón) commented: Now that CA has open sourced Ingres what future do you guess to Postgresql and MySQL? Don't missunderstand me, I have been using Postgresql for more than 3 years and developing apps against it and all I got is possitive impressions, but comparing the upcoming 8.0 (7.5) release with Ingres, it looks that Ingres is much more advanced (clustering, load-balancing, XML, ...) and the main advantage Postgresql had in its open source nature looks to be vanished. More one, CA looks really serious about Ingres that now is a core tool in more of 100 derivates CA products, and it's said they had doubled the number of Ingres developers. Also the new version provides a great compatibility with Oracle and easify Oracle to Ingres port. Is there any OBJETIVE reason not to change to Ingres? Let me point to an article just released in InfoWorld, directly addressing this issue: http://www.infoworld.com/article/04/08/13/33OPcurve_1.html Check out the second paragraph: Then there are vendors that open up software, usually vintage code that has no commercial value. IBM opened its Cloudscape Java DBMS, a move that's a little late compared to Borland's opening of InterBase and a little irrelevant next to powerful and widely used open DBMSes such as MySQL and PostgreSQL, the latter being my current favorite. Computer Associates' qualified open sourcing of Ingres is, like Cloudscape and Microsoft's restrictive Shared Source Initiative opening of parts of .Net and other properties, an apt illustration of how selective corporate code charity is. I have been watching different parts of the computer biz for _years_, and I have seen plenty of projects using databases. Oracle? Plenty. Microsoft SQL Server? Lots. Informix? Sure. Sybase? I saw it chosen once, and I know one fellow who is presently consulting at Morgan Stanley that tells me they are a big customer of Sybase. But in the last ten years, I have never once heard mention of Ingres in a commercial context. I was aware of it via University Ingres and because of knowing a little history, both of which came from academia, not from the commercial world. Consider: - Monster.com shows 13 jobs mentioning Ingres; - PostgreSQL gets you 55 hits. I have to concur with Yager's characterization of the release. SAP's release of SAP-DB last year is another pretty evident case of a vendor opening up vintage code with little commercial value. They acquired it from Software AG a couple years ago, more than likely to get them some leverage when negotiating licensing fees with Oracle. They couldn't attract significant quantities of outside developers to work on the open source release even though it has considerable maturity and functionality. Back to the Ingres question, it is _possible_ that the Ingres code base may be usable / maintainable / improvable. It is by no means guaranteed that this is so. It seems much more likely that CA has concluded that they can't make any more money off of Ingres, and that they're essentially providing a way that any remaining shops that are _heavily_ invested in it have some capability to self support if CA stops doing maintenance. For all of the vendors that have been doing this sort of thing, there is also likely some notion of scorched earth policy in mind. If they can't make any money off their products, well, if they can do something that can injure earning potential on the part of the the leading vendor (e.g. - Oracle), they at least get _something_ out of a retreat from the marketplace. Note that, historically, a scorched earth policy probably most notable as being the strategy Russian defenders used to fight back those notable conquerors, Napoleon and Hitler. They didn't have the military might to directly fight off the conqueror, so they burned everything as they retreated. This left Stalingrad pretty much in ruins, but the attacking armies were, shortly thereafter, nearly destroyed by famine and frost. I somehow doubt we'll see Oracle sales managers falling to quite that kind of destruction, but it sure can't be enjoyable for them to see others' database software getting steadily cheaper. I wouldn't be shocked to see still more database products falling in similar manner, although I don't expect to see many more closed source DBs entering open source form. If you watch carefully, you'll notice that every one of the recently open sourced databases has emerged from a company to whom they represented a secondary sort of product. SAP _mostly_ sells R/3. CA sells plenty of other software as does IBM. Companies like Oracle, Informix, and Sybase, where the _only_ product is the database, have no room to do this. If sales falter, the company would fail before they could ever get a vital product given away. -- output = (cbbrowne @ ntlug.org) http://cbbrowne.com/info/sgml.html Purely applicative languages are poorly applicable. -- Alan Perlis
Re: [ADMIN] PG 7.2 varchar change
Quoth [EMAIL PROTECTED] (slane):
Hello all:
I am upgrading a web application from postgres 7.1.2 to 7.4.3 (too long in
coming to that point, I know).
I have sifted through the history files and identified a restricted number
of changes that potentially impact the app, a few of which I don¹t
understand.
Here¹s the currently most alarming, a change that is recorded for 7.2:
CHAR(), VARCHAR() now reject strings that are too long (Peter E)
Consider this example:
tutorial=# create table test_string (f1 varchar, f2 varchar(10));
CREATE TABLE
tutorial=# insert into test_string (f1, f2) values ('abc', 'def');
INSERT 2623360 1
tutorial=# insert into test_string (f1, f2) values ('abcdefghijklmnopqrstuvwxyz',
'def');
INSERT 2623361 1
tutorial=# insert into test_string (f1, f2) values ('abcdefghijklmnopqrstuvwxyz',
'abcdefghiasdfasdfa');
ERROR: value too long for type character varying(10)
If no maximum length is specified, PostgreSQL is free to stick a
goodly amount of data in the field.
But supposing you decide that a particular column is VARCHAR(10),
trying to stuff more than 10 characters into it will fail, as you see
above.
Doing similar with char:
tutorial=# create table test_chars (f1 char, f2 char(10));
CREATE TABLE
tutorial=# insert into test_chars (f1, f2) values ('abc', 'def');
ERROR: value too long for type character(1)
Does that illustrate what's going on? I hope so...
--
let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;;
http://www3.sympatico.ca/cbbrowne/spreadsheets.html
Fashion is a form of ugliness so intolerable that we have to alter it
every six months. -- Oscar Wilde
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Replication
The world rejoiced as [EMAIL PROTECTED] (Rodrigo Botana) wrote: There is any way to have a replication (master - slave) whithout install anything on the slave computer. I ask this because i would like to have my website (that don't have any replication service avaliable) with the same information of my internal system. As far as I am aware, the only replication system that works in that manner is eRServer; it only requires the replication triggers to be on the server. It's fairly likely that Slony-I will be a better solution, although it requires that stored procedures that include components in C be available on all nodes. That mandates having the Slony-I software installed on your slave host. -- let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;; http://www3.sympatico.ca/cbbrowne/lisp.html If you give a man a fish, he will eat for a day. If you teach him how to fish, he will sit in a boat and drink beer all day. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Slony NG
Oops! [EMAIL PROTECTED] (Gaetano Mendola) was seen spray-painting on a wall: | You are aware that there is a slony mailing list at | http://gborg.postgresql.org/mailman/listinfo/slony1-general right? Does | that not serve your purposes? I knew it, some times I'm in IRC too, but I feel better using a NG instead of a mailing list, just because I can consult it also if my laptop is not with me. If someone were to ask the folks at gmane to mirror/news-gateway the Slony mailing list, that could probably work. -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://cbbrowne.com/info/advocacy.html I can't escape the sensation that I have already been thinking in Lisp all my programming career, but forcingthe ideas into the constraints of bad languages, which explode those ideas into a bewildering array of details, most of which are workarounds for the language. -- Kaz Kylheku ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Replication in main PostgreSQL codebase
After a long battle with technology, [EMAIL PROTECTED] (Matt Browne), an earthling, wrote: I apologise in advance if any of my questions are in a FAQ somewhere - I haven't seen them... Does anyone know when (if ever) replication support will be added to the main PostgreSQL codebase? Is there any chance it'll be included in the upcoming 7.5 release? Not much of a chance. I've taken a look at the replication solutions already available, including the sterling effort made on Slony-I, but we're really looking for integrated, base-level support rather than an add-in. The problem is that replication doesn't tend to mean one thing, but people rather have different ideas of what it means. Jan Wieck puts it fairly nicely: The number of different replication solutions available supports the theory that ``one size fits all'' is not true when it comes to database replication. -- let name=cbbrowne and tld=acm.org in name ^ @ ^ tld;; http://www.ntlug.org/~cbbrowne/emacs.html You shouldn't anthropomorphize computers; they don't like it. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Dumping partial database content
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Bender, Cheryl) wrote: Just wondering--is it possible to dump on a temporary table? The temp table is only visible inside the context of the transaction under which it was created. A pg_dump session will create an independent transaction context, where the table won't be visible, alas. So you can't use pg_dump to dump the data out. You may, however, use COPY to dump it out yourself. select * into temp table foo from bar; copy foo to '/tmp/foo_contents.txt'; -- If this was helpful, http://svcs.affero.net/rm.php?r=cbbrowne rate me http://www.ntlug.org/~cbbrowne/postgresql.html Rules of the Evil Overlord #50. My main computers will have their own special operating system that will be completely incompatible with standard IBM and Macintosh powerbooks. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] SERIAL type not autoincremented
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] wrote:
Hello
i have:
create table student(
id SERIAL NOT NULL,
name VARCHAR(35) NOT NULL,
primary key (id)
);
and when i try to insert like this:
insert into student (name) values('me');
i receive error:
ERROR: duplicate key violates unique constraint student_pkey
Why ? Do i understeand correctly that SERIAL == AUTO_INCREMENT ?
As far as i remember i used such type in the past and had no problem.
What do i miss ?
[EMAIL PROTECTED]:~$ psql osdb
Welcome to psql 7.4.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
/* [EMAIL PROTECTED]/wolfe osdb=*/ create table student(
/*osdb(#*/id SERIAL NOT NULL,
/*osdb(#*/name VARCHAR(35) NOT NULL,
/*osdb(#*/primary key (id)
/*osdb(#*/);
NOTICE: CREATE TABLE will create implicit sequence student_id_seq
for serial column student.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
student_pkey for table student
CREATE TABLE
/* [EMAIL PROTECTED]/wolfe osdb=*/ insert into student (name)
values('me');
INSERT 19423269 1
/* [EMAIL PROTECTED]/wolfe osdb=*/ \q
It sounds as though there's something more going on that you haven't
told us about.
What data was already in that table? If there wasn't anything already
there, there could hardly be a duplicate.
--
let name=cbbrowne and tld=cbbrowne.com in String.concat @ [name;tld];;
http://www3.sympatico.ca/cbbrowne/lsf.html
Academics denigrating Popularizers
During the rise of the merchant class, the landed aristocracy
understood the value of creating food, but didn't appreciate that food
isn't valuable unless it reaches hungry mouths.
New ideas aren't valuable unless they reach hungry minds.
-- Mark Miller
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [ADMIN] Replication/Mirror Help
In the last exciting episode, [EMAIL PROTECTED] (Mr. Darshan Patel) wrote: I had install postgresql server on two different port : default(5432) and other(5545), now I have to setup replication/mirror between them So what are the steps/setup procedure. That presumably depends on which replication system you have chosen to use. eRServ has one set of procedures; Slony1 has another; RServ has still another; Mammoth Replicator doubtless adds a fourth set. These sorts of issues probably should be addressed to the development teams involved with those particular replication systems. -- output = (cbbrowne @ ntlug.org) http://cbbrowne.com/info/x.html Rules of the Evil Overlord #38. If an enemy I have just killed has a younger sibling or offspring anywhere, I will find them and have them killed immediately, instead of waiting for them to grow up harboring feelings of vengeance towards me in my old age. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Postgresql on a shared storage
[EMAIL PROTECTED] (Vasil Kolev) wrote: I'm working on a system that has 2 servers with postgresql, and a FC shared storage between them, where the database is stored. After some weeks of using google and reading lists, I've come to the conclusion, that there is no way (using F(L)OSS tools) to use both databases R/W, or even one of them R/O, and one of the machines has to do all the work, and the other one to be a hot spare. Am I right, or have I missed something? And is there something tested and usable for my case, even if it's commercial, that will run under linux? The only quasi-FLOSS system that bills itself as being good for this sort of thing is Backplane http://www.backplane.com/; it is _way_ too early for it to be considered mature for the purpose, and it's licensed under much the same sort of if you're doing anything commercial, you have to pay for commercial licenses arrangement as MySQL. The most relevant Oracle thing is RAC. It looks like it's _very_ expensive, and it's not evident that it allows multiple instances to share the FC storage. http://www.dba-oracle.com/art_rac.htm I'm not sure you'll find it easy to find commercial databases that allow you to spread write load across multiple servers. What you're looking for is some form of multimaster replication; that's problematic on any platform. -- let name=cbbrowne and tld=acm.org in name ^ @ ^ tld;; http://cbbrowne.com/info/linuxdistributions.html Optimization hinders evolution. -- Alan Perlis ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Performance Problem
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Frank Smith) would write: Hi ID:7 I am running PostgreSQL 7.2.2 on Red Hat 9 and I am suffering a growing performance problem. The problem shows through a slowing of queries and an increase in the system CPU usage. Queries that took less than 6 seconds clime to take more than 5 minutes and as the system is driven by Apache through Perl scripts, the web server times out. Clearly I could reset the Apache timers, however this would just hide the problem for a little longer and of course once the problem starts to happen the system tends to cascade because the users try again and the orphaned processes continue to use processor time until they complete. I use Cron to 'VACUUM ANALIZE' the system every night and this greatly improved the performance but has not stopped the delay from growing. The strange thing is that apart from the delay everything seems fine. Some facts: Rebooting does not remove the problem. Restarting Apache and/or Postgres makes no difference. Other that the ANALIZE mentioned above the system has not been tuned in anyway. The following sequence does however cure the problem; pg_dump database temp.db dropdb database createdb database psql -e database temp.db I have not tried leaving out the dropdb and createdb but I do not need to stop postgres. Has anybody any idea how to fix my problem is it something I have or have not done? Please do not tell me to upgrade to the latest version of Postgres unless it is a clearly identifiable Postgres problem that is at the rot of my dilemma. Well, there certainly have been _major_ improvements in performance between 7.2 and 7.4, so it is more than plausible that that could have a significant impact on performance. It sounds as though the traffic on the system is causing query plans to get out of whack. But it is not clear what your problem actually is; you have not explained the nature of the queries that are performing badly. I would imagine that it would be beneficial to run VACUUM and/or ANALYZE somewhat more often. - Let's suppose you run VACUUM on tables getting lots of UPDATEs and DELETEs (note, I did _not_ say INSERT; tables that only see INSERTs essentially NEVER need to be vacuumed) once every 4 hours. - Tables seeing frequent updates of any sort (INSERT/DELETE/UPDATE) perhaps should get an ANALYZE every hour. There are significant merits to jumping to a newer version in this regard: 1. As of 7.3, you can run pg_autovacuum, which will automatically do VACUUMs and ANALYZEs, which hopefully would prevent things from going downhill. 2. As of 7.4, there is a new logging parameter that allows logging just those queries that are running slowly, which should help you to find just the offending queries. The sort of information we'd actually need to help fix the problem is the queries and the output provided by running EXPLAIN ANALYZE on some of the slow queries. There are some tables that we have in our applications that we happen to ANALYZE _and_ VACUUM every couple of minutes, as they are UPDATEd almost continually. Waiting an hour between VACUUMS would lead to _really_ bad performance. You might have an instance of a table like that, but you haven't said anything that would indicate that. -- (reverse (concatenate 'string gro.mca @ enworbbc)) http://cbbrowne.com/info/sgml.html Rules of the Evil Overlord #149. Ropes supporting various fixtures will not be tied next to open windows or staircases, and chandeliers will be hung way at the top of the ceiling. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] two versions on same linux
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Paul Gimpelj) wrote: I have redhat 7.3 and postgres 7.2 Is there a way to have 7.4 installed together with postgres 7.2 ? and both running at the same time, with of course different data directories. should i use the generic postgres or the rpm'd. Presumably the respective RPMs will conflict between the different versions, which is a bit of a problem. You could of course install one or the other or even both from source, as long as you choose a specific place to stick them. Under the circumstances, I'd be inclined to do this: - Install the PG 7.4 RPMs, in order to have things like Perl support managed by the package management system. Personally, I'd rather stick hot needles in my eyes (add further gruesome details as needed ;-)) than manage Perl stuff by hand. - Install PG 7.2 AND 7.4 in source code form in some place that YOU manage, and run the database clusters out of that. A naming convention could be to stick them in /opt; you'd configure the source builds something like: %postgresql-7.2.5 ./configure --prefix=/opt/postgres/7.2.5 [then build and install 7.2.5] %postgresql-7.4.2 ./configure --prefix=/opt/postgres/7.4.2 [then build and install 7.4.2] You'd need to customize both init scripts in /etc/rc.d, perhaps basing them on the ones provided by the RPM file. The existing redhat has no passwords for postgres, and even if if assign one to user postgres, postgres ignores it. That's probably based on the ACLs configured in the pg_hba.conf file; if there are trust entries there, passwords can get ignored... -- (reverse (concatenate 'string gro.mca @ enworbbc)) http://www.ntlug.org/~cbbrowne/emacs.html We live in a Newtonian world of Einsteinian physics ruled by Frankenstein logic. -- David Russell ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Missing or errooneous pg_hba.conf file
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Tom Lane) belched out: Bill Chandler [EMAIL PROTECTED] writes: LOG: invalid entry in pg_hba.conf file at line 60, token 255.255.255.255 I have found that if I change this value to anything other than 255.255.255.255 (e.g. 255.255.255.254) the message goes away. Bizarre. AFAICS this must indicate that getaddrinfo() is rejecting the value, which perhaps could happen if some bit of it were miscoded to use -1 as a failure code. Interestingly the same 7.4.1 binary running on a different machine did not exhibit the problem. Could it be a Solaris patch level thing? Seems likely. Can you update and see if that fixes it? Hmm... I think I have seen the same thing. It went away when I substituted in some grouped entries, applying policies to subnets rather than to individual hosts. That happened to be a more appropriate model to use, as it cuts WAY down on the need for pg_hba.conf entries, and on the need to modify configuration in the future. (We used to have, on test boxes, named IP addresses for people that now get their IPs assigned by DHCP... Without subnet definitions, that's a nightmare!) We've got a box getting patched Saturday; I'll watch for that... -- cbbrowne,@,ntlug.org http://www3.sympatico.ca/cbbrowne/ Do Roman paramedics refer to IV's as 4's? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] postgresql not using enough memory?
Oops! [EMAIL PROTECTED] was seen spray-painting on a wall: Hello list, I know this is a complicated issue, but I'll throw it out there... Our DB box has 3GB of ram - which is supposed to be used by postgres. Whenever I use top, it only shows about 800MB being used with 2.2GB free. What can I do to tell postgres to use more of that memory - maybe 2GB ? The one change that might be appropriate would be to bump up sort memory. Not too much; 40MB is a terrifically large amount unless you're doing REALLY enormous sorts. Aside from that, it is quite likely that everything is AOK. PostgreSQL benefits from having data in the filesystem cache, and if you up the shared buffer cache, you risk having the commonly-used data stored twice, once in the buffer cache, and once in the filesystem cache, which is wasteful. -- output = (cbbrowne @ ntlug.org) http://cbbrowne.com/info/rdbms.html Let me control a planet's oxygen supply and I don't care who makes the laws. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Real time replication of PG DBs accross two servers - any experiences?
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Peter Eisentraut) wrote: Bradley Kieser wrote: I desperately need to set up a real time replication of several databases (for failover) between two servers. Last time I looked at the PG replication it wasn't yet production level. I wonder if anyone on this list is doing this successfully and if you won't mind sharing your experience and giving me any tips that you may think would be handy from a real world perspective I would very much appreciate it. To ensure high availability, using DRBD to replicate the storage or using a shared storage (e.g., EMC^2) does the job. That saves you the trouble of having to set up and manage a database replication solution at all. Regrettably, disk replication schemes aren't generally able to cope with keeping the database up and alive while replication is taking place. You need to shut down the database that is attached to a target/slave system every time you sync the target/slave against the source/master. If the _sole_ goal is failover, then that will be defined to be fine. But when the people paying for the duplicate set of hardware realize that it's sitting there otherwise useless at any time that there hasn't been a failure, they'll presumably agitate for some 'better' sort of replication... -- (format nil [EMAIL PROTECTED] cbbrowne acm.org) http://cbbrowne.com/info/lisp.html Computers double in speed every 18 months or so, so any exponential time problem can be solved in linear time by waiting the requisite number of months for the problem to become solvable in one month and then starting the computation. -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] [PERFORM] Raw devices vs. Filesystems
[EMAIL PROTECTED] (Josh Berkus) wrote: Well, as I said, that's why I was asking - I'm willing to give it a go if nobody can prove me wrong. :) Why not? If you have time? True enough. I thought you knew - OCFS, OCFS-Tools and OCFSv2 have not only been open- source for quite a while now - they're released under the GPL. Keen! Wonder if we can make them regret it. Seriously, if Oracle opened this stuff, it's probably becuase they used some GPL components in it. It also probably means that it won't work for anything but Oracle ... It could be that the experiment shows that OCFS isn't all that helpful. Or that it helps cover inadequacies in certain aspects of how Oracle accesses filesystems. If it _does_ show that it is helpful, then that may suggest a filesystem implementation strategy useful for the BSD folks. The main failure case would be if the exercise shows that using OCFS is pretty futile. -- select 'cbbrowne' || '@' || 'acm.org'; http://www3.sympatico.ca/cbbrowne/linux.html Do you know where your towel is? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Raw vs Filesystem
[EMAIL PROTECTED] (Jaime Casanova) asked:
so, the real question is what is the best filesystem for optimal speed
in postgresql?
The smart-alec answer would be... Veritas, of course!
But seriously, it depends on many factors you have not provided
information about.
- Different operating systems support different filesystems; you
should probably specify what OS you are using.
- Hardware choices have ENORMOUSLY more impact on speed than any
choice of filesystems.
If your hardware is too slow, changing filesystems will not help you.
And you would be _way_ better off with a filesystem that is 3% slower
if it improves reliability by a significant factor.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www3.sympatico.ca/cbbrowne/lsf.html
We should start referring to processes which run in the background by
their correct technical name:... paenguins. -- Kevin M Bealer
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [ADMIN] Raw devices vs. Filesystems
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Jaime Casanova) belched out: Can you tell me (or at least guide me to a palce where i can find the answer) what are the benefits of filesystems over raw devices? For PostgreSQL, filesystems have the merit that you can actually use them. PostgreSQL doesn't support use of raw devices. Two major benefits of using filesystems as opposed to raw devices are that: a) The use of raw devices is dramatically non-portable; you have to reimplement data access on every platform you are trying to support; b) The use of raw devices essentially mandates that you implement some form of generic filesystem on top of them, which adds considerable complexity to your code. Two benefits to raw devices are claimed... c) It's faster. But that assumes that the cooked filesystems are implemented fairly badly. That was typically true, a dozen years ago, but it isn't so typical now, particularly with a fancy cacheing controller. d) It guarantees application control of update ordering. Of course, with a cacheing controller, or disk drives that lie to one degree or another, those guarantees might be gone anyways. There are other filesystem advantages, such as e) Shifting cooked data around may be as simple as a mv, whereas reorganizing on raw disk requires DB-specific tools... And what filesystem is the best for postgresql performance? That would depend, assortedly, on what OS you are using, what kind of hardware you are running on, what kind of usage patterns you have, as well as on how you define the notion of best. Absent of any indication of any of those things, the best that can be said is that depends... -- (format nil [EMAIL PROTECTED] cbbrowne acm.org) http://cbbrowne.com/info/languages.html TTY Message from The-XGP at MIT-AI: [EMAIL PROTECTED] 02/59/69 02:59:69 Your XGP output is startling. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] plpgsql editor(s)?
After a long battle with technology, [EMAIL PROTECTED] (Andrew Biagioni), an earthling, wrote: Can anyone recommend an editor (windows OR linux) for writing plpgsql code, that might be friendlier than a standard text editor? Nice features I can think of might be: - smart tabbing (1 tab = N spaces) - code coloring (esp. quoted strings!) - parens/brackets matching Building an electric mode for Emacs would almost certainly be The Right Answer. Nobody has done so yet. -- cbbrowne,@,ntlug.org http://www.ntlug.org/~cbbrowne/internet.html Why do we drive on parkways and park on driveways? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Do Petabyte storage solutions exist?
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Bradley Kieser) transmitted: I think as far as PG storage goes you're really on a losing streak here because PG clustering really isn't going to support this across multiple servers. We're not even close to the mark as far as clustered servers and replication management goes, let alone the storate limit of 2GB per table. So sadly, PG would have to bow out of this IMHO unless someone else nukes me on this! Are you trying to to do a bad April Fool's joke? A 2GB limit is simply nonsense. I work with a number of databases where tables contain 2GB of data. While there are some of the pointy-clicky approaches to clustering and replication that aren't there for PostgreSQL, a '2GB limit' is certainly NOT one of the reasons to avoid PG. -- If this was helpful, http://svcs.affero.net/rm.php?r=cbbrowne rate me http://www.ntlug.org/~cbbrowne/oses.html Let me get this straight: A company that dominates the desktop, and can afford to hire an army of the world's best programmers, markets what is arguably the world's LEAST reliable operating system? What's wrong with this picture? -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] [PHP] upgrading to 7.4.2
Quoth [EMAIL PROTECTED] (Goulet, Dick): Comparing a MySql upgrade to anything else is comparing apples to eggplants. Their not even in the same group. mySql likes to leave their datafiles alone making all of their changes in the binaries. Now while that is good from an upgrade standpoint, what do you upgrade for? New Features. MySql doesn't like new features as it will slow down the database. Oh, but that's not true anymore. MySQL(tm) is supposed to support subqueries, stored procedures, triggers, and views Real Soon Now... And remember, their reason for being is to provide the world's most popular open source database. If popularity requires dressing up in slower clothing, well, that's presumably going to happen... -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://cbbrowne.com/info/nonrdbms.html Rules of the Evil Overlord #228. If the hero claims he wishes to confess in public or to me personally, I will remind him that a notarized deposition will serve just as well. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] constraints and performance
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Jodi Kanter) wrote:
Do constraints effect performance significantly?
They would be expected to provide a significant enhancement to
performance over:
a) Firing triggers,
b) Firing rules, and
c) Forcing the application to validate the data, and then adding
in data validation reports to report on the cases where a buggy
application violated the constraints.
So yes, they should be considerably faster than any of their
alternatives.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://cbbrowne.com/info/lsf.html
The only constructive theory connecting neuroscience and psychology
will arise from the study of software.
-- Alan Perlis
[To the endless aggravation of both disciplines. Ed.]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] reduce downtime when upgrading 7.3 - 7.4
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Palle Girgensohn) wrote: We use postgresql for rather large databases. For a typical installation, a pg_restore takes a couple of hours, at least (the dumpfiles are usually 2-4 gigabytes or so, including BLOBs). The machines are expected to be up 24/7, so this dump/restore procedure makes upgrading unpopular. Is there any (safe) way to speed this process up? The most obvious question is, can we use pg_upgrade from contrib? It seems not to have been updated since 7.3, and is generally documented as untested. What kind of problems can we get, can they be tested for in a testbed in advance? If pg_upgrade is not a good idea, how can I speed up pg_restore? Best way to set things like fsync etc in postgresql.conf? Will it make a big difference? We use FreeBSD-4.9 and want to upgrade from 7.3.4 - 7.4.1. A faster method would be to use one of the replication systems, such as ERserv. You have your existing database, running 7.3.4, and set up another DB instance (perhaps on the same box) running 7.4.1. You replicate the 7.3 DB over to the 7.4 one. It may take a substantial period of time to get them in near sync, but once you get them close, you can disconnect the application that is injecting updates to the 7.3 DB, and it should take mere minutes to get those updates into the 7.4 system. You then shut down the 7.3 system, shift the 7.4 one to the ports your application expects to use, and voila! You did it all with a mere 10 minute outage. You may need a few minutes to add back integrity constraints that the replication system required you to drop (because it may not order inserts in the exact same order that they went into place in the origin system). All that being said, ERserv may not cope perfectly with BLOBs, so you may need to do something special about that. But the above approach, while it has aspects that are fragile, can certainly cut down down time REALLY substantially. The other major approach that would provide something equivalent is the Holy Grail of PITR, which some people are thinking of working on now. That would be a more universal scheme which would be logically quite similar: - You start a pg_dump to send to the 7.5 system, and start archiving WAL files. - You load the pg_dump into the 7.5 system. - You then move over the WAL files, and replay them into the 7.5 system. (Big magic occurs here!) - You shut down the 7.3 system, copy the last WAL files over, and and load them into 7.5. And voila! You have a 7.5 database that started with the contents of the pg_dump, and then had all of the subsequent transactions applied to it, bringing it up to date. The Big Magic part is of the need to load 7.3 WAL data into a 7.5 system. If anything about data format changes, something fancy has to happen read it in properly. I wrote 7.5 rather than 7.4 because this is certainly not something that will be ready for a 7.4 release. If you need something sooner, then you'll need to look into the existing replication solutions. -- output = (cbbrowne @ ntlug.org) http://cbbrowne.com/info/rdbms.html If con is the opposite of pro, is Congress the opposite of progress? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] book for postgresql
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Ed Wong) wrote: I am an oracle dba and new to postgresql. Could you tell me what is the best postgres book out there to start with? I am looking for a book which is sort of a complete reference including some dba chapters as well. There are three fairly good books in printed form: - New Riders has one that is about the most recent one published, which, it seems to me, has about the best explanation of the query optimizer going, as well as quite a lot of other fairly deep technical material; - Addison Wesley published Bruce Momjian's book which is getting a bit dated, but which is still quite good; - O'Reilly has a third one that is also dated but good. The online material is also a good source, and is commonly included along with the PostgreSQL installation. All four of these sources are quite good. I have all but Bruce's book on my desk... -- output = (cbbrowne @ cbbrowne.com) http://www3.sympatico.ca/cbbrowne/linuxdistributions.html If a hole in the street is a manhole, is a hole in a man a streethole? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] R: R: slow seqscan after vacuum analize
In the last exciting episode, [EMAIL PROTECTED] (Edoardo Ceccarelli) wrote: Yes, you are right but it wasn't the case this time, I have run the explain plenty of times with same results. I think that the reason was that I made a simple VACUUM, after a VACUUM FULL ANALYZE (1h!!) things are ok It sounds as though you weren't vacuuming (just plain vacuum) often enough. What tends to go wrong is when the table winds up with a LOT of empty space due to there being a lot of updates to the table without dead tuples being cleaned out. The table winds up big, with no way to shrink it without the cost of a VACUUM FULL. If you vacuumed more often, the size of the table would likely stay smaller which is sure to be helpful. Another factor worth considering: If a few values are very common in the field you are selecting on, then the query optimizer can get convinced (wrongly) that a Seq Scan is the best choice. Using ALTER TABLE T ALTER COLUMN C SET STATISTICS [some value] to increase the number of bins can be helpful in such cases. (My pet theory is that the present default value of 10 is a little low, and that a lot of optimizer errors might be resolved by bumping it up a bit...) -- (format nil [EMAIL PROTECTED] cbbrowne ntlug.org) http://www.ntlug.org/~cbbrowne/sgml.html But what can you do with it? -- ubiquitous cry from Linux-user partner. -- Andy Pearce, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] R: slow seqscan after vacuum analize
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Iain) would write: I'd like to know more about the possibility of plain vacuums harming performance. This is the first I've heard of it. Vacuum full is not always an option in a production environment. There certainly are known cases where systems where the I/O bus is already fairly much saturated will suffer BADLY when a big vacuum is thrown at them. The problem in such cases is that the vacuum draws the pages that it is working on into the buffer cache, pushing out data that is actually useful to cache. There are experimental patches for 7.4, 7.3, and even, I believe, 7.2, for a sleeping vacuum that tries to limit the damage by sleeping every so often so that the vacuum does not dominate, and so that ordinary traffic gets a chance to reclaim cache. And there are efforts in 7.5 to improve cache management, so that pages brought in by VACUUM would be put at the opposite end of the LRU queue. That way, instead of them being treated as Most Recently Used, pushing everything the least bit older towards being dropped from the buffer cache, the vacuumed pages would be treated as if they were LRU, so they would get evicted FIRST. But if the Original Poster is encountering that the database is doing Seq Scans when it would be better to do an Index Scan, that is a separate problem, and focusing on the VACUUM may distract from the _real_ problem... -- let name=cbbrowne and tld=cbbrowne.com in String.concat @ [name;tld];; http://www.ntlug.org/~cbbrowne/advocacy.html Rules of the Evil Overlord #195. I will not use hostages as bait in a trap. Unless you're going to use them for negotiation or as human shields, there's no point in taking them. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] postgresql crushed with XLogWrite error
[EMAIL PROTECTED] (Andrew Sullivan) writes: On Mon, Jan 05, 2004 at 07:35:16PM +0200, Tsirkin Evgeny wrote: On Mon, 5 Jan 2004 11:23:08 -0500, Andrew Sullivan [EMAIL PROTECTED] wrote: I want to keep my configs.Of course i can save my postgresql.conf (and other files that i changed and don't currently remember) to another location and then put it back in place of what rpm will put ,but as i said ,i don't actually remember what i have changed :( The only things that should make a difference to you should be in the /etc directory, assuming the RPM follows the filesystem standard (I believe it does). My suggestion is to back everything up, then replace the RPM in the standard way, and then compare the files one at a time to see what's different. I'll bet the binaries and libraries, of course, and things under /etc/[postgres?]/, and that's it. Most likely, it'll be postgresql.conf and pg_hba.conf, and maybe pg_ident.conf. It's easy enough to check what files the RPM intends to update... bash-2.05a$ rpm -q -p -l netpbm-devel-9.24-3.i386.rpm /usr/include/pam.h /usr/include/pammap.h /usr/include/pbm.h /usr/include/pbmshhopt.h /usr/include/pgm.h /usr/include/pm.h ... other files omitted ... That can diminish how much is in the everything that needs to be backed up... -- let name=cbbrowne and tld=libertyrms.info in String.concat @ [name;tld];; http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Incremental clustering?
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (John Siracusa) wrote: The docs say: Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order. If one wishes, one can periodically recluster by issuing the command again. and During the cluster operation, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes. I don't relish totally locking and making a temporary copy of my biggest table, but that's exactly the table that seems to need clustering the most. Will subsequent cluster command also make a complete copy? Some form of incremental clustering would be nice... Unfortunately, making this work in a manner that allows concurrency is likely to be really troublesome. The cluster operation potentially has to reorder all the tuples, and the fact that the table is already _partially_ organized only diminishes the potential. If the new data, generally added at the end, has values that are fairly uniformly distributed across the index, then the operation really will have to reorder all of the tuples... It would be pretty sweet to have a process analagous to 'non-blocking VACUUM' as opposed to 'VACUUM FULL.' But there's no particularly easy way, and, to do so, you'd essentially have to throw away a fair chunk of the benefits of the clustered properties. -- cbbrowne,@,acm.org http://www.ntlug.org/~cbbrowne/rdbms.html When you have eliminated the impossible, whatever remains, however improbable, must be the truth. -- Sir Arthur Conan Doyle (1859-1930), English author. Sherlock Holmes, in The Sign of Four, ch. 6 (1889). [...but see the Holmesian Fallacy, due to Bob Frankston... http://www.frankston.com/public/Essays/Holmesian%20Fallacy.asp] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] 2CPU 4GBRAM
[EMAIL PROTECTED] (Peter Eisentraut) writes: Jose Mendoza wrote: I intend to use the postgreSQL in several projects that requiere to attend about 1000 - 2500 users in a chain of retail stores. Is is that possible? Or is beyond that the postgresql possibilities? That is certainly easily possible. Well, a couple thousand concurrent users, on a fairly small server, may prove challenging. The database connections alone will chew a fair bit of memory, and postmasters rather more. It may be a nifty idea to build an application that sits in between that queues up requests so that the DBMS doesn't have to cope with managing thousands of concurrent connections, that are not, individually, terribly busy, but instead has 50 connections that are kept pretty busy. IBM sells a product called MQSeries that does that; I seem to recall that Microsoft paid an Israeli company to develop a 'clone,' now called MSMQ. Apparently pretty good stuff, where applicable... -- let name=cbbrowne and tld=libertyrms.info in String.concat @ [name;tld];; http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] 2CPU 4GBRAM
[EMAIL PROTECTED] (Jose Mendoza) writes: How can be efectivly configured the PostgreSQL to run in a machine with 2 CPUs Xeon 2.4 GHz and 4GB of RAM? The PostgreSQL work with the 2 processors? I had run tests and the tiems is always the same that in a computer with 1 CPU pentium 4 2.4GHz and 2GB RAM. I intend to use the postgreSQL in several projects that requiere to attend about 1000 - 2500 users in a chain of retail stores. Is is that possible? Or is beyond that the postgresql possibilities? Well, if the tests that you have done have been presented, serially, to one connection, then they were all submitted to one backend process, in order, and you couldn't expect to get any benefit from having multiple CPUs. If you want to test how things scale with 2 CPUs and a whole bunch of users, what you most importantly have to do is to simulate the whole bunch of users part. You should set up programs, perhaps running on 10 computers, that generate traffic to resemble the actions of 1000 users. If you build a more proper test, you should find that the extra CPU does make some difference. But if all that you do amounts to running a single serial report, you surely won't see that... -- let name=cbbrowne and tld=libertyrms.info in String.concat @ [name;tld];; http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] PGSQL and DNCL
After a long battle with technology, [EMAIL PROTECTED] (Renney Thomas), an earthling, wrote: Has anyone any experience with PGSQL 7.x and implenting the FTC do-not-call list - which is about 50 million 10 digit N. American phone numbers? If so what structures have you used and what have you done interms of performance tweaks? Is there an equivalent to Oracle's IOT (index organized tables) in PGSQL? There is a PostgreSQL keyword called CLUSTER which clusters a table according to an index. That organizes the table based on a (specified) index. All US and Canada phone numbers fit into 2^34, which is regrettably slightly larger than 2^32. It is highly unfortunate that 2^31 is only about 2.1 billion, because it would be Really Sweet to be able to express the phone numbers as 32 bit integers. Using 32 bit ints would be GREATLY efficient because that fits with common register sizes. What you might do would be to create a table like the following: create table do_not_call ( first_8_digits integer, last_digit int2 ); create index fast_index on do_not_call(first_8_digits); And you'd put the first 8 digits into the obvious field. The index would get you to the right page of the index Right Quick, and the structure will be reasonably compact. It's a useful way of thinking to try to make use of the HIGH efficiency of having a 32 bit value express most of what you need... -- let name=cbbrowne and tld=ntlug.org in name ^ @ ^ tld;; http://www.ntlug.org/~cbbrowne/lisp.html Why do we drive on parkways and park on driveways? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] eRserver
[EMAIL PROTECTED] (Renney Thomas) writes: I would like to hear about any issues related to erserver. I was a little concerned about its use of Java. Java is a great tool for creating application frameworks for the payroll department, but using it for back-end system-level application programming is a bit unnerving. Java is generally slow, memory and CPU intensive and doesn't provide for tight integration like C/C++ applications. There are things about Java that cause me concern, but I would dispute this being the total story. The thing about database-based applications is that they wind up hitting the _database_ pretty hard. And when the bulk of the work is database queries, where it's _PostgreSQL_ doing the work, it's not Java that is likely to be the bottleneck. Replication is certainly no exception to this. The bulk of replication work takes place in the database. In extreme cases, there _may_ be Java-based bottlenecks to be found, but that doesn't seem to be the typical case. In addition, I think you're looking at Java as how it was 4 years ago. Sun has relearned some of the things about garbage collection learned 15 years earlier in the Lisp community. They have built larger sets of compiled-to-machine-language libraries akin to LIBC, so that increasing portions of system calls are run as plenty fast compiled code. And JIT means that raw Java isn't as slow as it used to be. -- let name=cbbrowne and tld=libertyrms.info in String.concat @ [name;tld];; http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] pg_restore and transaction id wraparound
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (ow) wrote: --- Tom Lane [EMAIL PROTECTED] wrote: Actually you can only have 4 billion SQL commands per xid, because the CommandId datatype is also just 32 bits. I've never heard of anyone running into that limit, though. Perhaps noone yet had a table with 4B records in pgSql. Otherwise, how would they dump/restore it? I may have been guilty of hyperbole, by using the number 10 billion, but not of proving this impossible. If you had a table that large, dump/restore wouldn't have any XID problems because the normal dump/restore involves copying the data out (ONE query, ONE XID), and then reading it via the COPY command (again, ONE query, ONE XID). And I think I would be quite displeased if I had a table with that many records, in any case, because dump/restore would take an enormously long time as would reindexing. -- (format nil [EMAIL PROTECTED] cbbrowne ntlug.org) http://cbbrowne.com/info/linuxdistributions.html 16-inch Rotary Debugger: A highly effective tool for locating problems in computer software. Available for delivery in most major metropolitan areas. Anchovies contribute to poor coding style. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Question On 7.4RC2
[EMAIL PROTECTED] (Glenn Wiorek) writes: I just finished compiling 7.4RC2 on Solaris 8 and ran the regression tests (make check). The last test out of 93 (stats) failed. Is this of concern? I haven't regressed 7.4RC2 on Solaris, though I did do RC1, without any problems. Let's see... What might be interesting differences... Could you share what versions of things you're using? For instance, here's the uname, pg_config, and gcc versioning information I find. If there are interesting differences, that might point to where the problem is... [EMAIL PROTECTED] /disk3/OXRS/postgresql-7.4RC1 uname -a SunOS ringo 5.8 Generic_108528-17 sun4u sparc SUNW,Ultra-4 [EMAIL PROTECTED] /disk3/OXRS/postgresql-7.4RC1 /opt/OXRS/pgsql74rc1/bin/pg_config --configure '--prefix=/opt/OXRS/pgsql74rc1' '--with-includes=/opt/OXRS/readline-4.2/include' '--with-libraries=/opt/OXRS/readline-4.2/lib' '--enable-locale' '--enable-syslog' '--enable-debug' [EMAIL PROTECTED] /disk3/OXRS/postgresql-7.4RC1 gcc -v Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/3.2/specs Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --disable-nls Thread model: posix gcc version 3.2 -- (format nil [EMAIL PROTECTED] cbbrowne libertyrms.info) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Which hardware/filesystem for postgresql?
In the last exciting episode, Cristian Veronesi [EMAIL PROTECTED] wrote: Hello, my company is starting to propose postgresql-based solutions to our clients. Our recommended operating system is SuSE Linux. Which disk architecture should we recommend for postgresql servers? I was thinking about RAID10. That all depends on what disk hardware there is, and what you're doing. The reason why they have all of the _various_ RAID levels, instead of just 1, is that flexibility is often needed when hardware varies and when the meaning of best varies. Also, what Linux filesystem should we use? I was thinking about XFS. Which filesystems are you using? The best performance results I have seen on Linux systems have involved the use of JFS. I found XFS to be a little slower, and it has the distinct demerit that it is not in the 'official' kernel tree yet, thereby meaning that you have to get into the pain of managing heavily-patched kernels. The kernel management issue strikes me as being a much bigger deal than the relatively minor performance difference. -- output = reverse(ac.notelrac.teneerf @ 454aa) http://cbbrowne.com/info/sgml.html Wintel: A Wasteland of Useless Software - If the bazillions of programs out there actually amount to something, why is everyone using MICROS~1 Office, Word, Excel, PowerPoint, Access, ... -- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Which hardware/filesystem for postgresql?
Cris Carampa [EMAIL PROTECTED] writes: Christopher Browne wrote: The best performance results I have seen on Linux systems have involved the use of JFS. I found XFS to be a little slower, and it has the distinct demerit that it is not in the 'official' kernel tree yet, thereby meaning that you have to get into the pain of managing heavily-patched kernels. The kernel management issue strikes me as being a much bigger deal than the relatively minor performance difference. Thank you for your answer (it's still me, now I'm using my official usenet account :)) You may see my response from a different account, too... :-) Kernel management is not an issue for me because recent SuSE 2.4.x kernels already include XFS support by default. What worries me is stability and tolerance to power failures and other bad treatments. I have EXT2 here and I'm happy with it but since the servers would be located in client shops I wish to have something that doesn't need human input in such cases. Have you experienced (or heard) horror stories about XFS, expecially related to postgresql? Do you think JFS is better than XFS in this field too? Thanks again. Kind regards, I _would_ recommend having a journalling filesystem as opposed to ext2, from the perspective of atrocious fsck times, but I don't have a metric that I am particularly confident in by which to evaluate JFS as better than XFS, or vice-versa, from a stability perspective. Neither has been available for long enough for there to be a large body of results to report on. I used to follow ReiserFS development (I was one of the early adoptors; I have had filesystems on that FS since about 1998), and heard [though did not personally experience] horror stories. I recently had some minor data loss due to problems with ReiserFS, and would definitely NOT recommend it for a PostgreSQL partition, as its strengths don't fit with what PostgreSQL does. But I haven't been following JFS/XFS mailing lists to hear horror stories. -- output = (cbbrowne @ libertyrms.info) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] Upgrading to Solaris 9
In the last exciting episode, [EMAIL PROTECTED] (Andrew Sullivan) wrote: On Tue, Nov 11, 2003 at 01:52:26PM -0700, scott.marlowe wrote: I thought .org and .info were being run on postgresql/solaris? They are. I'd happily dump the Solaris use overboard, however, if it weren't for all the nifty hardware support it give us. It is dog-slow for handling large numbers of processes. We get around that by throwing hardware at the problem. One might choose to use Solaris for a number of reasons _other_ than it being the fastest platform for PostgreSQL: - If some applications are written in Java, Sun might be chosen as the hardware vendor because Java works there best. - If you're buying hardware in bulk, it may be a better deal to have to throw extra money at the DB server if you saved money on the other boxes. - Local familiarity might be worth something; better the devil you know than ones you don't. - Reprovisioned hardware from another project is cheaper than starting afresh. I wouldn't warrant that any of those are necessarily applicable in this particular case, but which platform runs PG fastest could readily get pushed lower on the list by these sorts of factors... -- let name=cbbrowne and tld=ntlug.org in String.concat @ [name;tld];; http://www.ntlug.org/~cbbrowne/postgresql.html How much more helpful could I be than to provide you with the appropriate e-mail address? I could engrave it on a clue-by-four and deliver it to you in Chicago, I suppose. -- Seen on Slashdot... ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] easy way to copy all DBs and users on a server
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (exciteworks hosting) transmitted: Is there an easy way to copy all DBs and users on a server to another server? I need to get an exact duplicate. How exact is exact? One notion of exact would involve stopping the database, building a tarball that contains all of the data, copying it over and extracting it. Another would involve using pg_dumpall to dump all of the databases and users and generating the SQL required to recreate it all. If you're not sure how those approaches' differences would affect your system, then it's pretty likely that pg_dumpall is the preferable answer... -- output = reverse(gro.gultn @ enworbbc) http://www3.sympatico.ca/cbbrowne/rdbms.html If you've done six impossible things this morning, why not round it off with breakfast at Milliways, the Restaurant at the End of the Universe? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] [pg 7.1.rc2] pg_restore and large tables
[EMAIL PROTECTED] (Shane Wright) writes: I have found, on 7.3.4, a _massive_ performance difference on restoring without indices - on a 25million row table from 8 hours down to 1 hour! I've found the best way is to do this... (there may be a script somewhere that automates this) - do a --schema-only restore to create the tables - manually drop the indices using psql - do a --data-only restore, also using --disable-triggers - manually recreate the indices. IIRC, it also helps to turn off fsync None of this should come as any great surprise... All but the turn off fsync part is described in the documentation tree thus: http://www.postgresql.org/docs/7.2/interactive/populate.html -- output = reverse(ofni.smrytrebil @ enworbbc) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] performance problem - 10.000 databases
Quoth [EMAIL PROTECTED] (Stephen Frost):
* Christopher Browne ([EMAIL PROTECTED]) wrote:
On one of our test servers, I set fsync=false, and a test load's
load time dropped from about 90 minutes to 3 minutes. (It was
REALLY update heavy, with huge numbers of tiny transactions.)
Which is, yes, quite spectacularly faster. But also quite
spectacularly unsafe.
I'm willing to live with the risk on a test box whose purpose is
_testing_; it's certainly not a good thing to do in production.
Would it be possible to have the effectively done for a specific
transaction? If this was done as a single large transaction could
there be an option to say don't fsync this until it's all done and
then do it all or something? Just looking for a way to get the
'best of both worlds'...
Oh, for sure, the whole thing could be invoked as one giant
transaction, which would reduce the cost dramatically.
But it diminishes the value of the benchmark for my purposes. It's
useful to measure how costly those individual transactions are.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','cbbrowne.com').
http://www.ntlug.org/~cbbrowne/oses.html
As far as Saddam Hussein being a great military strategist, he is
neither a strategist, nor is he schooled in the operational arts, nor
is he a tactician, nor is he a general, nor is he as a soldier. Other
than that, he's a great military man, I want you to know that.
-- General Norman Schwarzkopf, 2/27/91
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] performance problem - 10.000 databases
[EMAIL PROTECTED] (Jeff) writes: On 06 Nov 2003 15:21:03 +0100 Marek Florianczyk [EMAIL PROTECTED] wrote: fsync = false HOLD THE BOAT THERE BATMAN! I would *STRONGLY* advise not running with fsync=false in production as PG _CANNOT_ guaruntee data consistancy in the event of a hardware failure. It would sure suck to have a power failure screw up your nice db for the users! On one of our test servers, I set fsync=false, and a test load's load time dropped from about 90 minutes to 3 minutes. (It was REALLY update heavy, with huge numbers of tiny transactions.) Which is, yes, quite spectacularly faster. But also quite spectacularly unsafe. I'm willing to live with the risk on a test box whose purpose is _testing_; it's certainly not a good thing to do in production. -- let name=cbbrowne and tld=libertyrms.info in String.concat @ [name;tld];; http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Microsoft access verses postgresql
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Geoffrey) wrote:
I've got a client who is following my suggestion that they replace a
set of excel spreadsheets with a database solution. They are
looking at two proposals, postgresql solution or an Access solution.
The requirements will include vpn connectivity from one site to
another. It appears they will be going with the Access solution.
I've got concerns regarding this based on research I've done that
seems to indicate that Access, when used in a multi-user solution is
easily corrupted. Does anyone have any knowledge/experience with
such issues?
If the application will be lightly loaded, it may not be a big deal.
(After all, if the load is light enough, people might be able to do
the job by filling in 3x5 index cards and filing them in a box.)
But if there are a bunch of users _actively_ updating the database,
Access gets unacceptable pretty quickly.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org').
http://www.ntlug.org/~cbbrowne/rdbms.html
Linux poses a real challenge for those with a taste for late-night
hacking (and/or conversations with God). -- Matt Welsh
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] pg_clog vacuum oddness
[EMAIL PROTECTED] (Jeff) writes: On Wed, 29 Oct 2003 11:53:38 -0500 DHS Webmaster [EMAIL PROTECTED] wrote: We vacuum our working database nightly. Although this is not a 'full', we don't exclude any tables. We don't do anything with template1 (knowingly), so we do not perform any maintenance on it either. Why not go through the list in pg_database to make sure you didn't forget about any (like I did). given that template0 and 1 rarely change.. I don't see why we'd need to vacuum them template0 is probably set to 'not changeable' so that you can't even log in to it. template1 probably isn't hit a _lot_, but surely not not at all. It is accessed at such times as: - When you run createdb, data probably gets used from there to populate the new DB. - When you update user IDs, that's shared information likely to touch template1. You don't need to vacuum it often, but seldom is not quite the same as never. -- output = (cbbrowne @ libertyrms.info) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Lock!
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Oliver Elphick) transmitted: On Mon, 2003-10-13 at 11:30, Szabó Péter wrote: Hi! I have a lock problem. If i lock a record with SELECT FOR UPDATE, than i try to lock again, the process just wait until the record free. But i have to send a message to the user like 'The record is locked, try later.'. But i can't. How can i do this? You can't. Quote from the manual: So long as no deadlock situation is detected, a transaction seeking either a table-level or row-level lock will wait indefinitely for conflicting locks to be released. This means it is a bad idea for applications to hold transactions open for long periods of time (e.g., while waiting for user input). You need to rethink your application. What I have done is to read the data with SELECT. Just before changing it I do SELECT FOR UPDATE and tell the user if anything he is changing has changed since he read it in. If there has been no change, I go ahead with the UPDATE. This means that records are only locked for a very short time. Now, instead of the possibility of being locked out for a long time, there is a possibility of having to throw away some editing, but I estimate that to be less of a cost overall. Another strategy that some of our folks have been trying out is that of optimistic locking. It's an in-the-application scheme, which is arguably not totally ideal, but it has the not-inconsiderable merit that its cost is _very_ low for the common case where there is no conflict. General idea: You start by SELECTing a lock field on the data you want to update. SELECT STUFF, LOCK_FIELD FROM SOME_RELATION; When you actually do the update, you do it as: UPDATE SOME_RELATION SET THIS='This', THAT='That', LOCK_FIELD=txn_id WHERE [normal criteria] AND LOCK_FIELD='Value_I_Found_Earlier'; If the record has been updated, then LOCK_FIELD will have a different value, and this transaction fails; you'll have to do something to recover, probably a ROLLBACK, and perhaps a retry. On the other hand, if the record _hasn't_ been touched by anyone else, then this change will go through, and there wasn't any costly locking done in the DBMS. It's not new; it was presented in the IEEE Transactions on Software Engineering back in '91, and that might not be its genesis... http://www.computer.org/tse/ts1991/e0712abs.htm There's discussion of it in a Squeak Wiki... http://minnow.cc.gatech.edu/squeak/2634 It seems to have become publicly popular in the Java world, presumably due to them finding it expensive to do pessimistic locking (e.g. - as in starting out with the SELECT FOR UPDATE). -- output = (aa454 @ freenet.carleton.ca) http://cbbrowne.com/info/linux.html debugging, v: Removing the needles from the haystack. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Question about DB VACUUM
In the last exciting episode, [EMAIL PROTECTED] (Chris White (cjwhite)) wrote:
BTW, the connection I shutdown, had not read, written or deleted any
large objects. It had read and written to other tables. This is causing
me concern as I am using a thread pool to provide access to the data in
the large object table, and this seems to imply I have to close each
connection after reading/writing or deleting a large object in order for
me to truly reclaim unused space when I issue my periodic vacuum
command.
Yup, that sounds like a more-than-vaguely familiar story...
The implication may not be _precisely_ correct, but the difference
between what you're expecting and reality seems to be difficult to get
at.
I would expect that if you fired a (perhaps trivial) transaction
through each of the connections once in a while, that would clear
things up too. How to accomplish that may be the challenge...
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://www.ntlug.org/~cbbrowne/postgresql.html
With sufficient thrust, pigs fly just fine. However, this is not
necessarily a good idea. It is hard to be sure where they are going to
land, and it could be dangerous sitting under them as they fly
overhead. -- RFC 1925
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Partial indexes ... any good theoretical discussion?
In an attempt to throw the authorities off his trail, Jeff Boes [EMAIL PROTECTED] transmitted: I've only just now noticed that CREATE INDEX accepts a 'WHERE' clause. This is used to create something called a partial index. Hmm, ever being one who sees the world as made of nails when first given a hammer ... One of our tables, with a couple hundred thousand rows) has a date-column index. We expire things out of the table every day (the vast majority, but not exclusively, stuff that's a couple days old). We're frequently running queries against the table, looking for everything since this time yesterday; we hardly ever look back more than 24 hours. If I created the index as something like: CREATE INDEX ix_foo ON foo(the_date) WHERE the_date = now() - interval '24 hours'; what might I expect as the impact? Do index values older than 24 hours drop out? Or must I refresh the index from time to time (in our application, probably a couple dozen times a day)? That won't work, unfortunately. [somedatabase]=# create index partial on transaction_log(trans_on) where trans_on now() - '5 days'::interval; ERROR: functions in index predicate must be marked IMMUTABLE [somedatabase]=# You can't have now() (which is certainly *not* immutable) as part of the index. A better idea would be to have a set of several tables, one for each day, UNION ALL them together to generate a view to support queries, and use a sequence to control which table is inserted to on any given day, alongside some rules for insert/deletes. -- (format nil [EMAIL PROTECTED] aa454 freenet.carleton.ca) http://cbbrowne.com/info/linuxxian.html Waving away a cloud of smoke, I look up, and am blinded by a bright, white light. It's God. No, not Richard Stallman, or Linus Torvalds, but God. In a booming voice, He says: THIS IS A SIGN. USE LINUX, THE FREE Unix SYSTEM FOR THE 386. -- Matt Welsh ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] File Sytems Types and Os Recomendations
[EMAIL PROTECTED] (John Allgood) writes: I am looking for information on what operating systems and filesystems people are running postgresql on. I have read so much on this I decided to get some input from other people. I at first was leaning toward FreeBSD and using its filesystem. We are a linux shop and love it. What are thoughts on using ext3 or some other Linux filesystems is there really that much performance difference by using ext3 v/s BSD/FS/UFS. I did some update-heavy benchmarking recently, comparing Linux FSes, and found that of (ext3, XFS, JFS), the fastest one, by a moderate margin, was JFS. It was not unexpected that XFS and JFS were faster than ext3; what was a bit surprising was that JFS was quite a bit faster than XFS. The last I had heard, JFS was considered pretty slow as it hadn't been tuned too much; apparently that has changed. I didn't get around to testing FreeBSD with soft updates; the downside to that was, in my environment, and may also be, for you, that there was a lack of administrative familiarity. We have too many heads exploding from absorbing other things right now to be able to afford to throw in the learning curve of FreeBSD at this point in time. -- let name=cbbrowne and tld=acm.org in name ^ @ ^ tld;; http://cbbrowne.com/info/nonrdbms.html The nice thing about standards is that you have so many to choose from. Furthermore, if you do not like any of them, you can just wait for next year's model. -- Tanenbaum, Computer Networks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Does VACUUM ever free up any disk space?
[EMAIL PROTECTED] (Chris Miles) writes: I've read a lot where people recommend using VACUUM FULL to free up disk space, especially after many updates/inserts. But does a regular VACUUM (or VACUUM ANALYSE) ever free up any space? Yes, VACUUM surely can and does. [extract from recent vacuum on a fairly busy system running 7.2.4] NOTICE: --Relation pg_class-- NOTICE: Index pg_class_oid_index: Pages 11369; Tuples 173: Deleted 944. CPU 2.60s/0.45u sec elapsed 3.36 sec. NOTICE: Index pg_attribute_relid_attnam_index: Pages 12928; Tuples 4248: Deleted 1400. CPU 3.85s/0.79u sec elapsed 5.37 sec. NOTICE: Index pg_attribute_relid_attnum_index: Pages 4220; Tuples 4268: Deleted 1400. CPU 1.27s/0.27u sec elapsed 2.14 sec. NOTICE: Removed 1400 tuples in 24 pages. CPU 0.00s/0.03u sec elapsed 0.37 sec. NOTICE: Pages 295: Changed 0, Empty 0; Tup 4168: Vac 1400, Keep 0, UnUsed 1. Total CPU 5.17s/1.13u sec elapsed 7.99 sec. NOTICE: Truncated 295 -- 57 pages. CPU 0.17s/0.02u sec elapsed 0.34 sec. NOTICE: --Relation pg_class-- Apparently pg_class will need to get reindexed at some point, as there are more pages than there are live tuples, but notice that this Plain Old Vacuum truncated the table itself down from 295 pages to 57 pages. ANALYZE has nothing to do with this; ANALYZE modifies statistics in pg_statistic, which will usually have the effect of adding some garbage to that table. -- (reverse (concatenate 'string ofni.smrytrebil @ enworbbc)) http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Performance Issues
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Ravi T Ramachandra) wrote: I recently setup postgres on a Linux box with 4GB Ram and 2.5 GHz processor. We have created a database with 1.5 million rows in a table. When we try to select rows from the table, it is taking enormous time with the default configuration. It takes 2 to 3 seconds to select 1 row that has been selected with indexed columns. SELECT * FROM TABLE A WHERE COL1 = 1 AND COL2 = 'ABC'. We have created index definition as follows CREATE INDEX IDX ON A(COL1, COL2); Explain on the above statement shows it is sequential scan. The process size for the postmaster shows as 4MB (is this normal ?) The size seems normal for a database with default parameters. You might want to do some tuning of parameters in postgresql.conf to indicate the realistic size of your hardware, instead of its *very* conservative assumptions. And as for the SEQ SCAN, there are two most likely reasons: 1. If the query planner thinks that most of the rows will be returned by the query, then it would indeed be preferable to do a seq scan. Somehow, I doubt that's the case here, but this sort of thing *does* happen, and surprises people... 2. Did you ever run ANALYZE on the table to give the query planner some statistics on what actually is in the table? If there are no useful stats (in pg_statistic), then the query planner will do a seq scan because it has no reason to prefer anything else. Run VACUUM ANALYZE VERBOSE; on the database, and see if that changes things. I would surely expect it to... -- select 'cbbrowne' || '@' || 'ntlug.org'; http://cbbrowne.com/info/postgresql.html But life wasn't yes-no, on-off. Life was shades of gray, and rainbows not in the order of the spectrum. -- L. E. Modesitt, Jr., _Adiamante_ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
