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

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