Обсуждение: 9.5 beta pg_upgrade documentation

Hi All.

I setup two test VM's with my PG93 database to test upgrading to PG95.
I have a primary and standby using wal shipping.
The database is about 150Gig, and the two servers (the real servers) are
far apart.  The vm's are both running on my desktop.

I would love to use pg_upgrade on both primary and standby to save from
copying 150Gig a very long distance.

I'm reading:
http://www.postgresql.org/docs/9.5/static/pgupgrade.html

At step 9, the first sentence says "If you have ... Log-Shipping ...
follow these steps .. before starting any servers".

At first I thought a step was missing because it never says to run
pg_upgrade on the standby.  Then I realized you only run pg_upgrade on
the primary then rsync the standby.  Would a quick description of the
process be helpful?  Something like:

"If you have Streaming Replication (Section 25.2.5) or Log-Shipping
(Section 25.2) standby servers, follow these steps to upgrade them.
After you have performed pg_upgrade on the primary don't start it up yet
because you can rsync it to the standby for an efficient standby upgrade."



I'm confused by step 5 (verify).  There are 4 PG instances we're talking
about (primary new/old and standby new/old)  Which two do I run
pg_controldata on?  And how does running it "prevent old standby servers
from being modified"?  And if step 5 requires standby shutdown after the
primary, isn't that an important thing to say near the top?  Maybe by
step 7: Stop both servers.


On a side note, I'm confusing myself by the step numbers.  There's two
step 7's.  Can we renumber the step 9 sub steps to be 9.1, 9.2, etc?


I think I understand step 9.7, of the four PG instances, "the new
master" tells me which to start and stop.  Although I'm not sure how
long I need to keep it up.  Is as fast as I can type enough time?
Should I count a few potato's between stop and start?

Step 9.8 sounds scary.  Can we specifically say that old_pgdata refers
to the new pg on the primary and new_pgdata refers to the new pg on the
standby?  Is that even right?  Any chance we could get examples of
setting old/new_pgdata?

Thanks all on another great release!

-Andy

Andy Colson wrote:

> On a side note, I'm confusing myself by the step numbers.  There's two step
> 7's.  Can we renumber the step 9 sub steps to be 9.1, 9.2, etc?

I've had this lying about for a while, which does more or less what you
want, numbering the substeps "a, b, c" instead of "1, 2, 3".

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

I finished running pg_upgrade on the primary, so far so good, and now I'm at step 9.5 (Verify).

On the primary I see:
root@test1:/pub/pg95# /usr/local/pg95/bin/pg_controldata -D /pub/pg95|grep "Latest check"
Latest checkpoint location:           1D2/36000028

I cannot run pg93 pg_controldata because pg_control was renamed with a .old, but I'm not sure if I even need to.
pg_controldata: could not open file "/pub/pg93/global/pg_control" for reading: No such file or directory



On the standby I see:
postgres@test2:~$ /usr/local/pg93/bin/pg_controldata /pub/pg93|grep "Latest check"
Latest checkpoint location:           1D1/AF000060


So these numbers dont match, so that that mean:
1) I compared the wrong ones
2) Its broke, dont go any further
3) Its fine, rsync away

Thanks for your time,

-Andy

I think we should add a step 6.5 (before step 7 Stop both servers) with something like:

If you are upgrading both a primary and standby, then we need to make sure the standby is caught up.
If you are wal shipping then on primary run: select pg_switch_xlog();
shut down primary
before you shut down the standby make sure it gets caught up to the primary.

I don't think its 100% required for them to be exact, is it?  If they are a little different then rsync has more data
toxfer from primary to standby ... but it would still work.  Right? 

-Andy

On Sun, Aug 23, 2015 at 09:45:50AM -0500, Andy Colson wrote:
> I think we should add a step 6.5 (before step 7 Stop both servers) with something like:
>
> If you are upgrading both a primary and standby, then we need to make sure the standby is caught up.
> If you are wal shipping then on primary run: select pg_switch_xlog();
> shut down primary
> before you shut down the standby make sure it gets caught up to the primary.
>
> I don't think its 100% required for them to be exact, is it?  If they are a little different then rsync has more data
toxfer from primary to standby ... but it would still work.  Right? 

You are one of the first to use this new ability so it is good to get
your feedback.  I have developed the attached applied patch to address
the problems you saw.

First, the verification has to happen earlier, before pg_upgrade is run.
I think what is happening is that a checkpoint on server shutdown is
changing the value while pg_upgrade is running, and the rename of the
controldata file is another issue, so doing it right after the primary
is shut down is the right place.

I also added a mention that rsync, not pg_upgrade, will be run on the
standbys.  You can see all the results of the patch here:

    http://momjian.us/pgsql_docs/pgupgrade.html

Thanks.

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

  + Everyone has their own god. +

On 09/01/2015 04:09 PM, Bruce Momjian wrote:
> On Sun, Aug 23, 2015 at 09:45:50AM -0500, Andy Colson wrote:
>> I think we should add a step 6.5 (before step 7 Stop both servers) with something like:
>>
>> If you are upgrading both a primary and standby, then we need to make sure the standby is caught up.
>> If you are wal shipping then on primary run: select pg_switch_xlog();
>> shut down primary
>> before you shut down the standby make sure it gets caught up to the primary.
>>
>> I don't think its 100% required for them to be exact, is it?  If they are a little different then rsync has more
datato xfer from primary to standby ... but it would still work.  Right? 
>
> You are one of the first to use this new ability so it is good to get
> your feedback.  I have developed the attached applied patch to address
> the problems you saw.
>
> First, the verification has to happen earlier, before pg_upgrade is run.
> I think what is happening is that a checkpoint on server shutdown is
> changing the value while pg_upgrade is running, and the rename of the
> controldata file is another issue, so doing it right after the primary
> is shut down is the right place.
>
> I also added a mention that rsync, not pg_upgrade, will be run on the
> standbys.  You can see all the results of the patch here:
>
>     http://momjian.us/pgsql_docs/pgupgrade.html
>
> Thanks.
>

