Re: BUG #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2) - Mailing list pgsql-bugs

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