[ADMIN] Postgressql backup/restore question
Hi all Can we do a point-in-time restore of a single database out of n databases??. -- I have 5 databases in the postgresql server. I have taken full-backup of the entire data directory (/opt/postgresql/data) and individual dumps also (for safety). then i have taken all the log files till now. Now, the server is crashed. Is there anyway to restore only 1 database out of total 5 database to particular point-in-time. (similar to Mysql) I have individual dumps of each database and all corresponding log files from that time. Can i selectively restore the databases. Can I use dump files + log files to restore the databases. - Please help me in this regards Thanks Srikanth
Re: [ADMIN] Postgressql backup/restore question
samana srikanth wrote: Can we do a point-in-time restore of a single database out of n databases??. In principle no. But you could invent workarounds such as recovering to the point where you are happy with your restored one database, and then restore the other n-1 databases from an SQL dump. -- 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] Database in use?
The users are accessing the database using PhpPgAdmin. I thought that this might happen if they closed the browser without closing logging off. I know that if you do that the browser will return to where you left it, when you open it again. I tried that with my own database, and I couldn't reproduce the condition. Carol On Mar 2, 2009, at 8:31 PM, Tom Lane wrote: Carol Walter walt...@indiana.edu writes: I'm not sure I understand. If there is a lag time between when someone exits the database and when the database knows that no one is still in it, then that shouldn't be the problem here. I tried a number of time over the course of 24 hours to rename the database and it always reported that the database was in use. No one was logged in but me, but the database still reported that the database was in use. Hmph. Are you sure nothing was connected? One thing that frequently trips people up here is uncommitted prepared transactions --- look in pg_prepared_xacts. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- 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] Database in use?
I'm using psql from the command line. Doing an ALTER DATABASE command. Carol On Mar 3, 2009, at 2:36 AM, Julius Tuskenis wrote: Hello, Carol. how exactly are you trying to change the DB name? In console or using some management tool like pgAdmin? Carol Walter rašė: Periodically, my databases will give me a message that says that someone is using the database when it appears that no one is. What causes this? What can I do about it? Thanks, Carol -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- 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] getting 'full' names of functions?
Hello Ashish, Hello Tom, thank you very much for your quick and helpful replies - I really appreciate that. May be this will help you: Easier is just select oid::regprocedure from pg_proc where whatever I guess I'll go with the very nifty type cast suggested by Tom though as this covers aggregate functions as well. This possibility should be mentioned in the docs - together with the system information functions http://www.postgresql.org/docs/8.3/interactive/functions-info.html Too bad that this function syntax isn't part of information_schema. Thank you very much to both of you. Peter -- Computer Bild Tarifsieger! GMX FreeDSL - Telefonanschluss + DSL für nur 17,95 ¿/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a -- 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] Backing up postgresql database
Jakov Sosic jakov.so...@srce.hr wrote: How do you mean, do fewer updates? Well, without knowing the application it's hard for me to say; but, as a guess, perhaps the application could be modified to accumulate, say, a minute's worth of data and update it in summary, rather than updating each sample once per second. That would leave you vulnerable to losing up to a minute's worth of data, but would cut the rate of WAL generation to less than 2% of its current rate. And what do you mean by keeping WAL's for less time? Do base backups more frequently. If you need to keep more than two base backup's worth, only keep snapshots of older backups -- just the base and enough WAL files to cover the range specified in the *.backup file generated by the start and stop functions run during the base backup. I've read about gziping WAL's, and I will do it offcourse, but that only makes problem a little smaller, doesn't solve it :) Well, cutting the scope of a problem by two thirds is sometimes enough. If you combine that with the summary updates mentioned above, you might reduce the space needed to store WAL files to less than 1% of current requirements. Judicious use of the snapshot technique could let you keep a few snapshots going back 90 days and only require a small fraction of 1% of what you're thinking it will take based on current numbers. -Kevin -- 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] Database in use?
That's interesting and is probably the answer to my question as to why this happens. Thanks, Nick. What it doesn't explain is why I was able to connect to a test database using PhpPgAdmin and close the browser, without logging out, and I was able to rename my test database. I couldn't reproduce the behavior. I wonder if it is a property of the particular browser or desktop platform. I was using Firefox on a Mac. The users use Windows. I'm not sure which browser they are using. I also still want to know if there is a way to gracefully break that persistent connection on the server side. Using a PHP front end over the web, I don't always know who has a browser open. Thanks, Carol On Mar 4, 2009, at 9:36 AM, Hajek, Nick wrote: The users are accessing the database using PhpPgAdmin. I thought that this might happen if they closed the browser without closing logging off. I know that if you do that the browser will return to where you left it, when you open it again. I tried that with my own database, and I couldn't reproduce the condition. Carol A persistent connection is often used with PHP. With this, PHP will maintain the connection after a browser is closed or a user is logged off so that the next time a db connection is required, the response time is better. To break any persistent connections would require restarting the browser. Nick Hajek -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] standby waiting for what?
Testing pg_standby in 8.3.6. I've gotten this standby into some sort of bind. It seems like it may be waiting for some WAL. How can I tell what it is waiting on? I don't really know how this works, so I may -- 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] standby waiting for what?
On Wed, 2009-03-04 at 15:06 -0500, Ray Stell wrote: Testing pg_standby in 8.3.6. I've gotten this standby into some sort of bind. It seems like it may be waiting for some WAL. How can I tell what it is waiting on? I don't really know how this works, so I may Looks like you were cut off a bit. What do the logs say and your ps output on the standby? Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] standby waiting for what?
On Wed, Mar 04, 2009 at 03:06:12PM -0500, Ray Stell wrote: Testing pg_standby in 8.3.6. I've gotten this standby into some sort of bind. It seems like it may be waiting for some WAL. How can I tell what it is waiting on? I don't really know how this works, so I may say something silly. The standby log says: ,2512,,2009-03-04 12:23:01.483 EST,49aeb8f5.9d0,1,2009-03-04 12:23:01 EST,0, LOG: database system was interrupted; last known up at 2009-03-04 12:20:29 EST ,2512,,2009-03-04 12:23:01.483 EST,49aeb8f5.9d0,2,2009-03-04 12:23:01 EST,0, LOG: starting archive recovery ,2512,,2009-03-04 12:23:01.484 EST,49aeb8f5.9d0,3,2009-03-04 12:23:01 EST,0, LOG: restore_command = '/usr/local/pgsql/bin/pg_standby /data/pgsql/wals/alerts_oamp %f %p %r /home/postgresql/log/alerts_oamp/recovery.log' alerts_oamp]$ cat postmaster.pid 2510 /data/pgsql/alerts_oamp 5498001 4194312 alerts_oamp]$ ps -ef | grep 1005 1005 903 901 0 10:10 ?00:00:00 sshd: postgre...@pts/0 1005 904 903 0 10:10 pts/000:00:00 -bash 1005 1016 1013 0 10:21 ?00:00:00 sshd: postgre...@pts/1 1005 1017 1016 0 10:21 pts/100:00:00 -bash 1005 2510 1 0 12:23 pts/000:00:00 /usr/local/pgsql836/bin/postgres -D /data/pgsql/alerts_oamp 1005 2511 2510 0 12:23 ?00:00:00 postgres: logger process 1005 2512 2510 0 12:23 ?00:00:00 postgres: startup process 1005 2520 2512 0 12:23 ?00:00:00 sh -c /usr/local/pgsql/bin/pg_standby /data/pgsql/wals/alerts_oamp 0002001C.00512178.backup pg_xlog/RECOVERYHISTORY /home/postgresql/log/alerts_oamp/recovery.log 1005 2521 2520 0 12:23 ?00:00:00 /usr/local/pgsql/bin/pg_standby /data/pgsql/wals/alerts_oamp 0002001C.00512178.backup pg_xlog/RECOVERYHISTORY 1005 2615 1017 0 12:27 pts/100:00:00 tail -f alerts_oamp-2009-03-04_122301.log 1005 3271 904 0 15:11 pts/000:00:00 ps -ef 1005 3272 904 0 15:11 pts/000:00:00 grep 1005 alerts_oamp]$ ls -l /data/pgsql/wals/alerts_oamp/ total 114828 -rw--- 1 postgresql postgresql 16777216 Mar 4 11:28 0002001A -rw--- 1 postgresql postgresql 16777216 Mar 4 11:29 0002001B -rw--- 1 postgresql postgresql 16777216 Mar 4 12:24 0002001C -rw--- 1 postgresql postgresql 16777216 Mar 4 12:25 0002001D -rw--- 1 postgresql postgresql 16777216 Mar 4 12:26 0002001E -rw--- 1 postgresql postgresql 16777216 Mar 4 14:45 0002001F -rw--- 1 postgresql postgresql 16777216 Mar 4 14:45 00020020 any ideas what this guy is hurt by? -- 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] Postgressql backup/restore question
On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote: samana srikanth wrote: Can we do a point-in-time restore of a single database out of n databases??. In principle no. But you could invent workarounds such as recovering to the point where you are happy with your restored one database, and then restore the other n-1 databases from an SQL dump. It is possible, but we just don't currently support it. My submission on rmgr plugins would have provided this feature though it was rejected as not wanted. I have code hooks required to do this, if people want to contact me off-list. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] getting 'full' names of functions?
Hello Ashish, Hello Tom, before I reinvent the wheel I'd like to know whether there's a shortcut for getting the 'full' name (incl. argtypes) of the functions within a database in order to REVOKE priviliges on them given to certain users. I combined your suggestions into this query I'll be using for now: SELECT DISTINCT n.nspname || '.' || p.oid::regprocedure::text FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_function_is_visible(p.oid) AND nspname !~* '^pg_' AND nspname != 'information_schema'; Thank you very much again, Peter -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger01 -- 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] standby waiting for what?
On Wed, 2009-03-04 at 15:14 -0500, Ray Stell wrote: On Wed, Mar 04, 2009 at 03:06:12PM -0500, Ray Stell wrote: Testing pg_standby in 8.3.6. I've gotten this standby into some sort of bind. It seems like it may be waiting for some WAL. How can I tell what it is waiting on? I don't really know how this works, so I may say something silly. The standby log says: ,2512,,2009-03-04 12:23:01.483 EST,49aeb8f5.9d0,1,2009-03-04 12:23:01 EST,0, LOG: database system was interrupted; last known up at 2009-03-04 12:20:29 EST ,2512,,2009-03-04 12:23:01.483 EST,49aeb8f5.9d0,2,2009-03-04 12:23:01 EST,0, LOG: starting archive recovery ,2512,,2009-03-04 12:23:01.484 EST,49aeb8f5.9d0,3,2009-03-04 12:23:01 EST,0, LOG: restore_command = '/usr/local/pgsql/bin/pg_standby /data/pgsql/wals/alerts_oamp %f %p %r /home/postgresql/log/alerts_oamp/recovery.log' You've set archive_timeout? http://developer.postgresql.org/pgdocs/postgres/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] standby waiting for what?
On Wed, Mar 04, 2009 at 08:31:16PM +, Simon Riggs wrote: You've set archive_timeout? no, but new WAL files seem to be getting created and replicated to the standby, just the pg_standby command seems snagged on something. I probably have done something dumb, ready, fire, aim. I'm more interested in how to analyze the state. Thanks. -- 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] Postgressql backup/restore question
Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote: samana srikanth wrote: Can we do a point-in-time restore of a single database out of n databases??. In principle no. But you could invent workarounds such as recovering to the point where you are happy with your restored one database, and then restore the other n-1 databases from an SQL dump. It is possible, but we just don't currently support it. It's not as easy as all that. What will you do with updates to shared catalogs? regards, tom lane -- 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] getting 'full' names of functions?
jan-peter.seif...@gmx.de writes: I combined your suggestions into this query I'll be using for now: SELECT DISTINCT n.nspname || '.' || p.oid::regprocedure::text FROM This is flat *wrong*, as you'll soon find if you are working with functions in more than one schema. regprocedure already puts a schema qualification on the name if one is needed. regards, tom lane -- 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] Postgressql backup/restore question
On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-03-04 at 15:59 +0200, Peter Eisentraut wrote: samana srikanth wrote: Can we do a point-in-time restore of a single database out of n databases??. In principle no. But you could invent workarounds such as recovering to the point where you are happy with your restored one database, and then restore the other n-1 databases from an SQL dump. It is possible, but we just don't currently support it. It's not as easy as all that. What will you do with updates to shared catalogs? Apply them. So: its possible to do shared catalogs plus a subset of other databases. I was assuming that updates to shared catalogs were small overall. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] postgres: stats collector process
Hello I want to see statistics about the use of my postgresql 8.3 in Ubuntu 8.04.1 (Hardy) Server. The package installed is http://packages.ubuntu.com/hardy/postgresql http://packages.ubuntu.com/hardy/postgresql The statistic in this version of postgres are by default enabled, but I didn't see that the process postgres: stats collector process as say in http://www.postgresql.org/docs/8.3/static/monitoring-ps.html http://www.postgresql.org/docs/8.3/s...toring-ps.html is running in my server. Thanks in advanced. VI Conferencia Internacional de Energía Renovable, Ahorro de Energía y Educación Energética 9 - 12 de Junio 2009, Palacio de las Convenciones ...Por una cultura energética sustentable www.ciercuba.com
Re: [ADMIN] standby waiting for what?
For some reason it is looking for 0002001C.00512178.backup file which is not the WAL file. Are you sure that you made initial recovery properly? Yauheni Labko (Eugene Lobko) Junior System Administrator Chapdelaine Co. (212)208-9150 On Wednesday 04 March 2009 03:14:51 pm Ray Stell wrote: On Wed, Mar 04, 2009 at 03:06:12PM -0500, Ray Stell wrote: Testing pg_standby in 8.3.6. I've gotten this standby into some sort of bind. It seems like it may be waiting for some WAL. How can I tell what it is waiting on? I don't really know how this works, so I may say something silly. The standby log says: ,2512,,2009-03-04 12:23:01.483 EST,49aeb8f5.9d0,1,2009-03-04 12:23:01 EST,0, LOG: database system was interrupted; last known up at 2009-03-04 12:20:29 EST ,2512,,2009-03-04 12:23:01.483 EST,49aeb8f5.9d0,2,2009-03-04 12:23:01 EST,0, LOG: starting archive recovery ,2512,,2009-03-04 12:23:01.484 EST,49aeb8f5.9d0,3,2009-03-04 12:23:01 EST,0, LOG: restore_command = '/usr/local/pgsql/bin/pg_standby /data/pgsql/wals/alerts_oamp %f %p %r /home/postgresql/log/alerts_oamp/recovery.log' alerts_oamp]$ cat postmaster.pid 2510 /data/pgsql/alerts_oamp 5498001 4194312 alerts_oamp]$ ps -ef | grep 1005 1005 903 901 0 10:10 ?00:00:00 sshd: postgre...@pts/0 1005 904 903 0 10:10 pts/000:00:00 -bash 1005 1016 1013 0 10:21 ?00:00:00 sshd: postgre...@pts/1 1005 1017 1016 0 10:21 pts/100:00:00 -bash 1005 2510 1 0 12:23 pts/000:00:00 /usr/local/pgsql836/bin/postgres -D /data/pgsql/alerts_oamp 1005 2511 2510 0 12:23 ?00:00:00 postgres: logger process 1005 2512 2510 0 12:23 ?00:00:00 postgres: startup process 1005 2520 2512 0 12:23 ?00:00:00 sh -c /usr/local/pgsql/bin/pg_standby /data/pgsql/wals/alerts_oamp 0002001C.00512178.backup pg_xlog/RECOVERYHISTORY /home/postgresql/log/alerts_oamp/recovery.log 1005 2521 2520 0 12:23 ?00:00:00 /usr/local/pgsql/bin/pg_standby /data/pgsql/wals/alerts_oamp 0002001C.00512178.backup pg_xlog/RECOVERYHISTORY 1005 2615 1017 0 12:27 pts/100:00:00 tail -f alerts_oamp-2009-03-04_122301.log 1005 3271 904 0 15:11 pts/000:00:00 ps -ef 1005 3272 904 0 15:11 pts/000:00:00 grep 1005 alerts_oamp]$ ls -l /data/pgsql/wals/alerts_oamp/ total 114828 -rw--- 1 postgresql postgresql 16777216 Mar 4 11:28 0002001A -rw--- 1 postgresql postgresql 16777216 Mar 4 11:29 0002001B -rw--- 1 postgresql postgresql 16777216 Mar 4 12:24 0002001C -rw--- 1 postgresql postgresql 16777216 Mar 4 12:25 0002001D -rw--- 1 postgresql postgresql 16777216 Mar 4 12:26 0002001E -rw--- 1 postgresql postgresql 16777216 Mar 4 14:45 0002001F -rw--- 1 postgresql postgresql 16777216 Mar 4 14:45 00020020 any ideas what this guy is hurt by? -- 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] Postgressql backup/restore question
Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: It's not as easy as all that. What will you do with updates to shared catalogs? Apply them. ... which leaves your other databases in inconsistent states. regards, tom lane -- 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] standby waiting for what?
On Wed, Mar 04, 2009 at 03:41:06PM -0500, Yauheni Labko wrote: For some reason it is looking for 0002001C.00512178.backup file which is not the WAL file. Are you sure that you made initial recovery properly? I could have fouled this in any number of ways. Like I said I'm trying to understand how to analyze the situation and maybe learn something. OK, so my recovery.conf is set like this: restore_command='/usr/local/pgsql/bin/pg_standby /data/pgsql/wals/alerts_oamp %f %p %r /home/postgresql/log/alerts_oamp/recovery.log' So, the %f arg sent to the pg_standby command has a value of 0002001C.00512178.backup, right? Is that wrong? If so, where could that have come from or how could I have trashed the thing. I love fishing. -- 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] Database in use?
This has happened or is happening to me again, only this time, it's a database that I just created. I restored another database into a test database. One of the tables is empty. I want to drop the test database and create a new one. When I try to drop the test database, I get this error - template1=# drop database km_tezt; ERROR: database km_tezt is being accessed by other users template1=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -+-+--+---+-- (0 rows) This database is one that I created just a few hours ago just to test some things myself. There can be no one in it except me. I completely closed out of the database, closed out of the system, and closed my terminal session. Still I get the error. This time the database has not been accessed through PHP at all. Carol On Mar 2, 2009, at 8:31 PM, Tom Lane wrote: Carol Walter walt...@indiana.edu writes: I'm not sure I understand. If there is a lag time between when someone exits the database and when the database knows that no one is still in it, then that shouldn't be the problem here. I tried a number of time over the course of 24 hours to rename the database and it always reported that the database was in use. No one was logged in but me, but the database still reported that the database was in use. Hmph. Are you sure nothing was connected? One thing that frequently trips people up here is uncommitted prepared transactions --- look in pg_prepared_xacts. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- 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] Database in use?
Have you got any copies of psql or tools like pgadmin open. I've been caught out by this. try select * from pg_stat_activity it should tell you what connections are open on the table (look at the datname column) Carol Walter wrote: This has happened or is happening to me again, only this time, it's a database that I just created. I restored another database into a test database. One of the tables is empty. I want to drop the test database and create a new one. When I try to drop the test database, I get this error - template1=# drop database km_tezt; ERROR: database km_tezt is being accessed by other users template1=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -+-+--+---+-- (0 rows) This database is one that I created just a few hours ago just to test some things myself. There can be no one in it except me. I completely closed out of the database, closed out of the system, and closed my terminal session. Still I get the error. This time the database has not been accessed through PHP at all. Carol On Mar 2, 2009, at 8:31 PM, Tom Lane wrote: Carol Walter walt...@indiana.edu writes: I'm not sure I understand. If there is a lag time between when someone exits the database and when the database knows that no one is still in it, then that shouldn't be the problem here. I tried a number of time over the course of 24 hours to rename the database and it always reported that the database was in use. No one was logged in but me, but the database still reported that the database was in use. Hmph. Are you sure nothing was connected? One thing that frequently trips people up here is uncommitted prepared transactions --- look in pg_prepared_xacts. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- 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] Database in use?
Carol Walter walt...@indiana.edu writes: This has happened or is happening to me again, only this time, it's a database that I just created. I restored another database into a test database. One of the tables is empty. I want to drop the test database and create a new one. When I try to drop the test database, I get this error - template1=# drop database km_tezt; ERROR: database km_tezt is being accessed by other users template1=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -+-+--+---+-- (0 rows) Nothing in pg_stat_activity either? regards, tom lane -- 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] Database in use?
On Wed, Mar 4, 2009 at 2:40 PM, Carol Walter walt...@indiana.edu wrote: This has happened or is happening to me again, only this time, it's a database that I just created. I restored another database into a test database. One of the tables is empty. I want to drop the test database and create a new one. When I try to drop the test database, I get this error - template1=# drop database km_tezt; ERROR: database km_tezt is being accessed by other users template1=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -+-+--+---+-- (0 rows) What does select * from pg_stat_activity say about this db? This database is one that I created just a few hours ago just to test some things myself. There can be no one in it except me. I completely closed Correct me if I'm wrong, but isn't that enough ((just you) to cause the drop database to fail? -- 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] Database in use?
I had forgotten that I had used Aqua Data Studio to draw ERD's for this test database. Even though, I closed it, it still had the database open. When I reopened it, and did a disconnect, if allowed me to drop the database. Thanks, Carol On Mar 4, 2009, at 4:45 PM, John Lister wrote: Have you got any copies of psql or tools like pgadmin open. I've been caught out by this. try select * from pg_stat_activity it should tell you what connections are open on the table (look at the datname column) Carol Walter wrote: This has happened or is happening to me again, only this time, it's a database that I just created. I restored another database into a test database. One of the tables is empty. I want to drop the test database and create a new one. When I try to drop the test database, I get this error - template1=# drop database km_tezt; ERROR: database km_tezt is being accessed by other users template1=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -+-+--+---+-- (0 rows) This database is one that I created just a few hours ago just to test some things myself. There can be no one in it except me. I completely closed out of the database, closed out of the system, and closed my terminal session. Still I get the error. This time the database has not been accessed through PHP at all. Carol On Mar 2, 2009, at 8:31 PM, Tom Lane wrote: Carol Walter walt...@indiana.edu writes: I'm not sure I understand. If there is a lag time between when someone exits the database and when the database knows that no one is still in it, then that shouldn't be the problem here. I tried a number of time over the course of 24 hours to rename the database and it always reported that the database was in use. No one was logged in but me, but the database still reported that the database was in use. Hmph. Are you sure nothing was connected? One thing that frequently trips people up here is uncommitted prepared transactions --- look in pg_prepared_xacts. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- 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] standby waiting for what?
No. %f is the WAL filename which is needed by the server to start recovery. 0002001C.00512178.backup will give you start and end of WAL segment, the WAL filename containing this segment and your label to identify where it might be. That's why I asked you about your backup. What is the archive_command for primary server? Yauheni Labko (Eugene Lobko) Junior System Administrator Chapdelaine Co. (212)208-9150 On Wednesday 04 March 2009 04:35:00 pm you wrote: On Wed, Mar 04, 2009 at 03:41:06PM -0500, Yauheni Labko wrote: For some reason it is looking for 0002001C.00512178.backup file which is not the WAL file. Are you sure that you made initial recovery properly? I could have fouled this in any number of ways. Like I said I'm trying to understand how to analyze the situation and maybe learn something. OK, so my recovery.conf is set like this: restore_command='/usr/local/pgsql/bin/pg_standby /data/pgsql/wals/alerts_oamp %f %p %r /home/postgresql/log/alerts_oamp/recovery.log' So, the %f arg sent to the pg_standby command has a value of 0002001C.00512178.backup, right? Is that wrong? If so, where could that have come from or how could I have trashed the thing. I love fishing. -- 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] Database in use?
On Mar 4, 2009, at 4:53 PM, Scott Marlowe wrote: On Wed, Mar 4, 2009 at 2:40 PM, Carol Walter walt...@indiana.edu wrote: This has happened or is happening to me again, only this time, it's a database that I just created. I restored another database into a test database. One of the tables is empty. I want to drop the test database and create a new one. When I try to drop the test database, I get this error - template1=# drop database km_tezt; ERROR: database km_tezt is being accessed by other users template1=# select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -+-+--+---+-- (0 rows) What does select * from pg_stat_activity say about this db? This database is one that I created just a few hours ago just to test some things myself. There can be no one in it except me. I completely closed Correct me if I'm wrong, but isn't that enough ((just you) to cause the drop database to fail? Yes, of course, just me is enough for it to fail. I thought that I wasn't in it, either. That's what I meant. As it turned out, I was in it, having used Aqua Data Studio to draw an ERD. Everything worked after I got back into Aqua Data and disconnected from the database. I was able to drop the database. I still have my original question though. Is there a graceful way to close all connections to a database from the server side. I'm in an academic environment and I can count on people not writing bad code. I don't want it to close down in the midst of someone's query, but I would like to be able to disconnect users if the aren't running queries. We have regular system maintenance on Friday evenings. Currently, the only way I have to break these connections is to stop the database. That stops it for all databases, when only one may be the problem. I would like to be able to do this when there is a problem with a database and not have to wait until my scheduled maintenance. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- 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] Postgressql backup/restore question
On Wed, 2009-03-04 at 16:27 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: It's not as easy as all that. What will you do with updates to shared catalogs? Apply them. ... which leaves your other databases in inconsistent states. Which is not a problem if you didn't want to restore them in the first place. You might complain that we would need safeguards to protect people from trying to access non-restored databases and then failing to understand why they aren't there. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Postgressql backup/restore question
Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-03-04 at 16:27 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-03-04 at 15:37 -0500, Tom Lane wrote: It's not as easy as all that. What will you do with updates to shared catalogs? Apply them. ... which leaves your other databases in inconsistent states. Which is not a problem if you didn't want to restore them in the first place. Only for small values of not a problem. For example, you might have pg_shdepend entries saying that various objects in some other database depend on some role. If you then want to drop the role, you can't; and you can't attach to the other database to get rid of the objects, since it's not there. You'd also still have pg_database entries pointing at the not-there databases. This behavior might be all right for an emergency recovery kind of tool, but I can't see us considering it a supported feature. The larger point though is that I suspect what the OP really is looking for is restore just this one database into my existing cluster, without breaking the other databases that are already in it. There is zero chance of ever doing that with a WAL-based backup --- transaction ID inconsistencies would break it, even without considering the contents of shared catalogs. regards, tom lane -- 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] standby waiting for what?
Btw i think you may remove %r from the restore command. Yauheni Labko (Eugene Lobko) Junior System Administrator Chapdelaine Co. (212)208-9150 -- 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] Database in use?
Scott Marlowe scott.marl...@gmail.com writes: On Wed, Mar 4, 2009 at 2:40 PM, Carol Walter walt...@indiana.edu wrote: This database is one that I created just a few hours ago just to test some things myself. There can be no one in it except me. Correct me if I'm wrong, but isn't that enough ((just you) to cause the drop database to fail? If you try to drop the current session's database, you get a different error message. regression=# drop database regression; ERROR: cannot drop the currently open database regards, tom lane -- 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] warm standby, pg_standby, invalid checkpoint record
does a file named 000100CD exist anywhere on your disk?
-lee
On Fri, Feb 27, 2009 at 2:47 AM, Brad Wiemerslage wieme...@yahoo.com wrote:
I'm attempting to get warm standby up and running with a pair of servers
running ubuntu 8.04 and postgresql 8.3. Been following the docs:
http://www.postgresql.org/docs/8.3/static/warm-standby.html
http://www.postgresql.org/docs/current/static/pgstandby.html
Also, basically following the ideas here in this blog post:
http://scale-out-blog.blogspot.com/2009/02/simple-ha-with-postgresql-point-in-time.html
I've customized the original script he refers to in the article, which is
here in its entirety for reference:
https://s3.amazonaws.com/extras.continuent.com/standby.sh
Here is the meat of my customized script, which runs on the standby. The
postgresql server on the standby is stopped first.
start_backup=SELECT pg_start_backup('my_backup');
stop_backup=SELECT pg_stop_backup();
echo $start_backup | $psql -h$PRIMARY -U myuser -d mydb -e
rsync --delete -avz -e ssh -i /path/to/key myu...@$primary:$PG_DATA/
$PG_DATA
echo $stop_backup | $psql -h $PRIMARY -U myuser -d mydb -e
The files seem to copied over to the standby machine just fine. Success is
reported with respect to the backup commands. Permissions seem fine.
Next, there are some steps which blow out some files. As I understand it,
you no longer need the files on the standby that were in pg_xlog on the
primary.
rm -f $PG_DATA/recovery.*
rm -f $PG_DATA/8.3/main/logfile
rm -f $PG_DATA/8.3/main/postmaster.pid
rm -f $PG_DATA/8.3/main/pg_xlog/0*
rm -f $PG_DATA/8.3/main/pg_xlog/archive_status/0*
This step seems to work fine.
Then, the archives are pulled. They are pulled to /mnt/postgresql_archives
with this command:
rsync --delete -avz -e ssh -i /path/to/key myu...@$primary:$PG_ARCHIVES/
$PG_ARCHIVES
Everything looks good. I end up with an up to date list of WAL files in
/mnt/postgresql_archives on the standby. Here is a listing:
r...@standby:/mnt/postgresql_archives# ls
total 688996
drwxr-xr-x 2 postgres postgres 4096 2009-02-27 01:13 .
drwxr-xr-x 14 root root 4096 2009-02-27 01:17 ..
-rw-rw 1 postgres postgres 16777216 2009-02-27 00:19
000100CB
-rw-rw 1 postgres postgres 16777216 2009-02-27 00:29
000100CC
-rw-rw 1 postgres postgres 16777216 2009-02-27 00:38
000100CD
-rw-rw 1 postgres postgres 245 2009-02-27 00:38
000100CD.0020.backup
-rw-rw 1 postgres postgres 16777216 2009-02-27 00:48
000100CE
-rw-rw 1 postgres postgres 16777216 2009-02-27 00:54
000100CF
-rw-rw 1 postgres postgres 16777216 2009-02-27 00:58
000100D0
-rw-rw 1 postgres postgres 16777216 2009-02-27 01:01
000100D1
-rw-rw 1 postgres postgres 16777216 2009-02-27 01:03
000100D2
-rw-rw 1 postgres postgres 16777216 2009-02-27 01:13
000100D3
Then, the recovery.conf is put in place. I've tried two different versions,
which end up giving me the same error. Here are the two different versions.
#1: restore_command = '/usr/lib/postgresql/8.3/bin/pg_standby -c -d -s 2 -t
/mnt/postgresql_archives/pgsql.trigger /mnt/postgresql_archives %f %p
/mnt/postgresql_archives/standby.log 12'
#2: restore_command = 'cp /mnt/server/archivedir/%f %p'
I don't believe that #2 is suitable for warm standby, but just tried it to
debug after #1 wouldn't work. Now, I try to start up the server. For it to
work in standby mode, additional archive files will be pulled from the
primary machine on a periodic basis. I'm using this command, which deletes
them on the primary when they are no longer necessary. It also seems to work
fine.
rsync -avz -e ssh -i /path/to/key myu...@$primary:$PG_ARCHIVES/ $PG_ARCHIVES
I guess I'm a little confused about exactly what is happening here when the
server comes up, but here is the error message I'm getting. It seems to be
looking for the files in pg_pxlog, which is cleared out. So, the error makes
sense. But isn't it supposed to be looking in /mnt/postgresql_archives per
the restore_command(s)? The files are available there.
2009-02-27 01:26:52.867 EST,,,7422,,49a787ac.1cfe,2,,2009-02-27 01:26:52
EST,,0,LOG,58P01,could not open file pg_xlog/000100CD
(log file 0, segment 20
5): No such file or directory
2009-02-27 01:26:52.867 EST,,,7422,,49a787ac.1cfe,3,,2009-02-27 01:26:52
EST,,0,LOG,0,invalid checkpoint record
2009-02-27 01:26:52.867 EST,,,7422,,49a787ac.1cfe,4,,2009-02-27 01:26:52
EST,,0,PANIC,XX000,could not locate required checkpoint record,,If you are
not restoring from a
backup, try removing the file
/var/lib/postgresql/8.3/main/backup_label.,,
2009-02-27 01:26:52.868
Re: [ADMIN] getting 'full' names of functions?
jan-peter.seif...@gmx.de wrote: Hello Ashish, Hello Tom, thank you very much for your quick and helpful replies - I really appreciate that. May be this will help you: Easier is just select oid::regprocedure from pg_proc where whatever I guess I'll go with the very nifty type cast suggested by Tom though as this covers aggregate functions as well. This possibility should be mentioned in the docs - together with the system information functions http://www.postgresql.org/docs/8.3/interactive/functions-info.html Too bad that this function syntax isn't part of information_schema. Thank you very much to both of you. note that this method doesn't produce a complete function signature. the precision and scale of numerics are not included in the output. hopefully, that won't matter for your needs. cheers, raf -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
