Re: BUG #6733: All Tables Empty After pg_upgrade (PG 9.2.0 beta 2)

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)
root(at)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=/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"

As a test I have also been using the pg_upgrade from 9.1.4 which does work:
<pg_upgrade 9.1.4>
…
relname: pg_toast.pg_toast_948075: reloid: 948079 reltblspace:
relname: pg_toast.pg_toast_948075_index: reloid: 948081 reltblspace:
relname: c0.page_metadata_values_pkey: reloid: 948082 reltblspace:
relname: c0.i_page_metadata_values_short_name: reloid: 948084 reltblspace:

Database: postgres
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:

Database: template1
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:

executing: SELECT spclocation FROM pg_catalog.pg_tablespace WHERE spcname != 'pg_default' AND spcname != 'pg_global'
…
</>

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

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 default. 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='postgres' where oid=10;\""

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 correctly if there isn't a postgres user so I imagine I need to correct this issue before running the upgrade procedure anyway.

For now I am stymied in my attempt to upgrade and may have to look at trying to get the non-link version of the upgrade working. That would be relatively 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 in size. I am really looking forward to 9.2's index only scans due to the size of the DB!

Cheers and thanks for any information you have on the issue.

Mike Wilson
mfwilson(at)gmail(dot)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
>
> 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(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +