9.2 to 9.5 pg_upgrade losing data

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Directories are correct. We do not utilize tablespaces.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

We have not found any obvious differences, other than the size of the databases (~ 100 gig on the testing site vs 400 Gig in production). In fact, the upgrade path seems to work with our production db when testing on our offsite replica that lives in our backup datacenter, near identical hardware and setup with same install scripts.

On 08/15/2016 08:04 AM, Pete Fuller wrote:

We have not found any obvious differences, other than the size of the
databases (~ 100 gig on the testing site vs 400 Gig in production). In
fact, the upgrade path seems to work with our production db when testing
on our offsite replica that lives in our backup datacenter, near
identical hardware and setup with same install scripts.

Hmm, tried another cup of coffee, that did not work, I got nothing at
the moment:(

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,
On Mon, 2016-08-15 at 08:45 -0700, Adrian Klaver wrote:

On 08/15/2016 08:04 AM, Pete Fuller wrote:

We have not found any obvious differences, other than the size of
the
databases (~ 100 gig on the testing site vs 400 Gig in
production).  In
fact, the upgrade path seems to work with our production db when
testing
on our offsite replica that lives in our backup datacenter, near
identical hardware and setup with same install scripts.

Hmm, tried another cup of coffee, that did not work, I got nothing
at 
the moment:(

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Assuming you have enough spare disk capacity, have you not thought of
going from 9.2 to 9.3 and from there to 9.4, etc., instead of doing it
in one giant leap?

If the upgrade to 9.3 does not cause any hassles, you could at least
put that into production and then test out the 9.3 to 9.4 upgrade, and
so on.

Just my two cents worth.

Cheers,
rob

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 08/15/2016 08:04 AM, Pete Fuller wrote:

We have not found any obvious differences, other than the size of the
databases (~ 100 gig on the testing site vs 400 Gig in production). In
fact, the upgrade path seems to work with our production db when testing
on our offsite replica that lives in our backup datacenter, near
identical hardware and setup with same install scripts.

Idea that popped into head, how do you get the data to the dbs in the
offsite replica versus how do you get the data to the live replica?

Also are all the machines running the same chip architecture?

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Same chips, same model Dell Poweredge actually. The process is pretty much the same between the local and offsite replicas, we have a home-brew script that runs an rsync from the master to the replica. On the local replica, it then takes the machine out of pacemaker standby, on the offsite replica it starts up postgres directly.

One thought we had today. There is a chance some local crons and some monitoring scripts might be attempting to run queries on the DB during the migration. Is there any chance that could foul something up? its the one difference we could think of after a brainstorming session here. We are going to attempt another test this evening with all crons disabled and monitoring disabled.

Pete Fuller, Tracking Server Systems Administrator
2055 North Brown Road, Suite 225, Lawrenceville, GA 30043
M: 678.677.5864
pete_fuller@3sisecurity.com │ 3sisecurity.com <http://www.3sisecurity.com/&gt;

Innovation That Protects

On 08/15/2016 11:52 AM, Pete Fuller wrote:

Same chips, same model Dell Poweredge actually. The process is pretty
much the same between the local and offsite replicas, we have a
home-brew script that runs an rsync from the master to the replica. On
the local replica, it then takes the machine out of pacemaker standby,
on the offsite replica it starts up postgres directly.

One thought we had today. There is a chance some local crons and some
monitoring scripts might be attempting to run queries on the DB during
the migration. Is there any chance that could foul something up? its
the one difference we could think of after a brainstorming session here.
We are going to attempt another test this evening with all crons
disabled and monitoring disabled.

https://www.postgresql.org/docs/9.5/static/pgupgrade.html

"Obviously, no one should be accessing the clusters during the upgrade.
pg_upgrade defaults to running servers on port 50432 to avoid unintended
client connections. You can use the same port number for both clusters
when doing an upgrade because the old and new clusters will not be
running at the same time. However, when checking an old running server,
the old and new port numbers must be different."

In your OP you do not show overriding pg_upgrade defaults for ports, so
assuming the scripts are looking for the live ports and not the upgrade
ports that should not be an issue, with the caveat below.

There is is windows of opportunity here(again from your OP):

"
• 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.
"

Pete Fuller, Tracking Server Systems Administrator
2055 North Brown Road, Suite 225, Lawrenceville, GA 30043
M: 678.677.5864
pete_fuller@3sisecurity.com <mailto:pete_fuller@3sisecurity.com>
│ 3sisecurity.com <http://www.3sisecurity.com/&gt;

/Innovation That Protects/

On Aug 15, 2016, at 1:54 PM, Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 08/15/2016 08:04 AM, Pete Fuller wrote:

We have not found any obvious differences, other than the size of the
databases (~ 100 gig on the testing site vs 400 Gig in production). In
fact, the upgrade path seems to work with our production db when testing
on our offsite replica that lives in our backup datacenter, near
identical hardware and setup with same install scripts.

Idea that popped into head, how do you get the data to the dbs in the
offsite replica versus how do you get the data to the live replica?

Also are all the machines running the same chip architecture?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Aug 15, 2016 at 12:18:04PM -0700, Adrian Klaver wrote:

https://www.postgresql.org/docs/9.5/static/pgupgrade.html

"Obviously, no one should be accessing the clusters during the upgrade.
pg_upgrade defaults to running servers on port 50432 to avoid unintended
client connections. You can use the same port number for both clusters when
doing an upgrade because the old and new clusters will not be running at the
same time. However, when checking an old running server, the old and new
port numbers must be different."

In your OP you do not show overriding pg_upgrade defaults for ports, so
assuming the scripts are looking for the live ports and not the upgrade
ports that should not be an issue.

Agreed. I have no idea what would cause this, and have never heard a
report like this before.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Aug 15, 2016 at 3:56 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Mon, Aug 15, 2016 at 12:18:04PM -0700, Adrian Klaver wrote:

https://www.postgresql.org/docs/9.5/static/pgupgrade.html

"Obviously, no one should be accessing the clusters during the upgrade.
pg_upgrade defaults to running servers on port 50432 to avoid unintended
client connections. You can use the same port number for both clusters

when

doing an upgrade because the old and new clusters will not be running at

the

same time. However, when checking an old running server, the old and new
port numbers must be different."

In your OP you do not show overriding pg_upgrade defaults for ports, so
assuming the scripts are looking for the live ports and not the upgrade
ports that should not be an issue.

Agreed. I have no idea what would cause this, and have never heard a
report like this before.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Just out of curiosity, have you you ANALYZE on you db after the upgrade but
before doing a count compare?

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

We first noticed the issue after running the generated analyze script, in subsequent tests it shows up before the analyze though.

Ran thru the process as a test monday night and it worked. This time however, I deleted the complete 9.2 data directory before running our rsync scripts that refresh the data and reestablish replication to the master before the attempt. Also disabled all crons and monitoring (zabbix) that could possibly query the server during the attempt.
Will attempt this again tonight using the same process and see if it will work correctly again.