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

> jesper(at)krogh(dot)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(at)pal:~$ psql -p 5433
psql (9.0beta1)
Type "help" for help.

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

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

data=# \q

Then the old one.

jk(at)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 | 2073823552
postgres | 2023820521
data | 2023782337
jk | 2023822188
template1 | 2073823552
workqueue | 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