Thread: Lost databases
Hi ! I'm in the ultimate state of dispair. Over the night some of my databases just disapeared. But they did not dissaper totally. I can still connect to them but I cannot dump them. When doing a "select * from pg_database" I see only 2 databases I hardly ever use (they are used once in a couple of mounths). Template0 and template1 are missing. All the databases that I currently use dissapeared! Please help me to restore the integrity of the system. I'm in dense fog here :( Liviu
Let's start with the version of postgresql you're using. Next, were you running VACUUM FULL regularly? It sounds like XID wraparound to me, no idea how to solve that. Got backups? On Wed, Nov 10, 2004 at 12:56:33PM +0200, Liviu BURCUSEL wrote: > Hi ! > > I'm in the ultimate state of dispair. Over the night some of my > databases just disapeared. But they did not dissaper totally. I can > still connect to them but I cannot dump them. When doing a "select * > from pg_database" I see only 2 databases I hardly ever use (they are > used once in a couple of mounths). Template0 and template1 are missing. > All the databases that I currently use dissapeared! > > Please help me to restore the integrity of the system. I'm in dense fog > here :( > > Liviu > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
--- Liviu BURCUSEL <liviu@voxline.ro> wrote: > Hi ! > > I'm in the ultimate state of dispair. Over the night > some of my > databases just disapeared. But they did not dissaper > totally. I can > still connect to them but I cannot dump them. When > doing a "select * > from pg_database" I see only 2 databases I hardly > ever use (they are > used once in a couple of mounths). Template0 and > template1 are missing. > All the databases that I currently use dissapeared! > > Please help me to restore the integrity of the > system. I'm in dense fog > here :( What Postgresql version is this? Your problem sounds like "oid wraparound". There was a recent discussion of this on the list; try the list archives to see what was recommended in that case. > > Liviu > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > > http://www.postgresql.org/docs/faqs/FAQ.html > __________________________________ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com
On Wed, 10 Nov 2004 08:11:01 -0800 (PST) Jeff Eckermann <jeff_eckermann@yahoo.com> wrote: > --- Liviu BURCUSEL <liviu@voxline.ro> wrote: > > > Hi ! > > > > I'm in the ultimate state of dispair. Over the night > > some of my > > databases just disapeared. But they did not dissaper > > totally. I can > > still connect to them but I cannot dump them. When > > doing a "select * > > from pg_database" I see only 2 databases I hardly > > ever use (they are > > used once in a couple of mounths). Template0 and > > template1 are missing. > > All the databases that I currently use dissapeared! > > > > Please help me to restore the integrity of the > > system. I'm in dense fog > > here :( > > What Postgresql version is this? Your problem sounds > like "oid wraparound". There was a recent discussion > of this on the list; try the list archives to see what > was recommended in that case. > The Postgresql version is 7.4.1 Thanks. I'll look in the archive. Liviu
Martijn van Oosterhout wrote: > Let's start with the version of postgresql you're using. > Next, were you running VACUUM FULL regularly? > > It sounds like XID wraparound to me, no idea how to solve that. Got > backups? > > On Wed, Nov 10, 2004 at 12:56:33PM +0200, Liviu BURCUSEL wrote: > >>Hi ! >> >>I'm in the ultimate state of dispair. Over the night some of my >>databases just disapeared. But they did not dissaper totally. I can >>still connect to them but I cannot dump them. When doing a "select * >>from pg_database" I see only 2 databases I hardly ever use (they are >>used once in a couple of mounths). Template0 and template1 are missing. >>All the databases that I currently use dissapeared! >> >>Please help me to restore the integrity of the system. I'm in dense fog >>here :( >> >>Liviu >> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faqs/FAQ.html > > Only VACUUM FULL solves XID wraparound problem? I mean VACUUM FULL locks the entire table. How can I test if I am close to XID wraparround? Kostis
Look at the documentation here: http://www.postgresql.org/docs/7.4/interactive/maintenance.html The upshot is that you should vacuum every table in the database at least once every billion transaction. It doesn't have to be a VACUUM FULL, just run VACUUM without specifying a table. There's some info in there about how to work out when it is necessary. Hope this helps, On Tue, Nov 16, 2004 at 12:22:46PM +0200, Kostis Mentzelos wrote: > Only VACUUM FULL solves XID wraparound problem? I mean VACUUM FULL locks > the entire table. > > How can I test if I am close to XID wraparround? > > Kostis > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.