Thread: BUG #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2)
The following bug has been logged on the website:
Bug reference: 6733
Logged by: Mike Wilson
Email address: mfwilson@gmail.com
PostgreSQL version: 9.1.4
Operating system: Solaris 10
Description:=20=20=20=20=20=20=20=20
Filing this under PG914 since there isn't an option for PG9.2beta2.=20
Possibly this should be filed elsewhere? If so apologies in advance.
We are starting to prepare for the PG9.2 release so I have been performing
some test pg_upgrades on our existing PG8.4.2 cluster. We have a sizable
database and limited space so we have been testing upgrading our cluster
using --link mode of pg_upgrade. After performing an upgrade though the new
PG92 database has only empty tables.
Running this:
export NEWPG=3D/usr/postgres/postgresql-9.2.0_slony-2.1.1
export OLDPG=3D/usr/postgres/pg842_slony210
LD_LIBRARY_PATH=3D${NEWPG}/lib PATH=3D${NEWPG}/bin:${PATH} /usr/bin/time
${NEWPG}/bin/pg_upgrade --verbose --link \
--old-datadir=3D/opt/postgres/db/root/old
--new-datadir=3D/opt/postgres/db/root/new --old-bindir=3D${OLDPG}/bin/64/ \
--new-bindir=3D${NEWPG}/bin/ --old-port=3D5432 --new-port=3D5920 --user=3Dp=
ostgres
This runs without errors and I am able to start the new upgraded PG92beta2
cluster. The schemas appear to be correct as well as the table definitions
and other schema objects. The tables themselves though are all empty. Not
sure what is going on
mfwilson@gmail.com writes:
> This runs without errors and I am able to start the new upgraded PG92beta2
> cluster. The schemas appear to be correct as well as the table definitions
> and other schema objects. The tables themselves though are all empty.
Hmm, maybe it's confused about XID past/future? Could we see the output
of pg_controldata for both old and new clusters?
regards, tom lane
On Thu, Jul 12, 2012 at 10:16:18PM +0000, mfwilson@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6733
> Logged by: Mike Wilson
> Email address: mfwilson@gmail.com
> PostgreSQL version: 9.1.4
> Operating system: Solaris 10
> Description:
>
> Filing this under PG914 since there isn't an option for PG9.2beta2.
> Possibly this should be filed elsewhere? If so apologies in advance.
>
> We are starting to prepare for the PG9.2 release so I have been performing
> some test pg_upgrades on our existing PG8.4.2 cluster. We have a sizable
> database and limited space so we have been testing upgrading our cluster
> using --link mode of pg_upgrade. After performing an upgrade though the new
> PG92 database has only empty tables.
>
> Running this:
> export NEWPG=/usr/postgres/postgresql-9.2.0_slony-2.1.1
> export OLDPG=/usr/postgres/pg842_slony210
>
> LD_LIBRARY_PATH=${NEWPG}/lib PATH=${NEWPG}/bin:${PATH} /usr/bin/time
> ${NEWPG}/bin/pg_upgrade --verbose --link \
> --old-datadir=/opt/postgres/db/root/old
> --new-datadir=/opt/postgres/db/root/new --old-bindir=${OLDPG}/bin/64/ \
> --new-bindir=${NEWPG}/bin/ --old-port=5432 --new-port=5920 --user=postgres
>
> This runs without errors and I am able to start the new upgraded PG92beta2
> cluster. The schemas appear to be correct as well as the table definitions
> and other schema objects. The tables themselves though are all empty. Not
> sure what is going on
OK, I just tested an upgrade from 9.1.4 to 9.2 HEAD and it worked just
fine using the regression database as content. The big question is what
is different about your setup. Can you check the data files in
/data/base/db_oid and see if they are all zero length in the new
cluster? A simple 'ls -l' should show it:
$ pwd
/u/pg/data/base/16413
$ ls -l
-rw------- 1 postgres postgres 122880 Jul 12 18:39 11744
-rw------- 1 postgres postgres 24576 Jul 12 18:39 11744_fsm
-rw------- 1 postgres postgres 8192 Jul 12 18:39 11744_vm
-rw------- 1 postgres postgres 16384 Jul 12 18:39 11746
-rw------- 1 postgres postgres 24576 Jul 12 18:39 11746_fsm
-----
These should _not_ be all zeros. Please let me know what you find.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Thu, Jul 12, 2012 at 06:44:06PM -0400, Tom Lane wrote: > mfwilson@gmail.com writes: > > This runs without errors and I am able to start the new upgraded PG92beta2 > > cluster. The schemas appear to be correct as well as the table definitions > > and other schema objects. The tables themselves though are all empty. > > Hmm, maybe it's confused about XID past/future? Could we see the output > of pg_controldata for both old and new clusters? Uh, I thought we only changfed the xlog stuff in 9.3, not 9.2, so I am confused what would have changed in that area. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
This can be closed. I figured out what I was doing wrong, which was after = the conversion I was cleaning up the old datadir by deleting it, which dest= royed the hard links to the data since I am using pg_upgrade --link Mike Wilson On Jul 12, 2012, at 3:49 PM, Bruce Momjian wrote: > On Thu, Jul 12, 2012 at 06:44:06PM -0400, Tom Lane wrote: >> mfwilson@gmail.com writes: >>> This runs without errors and I am able to start the new upgraded PG92be= ta2 >>> cluster. The schemas appear to be correct as well as the table definit= ions >>> and other schema objects. The tables themselves though are all empty. >>=20 >> Hmm, maybe it's confused about XID past/future? Could we see the output >> of pg_controldata for both old and new clusters? >=20 > Uh, I thought we only changfed the xlog stuff in 9.3, not 9.2, so I am > confused what would have changed in that area. >=20 > --=20 > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com >=20 > + It's impossible for everything to be true. +
On Thu, Jul 12, 2012 at 05:21:31PM -0700, Mike Wilson wrote: > This can be closed. I figured out what I was doing wrong, which was > after the conversion I was cleaning up the old datadir by deleting it, > which destroyed the hard links to the data since I am using pg_upgrade > --link Uh, actually, a hard link has two directory entries pointing to the same file, so you can delete the old datadir and the new datadir should not be affected. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
I've had some time to examine this closer over the weekend. It appears tha=
t pg_upgrade for 9.2b2 segfaults which more than likely has something to do=
with the resulting converted database appearing to have no rows. Earlier =
in this thread I reported that I was able to get the upgrade to work and th=
is thread to be closed but I was in error. At the time I was also testing =
with the 9.1.4 pg_upgrade which does work and I thought that I had a succes=
sful 9.2b2 pg_upgrade run. Apologies for the confusion and let me know if =
you would like me to start a new thread.
<pg_upgrade 9.2b2>
...
pg_toast.pg_toast_948075_index: 948081 to 948081
c0.page_metadata_values_pkey: 948082 to 948082
c0.i_page_metadata_values_short_name: 948084 to 948084
Segmentation Fault (core dumped)
root@db4 /
</>
My upgrade procedure is scripted and I hadn't noticed the core dump when I =
first reported the bug. Here are the parameters of the run:
su - postgres -c "pg_upgrade --verbose --link \
--old-datadir=3D/opt/postgres/db/root/old --new-datadir=3D/opt/postgres/db/=
root/new --old-bindir=3D${OLDPG}/bin/64/ \
--new-bindir=3D${NEWPG}/bin/ --old-port=3D5432 --new-port=3D5920 --user=3Dp=
ostgres"
As a test I have also been using the pg_upgrade from 9.1.4 which does work:
<pg_upgrade 9.1.4>
=85
relname: pg_toast.pg_toast_948075: reloid: 948079 reltblspace:=20
relname: pg_toast.pg_toast_948075_index: reloid: 948081 reltblspace:=20
relname: c0.page_metadata_values_pkey: reloid: 948082 reltblspace:=20
relname: c0.i_page_metadata_values_short_name: reloid: 948084 reltblspace:=
=20
Database: postgres
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:=20
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:=
=20
Database: template1
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:=20
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:=
=20
executing: SELECT spclocation FROM pg_catalog.pg_tablespace WH=
ERE spcname !=3D 'pg_default' AND spcname !=3D 'pg_global'
=85
</>
I've also tried a step-wise migration by first converting to PG914 and then=
to PG92b2. This also fails with a similar segfault after the c0.i_page_me=
tadata_values_short_name index.=20=20
Of possible note in this DB is that the previous DBA renamed the "postgres"=
user. As part of this conversion process I am renaming it back to it's de=
fault. I'm doing this before running pg_upgrade:
# shift jibjab su (postgres) account back to postgres rolname
su - postgres -c "psql -U jibjab c0 -c \"update pg_authid set rolname=3D'po=
stgres' where oid=3D10;\""
This probably isn't an issue as the 9.1.4 conversion works but I thought I =
should at least mention it. Actually I don't think pg_upgrade will run cor=
rectly if there isn't a postgres user so I imagine I need to correct this i=
ssue before running the upgrade procedure anyway.
For now I am stymied in my attempt to upgrade and may have to look at tryin=
g to get the non-link version of the upgrade working. That would be relati=
vely painful though as this upgrade will be for a commercial internet site =
that can't easily tolerate a long down and the production DB is over a TB i=
n size. I am really looking forward to 9.2's index only scans due to the s=
ize of the DB!=20=20
Cheers and thanks for any information you have on the issue.
Mike Wilson
mfwilson@gmail.com
On Jul 12, 2012, at 6:52 PM, Bruce Momjian wrote:
> On Thu, Jul 12, 2012 at 05:21:31PM -0700, Mike Wilson wrote:
>> This can be closed. I figured out what I was doing wrong, which was
>> after the conversion I was cleaning up the old datadir by deleting it,
>> which destroyed the hard links to the data since I am using pg_upgrade
>> --link
>=20
> Uh, actually, a hard link has two directory entries pointing to the same
> file, so you can delete the old datadir and the new datadir should not
> be affected.
>=20
> --=20
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>=20
> + It's impossible for everything to be true. +
Mike Wilson <mfwilson@gmail.com> writes:
> I've had some time to examine this closer over the weekend. It
> appears that pg_upgrade for 9.2b2 segfaults which more than likely has
> something to do with the resulting converted database appearing to
> have no rows.
Yeah, more than likely :-(. Could we see a stack trace from the
segfault?
> Of possible note in this DB is that the previous DBA renamed the
> "postgres" user.
Hmm. There is a known bug in beta2 that's triggered by old and new
clusters not having the same name for the bootstrap superuser; although
I don't recall that the symptoms included a segfault. In any case,
I'd suggest making sure the new cluster is initdb'd under the same
account that currently owns the old cluster.
regards, tom lane
core stack: root@db4 / $ pstack ~postgres/core core '/opt/postgres/core' of 19868: pg_upgrade --verbose --link --old-d= atadir=3D/opt/postgres/db/root/old -- fffffd7ffeda1148 memcpy () + 6b8 000000000040b8b6 transfer_single_new_db () + fa 000000000040b6ea transfer_all_new_dbs () + 116 000000000040ae62 main () + 106 000000000040580c ???????? () As to the ownership, the bash script I am testing 9.1.4 and 9.2.0 with recu= rsively chowns the directory that owns the old and the new PGDATA directory= before running pg_upgrade.=20=20 Mike Wilson mfwilson@gmail.com On Jul 15, 2012, at 2:45 PM, Tom Lane wrote: > Mike Wilson <mfwilson@gmail.com> writes: >> I've had some time to examine this closer over the weekend. It >> appears that pg_upgrade for 9.2b2 segfaults which more than likely has >> something to do with the resulting converted database appearing to >> have no rows. >=20 > Yeah, more than likely :-(. Could we see a stack trace from the > segfault? >=20 >> Of possible note in this DB is that the previous DBA renamed the >> "postgres" user. >=20 > Hmm. There is a known bug in beta2 that's triggered by old and new > clusters not having the same name for the bootstrap superuser; although > I don't recall that the symptoms included a segfault. In any case, > I'd suggest making sure the new cluster is initdb'd under the same > account that currently owns the old cluster. >=20 > regards, tom lane
Just for my interest I also created a new PG 842 db (initdb) and put some s= ample data it it and successfully did a pg_upgrade from 842 -> 920b2. What= ever the issue is it seems to be related to my actual database as a sample = db conversion works. Again, 842-914 conversion works for my db though so p= ossibly it was some change that happened to pg_upgrade since 914 was releas= ed that is having poor interaction with my actual db cluster. Also, I wanted to make sure I wasn't using some git checkout of 920 I downl= oaded the postgresql-9.2.beta2.tgz from the PostgreSQL site dir-browser and= re-compiled. Still have the problem unfortunately. Cheers. Mike Wilson mfwilson@gmail.com On Jul 15, 2012, at 2:45 PM, Tom Lane wrote: > Mike Wilson <mfwilson@gmail.com> writes: >> I've had some time to examine this closer over the weekend. It >> appears that pg_upgrade for 9.2b2 segfaults which more than likely has >> something to do with the resulting converted database appearing to >> have no rows. >=20 > Yeah, more than likely :-(. Could we see a stack trace from the > segfault? >=20 >> Of possible note in this DB is that the previous DBA renamed the >> "postgres" user. >=20 > Hmm. There is a known bug in beta2 that's triggered by old and new > clusters not having the same name for the bootstrap superuser; although > I don't recall that the symptoms included a segfault. In any case, > I'd suggest making sure the new cluster is initdb'd under the same > account that currently owns the old cluster. >=20 > regards, tom lane
Mike Wilson <mfwilson@gmail.com> writes:
> Just for my interest I also created a new PG 842 db (initdb) and put
> some sample data it it and successfully did a pg_upgrade from 842 ->
> 920b2. Whatever the issue is it seems to be related to my actual
> database as a sample db conversion works.
Yeah, I was suspecting that, because there's no obvious problem in the
part of pg_upgrade that your stack trace fingers.
What we need to do next is get something that one of the PG developers
can put under a microscope. What I would suggest first is doing a
"pg_dumpall -s" (ie no data) of your database, and seeing whether
loading that into 8.4 produces a database on which pg_upgrade fails.
If so, then you could send us that dump (perhaps after some sanitizing
of names) and we could have at it.
If you aren't able to create a self-contained reproducible case in this
way, the only other way forward is for you to debug it yourself or let
one of the PG developers have access to your system to poke at it.
regards, tom lane
On Sun, Jul 15, 2012 at 02:15:35PM -0700, Mike Wilson wrote: > I've had some time to examine this closer over the weekend. It appears that > pg_upgrade for 9.2b2 segfaults which more than likely has something to do with > the resulting converted database appearing to have no rows. Earlier in this > thread I reported that I was able to get the upgrade to work and this thread to > be closed but I was in error. At the time I was also testing with the 9.1.4 > pg_upgrade which does work and I thought that I had a successful 9.2b2 > pg_upgrade run. Apologies for the confusion and let me know if you would like > me to start a new thread. > > <pg_upgrade 9.2b2> > ... > pg_toast.pg_toast_948075_index: 948081 to 948081 > c0.page_metadata_values_pkey: 948082 to 948082 > c0.i_page_metadata_values_short_name: 948084 to 948084 > > > Segmentation Fault (core dumped) What is the actual program output that appears before the core dump message? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Tue, Jul 17, 2012 at 04:01:08PM -0700, Mike Wilson wrote: > Please find below the full pg_upgrade output. Let me know if there are any other questions that I may have missed. Note,the attached pg_upgrade stdout log is for the schema only pg_dumpall upgrade attempt from PG842 -> PG920b2. > > -rw-r--r-- mwilson/staff 314381 2012-07-17 18:57 pg842_pg_upgrade.log FYI, I was supplied with an anonymous SQL dump of the database and was unable to reproduce the failure on Debian. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Given the stack trace previously supplied and that the pg_upgrade from 9.1.= 4 does actually complete successfully can you recommend how I might continu= e to diagnose the issue myself? Since the 9.1.4 version of pg_upgrade did = work I've started to look at the source for pg_upgrade in an attempt to see= if the 9.1.4 version of the tool could be used instead of the 9.2.0 versio= n. Due to the # of differences in the pg_upgrade source though a port seems= unlikely. Can I get the git version string of the PG source you used for the version = of the source that you tested with? I'd like to make sure that I'm using t= he same version as your successful test. Thanks. Mike Wilson mfwilson@gmail.com On Jul 17, 2012, at 4:39 PM, Bruce Momjian wrote: > On Tue, Jul 17, 2012 at 04:01:08PM -0700, Mike Wilson wrote: >> Please find below the full pg_upgrade output. Let me know if there are = any other questions that I may have missed. Note, the attached pg_upgrade = stdout log is for the schema only pg_dumpall upgrade attempt from PG842 -> = PG920b2.=20=20 >>=20 >=20 >> -rw-r--r-- mwilson/staff 314381 2012-07-17 18:57 pg842_pg_upgrade.log >=20 > FYI, I was supplied with an anonymous SQL dump of the database and was > unable to reproduce the failure on Debian. >=20 > --=20 > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com >=20 > + It's impossible for everything to be true. +
On Tue, Jul 17, 2012 at 05:41:05PM -0700, Mike Wilson wrote: > Given the stack trace previously supplied and that the pg_upgrade from 9.1.4 > does actually complete successfully can you recommend how I might continue to > diagnose the issue myself? Since the 9.1.4 version of pg_upgrade did work I've > started to look at the source for pg_upgrade in an attempt to see if the 9.1.4 > version of the tool could be used instead of the 9.2.0 version. Due to the # of > differences in the pg_upgrade source though a port seems unlikely. > > Can I get the git version string of the PG source you used for the version of > the source that you tested with? I'd like to make sure that I'm using the same > version as your successful test. Thanks. I am using git head for testing. Tom sees a few things odd in load_directory() that might be causing some problems on Solaris, and this is new code for 9.2 for Solaris, so that might explain it. I think we need Tom to finish and then if you can grab our git source and test that, it would be great! -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> I am using git head for testing. Tom sees a few things odd in
> load_directory() that might be causing some problems on Solaris, and
> this is new code for 9.2 for Solaris, so that might explain it. I think
> we need Tom to finish and then if you can grab our git source and test
> that, it would be great!
The only thing I see that looks likely to represent a platform-specific
issue is the entrysize calculation. Mike, just out of curiosity, could
you see if the attached patch makes things better for you?
regards, tom lane
diff --git a/contrib/pg_upgrade/file.c b/contrib/pg_upgrade/file.c
index 1dd3722142c9e83c1ec228099c3a3fd302a2179b..c886a67df43792a1692eec6b3b90238413e9f844 100644
*** a/contrib/pg_upgrade/file.c
--- b/contrib/pg_upgrade/file.c
*************** load_directory(const char *dirname, stru
*** 259,265 ****
return -1;
}
! entrysize = sizeof(struct dirent) - sizeof(direntry->d_name) +
strlen(direntry->d_name) + 1;
(*namelist)[name_num] = (struct dirent *) malloc(entrysize);
--- 259,265 ----
return -1;
}
! entrysize = offsetof(struct dirent, d_name) +
strlen(direntry->d_name) + 1;
(*namelist)[name_num] = (struct dirent *) malloc(entrysize);
Tom, after patching pg_upgrade now runs successfully. I noticed that this = patch had been applied since yesterday to the REL9_2_STABLE so I also teste= d with a git pull without the patch that appears to work also. I think iss= ue has been resolved for me, thanks so much! You guys rock! Mike Wilson mfwilson@gmail.com On Jul 17, 2012, at 9:31 PM, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: >> I am using git head for testing. Tom sees a few things odd in >> load_directory() that might be causing some problems on Solaris, and >> this is new code for 9.2 for Solaris, so that might explain it. I think >> we need Tom to finish and then if you can grab our git source and test >> that, it would be great! >=20 > The only thing I see that looks likely to represent a platform-specific > issue is the entrysize calculation. Mike, just out of curiosity, could > you see if the attached patch makes things better for you? >=20 > regards, tom lane >=20 > diff --git a/contrib/pg_upgrade/file.c b/contrib/pg_upgrade/file.c > index 1dd3722142c9e83c1ec228099c3a3fd302a2179b..c886a67df43792a1692eec6b3= b90238413e9f844 100644 > *** a/contrib/pg_upgrade/file.c > --- b/contrib/pg_upgrade/file.c > *************** load_directory(const char *dirname, stru > *** 259,265 **** > return -1; > } >=20 > ! entrysize =3D sizeof(struct dirent) - sizeof(direntry->d_name) + > strlen(direntry->d_name) + 1; >=20 > (*namelist)[name_num] =3D (struct dirent *) malloc(entrysize); > --- 259,265 ---- > return -1; > } >=20 > ! entrysize =3D offsetof(struct dirent, d_name) + > strlen(direntry->d_name) + 1; >=20 > (*namelist)[name_num] =3D (struct dirent *) malloc(entrysize);