Sweet, I'm glad I stopped where I did.  I think I'm safe to pick up at step f.  Which seemed to work ok.  But now we
getto step g (run rsync).  I checked the rsync manual and don't see anything like this three directory argument thing
youare trying to run.  Unless you want to use --link-dest.  In which case I think the cmd would be like: 

rsync --archive --delete --hard-links --size-only --link-dest=old_pgdata new_pgdata remote_dir

I'm gonna try this now, will report back.

-Andy

On Tue, Sep  1, 2015 at 06:56:11PM -0500, Andy Colson wrote:
> >I also added a mention that rsync, not pg_upgrade, will be run on the
> >standbys.  You can see all the results of the patch here:
> >
> >    http://momjian.us/pgsql_docs/pgupgrade.html
> >
> >Thanks.
> >
>
> Sweet, I'm glad I stopped where I did.  I think I'm safe to pick up at step f.  Which seemed to work ok.  But now we
getto step g (run rsync).  I checked the rsync manual and don't see anything like this three directory argument thing
youare trying to run.  Unless you want to use --link-dest.  In which case I think the cmd would be like: 
>
> rsync --archive --delete --hard-links --size-only --link-dest=old_pgdata new_pgdata remote_dir
>
> I'm gonna try this now, will report back.

No, you are copying "old_pgdata and new_pgdata" to remote_dir.

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

  + Everyone has their own god. +

On 09/01/2015 07:00 PM, Bruce Momjian wrote:
> On Tue, Sep  1, 2015 at 06:56:11PM -0500, Andy Colson wrote:
>>> I also added a mention that rsync, not pg_upgrade, will be run on the
>>> standbys.  You can see all the results of the patch here:
>>>
>>>     http://momjian.us/pgsql_docs/pgupgrade.html
>>>
>>> Thanks.
>>>
>>
>> Sweet, I'm glad I stopped where I did.  I think I'm safe to pick up at step f.  Which seemed to work ok.  But now we
getto step g (run rsync).  I checked the rsync manual and don't see anything like this three directory argument thing
youare trying to run.  Unless you want to use --link-dest.  In which case I think the cmd would be like: 
>>
>> rsync --archive --delete --hard-links --size-only --link-dest=old_pgdata new_pgdata remote_dir
>>
>> I'm gonna try this now, will report back.
>
> No, you are copying "old_pgdata and new_pgdata" to remote_dir.
>

Ohhh... I'll try again.  (The method above (run from the standby) doesn't work well at all).

-Andy

On 09/01/2015 08:46 PM, Andy Colson wrote:
> On 09/01/2015 07:00 PM, Bruce Momjian wrote:
>> On Tue, Sep  1, 2015 at 06:56:11PM -0500, Andy Colson wrote:
>>>> I also added a mention that rsync, not pg_upgrade, will be run on the
>>>> standbys.  You can see all the results of the patch here:
>>>>
>>>>     http://momjian.us/pgsql_docs/pgupgrade.html
>>>>
>>>> Thanks.
>>>>
>>>
>>> Sweet, I'm glad I stopped where I did.  I think I'm safe to pick up at step f.  Which seemed to work ok.  But now
weget to step g (run rsync).  I checked the rsync manual and don't see anything like this three directory argument
thingyou are trying to run.  Unless you want to use --link-dest.  In which case I think the cmd would be like: 
>>>
>>> rsync --archive --delete --hard-links --size-only --link-dest=old_pgdata new_pgdata remote_dir
>>>
>>> I'm gonna try this now, will report back.
>>
>> No, you are copying "old_pgdata and new_pgdata" to remote_dir.
>>
>
> Ohhh... I'll try again.  (The method above (run from the standby) doesn't work well at all).
>
> -Andy
>
>

Yeah, much better:

postgres@test1:/pub$ rsync --archive --stats --delete --hard-links --size-only pg93 pg95 test2:/pub/

Number of files: 373,393 (reg: 373,340, dir: 53)
Number of created files: 186,779 (reg: 186,751, dir: 28)
Number of deleted files: 1 (reg: 1)
Number of regular files transferred: 1,480
Total file size: 201,329,799,433 bytes
Total transferred file size: 1,445,463,198 bytes
Literal data: 1,445,463,198 bytes
Matched data: 0 bytes
File list size: 7,208,811
File list generation time: 0.001 seconds
File list transfer time: 0.000 seconds
Total bytes sent: 1,458,717,642
Total bytes received: 5,629,388

sent 1,458,717,642 bytes received 5,629,388 bytes 8,956,250.95 bytes/sec
total size is 201,329,799,433 speedup is 137.49


test1 is the primary, and test2 the standby.  Both have /pub/pg93 and /pub/pg95
I copied over a recovery.conf and the standby started up.
I started pg95 on the primary and am running analyze now.  I'll copy over wal to the standby and continue testing.

Looks good so far!

Thanks,

-Andy