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

On Thu, Jul 12, 2012 at 10:16:18PM +0000, mfwilson(at)gmail(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6733
> Logged by: Mike Wilson
> Email address: mfwilson(at)gmail(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +