Thread: 9.2 to 9.5 pg_upgrade losing data

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,
currently9.5.4. In testing and on our development cluster, the upgrade worked without issue but something is happening
onour 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.2db 
    • Removing the recovery.conf file and starting the 9.2 server manually to get some stats to verify later (counts of
rowsfrom 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  
    • 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
countswe collected immediately before the upgrade and we are seeing different data, sometimes drastically. This data
includesmissing 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
withoutcomplaint and if we did not do these manual checks we would not see a problem. We have attempted this upgrade on
thismachine 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
ourrudimentary 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
inpostgresql that cents back ported - unix_socket_directories. Using the rename of pg_ctl that is referenced in the top
answerhere 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.

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,
currently9.5.4. In testing and on our development cluster, the upgrade worked without issue but something is happening
onour 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
the9.2 db 
>     • Removing the recovery.conf file and starting the 9.2 server manually to get some stats to verify later (counts
ofrows 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
countswe collected immediately before the upgrade and we are seeing different data, sometimes drastically. This data
includesmissing 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
withoutcomplaint and if we did not do these manual checks we would not see a problem. We have attempted this upgrade on
thismachine 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
ourrudimentary 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
optionin postgresql that cents back ported - unix_socket_directories. Using the rename of pg_ctl that is referenced in
thetop 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

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

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

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

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

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/>
>
> /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

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 +