Re: [GENERAL] Database Recovery from Corrupted Dump or Raw database table file.
On 07/11/2016 13:44, Vick Khera wrote: On Mon, Nov 7, 2016 at 8:23 AM, Howard News wrote: pg_restore: executing SEQUENCE SET example_seq pg_restore: processing data for table example_table pg_restore: [compress_io] ** crash ** What crashes? the pg_restore process or the backend server? The pg_restore crashes. For example if I run the "pg_restore -f example.sql backup" then it will fall over when it tries to restore one of the corrupted tables. Presumably the backup itself is corrupted. The "pg_restore --list" works fine, so I can run a selective restore by commenting out the dodgy tables using the "pg_restore -L" option. Regarding the postgres ids, these appear to be listed in the pg_restore -L so I can find out which data file the tables refer to, but I have not found out how to restore a single one of these files to a usable or readable state yet. -- 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] Database Recovery from Corrupted Dump or Raw database table file.
On Mon, Nov 7, 2016 at 8:23 AM, Howard News wrote: > pg_restore: executing SEQUENCE SET example_seq > pg_restore: processing data for table example_table > pg_restore: [compress_io] > > ** crash ** What crashes? the pg_restore process or the backend server? -- 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] Database Recovery from Corrupted Dump or Raw database table file.
On 07/11/2016 13:12, Albe Laurenz wrote: Howard News wrote: I have a raid catastrophe which has effectively blitzed a cluster data directory. I have several pg_dump backups but these will not restore cleanly. I assume the disk has been failing for some time and the backups are of the corrupted database. Using a selective pg_restore on the dumps, I have restored 2/3rds of the data but some tables I cannot recover directly, so I would like to see if it is possible to examine the dumps (they are in compressed format) to see if there are any rows which are recoverable. I do not know how or if it is even possible to pick out rows from an individual table, when in all likelyhood the file itself is corrupted. I also have some parts of the data directory, so the tables may be accessible if I know the ID of the corrupt datatables. Is the ID listed in the pg_dump --list file? And can the data be extracted from the raw data files without running a cluster. I am unsure if there is enough data to actually start a cluster. A backup created with pg_dump consists of SQL statements to recreate the objects. You can extract the SQL statements as text with pg_restore -f sqlfile backupfilename That should help you with restoring the data. What exactly do you mean by "do not restore cleanly"? Do you get error messages or is the content not ok? Yours, Laurenz Albe Hi Laurenz, Yes I get errors when trying to restore. When I try to restore to an uncompressed file as suggested, I get the same errors - or the pg_restore crashes. e.g. in the console I get the following... ... pg_restore: executing SEQUENCE SET example_seq pg_restore: processing data for table example_table pg_restore: [compress_io] ** crash ** If I selectively restore I have about 10 tables that do not recover, and all of the Blobs. Howard. -- 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] Database Recovery from Corrupted Dump or Raw database table file.
Howard News wrote: > I have a raid catastrophe which has effectively blitzed a cluster data > directory. I have several pg_dump backups but these will not restore > cleanly. I assume the disk has been failing for some time and the > backups are of the corrupted database. > > Using a selective pg_restore on the dumps, I have restored 2/3rds of the > data but some tables I cannot recover directly, so I would like to see > if it is possible to examine the dumps (they are in compressed format) > to see if there are any rows which are recoverable. I do not know how or > if it is even possible to pick out rows from an individual table, when > in all likelyhood the file itself is corrupted. > > I also have some parts of the data directory, so the tables may be > accessible if I know the ID of the corrupt datatables. Is the ID listed > in the pg_dump --list file? And can the data be extracted from the raw > data files without running a cluster. I am unsure if there is enough > data to actually start a cluster. A backup created with pg_dump consists of SQL statements to recreate the objects. You can extract the SQL statements as text with pg_restore -f sqlfile backupfilename That should help you with restoring the data. What exactly do you mean by "do not restore cleanly"? Do you get error messages or is the content not ok? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database Recovery from Corrupted Dump or Raw database table file.
Hi all, I have a raid catastrophe which has effectively blitzed a cluster data directory. I have several pg_dump backups but these will not restore cleanly. I assume the disk has been failing for some time and the backups are of the corrupted database. Using a selective pg_restore on the dumps, I have restored 2/3rds of the data but some tables I cannot recover directly, so I would like to see if it is possible to examine the dumps (they are in compressed format) to see if there are any rows which are recoverable. I do not know how or if it is even possible to pick out rows from an individual table, when in all likelyhood the file itself is corrupted. I also have some parts of the data directory, so the tables may be accessible if I know the ID of the corrupt datatables. Is the ID listed in the pg_dump --list file? And can the data be extracted from the raw data files without running a cluster. I am unsure if there is enough data to actually start a cluster. Thanks, Howard. -- 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] Database recovery.
On 2011-03-24, Waqar Azeem wrote: > --0015174766a0ffbf86049f35206e > Content-Type: text/plain; charset=ISO-8859-1 > > My XP is crashed and now I have to take a full backup of my postgresql 8.4 > > I am used to get backup of ldf/mdf files in case of SQLServer > > Please let me know the right way of doing this for postgresql 8.4. You need the data directory, and the service user "postgres" needs to own it's contents. consider using runas and xcopy. -- ⚂⚃ 100% natural -- 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] Database recovery.
Waqar, > my windows event viewer says: > 2011-03-24 11:24:22 GMT FATAL: could not create lock file "postmaster.pid": > Permission denied > is this helpfull? yes. that says that the windows-user your PostgreSQL Service is running as has no write priv on the data directory. Check your file systems permissions and allow writing to the data directory for the user your PostgreSQL service runs as. Best wishes HArald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH GF: Harald Armin Massa Amtsgericht Stuttgart, HRB 736399 -- 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] Database recovery.
Thanks Karsten n' Raymond, Finally, I change the logging in account to Local System account as suggested in your URL. And then change it back to .\postgres This magically worked for me. I think ctrl+alt+del was a compulsory part of XP trouble shootings :) -- Waqar On Thu, Mar 24, 2011 at 5:10 PM, Waqar Azeem wrote: > my windows event viewer says: > > 2011-03-24 11:24:22 GMT FATAL: could not create lock file > "postmaster.pid": Permission denied > > is this helpfull? > > > On Thu, Mar 24, 2011 at 4:31 PM, Waqar Azeem > wrote: > >> I copy the whole folder 'Data' to another PC where fresh postgresql 8.4 is >> installed (same as default config. that previous version have had) >> >> >> >> when i started the server got following ... >> >> >> The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. >> >> The service did not report an error. >> >> More help is available by typing NET HELPMSG 3534. >> >> The start command returned an error (2) >> >> Press to continue... >> >> >> any clue? >> >> >> >> On 03/24/11 12:07 AM, Waqar Azeem wrote: >> >> My XP is crashed and now I have to take a full backup of my postgresql >> 8.4 >> >> I am used to get backup of ldf/mdf files in case of SQLServer >> >> Please let me know the right way of doing this for postgresql 8.4. >> >> >> you need everything in the $PGDATA directory. On my system here, >> itsD:\Postgres\8.4\data >> but of course, your mileage may vary. >> >> >> On Thu, Mar 24, 2011 at 12:07 PM, Waqar Azeem < >> waqarazeem.priv...@gmail.com> wrote: >> >>> My XP is crashed and now I have to take a full backup of my postgresql >>> 8.4 >>> >>> I am used to get backup of ldf/mdf files in case of SQLServer >>> >>> Please let me know the right way of doing this for postgresql 8.4. >>> >>> -- >>> Thanks & Best Regards, >>> Waqar Azeem >>> >> >> >> >> -- >> Thanks & Best Regards, >> Waqar Azeem >> > > > > -- > Thanks & Best Regards, > Waqar Azeem > -- Thanks & Best Regards, Waqar Azeem
Re: [GENERAL] Database recovery.
my windows event viewer says: 2011-03-24 11:24:22 GMT FATAL: could not create lock file "postmaster.pid": Permission denied is this helpfull? On Thu, Mar 24, 2011 at 4:31 PM, Waqar Azeem wrote: > I copy the whole folder 'Data' to another PC where fresh postgresql 8.4 is > installed (same as default config. that previous version have had) > > > when i started the server got following ... > > > The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. > > The service did not report an error. > > More help is available by typing NET HELPMSG 3534. > > The start command returned an error (2) > > Press to continue... > > > any clue? > > > > On 03/24/11 12:07 AM, Waqar Azeem wrote: > > My XP is crashed and now I have to take a full backup of my postgresql 8.4 > > I am used to get backup of ldf/mdf files in case of SQLServer > > Please let me know the right way of doing this for postgresql 8.4. > > > you need everything in the $PGDATA directory. On my system here, > itsD:\Postgres\8.4\data > but of course, your mileage may vary. > > > On Thu, Mar 24, 2011 at 12:07 PM, Waqar Azeem < > waqarazeem.priv...@gmail.com> wrote: > >> My XP is crashed and now I have to take a full backup of my postgresql 8.4 >> >> I am used to get backup of ldf/mdf files in case of SQLServer >> >> Please let me know the right way of doing this for postgresql 8.4. >> >> -- >> Thanks & Best Regards, >> Waqar Azeem >> > > > > -- > Thanks & Best Regards, > Waqar Azeem > -- Thanks & Best Regards, Waqar Azeem
Re: [GENERAL] Database recovery.
On Thu, Mar 24, 2011 at 12:37:25PM +0100, Karsten Hilbert wrote: > > More help is available by typing NET HELPMSG 3534. ... > > any clue? And the second Google result has this: http://archives.postgresql.org/pgsql-bugs/2009-05/msg5.php Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Database recovery.
On 24/03/2011 11:31, Waqar Azeem wrote: I copy the whole folder'Data' to another PC where freshpostgresql8.4 is installed (same as default config. that previous version have had) when i started the server got following ... The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Press to continue... Have a look in the Postgres log - there's often (usually?) information there that doesn't get sent to the Windows event log. It's probably in $PGDATA/pg_log, unless you changed something in the logging configuration. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Database recovery.
On Thu, Mar 24, 2011 at 04:31:19PM +0500, Waqar Azeem wrote: > The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. > > The service did not report an error. > > More help is available by typing NET HELPMSG 3534. > > The start command returned an error (2) > > Press to continue... > > > any clue? Hmmm. So, what's the output of running NET HELPMSG 3534 ? Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] Database recovery.
I copy the whole folder 'Data' to another PC where fresh postgresql 8.4 is installed (same as default config. that previous version have had) when i started the server got following ... The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Press to continue... any clue? On 03/24/11 12:07 AM, Waqar Azeem wrote: My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. you need everything in the $PGDATA directory. On my system here, itsD:\Postgres\8.4\data but of course, your mileage may vary. On Thu, Mar 24, 2011 at 12:07 PM, Waqar Azeem wrote: > My XP is crashed and now I have to take a full backup of my postgresql 8.4 > > I am used to get backup of ldf/mdf files in case of SQLServer > > Please let me know the right way of doing this for postgresql 8.4. > > -- > Thanks & Best Regards, > Waqar Azeem > -- Thanks & Best Regards, Waqar Azeem
Re: [GENERAL] Database recovery.
On 03/24/11 12:07 AM, Waqar Azeem wrote: My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. you need everything in the $PGDATA directory. On my system here, its D:\Postgres\8.4\data but of course, your mileage may vary. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database recovery.
My XP is crashed and now I have to take a full backup of my postgresql 8.4 I am used to get backup of ldf/mdf files in case of SQLServer Please let me know the right way of doing this for postgresql 8.4. -- Thanks & Best Regards, Waqar Azeem
Re: [GENERAL] Database recovery after dropdb
On Wed, 2010-07-14 at 18:18 +0200, easyCity Team wrote: > Hi guy, > > I just droped a development database by mistake using the dropdb command > witch contains valuable data. > > Can I still restaure it ? I have no backup. No. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database recovery after dropdb
Hi guy, I just droped a development database by mistake using the dropdb command witch contains valuable data. Can I still restaure it ? I have no backup. Best regards, Romain -- 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] Database recovery
Christian Schröder wrote: we have a PostgreSQL 8.2 database on an xfs filesystem. By mistake the first blocks of this filesystem were overwritten. An xfs_repair reconstructed the superblock and also found many orphaned files and directories. Actually, all we have on the filesystem now is in "lost+found". ;-) When I look in "lost+found" I have many files that *could* be database files, but since the original name of the files is unknown I cannot tell for sure. I have found a directory that looks like the original "data" directory, with stuff like "postmaster.log", "pg_hba.conf" and even subdirectories "base", "global" etc. in it. I have been able to start postgresql from this directory, but when I tried to access the most important database I got a message that the database directory could not be found. Indeed, this directory is missing in "base", but there is a chance that some of the other files might be the original content of this directory. Is there any way to find out which of the files is really a postgres data file? Or even for which database? Although the database file and page layout are described in the manual, I could not find an exact description of the file format, e.g. any magic numbers at the beginning of the file. Hmmm, no idea? :-( Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database recovery
Hi list, we have a PostgreSQL 8.2 database on an xfs filesystem. By mistake the first blocks of this filesystem were overwritten. An xfs_repair reconstructed the superblock and also found many orphaned files and directories. Actually, all we have on the filesystem now is in "lost+found". ;-) When I look in "lost+found" I have many files that *could* be database files, but since the original name of the files is unknown I cannot tell for sure. I have found a directory that looks like the original "data" directory, with stuff like "postmaster.log", "pg_hba.conf" and even subdirectories "base", "global" etc. in it. I have been able to start postgresql from this directory, but when I tried to access the most important database I got a message that the database directory could not be found. Indeed, this directory is missing in "base", but there is a chance that some of the other files might be the original content of this directory. Is there any way to find out which of the files is really a postgres data file? Or even for which database? Although the database file and page layout are described in the manual, I could not find an exact description of the file format, e.g. any magic numbers at the beginning of the file. Regards, Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database recovery
Hello! My filesystem (UFS-FreeBSD) was crashed and I have lost files from PGSQL DATA DIR FOLDER after fsck-utility. I have lost files from directory "pg_xlog" I reset my pg_xlog. And now have such error by starting pgsql-server: LOG: database system was interrupted at 2008-03-18 22:29:48 EET LOG: checkpoint record is at 3E/6C20 LOG: redo record is at 3E/6C20; undo record is at 3E/6C20; shutdown TRUE LOG: next transaction ID: 0/1624775486; next OID: 9528514 LOG: next MultiXactId: 6643; next MultiXactOffset: 13690 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 3E/6C68 LOG: redo is not required FATAL: could not access status of transaction 6643 DETAIL: Could not read from file "pg_multixact/offsets/" at offset 24576: Success. LOG: startup process (PID 6267) exited with exit code 1 LOG: aborting startup due to startup process failure LOG: logger shutting down Is it possible to lose this problem? -- 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] Database Recovery Procedures
Quoting Tom Lane <[EMAIL PROTECTED]>: > Network Administrator <[EMAIL PROTECTED]> writes: > >>> PANIC: read of clog file 5, offset 16384 failed: Success > > >> Hm, not good :-(. What files actually exist in $PGDATA/pg_clog/ (names > >> and sizes)? > > > -rw---1 postgres users 262144 Jul 20 15:53 > > -rw---1 postgres users 262144 Jul 31 12:57 0001 > > -rw---1 postgres users 262144 Aug 12 17:32 0002 > > -rw---1 postgres users 262144 Aug 26 00:15 0003 > > -rw---1 postgres users 262144 Sep 9 23:44 0004 > > -rw---1 postgres users 16384 Sep 10 21:21 0005 > > Okay, it's trying to read off the end of the clog, no doubt looking for > a transaction number just slightly larger than what's known to clog. > This probably indicates more serious problems (because WAL replay really > should have prevented such an inconsistency), but you can get past the > immediate panic relatively easily: just append an 8k page of zeroes to > clog. Assuming your system has /dev/zero, something like this should > do it: > > dd bs=8k count=1 < /dev/zero >> $PGDATA/pg_clog/0005 > > (do this with care of course, and you should probably shut down the > postmaster first). You might possibly have to add more than one page, > if you then get similar PANICs with larger offsets, but try one page > for starters. Well whatdoyaknow! That did it- EVERYTHING is there! I only needed the one page. I'm going to have to read up on pg_clog (WAL) so that I understand what it does a little better. It makes total sense too because obvious if there is no more space to write too anything on disk get "frozen" where as application is just going to keep moving along. At least for a time. > If this does suppress the failure messages, you are still not really out > of the woods; you should do what you can to check for data consistency. > A paranoid person would probably take a complete pg_dump and try to diff > it against the last known good dump. At the very least, I'd treat the > table involved in the problem with great suspicion. > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings Well, I fortunately was on the side of the road and not in the woods in this case. I'm assuming the next thing to do is run a vacuum analyse and they a dump all. I'll see how it things perform over the next 48 hours or so. Now that we've done that, how should should this surgery be documented? I would think a "tip" like this should be somewhere in the docs (not just the archive). I'd be more than will to write up this case but I'm trying to establish a long goal here- if I may be so bold as to suggest one. Thanks again. -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Database Recovery Procedures
Network Administrator <[EMAIL PROTECTED]> writes: >>> PANIC: read of clog file 5, offset 16384 failed: Success >> Hm, not good :-(. What files actually exist in $PGDATA/pg_clog/ (names >> and sizes)? > -rw---1 postgres users 262144 Jul 20 15:53 > -rw---1 postgres users 262144 Jul 31 12:57 0001 > -rw---1 postgres users 262144 Aug 12 17:32 0002 > -rw---1 postgres users 262144 Aug 26 00:15 0003 > -rw---1 postgres users 262144 Sep 9 23:44 0004 > -rw---1 postgres users 16384 Sep 10 21:21 0005 Okay, it's trying to read off the end of the clog, no doubt looking for a transaction number just slightly larger than what's known to clog. This probably indicates more serious problems (because WAL replay really should have prevented such an inconsistency), but you can get past the immediate panic relatively easily: just append an 8k page of zeroes to clog. Assuming your system has /dev/zero, something like this should do it: dd bs=8k count=1 < /dev/zero >> $PGDATA/pg_clog/0005 (do this with care of course, and you should probably shut down the postmaster first). You might possibly have to add more than one page, if you then get similar PANICs with larger offsets, but try one page for starters. If this does suppress the failure messages, you are still not really out of the woods; you should do what you can to check for data consistency. A paranoid person would probably take a complete pg_dump and try to diff it against the last known good dump. At the very least, I'd treat the table involved in the problem with great suspicion. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Database Recovery Procedures
Quoting Tom Lane <[EMAIL PROTECTED]>: > Network Administrator <[EMAIL PROTECTED]> writes: > > ..if I using the psql client, and issue a "select * from name> > > limit 5" if get this... > > > PANIC: read of clog file 5, offset 16384 failed: Success > > Hm, not good :-(. What files actually exist in $PGDATA/pg_clog/ (names > and sizes)? Here you go... -rw---1 postgres users 262144 Jul 20 15:53 -rw---1 postgres users 262144 Jul 31 12:57 0001 -rw---1 postgres users 262144 Aug 12 17:32 0002 -rw---1 postgres users 262144 Aug 26 00:15 0003 -rw---1 postgres users 262144 Sep 9 23:44 0004 -rw---1 postgres users 16384 Sep 10 21:21 0005 > > So back to my original question. What are the recover procedures (if > > any) that should be tried before I grab my PGDATA path from tape? > > You may be up against having to do that, but some investigation first > seems called for. Yep, its ready to go. When and if nothing surgical can be done. > regards, tom lane > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Database Recovery Procedures
Network Administrator <[EMAIL PROTECTED]> writes: > ..if I using the psql client, and issue a "select * from > limit 5" if get this... > PANIC: read of clog file 5, offset 16384 failed: Success Hm, not good :-(. What files actually exist in $PGDATA/pg_clog/ (names and sizes)? > So back to my original question. What are the recover procedures (if > any) that should be tried before I grab my PGDATA path from tape? You may be up against having to do that, but some investigation first seems called for. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Database Recovery Procedures
Network Administrator <[EMAIL PROTECTED]> writes: > PANIC: XLogWrite: write request 5/2D498000 is past end of log 5/2D498000 I'll bet you are running 7.3.3. You need to update to 7.3.4 to escape this startup bug. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Database recovery after fs crash
>> How do I do that? I don't know the format of the clog files. The only >> information I found was in clog.h and xlog.h. > > xlog is fairly easy, I think resetxlog or something can work there. For > clog I just noted from the logs which clog files it was missing and > created them filled with either 0x00 or 0x55 (I forget if it make a > difference) and kept going until done. Thanks a lot for your help. Whatever table I was able to identify I was able to recover. I just filled every clog file missing with 0x00 and used pg_resetxlog for the xlog files, just like you suggested. It worked :) Now, I have at least part of my data back. Christian ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Database recovery after fs crash
What operating system and file system? Can I guess Linux/ext2? --- Christian von Kietzell wrote: > Hi, > > I've got a serious problem. After a horrible filesystem crash the only thing > left of my database are some files in /lost+found (with changed names!). Is > there a way to recover the data from those files? I managed to identify the > files belonging to PostgreSQL (unfortunately, these are part of more than one > database (think of template0/1...)), but I don't know which file belongs to > which table. > I've searched the web for recovery tools but none of them seems capable of > what I want to do. Is there any documentation on the structure of PostgreSQL's > database files? > If this is the wrong place to ask, please tell me where I might get help. > > Cheers, > Christian > > P.S. I'm not subscribed to the list and therefore would appreciate direct > answers. > > -- > COMPUTERBILD 15/03: Premium-e-mail-Dienste im Test > -- > 1. GMX TopMail - Platz 1 und Testsieger! > 2. GMX ProMail - Platz 2 und Preis-Qualit?tssieger! > 3. Arcor - 4. web.de - 5. T-Online - 6. freenet.de - 7. daybyday - 8. e-Post > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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: [GENERAL] Database recovery after fs crash
> Hmm, presumably you don't have those. Unfortunately not. All backup measures I took failed on the exact same occasion - worst case possible. > I usually start by browsing files by hand to see if I can determine > from any embedded text which table it was. Seems easy enough. > Then if you create a new database and recreate the table *exactly* the > same and then copy the datafile over. You might need to create some > clog files but you *might* be able to recover some data. How do I do that? I don't know the format of the clog files. The only information I found was in clog.h and xlog.h. > It's a bit tricky though. Black magic and all that. Backups are absolutly > the best way. Definitely, if only my backup had worked... Christian ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Database recovery after fs crash
On Tue, Aug 26, 2003 at 10:17:48AM +1000, Martijn van Oosterhout wrote: > Wow. Take to pull out those backups... > > Hmm, presumably you don't have those. There are programs to read postgresql > data files however they need to know the exact structure of the table before > you can even start to decode them. I usually start by browsing files by hand > to see if I can determine from any embedded text which table it was. Hm, you fail to mention your own pgfsck tool. I wonder why. I have not tried it myself, but sounds like it can be handy. IIRC it should be in http://svana.org/kleptog somewhere. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "God is real, unless declared as int" ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Database recovery after fs crash
Wow. Take to pull out those backups... Hmm, presumably you don't have those. There are programs to read postgresql data files however they need to know the exact structure of the table before you can even start to decode them. I usually start by browsing files by hand to see if I can determine from any embedded text which table it was. Then if you create a new database and recreate the table *exactly* the same and then copy the datafile over. You might need to create some clog files but you *might* be able to recover some data. It's a bit tricky though. Black magic and all that. Backups are absolutly the best way. Hope this helps, On Mon, Aug 25, 2003 at 11:12:20PM +0200, [EMAIL PROTECTED] wrote: > Hi, > > I've got a serious problem. After a horrible filesystem crash the only thing > left of my database are some files in /lost+found (with changed names!). Is > there a way to recover the data from those files? I managed to identify the > files belonging to PostgreSQL (unfortunately, these are part of more than one > database (think of template0/1...)), but I don't know which file belongs to > which table. > I've searched the web for recovery tools but none of them seems capable of > what I want to do. Is there any documentation on the structure of > PostgreSQL's > database files? > If this is the wrong place to ask, please tell me where I might get help. > > Cheers, > Christian > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato pgp0.pgp Description: PGP signature
[GENERAL] Database recovery after fs crash
Hi, I've got a serious problem. After a horrible filesystem crash the only thing left of my database are some files in /lost+found (with changed names!). Is there a way to recover the data from those files? I managed to identify the files belonging to PostgreSQL (unfortunately, these are part of more than one database (think of template0/1...)), but I don't know which file belongs to which table. I've searched the web for recovery tools but none of them seems capable of what I want to do. Is there any documentation on the structure of PostgreSQL's database files? If this is the wrong place to ask, please tell me where I might get help. Cheers, Christian ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Database Recovery Help...
If you are sure that no other postmaster is running, try deleting the file /tmp/.s.PGSQL.5432, as the error message suggests. When you've done that, you should be able to start the postmaster. Poul L. Christiansen On Tue, 8 May 2001, John Clark L. Naldoza wrote: > Hi, > > > I recently came across a problem wherein I am not able to start my > postgreSQL 7.0.3 server. > > Here's what happens..;-( > > postgres@kahoy /root$ postmaster -D /var/lib/pgsql/data > FATAL: StreamServerPort: bind() failed: Permission denied > Is another postmaster already running on that port? > If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. > /usr/bin/postmaster: cannot create UNIX stream port > > There is no one binding the 5432 port, and no one is trying to connect > to that port. I may be left with no other alternative and restart from > scratch...;-( > > There are some pretty important data currently in the database that I'd > like to recover... > > Is there any way wherein I could recover or reconstruct the data? > > > Cheers, > > > John Clark > > P.S. I'm using Trustix 1.4.80..;-) > > > -- > /) John Clark Naldoza y Lopez (\ > / )Software Design Engineer II ( \ > _( (__ Web-Application Development_) )_ > (((\ \> /_>Cable Modem Network Management System <_\( \_/ / NEC Telecom Software Phils., Inc. \ \_/ ) > \ / \ / >\_/ phone: (+63 32) 233-9142 loc. 3112\_/ >/ / cellphone: (+63 919) 399-4742 \ \ > / / email: [EMAIL PROTECTED]\ \ > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Database Recovery Help...
Hi, I recently came across a problem wherein I am not able to start my postgreSQL 7.0.3 server. Here's what happens..;-( postgres@kahoy /root$ postmaster -D /var/lib/pgsql/data FATAL: StreamServerPort: bind() failed: Permission denied Is another postmaster already running on that port? If not, remove socket node (/tmp/.s.PGSQL.5432) and retry. /usr/bin/postmaster: cannot create UNIX stream port There is no one binding the 5432 port, and no one is trying to connect to that port. I may be left with no other alternative and restart from scratch...;-( There are some pretty important data currently in the database that I'd like to recover... Is there any way wherein I could recover or reconstruct the data? Cheers, John Clark P.S. I'm using Trustix 1.4.80..;-) -- /) John Clark Naldoza y Lopez (\ / )Software Design Engineer II ( \ _( (__ Web-Application Development_) )_ (((\ \> /_>Cable Modem Network Management System <_\ http://www.postgresql.org/search.mpl
[GENERAL] Database Recovery
Hi ! I have a big problem : all my datas are unavailable. When I do a vaccuum, that ok there are rows. When I do a "SELECT" or "\d", nothing. One of you told me that could be that I reached the max transaction ID. My pg_log is very big (1 Go !!) How could I retrieve my datas ? Of course I tried pg_dump : nothing ! Fils is the DB sub directories seems ok... I tried to make a new install, I re create all the DB, than moved my datafiles to each subdirectories. But nothing ! Then I moved to the old pg_log file to the new DB ! Nothing ! Is there a way to retrieve my datas, even without the indexes ??? thank you ! jean-arthur Le simple fait de passer par la fenetre ne suffit pas a la transformer en porte.
