Prev: [HACKERS] Documentation Bug/Misnomer?
Next: pg_upgrade - link mode and transaction-wraparound data loss
From: Bruce Momjian on 18 May 2010 12:57
jesper(a)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.

--
Bruce Momjian <bruce(a)momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Bruce Momjian on 18 May 2010 14:52
jesper(a)krogh.cc wrote:
> > 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..

Great.

> jk(a)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(a)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

This line above looks very odd because I didn't think the template0
datfrozenxid could be advanced. Can I see the output of this query:

SELECT datname, datfrozenxid, datallowconn FROM pg_database;

I am wondering if you set datallowconn for template0 to 'true'.

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

My other idea is that somehow recovery touches datallowconn for
template0.

--
Bruce Momjian <bruce(a)momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Bruce Momjian on 18 May 2010 15:56
Jesper Krogh wrote:
> On 2010-05-18 20:52, Bruce Momjian wrote:
> > This line above looks very odd because I didn't think the template0
> > datfrozenxid could be advanced. Can I see the output of this query:
> >
> > SELECT datname, datfrozenxid, datallowconn FROM pg_database;
> >
> >
>
> Only from the "old" database:
> data=# SELECT datname, datfrozenxid, datallowconn FROM pg_database;
> datname | datfrozenxid | datallowconn
> -----------+--------------+--------------
> template0 | 2073823552 | f
> postgres | 2023820521 | t
> data | 2023782337 | t
> jk | 2023822188 | t
> template1 | 2073823552 | t
> workqueue | 2023822188 | t
> (6 rows)

OK, datallowconn = false is right for template0, but I am still confused
how it got set to that high value.

> > I am wondering if you set datallowconn for template0 to 'true'.
>
> From this database, I cannot give any more results, I ran some other
> queries and then restarted postgres, subsequently it seemed
> totally broken. I'm in the process of running the test over again, but
> it'll take a while before data is in. I'll report back.

OK, thanks. This does seem odd. Frankly, having template0's
datfrozenxid be wrong would not cause any kind of instability because
template0 is used only by pg_dump, so I am wondering if something else
is seriously wrong.

--
Bruce Momjian <bruce(a)momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Bruce Momjian on 18 May 2010 16:17
jesper(a)krogh.cc wrote:
> First the new one..
>
> jk(a)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)

I just ran a test and all the datfrozenxids are less than the current
xid, so the only database that could be generating a wraparound warning
is 'template0'. But, again, I though that template0 was not touched for
wraparound protection --- I am starting to think I am wrong.

--
Bruce Momjian <bruce(a)momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Bruce Momjian on 18 May 2010 16:32
Alvaro Herrera wrote:
> Excerpts from jesper's message of mar may 18 13:22:12 -0400 2010:
>
> > jk(a)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)
>
> Does the old server have pg_database.datallowconn = true for template0?

The user reported back that it did not:

Only from the "old" database:
data=# SELECT datname, datfrozenxid, datallowconn FROM pg_database;
datname | datfrozenxid | datallowconn
-----------+--------------+--------------
template0 | 2073823552 | f
postgres | 2023820521 | t
data | 2023782337 | t
jk | 2023822188 | t
template1 | 2073823552 | t
workqueue | 2023822188 | t
(6 rows)

--
Bruce Momjian <bruce(a)momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 |  Next  |  Last
Pages: 1 2
Prev: [HACKERS] Documentation Bug/Misnomer?
Next: pg_upgrade - link mode and transaction-wraparound data loss