[GENERAL] pgstat wait timeout
Hello everyone Our system: Windows Server 2008 R2 Foundation PostgreSQL 9.0.1 64 bits Days ago, the following message began to appear in the logs every 50 seconds: 2011-03-15 00:07:06 BRT AVISO: pgstat wait timeout I searched the mailing lists. but couldn't find any useful information. Someone already encountered this problem before? Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Continuous recovery stopping
Hi all I have a hot standby replication setup. But for no apparent reason, it stops the recovery. Here is a part of the log: 2011-02-21 11:07:03 BRT LOG: restored log file 00010009001B from archive 2011-02-21 11:07:33 BRT LOG: restored log file 00010009001C from archive 2011-02-21 11:08:03 BRT LOG: restored log file 00010009001D from archive 2011-02-21 11:11:34 BRT LOG: could not open file pg_xlog/00010009001E (log file 9, segment 30): No such file or directory 2011-02-21 11:11:34 BRT LOG: redo done at 9/1DA2E410 2011-02-21 11:11:34 BRT LOG: last completed transaction was at log time 2011-02-21 09:12:00.61-03 2011-02-21 11:12:04 BRT LOG: restored log file 00010009001D from archive 2011-02-21 11:15:04 BRT LOG: selected new timeline ID: 2 2011-02-21 11:18:04 BRT LOG: archive recovery complete Formato de parĭetro incorreto - chive. 2011-02-21 11:18:04 BRT WARNING: recovery_end_command del D:/Archive/026/failover.trigger: return code 1 2011-02-21 11:18:05 BRT LOG: database system is ready to accept connections And here is my recovery.conf file: restore_command = 'C:/program files (x86)/postgresql/9.0/bin/pg_standby.exe -s 30 -t D:/Archive/026/failover.trigger D:/archive/026 %f %p %r 2D:/archive/026/pg_standby.log' trigger_file = 'D:/Archive/026/failover.trigger' recovery_end_command = 'del D:/Archive/026/failover.trigger' What can be making it stop the recovery. Any ideas? Thanks Norberto Dellê -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Canceling a DELETE query
Hi What are the effects of canceling a DELETE command before it completes execution? Something will be deleted at all? Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WAL Archiving Stopped
Em 3/1/2011 18:39, Filip Rembiałkowski escreveu: archiver process will retry later; it never stops trying, sleep time is just longer. 2011/1/3, Norberto Dellebetode...@gmail.com: Hi all I have a PostgreSQL 9.0.1 instance, with WAL Archiving. Today, after some failed tries to archive a WAL file, it stopped trying to archive the files, but the number of logfiles in the pg_xlog directory keep growing. Any ideas of what is going on? Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Hi Filip It was taking too long to retry, a matter of hours. I had to restart the service to it start trying to archive the wal files. Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WAL Archiving Stopped
Hi all I have a PostgreSQL 9.0.1 instance, with WAL Archiving. Today, after some failed tries to archive a WAL file, it stopped trying to archive the files, but the number of logfiles in the pg_xlog directory keep growing. Any ideas of what is going on? Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Failover on Windows
Em 1/11/2010 09:00, Fujii Masao escreveu: On Fri, Oct 29, 2010 at 9:58 PM, Norberto Dellebetode...@gmail.com wrote: I'm testing a warm standby setup using PostgreSQL 9 x64 on Windows 2008 R2. What command (pg_standby? cp?) is supplied in restore_command for warm-standby? Or you are testing streaming replication + hot standby? The problem is that when I put the trigger file on the location specified in the parameter 'trigger_file' of the recovery.conf, nothing happens. No log entries, the recovery just continues as if nothing has happened. Any clues of what may be wrong? At least if you use pg_standby, you have to create the trigger file on the location specified in -t option of pg_standby. Regards, Hi Masao Yes, I'm using pg_standby in the restore_command. I thought that to specify a trigger_file in the recovery.conf file would be enough to be able to stop the recovery process. So, I ignored the -t option of the pg_standby. By specifying it, now I'm able to stop the recovery process. Thanks for your help. Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Failover on Windows
Hi all I'm testing a warm standby setup using PostgreSQL 9 x64 on Windows 2008 R2. The problem is that when I put the trigger file on the location specified in the parameter 'trigger_file' of the recovery.conf, nothing happens. No log entries, the recovery just continues as if nothing has happened. Any clues of what may be wrong? Thanks for the attention. Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trying to stop warm standby server service
Hi all I'm trying to stop the service that controls a warm standby server, but issuing a 'net stop service_name' command fails to stop the service, however the service appears as stopped on the Services Snap-In. Checking the process tree, I can see that only the pg_ctl.exe process was removed from the root of tree, other postgres.exe processes remains running. Also the file postmaster.pid is present in the data directory. The only option that worked was to kill the processes and remove the postmaster.pid manually. Any Ideas of what's happenning? System: Windows 2008 R2 x64 standard, PostgreSQL 9.0.1 32bits Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Autovacuum and pg_largeobject
Hi all I would like to know if the large object table pg_largeobject is routinely checked by the autovacuum daemon. I ask about this because I have a database in wich the pg_largeobject table is being forcibly vacuumed because it's relfrozenxid is now greater than autovacuum_freeze_max_age, and it's killing the server performance. PostgreSQL version is 8.2.14. Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Autovacuum and pg_largeobject
Em 2/7/2010 13:57, Alvaro Herrera escreveu: Excerpts from Norberto Delle's message of vie jul 02 08:10:44 -0400 2010: Hi all I would like to know if the large object table pg_largeobject is routinely checked by the autovacuum daemon. I ask about this because I have a database in wich the pg_largeobject table is being forcibly vacuumed because it's relfrozenxid is now greater than autovacuum_freeze_max_age, and it's killing the server performance. PostgreSQL version is 8.2.14. It should, as it should vacuum any other table. Perhaps all the routine autovacuums were killed because of locking issues. I admit I haven't investigated the locking behavior of pg_largeobject in particular. Would it be locked more frequently than other system catalogs? As far as I know, It's not being locked. We use it to store digitalized documents and it's getting very big. I think the server is being turned off before it can complete an vacuum pass. The server is not kept on overnight. As a palliative measure, I increased the value of autovacuum_freeze_max_age from 200.000.000 to 300.000.000. I think I will have to schedule a vacuum on that table during the weekend. Thanks for the attention Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Could not locate a valid checkpoint record
Hi all I tried to start a PostgresSQL instance this morning, but it failed. So I checked the log: 2010-06-24 09:00:28 LOG: database system was shut down at 2010-06-24 08:13:58 2010-06-24 09:00:28 LOG: record with zero length at 9/E0E9D200 2010-06-24 09:00:28 LOG: invalid primary checkpoint record 2010-06-24 09:00:28 LOG: record with zero length at 9/E0E9D1B0 2010-06-24 09:00:28 LOG: invalid secondary checkpoint record 2010-06-24 09:00:28 PANIC: could not locate a valid checkpoint record This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2010-06-24 09:00:28 LOG: startup process (PID 1644) exited with exit code 3 2010-06-24 09:00:28 LOG: aborting startup due to startup process failure 2010-06-24 09:00:29 LOG: logger shutting down I know this error requires you to restore from backup, but, unfortunatelly, we have no backup. There is any way I can recover the data? Thanks Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Could not locate a valid checkpoint record
Em 24/6/2010 11:48, Tom Lane escreveu: Norberto Dellebetode...@gmail.com writes: 2010-06-24 09:00:28 PANIC: could not locate a valid checkpoint record I know this error requires you to restore from backup, but, unfortunatelly, we have no backup. There is any way I can recover the data? pg_resetxlog should allow you to restart the database, but it's anyone's guess as to whether the data will be corrupt. A dump and reload would be a good idea. You also need to investigate what happened to your pg_xlog files and take steps to make sure it doesn't happen again. (Since this is evidently Windows, I would suggest being sure you're using a recent/supported PG release, and take a close look at what AV software you're using.) regards, tom lane Yes, this instance is running on Windows XP. I ran pg_resetxlog against the data directory and I'm reloading the dumped data in a new cluster i created. The machine is running Avira AntVir, but Avira played well with Postgres so far. What kind of data verification do you recommend? A sucessfull dump/reload is a good sign that the data is healthy? Thanks for the attention Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Idle in transaction connection
Hi all I have a Postgresql 8.2.10 install running on w2k3, and recently, or more precisely, after I upgraded from 8.2.5 to 8.2.10, some connections keep in the 'idle in transaction' state. The real problem is that even after I kill the connection subprocess, the pg_stat_activity reports that the connection is still there, and the locks too. The only thing that solves this is restarting Postgres. Any clues of what might be happening? Thanks for tha attention. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Dump/Restore Large Object OID
Gurjeet Singh wrote: Why not give it a try once? Dump and restore once and see for yourself. You'd have done that by now, but if you haven't do give it a try instead of waiting any more. You may learn a thing or two in the process... On 11/29/07, Norberto Delle [EMAIL PROTECTED] wrote: Hi all If I don't use the --oids option when dumping a database with pg_dump, can I assure that the "loid" field of the pg_largeobject table will keep it's value when restoring? Of course, I already done that. I'm using pgAdmin to make database backups\restores, without checking the "With OIDs" option and it works well for the most of the cases. But sometimes referenced large objects are not found after the restore, that's is why I'm asking.
Re: [GENERAL] Dump/Restore Large Object OID
Gurjeet Singh wrote: Why not give it a try once? Dump and restore once and see for yourself. You'd have done that by now, but if you haven't do give it a try instead of waiting any more. You may learn a thing or two in the process... On 11/29/07, *Norberto Delle* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hi all If I don't use the --oids option when dumping a database with pg_dump, can I assure that the loid field of the pg_largeobject table will keep it's value when restoring? Of course, I already done that. I'm using pgAdmin to make database backups\restores, without checking the With OIDs option and it works well for the most of the cases. But sometimes referenced large objects are not found after the restore, that's is why I'm asking. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Dump/Restore Large Object OID
Hi all If I don't use the --oids option when dumping a database with pg_dump, can I assure that the loid field of the pg_largeobject table will keep it's value when restoring? Thanks in advance Norberto ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] WAL Archiving problem
Tom Lane writes: Norberto Delle [EMAIL PROTECTED] writes: I have a PostgreSQL 8.2.4 installation running under Windows XP with WAL archiving activated. But at some point Postgres began to ask to archive a WAL segment that isn't in the pg_xlog directory. I thought that a segment that isn't succesfully archived should remain in the pg_xlog directory, or am i wrong? Do you have the postmaster log from around the time that this started happening? I'm wondering about a file rename() failing, or some such. What files do you have, exactly, in pg_xlog and pg_xlog/archive_status? It'd be useful to see their modification timestamps as well as their names. Hi all Thank you Tom, for the quick answer Here is the part of the postmaster log where something wrong happened: -- This sequence of WAL files was originated by a restore (COPY FROM stdin) 2007-08-20 09:09:40 LOG: archived transaction log file 0001000200DC 2007-08-20 09:10:27 LOG: archived transaction log file 0001000200DD 2007-08-20 09:11:07 LOG: archived transaction log file 0001000200DE 2007-08-20 09:11:33 LOG: archived transaction log file 0001000200DF 2007-08-20 09:11:38 LOG: archived transaction log file 0001000200E0 2007-08-20 09:11:42 LOG: archived transaction log file 0001000200E1 2007-08-20 09:11:46 LOG: archived transaction log file 0001000200E2 2007-08-20 09:11:50 LOG: archived transaction log file 0001000200E3 2007-08-20 09:11:53 LOG: archived transaction log file 0001000200E4 2007-08-20 09:11:57 LOG: archived transaction log file 0001000200E5 2007-08-20 09:12:01 LOG: archived transaction log file 0001000200E6 2007-08-20 09:12:09 LOG: archived transaction log file 0001000200E7 2007-08-20 09:12:20 LOG: archived transaction log file 0001000200E8 2007-08-20 09:12:21 LOG: could not receive data from client: Unknown winsock error 10061 2007-08-20 09:12:21 LOG: could not receive data from client: Unknown winsock error 10061 2007-08-20 09:12:21 LOG: unexpected EOF on client connection 2007-08-20 09:12:21 LOG: unexpected EOF on client connection 2007-08-20 09:12:21 LOG: could not receive data from client: Unknown winsock error 10061 2007-08-20 09:12:21 LOG: unexpected EOF on client connection -- Note that here the WAL file '0001000200E9' was archived (Postgres thinks it was, -- because it's not present in the backup directory) 2007-08-20 09:12:33 LOG: archived transaction log file 0001000200E9 2007-08-20 09:12:46 LOG: archived transaction log file 0001000200EA 2007-08-20 09:12:57 LOG: archived transaction log file 0001000200EB -- And here Postgres is asking to archive '0001000200E9' again 2007-08-20 09:22:29 LOG: archive command C:\Imob\IMOBBackup\bbp.exe -wal=pg_xlog\0001000200E9 failed: return code 13 2007-08-20 09:22:31 LOG: archive command C:\Imob\IMOBBackup\bbp.exe -wal=pg_xlog\0001000200E9 failed: return code 13 2007-08-20 09:22:32 LOG: archive command C:\Imob\IMOBBackup\bbp.exe -wal=pg_xlog\0001000200E9 failed: return code 13 2007-08-20 09:22:32 WARNING: transaction log file 0001000200E9 could not be archived: too many failures Looking in bbp.exe log i realized that the archive command fails because pg_xlog\0001000200E9 is not found, and looking in the pg_xlog\archive_status directory there is a file named '0001000200E9.X.ready'. More information will be difficult to obtain because a don't have direct access to the server. I hope this information helps ---(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
