Re: 9.2 to 9.5 pg_upgrade losing data - Mailing list pgsql-general

On Aug 15, 2016, at 10:55 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 08/15/2016 07:40 AM, Pete Fuller wrote:

Directories are correct.  We do not utilize tablespaces.

Anything obviously different in the setup between your production servers and the testing and development clusters?

On Aug 15, 2016, at 10:06 AM, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 08/15/2016 06:20 AM, Pete Fuller wrote:

Hello all,

We are attempting to upgrade a production 9.2 postgres cluster to
9.5. The server is running Centos 7 with the Centos version -
currently 9.2.15 We are installing the postgresql provided rpm of
postgresql9.5 from the postgresql repo, currently 9.5.4. In testing
and on our development cluster, the upgrade worked without issue but
something is happening on our production DB that we can not figure out.

The 9.2 cluster is in streaming replication controlled by pacemaker.
Steps we have been using are the following.

• verify the replica server is in a good state with up to date data,
then put the machine in standby, stopping the 9.2 db
• Removing the recovery.conf file and starting the 9.2 server
manually to get some stats to verify later (counts of rows from
several key tables)
• Stop the 9.2 server, (/bin/pg_ctl stop -D /var/lib/pgsql/data ) and
install the 9.5 binaries.
• init the 9.5 db
• run a pg_upgrade with the link option. The full command we are using is
/usr/pgsql-9.5/bin/pg_upgrade -d /var/lib/pgsql/data -D
/var/lib/pgsql/9.5/data -b /bin -B /usr/pgsql-9.5/bin/ --link -r -v

Are you sure you are linking the appropriate directories?

Are there any tablespaces in the mix?

• run the full vacuum analyze script that pg_upgrade generated

What we are finding in testing is we get thru the upgrade process,
start the 9.5 server manually to compare the row counts we collected
immediately before the upgrade and we are seeing different data,
sometimes drastically. This data includes missing user accounts, etc,
that is not perishable data.

For example - 9.2 standalone before running pg_upgrade gives this
information
tracks=# select count(*) from users;
count
-------
13945
(1 row)

tracks=# select count(*) from sessions;
count
-------
 559
(1 row)

PG 9.5 after the upgrade - same machine, upgraded database

tracks=# select count(*) from sessions;
-------
 155
(1 row)

tracks=# select count(*) from users;
count
-------
13157
(1 row)


We have enabled verbose logging and have reviewed it but are seeing
no errors in the migration. The 9.5 server starts without complaint
and if we did not do these manual checks we would not see a problem.
We have attempted this upgrade on this machine repeatedly in prepping
for our maintenance windows, and on occasion our manual checks have
shown good numbers.  At this point, though, it has failed silently
enough times that I have little confidence in the data even if our
rudimentary checks do show identical counts after the upgrade.

Has anyone else seen this issue?

The only 'hack' during the upgrade that isn't straight from the
postgres_upgrade man page is to handle a renamed option in postgresql
that cents back ported - unix_socket_directories. Using the rename of
pg_ctl that is referenced in the top answer here to force a couple
variables into the centos provided pg_ctl binary -
http://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configuration-parameter-unix-socket-directory


Any information or suggestions would be helpful at this point.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

-- 
Adrian Klaver
adrian.klaver@aklaver.com