Re: [GENERAL] Suddenly all tables were gone
On Fri, Jan 3, 2014 at 8:37 PM, Adrian Klaver adrian.kla...@gmail.comwrote: I know the database is gone now, still for the record it would be nice to know what the sequence of events where. You mentioned pg_dump and upgrade from 9.1 - 9.3. So what was done and in what order? First the conversion to 9.3, then I was using pg_dump. Also what database where you trying to connect to, the one in 9.1 or 9.3? 9.3. The db was fine on 9.1. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] Suddenly all tables were gone
On 01/04/2014 10:15 AM, Moshe Jacobson wrote: On Fri, Jan 3, 2014 at 8:37 PM, Adrian Klaver adrian.kla...@gmail.com mailto:adrian.kla...@gmail.com wrote: I know the database is gone now, still for the record it would be nice to know what the sequence of events where. You mentioned pg_dump and upgrade from 9.1 - 9.3. So what was done and in what order? First the conversion to 9.3, then I was using pg_dump. So we are looking at a fresh 9.3 cluster. So you created an 9.3 instance and then used pg_dump to transfer the data from the 9.1 instance to the 9.3 instance? Also what database where you trying to connect to, the one in 9.1 or 9.3? 9.3. The db was fine on 9.1. So from previous posts, if I follow correctly, the database(with tables) was in the 9.3 cluster to begin with and then later you only had database but with no tables in it. Or to put it another way, at some point you could connect to the 'problem' database but could find no tables. Is that correct? Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle -- Adrian Klaver adrian.kla...@gmail.com -- 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] Suddenly all tables were gone
On 01/04/2014 12:58 PM, Moshe Jacobson wrote: Ccing the list. On Sat, Jan 4, 2014 at 2:08 PM, Adrian Klaver adrian.kla...@gmail.com mailto:adrian.kla...@gmail.com wrote: So we are looking at a fresh 9.3 cluster. So you created an 9.3 instance and then used pg_dump to transfer the data from the 9.1 instance to the 9.3 instance? No. I used pg_upgrade on the 9.1 cluster. The pg_dump was unrelated to the upgrade. So where does the pg_dump fit in? Or more to the point why do you think it could be a possible cause of the problem? at some point you could connect to the 'problem' database but could find no tables. Correct. So anything of note happen in that interval? Do the logs over that time period show anything? Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Suddenly all tables were gone
Yesterday I found that one of the databases in my database cluster suddenly lost all its tables. A \dt in psql showed nothing. I'm not sure how or when it happened, but it was either due to an upgrade of postgres from 9.1 to 9.3 or else something going wrong with pg_dump. Has anyone had this happen before? Thanks. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] Suddenly all tables were gone
Moshe Jacobson mo...@neadwerx.com writes: Yesterday I found that one of the databases in my database cluster suddenly lost all its tables. A \dt in psql showed nothing. I'm not sure how or when it happened, but it was either due to an upgrade of postgres from 9.1 to 9.3 or else something going wrong with pg_dump. This sounds like a transaction ID wraparound issue, ie, the pg_class rows are still there but you can't see them because they're in the future. VACUUM FREEZE would probably fix it, but the interesting question is why didn't the built-in wraparound defenses prevent this from happening. Could we see the output from pg_controldata? regards, tom lane -- 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] Suddenly all tables were gone
Yesterday I found that one of the databases in my database cluster suddenly lost all its tables. A \dt in psql showed nothing. Is there any chance this could be a search_path issue? Do you have a ~/.psqlrc? Or are you sure you're in the right database? If you are connecting as the postgres user, are you using \c? Sorry for the is it plugged in? questions, but hopefully it's something along those lines rather than anything more complicated! Paul On Fri, Jan 3, 2014 at 11:20 AM, Moshe Jacobson mo...@neadwerx.com wrote: Yesterday I found that one of the databases in my database cluster suddenly lost all its tables. A \dt in psql showed nothing. I'm not sure how or when it happened, but it was either due to an upgrade of postgres from 9.1 to 9.3 or else something going wrong with pg_dump. Has anyone had this happen before? Thanks. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle -- _ Pulchritudo splendor veritatis. -- 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] Suddenly all tables were gone
On Fri, Jan 3, 2014 at 2:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Could we see the output from pg_controldata? Here you go. The problem database has already been dropped and re-created, BTW: (0)(0j)[root@staging /var/lib/pgsql/9.3]# pg_controldata pg_control version number:937 Catalog version number: 201306121 Database system identifier: 5962287500209577471 Database cluster state: in production pg_control last modified: Fri 03 Jan 2014 03:07:25 PM EST Latest checkpoint location: 2FC/BB28 Prior checkpoint location:2FC/B928 Latest checkpoint's REDO location:2FC/BB28 Latest checkpoint's REDO WAL file:000102FC00BB Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/32280314 Latest checkpoint's NextOID: 2491780 Latest checkpoint's NextMultiXactId: 78785 Latest checkpoint's NextMultiOffset: 110 Latest checkpoint's oldestXID:1791 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestMultiXid: 78746 Latest checkpoint's oldestMulti's DB: 0 Time of latest checkpoint:Fri 03 Jan 2014 03:07:25 PM EST Fake LSN counter for unlogged rels: 0/1 Minimum recovery ending location: 0/0 Min recovery ending loc's timeline: 0 Backup start location:0/0 Backup end location: 0/0 End-of-backup record required:no Current wal_level setting:minimal Current max_connections setting: 100 Current max_prepared_xacts setting: 0 Current max_locks_per_xact setting: 64 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: 64-bit integers Float4 argument passing: by value Float8 argument passing: by value Data page checksum version: 0 Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 “Quality is not an act, it is a habit.” — Aristotle
Re: [GENERAL] Suddenly all tables were gone
On Fri, Jan 3, 2014 at 3:02 PM, Paul Jungwirth p...@illuminatedcomputing.comwrote: Is there any chance this could be a search_path issue? Do you have a ~/.psqlrc? Or are you sure you're in the right database? If you are connecting as the postgres user, are you using \c? Yeah, I was connecting as postgres, no special search_path, same config was working earlier. No harm in asking! Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] Suddenly all tables were gone
Moshe Jacobson mo...@neadwerx.com writes: On Fri, Jan 3, 2014 at 2:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Could we see the output from pg_controldata? Here you go. The problem database has already been dropped and re-created, Then the evidence is pretty much gone I'm afraid :-( Latest checkpoint's NextXID: 0/32280314 Hm, this cluster is nowhere near old enough for wraparound to be an issue. No idea now what happened to you. If it happens again, please rename the broken DB out of the way rather than summarily dropping it. We'll not have much chance of identifying the cause without some forensic work. regards, tom lane -- 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] Suddenly all tables were gone
On 1/3/2014 12:18 PM, Moshe Jacobson wrote: Yeah, I was connecting as postgres, no special search_path, same config was working earlier. No harm in asking! it was working AFTER the 9.1-9.3 upgrade ? -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Suddenly all tables were gone
On Fri, Jan 3, 2014 at 3:59 PM, John R Pierce pie...@hogranch.com wrote: it was working AFTER the 9.1-9.3 upgrade ? I think it was for some amount of time, since I used pg_dump to dump the contents, and pg_restore to restore those contents into another database in the same cluster. I was using the database to test some things, and I seem to recall that it was working fine. Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] Suddenly all tables were gone
On 01/03/2014 01:18 PM, Moshe Jacobson wrote: On Fri, Jan 3, 2014 at 3:59 PM, John R Pierce pie...@hogranch.com mailto:pie...@hogranch.com wrote: it was working AFTER the 9.1-9.3 upgrade ? I think it was for some amount of time, since I used pg_dump to dump the contents, and pg_restore to restore those contents into another database in the same cluster. I was using the database to test some things, and I seem to recall that it was working fine. I know the database is gone now, still for the record it would be nice to know what the sequence of events where. You mentioned pg_dump and upgrade from 9.1 - 9.3. So what was done and in what order? Also what database where you trying to connect to, the one in 9.1 or 9.3? Moshe Jacobson Manager of Systems Engineering, Nead Werx Inc. http://www.neadwerx.com 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339 Quality is not an act, it is a habit. -- Aristotle -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
