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@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
[email protected]
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 <[email protected]> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
