Hi folks,

I ran into big trouble - it seems that my DB is lost.

"select * from pg_database" gives me 0 rows, but I still can connect to
databases with \c and even select from tables there, although they're also
not visible with \dt.

After asking the guys in the #postgresql@irc.freenode.net channel they told
me that the reason is the "Transaction ID wraparound", because I have never
ran VACUUM on the whole database.

So they proposed to ask here for help. I have stopped the server, but what
could I do in order to save the data if it's possible at all?

You could also take a look at my pg_controldata output:
http://rafb.net/paste/results/ghcIb880.html

Regards,
Kouber Saparev


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

> I think you're pretty well screwed as far as getting it *all* back goes,
> but you could use pg_resetxlog to back up the NextXID counter enough to
> make your tables and databases reappear (and thereby lose the effects of
> however many recent transactions you back up over).
>
> Once you've found a NextXID setting you like, I'd suggest an immediate
> pg_dumpall/initdb/reload to make sure you have a consistent set of data.
> Don't VACUUM, or indeed modify the DB at all, until you have gotten a
> satisfactory dump.
>
> Then put in a cron job to do periodic vacuuming ;-)

This might seem like a stupid question, but since this is a massive data
loss potential in PostgreSQL, what's so hard about having the
checkpointer or something check the transaction counter when it runs and
either issue a db-wide vacuum if it's about to wrap, or simply
disallow any new transactions?

I think people'd rather their db just stopped accepting new transactions
rather than just losing data...

Chris

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

> This might seem like a stupid question, but since this is a massive
> data loss potential in PostgreSQL, what's so hard about having the
> checkpointer or something check the transaction counter when it runs
> and either issue a db-wide vacuum if it's about to wrap, or simply
> disallow any new transactions?

I think autovac-in-backend is the preferred solution to this, and it's
definitely on the TODO list...

-Doug

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

It must be possible to create a tool based on the PostgreSQL sources that
can read all the tuples in a database and dump them to a file stream. All
the data remains in the file until overwritten with data after a vacuum.
It *should* be doable.

If there data in the table is worth anything, then it would be worth
extracting.

It would, of course, be a tool of last resort.



> "Kouber Saparev" <postgresql@saparev.com> writes:
>> After asking the guys in the #postgresql@irc.freenode.net channel they
>> told
>> me that the reason is the "Transaction ID wraparound", because I have
>> never
>> ran VACUUM on the whole database.
>
>> So they proposed to ask here for help. I have stopped the server, but
>> what
>> could I do in order to save the data if it's possible at all?
>
> I think you're pretty well screwed as far as getting it *all* back goes,
> but you could use pg_resetxlog to back up the NextXID counter enough to
> make your tables and databases reappear (and thereby lose the effects of
> however many recent transactions you back up over).
>
> Once you've found a NextXID setting you like, I'd suggest an immediate
> pg_dumpall/initdb/reload to make sure you have a consistent set of data.
> Don't VACUUM, or indeed modify the DB at all, until you have gotten a
> satisfactory dump.
>
> Then put in a cron job to do periodic vacuuming ;-)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>> I think you're pretty well screwed as far as getting it *all* back goes,
>> but you could use pg_resetxlog to back up the NextXID counter enough to
>> make your tables and databases reappear (and thereby lose the effects of
>> however many recent transactions you back up over).
>>
>> Once you've found a NextXID setting you like, I'd suggest an immediate
>> pg_dumpall/initdb/reload to make sure you have a consistent set of data.
>> Don't VACUUM, or indeed modify the DB at all, until you have gotten a
>> satisfactory dump.
>>
>> Then put in a cron job to do periodic vacuuming ;-)
>
> This might seem like a stupid question, but since this is a massive data
> loss potential in PostgreSQL, what's so hard about having the
> checkpointer or something check the transaction counter when it runs and
> either issue a db-wide vacuum if it's about to wrap, or simply
> disallow any new transactions?
>
> I think people'd rather their db just stopped accepting new transactions
> rather than just losing data...
>

I would certainly prefer the system to issue an error and stop working
than complete data loss.


---------------------------(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

"Kouber Saparev" <postgresql@saparev.com> writes:
> After asking the guys in the #postgresql@irc.freenode.net channel they told
> me that the reason is the "Transaction ID wraparound", because I have never
> ran VACUUM on the whole database.

> So they proposed to ask here for help. I have stopped the server, but what
> could I do in order to save the data if it's possible at all?

I think you're pretty well screwed as far as getting it *all* back goes,
but you could use pg_resetxlog to back up the NextXID counter enough to
make your tables and databases reappear (and thereby lose the effects of
however many recent transactions you back up over).

Once you've found a NextXID setting you like, I'd suggest an immediate
pg_dumpall/initdb/reload to make sure you have a consistent set of data.
Don't VACUUM, or indeed modify the DB at all, until you have gotten a
satisfactory dump.

Then put in a cron job to do periodic vacuuming ;-)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

> Once you've found a NextXID setting you like, I'd suggest an immediate
> pg_dumpall/initdb/reload to make sure you have a consistent set of data.
> Don't VACUUM, or indeed modify the DB at all, until you have gotten a
> satisfactory dump.
>
> Then put in a cron job to do periodic vacuuming ;-)

Thank you, I just discovered in the mailing lists that I'm not the first nor
the last guy that have forgotten to VACUUM the database. ;-)

Regards,
Kouber Saparev


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> This might seem like a stupid question, but since this is a massive data
> loss potential in PostgreSQL, what's so hard about having the
> checkpointer or something check the transaction counter when it runs and
> either issue a db-wide vacuum if it's about to wrap, or simply
> disallow any new transactions?

The checkpointer is entirely incapable of either detecting the problem
(it doesn't have enough infrastructure to examine pg_database in a
reasonable way) or preventing backends from doing anything if it did
know there was a problem.

> I think people'd rather their db just stopped accepting new transactions
> rather than just losing data...

Not being able to issue new transactions *is* data loss --- how are you
going to get the system out of that state?

autovacuum is the correct long-term solution to this, not some kind of
automatic hara-kiri.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

> The checkpointer is entirely incapable of either detecting the problem
> (it doesn't have enough infrastructure to examine pg_database in a
> reasonable way) or preventing backends from doing anything if it did
> know there was a problem.

Well, I guess I meant 'some regularly running process'...

>>I think people'd rather their db just stopped accepting new transactions
>>rather than just losing data...
>
> Not being able to issue new transactions *is* data loss --- how are you
> going to get the system out of that state?

Not allowing any transactions except a vacuum...

> autovacuum is the correct long-term solution to this, not some kind of
> automatic hara-kiri.

Yeah, seems like it should really happen soon...

Chris

---------------------------(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

pgsql@mohawksoft.com writes:
> Maybe I'm missing something, but shouldn't the prospect of data loss (even
> in the presense of admin ignorance) be something that should be
> unacceptable? Certainly within the realm "normal PostgreSQL" operation.

[ shrug... ] The DBA will always be able to find a way to shoot himself
in the foot. We've seen several instances of people blowing away
pg_xlog and pg_clog, for example, because they "don't need log files".
Or how about failing to keep adequate backups? That's a sure way for an
ignorant admin to lose data too.

Once autovacuum gets to the point where it's used by default, this
particular failure mode should be a thing of the past, but in the
meantime I'm not going to panic about it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster