Re: 9.3 to 9.5 upgrade problems

On 07/03/2016 10:35 AM, Adrian Klaver wrote:
> On 07/03/2016 08:06 AM, Andy Colson wrote:
>> Hi all,
>>
>> I have a master (web1) and two slaves (web2, webserv), one slave is
>> quite far from the master, the db is 112 Gig, so pg_basebackup is my
>> last resort.
>>
>> I followed the page here:
>> https://www.postgresql.org/docs/9.5/static/pgupgrade.html
>>
>> including the rsync stuff.  I practiced it _twice_, once in PG 9.5 beta,
>> and again a week ago, on two VM's I created locally.  Both practice
>> sessions worked perfect.
>>
>> I just ran it on the live databases.  The master seems ok, its running
>> PG 9.5 now, I can login to it, and no errors in the log.
>>
>> Neither slave works.  After I'd gotten done with the pgupgrade steps,
>> both slaves gave me this error:
>>
>> FATAL:  database system identifier differs between the primary and standby
>>
>> Sure enough pg_controldata show'd their database system id different
>> (all three web1, web2, webserv were different.  no matches at all), so
>> I'm assuming the rsync didnt rsync right, or I missed a step and ran it
>> to early, or something ... I'm not quite sure.
>>
>> I needed to get the live website back up and running again, so I let the
>> master go, ran analyze, and when it was finished, used the steps here to
>> try and resync:
>>
>> https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
>>
>> on Master:
>> select pg_start_backup('clone',true);
>> rsync -av --exclude pg_xlog --exclude postgresql.conf /pub/pg95/*
>> web2:/pub/pg95/
>> select pg_stop_backup();
>> rsync -av /pub/pg95/pg_xlog web2:/pub/pg95/
>
> Not sure about above rsync, that seems to undo what you did previously.
>
> Also was the remote directory empty when you did this?
>

Not sure what you mean by undo.  pgupgrade.html page, step 10, has you rsync the master to the slave, so the pg95
directoryis hard linked to the pg93, which save's a ton to bandwidth when your servers are cross county. 

My second rsync did the same thing, but only on the pg95 directory (my db lives in /pub/pg95).

No, the directory was not empty, and I'm really trying to avoid a fresh copy of 112 Gig.

-Andy