Re: pg_upgrade - link mode and transaction-wraparound data loss

> jesper@krogh.cc wrote:
>> Hi
>>
>> I tried running pg_upgrade from the current snapshot of postgresql and
>> upgrading from 8.4.4 to the snapshot version. Everything seem to look
>> fine
>> in the process and all that came out was only "ok's" but when I tried a
>> simple query on the databse it keeps throwing these message out of the
>> back
>> side.
>>
>> DETAIL:  You might have already suffered transaction-wraparound data
>> loss.
>> WARNING:  some databases have not been vacuumed in over 2 billion
>> transactions
>>
>>
>> The database was around 600GB and it took a couple of minutes to run
>> pg_upgrade after I had all the binaries in the correct place.
>>
>> It is not really an easy task to throw around 600GB of data, so I cannot
>> gaurantee that the above is reproducible, but I'll see if I can get time
>> and try to reproduce it.
>
> This certainly should never have happened, so I am guessing it is a bug.
> pg_upgrade tries hard to make sure all your datfrozenxid and
> relfrozenxid are properly migrated from the old server, and the
> transaction id is set properly.  Unfortunately this is the first time I
> have heard of such a problem, so I am unclear on its cause.
>
> The warning is issued from vacuum.c::vac_truncate_clog().  Can you run
> this query and show us the output:
>
>     SELECT datname, datfrozenxid FROM pg_database;
>
> It would be good to see these numbers on both the old and new servers.
> I would also like to see:
>
>     SELECT txid_current();
>
> on the old and new servers, but if you can only provide these values on
> one of the two servers, it is still useful.  Thanks.

Hi Bruce, thanks for your prompt response.

First the new one..

jk@pal:~$ psql -p 5433
psql (9.0beta1)
Type "help" for help.

data=# SELECT datname, datfrozenxid FROM pg_database; datname  | datfrozenxid
-----------+--------------template0 |          654postgres  |   2374592801data      |   2023782337jk        |
2023822188template1|   2374592801workqueue |   2023822188
 
(6 rows)

data=#         SELECT txid_current();txid_current
--------------  2375384556
(1 row)

data=# \q

Then the old one.

jk@pal:~$ psql data
psql (9.0beta1, server 8.4.1)
WARNING: psql version 9.0, server version 8.4.        Some psql features might not work.
Type "help" for help.

data# SELECT datname, datfrozenxid FROM pg_database; datname  | datfrozenxid
-----------+--------------template0 |   2073823552postgres  |   2023820521data      |   2023782337jk        |
2023822188template1|   2073823552workqueue |   2023822188
 
(6 rows)

data=# SELECT txid_current();txid_current
--------------  2390524243
(1 row)


The old database has been "copied" over using rsync and
pg_start_backup()/pg_stop_backup() procecures and started up
using a recovery.conf file.

Jesper