Re: [BUGS] BUG #5929: ERROR: found toasted toast chunk for toast value 260340218 in pg_toast_260339342
On 16.03.2011 17:09, Kevin Grittner wrote: Tambet Matiisen wrote: Pre-live database is restored from live database dump every night. How is that done? A single pg_dump of the entire live database restored using psql? Are both database servers at the same PostgreSQL version? Yes, I use pg_dump on live server and the result is rdiff-backupped into development server. Whole SQL dump is 12G without compression and the rdiff delta is about 10-20MB every day. Then I drop pre-live database on development server and recreate it using createdb and psql. For a while development server was running 8.4 and live server 8.1. Now both are 8.4, but this shouldn't matter, as I do backup and restore via SQL. So far the errors have been in pre-live database, You're running pg_dump against a database you just restored from a pg_dump image? Hmm, yeah. This sounds rather dumb, but haven't got to that yet. Development server contains some additional databases as well, that do not exist on live server. Usually the next day error was gone. I mostly blamed badly timed backup and restore scripts, although this shouldn't result in errors. No it shouldn't -- if you're following any of the documented backup and restore techniques. I have a suspicion that you're just doing a file copy without stopping the live database or properly following the documented PITR backup and recovery techniques. No, I don't do any advanced backup tricks. Just plain pg_dump and psql. This time the error is not in pre-live database and therefore it doesn't go away. If I understand you, this sounds like corruption in the live database; nothing on the pre-live database is part of causing this problem. This would be the case when I do filesystem level copy, but I do not. The server is also running [...] Samba [...] I hope you're not trusting Samba too far. For a while we were using it in backups across our WAN, and it mangled at least one file almost every day. We had to take to running md5sum against both ends for each file to ensure we didn't get garbage (until we converted everything to use TCP communications, which have never mangled anything for us). As I said, I'm using rdiff-backup to transfer pure SQL files. Both fsync and full_page_writes are on. Good. Without those an OS or hardware crash can corrupt your database. Actually they are commented out, but I suppose this means "on". OK, I don't have UPS for this machine, but power has been stable. Current uptime is 32 days, which I bet is from the last kernel update. OK. A power outage wouldn't be too likely to matter if you have fsync and full_page_writes on. That's a relief :). Currently I blame either faulty memory or faulty software RAID driver. I can easily eliminate the memory cause by running memtest86 for few hours Is this ECC memory? If not, even a good test doesn't prove that a RAM problem didn't cause the corruption. It's not ECC memory. Now, off to buy UPS... Not a bad idea, but it doesn't sound like lack of that is likely to have caused the corruption in your live database, based on the settings you mentioned. (Assuming those settings are in use on the live server.) Checked live server, it has also fsync=on and full_page_writes=on. But it shouldn't matter, because backup of live server doesn't give any errors. It is possible, that restore of pre-live database using psql lasts so long, that backup of the same database using pg_dump is already kicking in. But again, this shouldn't matter and it doesn't explain why the last error is in another database, that hasn't changed for months. Now I have to find time to run memtest. Tambet -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5934: postgresql.conf: optional equal sign
On Thu, Mar 17, 2011 at 12:07 AM, Euler Taveira de Oliveira wrote: > Em 16-03-2011 12:25, Frederic Junod escreveu: >> >> "Error: Invalid line 502 in /etc/postgresql/8.4/main/postgresql.conf: >> »listen_addresses '*'«" >> > AFAICS it is a Debian bug not a PostgreSQL one. I already hit it once but > didn't investigate the problem. [looking at debian bugs...] It seems you > already reported the bug [1] for Debian guys. Yes, I realize that's a debian issue after reported this to this to postgresql. Please discard this bug report. Thanks, fredj > [1] http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=618583 > > > -- > Euler Taveira de Oliveira > http://www.timbira.com/ > -- Frédéric Junod Camptocamp SA -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5929: ERROR: found toasted toast chunk for toast value 260340218 in pg_toast_260339342
On 16.03.2011 22:29, Kevin Grittner wrote: Tambet Matiisen wrote: Yes, I use pg_dump on live server and the result is rdiff-backupped into development server. Whole SQL dump is 12G without compression and the rdiff delta is about 10-20MB every day. Then I drop pre-live database on development server and recreate it using createdb and psql. createdb, not initdb? I suggest you backup and delete everything in the data directory, and start with initdb, and see whether the problem still exists. If it goes away, the problem was in your shared system tables. If it persists, the problem is in your backup files, and I would try a delete and a fresh copy. If *that* fixes it you know the problem was with rdiff-backup. (Of course, keeping copies of things before the delete might provide useful forensic information.) Yes, I use createdb to recreate just one database. I doubt backup files could cause such an error, they are plain SQL files. Today I got another error, so it seems to get worse: Warning: pg_dump: WARNING: could not write block 188224 of base/2802415579/2802416218 DETAIL: Multiple failures --- write error might be permanent. pg_dump: SQL command failed pg_dump: Error message from server: ERROR: xlog flush request 200EB/9E4CD48 is not satisfied --- flushed only to CC/3F22EFB4 LINE 1: ...LECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog... ^ CONTEXT: writing block 188224 of relation base/2802415579/2802416218 pg_dump: The command was: SELECT tableoid, oid, nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, nspacl FROM pg_namespace pg_dumpall: pg_dump failed on database "hekotekerp", exiting Warning: Failed to dump pgsql cluster Strange that I have no problems actually using that database. For a while development server was running 8.4 and live server 8.1. Now both are 8.4, but this shouldn't matter, as I do backup and restore via SQL. I hope you were using the 8.4 version of pg_dump when you were in the dual-version situation. Using the earlier version of pg_dump is not guaranteed to provide a backup which can be cleanly installed on a later version. That could *possibly* be related to current problems. I used 8.1 version of pg_dump previously, but had no problems with it. Currently both are 8.4, so this is not a problem. Development server contains some additional databases as well, that do not exist on live server. So are you really using pg_dumpall or pg_dump? I'm using pg_dump on live server and pg_dumpall on development server. It's not ECC memory. Well, then there has been proven to be a non-negligible possibility of occasional random bit-flips. Seriously, next time you upgrade, make sure any database server has ECC RAM. Thanks for a tip, will do that. It is possible, that restore of pre-live database using psql lasts so long, that backup of the same database using pg_dump is already kicking in. Hmmm... You might want to do enough logging of the processes to be able to confirm or eliminate that possibility. Dumping an incompletely-restored database might generate some odd errors. Thanks Kevin for suggestions, investigating further... Tambet -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5842: Memory leak in PL/Python when taking slices of results
Excerpts from Daniel Popowich's message of mar ene 18 13:30:04 -0300 2011: > I traced the bug to PLy_result_slice() in src/pl/plpython/plpython.c. That > function calls the python API function PyList_GetSlice() and erroneously > increments the reference count before returning the result to the caller. > PyList_GetSlice returns a *new* reference, not a borrowed one, so it should > just return the object as-is. > > A patch is attached below. Thanks, applied and backpatched all the way back to 8.2. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5935: Log lotation not working for default log format
The following bug has been logged online: Bug reference: 5935 Logged by: Brad Nicholson Email address: bnichol...@hp.com PostgreSQL version: 9.0.3 Operating system: CentOS Release 5.5 Description:Log lotation not working for default log format Details: PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit CentOS release 5.5 (Final) (using the standard PG package) The logfile will not rotate if the log_filename is set to postgresql-%a.log, which is the default setting after an initdb on my platform. I have tested this by setting log_rotation_size and exceeding that threshold and manually trying to rotate the logs with SELECT pg_rotate_logfile(). Both fail to rotate the log file. Manual Rotate: [postgres@pg1 pgdata]$ ls -al logs/ total 24 drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_filename;" postgres log_filename --- postgresql-%a.log (1 row) [postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()" postgres pg_rotate_logfile --- t (1 row) [postgres@pg1 pgdata]$ ls -al logs/ total 24 drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log Size based automatic rotation: [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_rotation_size;" postgres log_rotation_size --- 1MB (1 row) [postgres@pg1 pgdata]$ ls -thl logs/ total 3.2M -rw--- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log If I switch the log_filename to something more specific like postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5935: Log lotation not working for default log format
What would you expect the new log file to be named? Your log_filename is set to postgresql-%a.log. The %a part expands to the current day of the week. If it's Thursday and you already have a file for Thursday, what would the new file name be? -- Korry > The following bug has been logged online: > > Bug reference: 5935 > Logged by: Brad Nicholson > Email address: bnichol...@hp.com > PostgreSQL version: 9.0.3 > Operating system: CentOS Release 5.5 > Description:Log lotation not working for default log format > Details: > > PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) > 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit > CentOS release 5.5 (Final) > > (using the standard PG package) > > The logfile will not rotate if the log_filename is set to postgresql-%a.log, > which is the default setting after an initdb on my platform. > > > I have tested this by setting log_rotation_size and exceeding that threshold > and manually trying to rotate the logs with SELECT pg_rotate_logfile(). > Both fail to rotate the log file. > > > Manual Rotate: > > [postgres@pg1 pgdata]$ ls -al logs/ > total 24 > drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . > drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. > -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log > [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_filename;" postgres > log_filename > --- > postgresql-%a.log > (1 row) > > [postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()" > postgres > pg_rotate_logfile > --- > t > (1 row) > > [postgres@pg1 pgdata]$ ls -al logs/ > total 24 > drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . > drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. > -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log > > > > Size based automatic rotation: > > [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_rotation_size;" postgres > log_rotation_size > --- > 1MB > (1 row) > > > > [postgres@pg1 pgdata]$ ls -thl logs/ > total 3.2M > -rw--- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log > > > > If I switch the log_filename to something more specific like > postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates. > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5935: Log lotation not working for default log format
You could appending something onto the logfile to differentiate it, but I can understand why you wouldn't want to as that would change the filename format. If this is the case, the call to pg_rotate_logfile() should return false. I'd also say that starting up and allowing a file size based rotation with a log filename that does not allow it to be rotated is also broken. Brad. > -Original Message- > From: Korry Douglas [mailto:korry.doug...@enterprisedb.com] > Sent: Thursday, March 17, 2011 4:00 PM > To: Nicholson, Brad (Toronto, ON, CA) > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #5935: Log lotation not working for default log > format > > What would you expect the new log file to be named? Your log_filename > is set to postgresql-%a.log. The %a part expands to the current day of > the week. If it's Thursday and you already have a file for Thursday, > what would the new file name be? > > -- Korry > > > The following bug has been logged online: > > > > Bug reference: 5935 > > Logged by: Brad Nicholson > > Email address: bnichol...@hp.com > > PostgreSQL version: 9.0.3 > > Operating system: CentOS Release 5.5 > > Description:Log lotation not working for default log format > > Details: > > > > PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc > (GCC) > > 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit > > CentOS release 5.5 (Final) > > > > (using the standard PG package) > > > > The logfile will not rotate if the log_filename is set to postgresql- > %a.log, > > which is the default setting after an initdb on my platform. > > > > > > I have tested this by setting log_rotation_size and exceeding that > threshold > > and manually trying to rotate the logs with SELECT > pg_rotate_logfile(). > > Both fail to rotate the log file. > > > > > > Manual Rotate: > > > > [postgres@pg1 pgdata]$ ls -al logs/ > > total 24 > > drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . > > drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. > > -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log > > [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_filename;" postgres > > log_filename > > --- > > postgresql-%a.log > > (1 row) > > > > [postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()" > > postgres > > pg_rotate_logfile > > --- > > t > > (1 row) > > > > [postgres@pg1 pgdata]$ ls -al logs/ > > total 24 > > drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . > > drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. > > -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log > > > > > > > > Size based automatic rotation: > > > > [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_rotation_size;" > postgres > > log_rotation_size > > --- > > 1MB > > (1 row) > > > > > > > > [postgres@pg1 pgdata]$ ls -thl logs/ > > total 3.2M > > -rw--- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log > > > > > > > > If I switch the log_filename to something more specific like > > postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates. > > > > -- > > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5935: Log lotation not working for default log format
> You could appending something onto the logfile to differentiate it, but I can > understand why you wouldn't want to as that would change the filename format. > > If this is the case, the call to pg_rotate_logfile() should return false. pg_rotate_logfile() doesn't actually rotate the log file, it just schedules the rotation to happen as soon as possible. So it doesn't know whether or not the rotation will actually succeed. pg_rotate_logfile() only returns FALSE if you haven't started the log collector. > I'd also say that starting up and allowing a file size based rotation with a > log filename that does not allow it to be rotated is also broken. That filename pattern *does* permit rotation, just no more frequently than every 24 hours. Consider what would happen if you set log_filename to something like pg_log.%H (where %H is replaced with the hour) and your log size exceeded the log_rotation_size within a given hour - same problem. The key is to pick a filename pattern that *can* rotate given the value you set for log_rotation_size. It might be nice if the logfile naming mechanism supported a new escape sequence that meant "replace me with the next available sequence number... %n might translate to _1, _2, _3, ...). -- Korry P.S. I'm not disagreeing with you, just explaining the code as it exists today. > > Brad. > >> -Original Message- >> From: Korry Douglas [mailto:korry.doug...@enterprisedb.com] >> Sent: Thursday, March 17, 2011 4:00 PM >> To: Nicholson, Brad (Toronto, ON, CA) >> Cc: pgsql-bugs@postgresql.org >> Subject: Re: [BUGS] BUG #5935: Log lotation not working for default log >> format >> >> What would you expect the new log file to be named? Your log_filename >> is set to postgresql-%a.log. The %a part expands to the current day of >> the week. If it's Thursday and you already have a file for Thursday, >> what would the new file name be? >> >> -- Korry >> >>> The following bug has been logged online: >>> >>> Bug reference: 5935 >>> Logged by: Brad Nicholson >>> Email address: bnichol...@hp.com >>> PostgreSQL version: 9.0.3 >>> Operating system: CentOS Release 5.5 >>> Description:Log lotation not working for default log format >>> Details: >>> >>> PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc >> (GCC) >>> 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit >>> CentOS release 5.5 (Final) >>> >>> (using the standard PG package) >>> >>> The logfile will not rotate if the log_filename is set to postgresql- >> %a.log, >>> which is the default setting after an initdb on my platform. >>> >>> >>> I have tested this by setting log_rotation_size and exceeding that >> threshold >>> and manually trying to rotate the logs with SELECT >> pg_rotate_logfile(). >>> Both fail to rotate the log file. >>> >>> >>> Manual Rotate: >>> >>> [postgres@pg1 pgdata]$ ls -al logs/ >>> total 24 >>> drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . >>> drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. >>> -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log >>> [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_filename;" postgres >>> log_filename >>> --- >>> postgresql-%a.log >>> (1 row) >>> >>> [postgres@pg1 pgdata]$ psql -p 6432 -c "SELECT pg_rotate_logfile()" >>> postgres >>> pg_rotate_logfile >>> --- >>> t >>> (1 row) >>> >>> [postgres@pg1 pgdata]$ ls -al logs/ >>> total 24 >>> drwxr-xr-x 2 postgres postgres 4096 Mar 17 13:11 . >>> drwxr-xr-x 4 postgres postgres 4096 Mar 17 13:08 .. >>> -rw--- 1 postgres postgres 150 Mar 17 13:11 postgresql-Thu.log >>> >>> >>> >>> Size based automatic rotation: >>> >>> [postgres@pg1 pgdata]$ psql -p 6432 -c "show log_rotation_size;" >> postgres >>> log_rotation_size >>> --- >>> 1MB >>> (1 row) >>> >>> >>> >>> [postgres@pg1 pgdata]$ ls -thl logs/ >>> total 3.2M >>> -rw--- 1 postgres postgres 3.2M Mar 17 13:24 postgresql-Thu.log >>> >>> >>> >>> If I switch the log_filename to something more specific like >>> postgresql-%Y-%m-%d_%H_%M_%S.log - the logfile rotates. >>> >>> -- >>> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-bugs > --- Korry Douglas Senior Database Dude EnterpriseDB Corporation The Enterprise PostgreSQL Company Phone: (804)241-4301 Mobile: (620) EDB-NERD -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
