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 directory is 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