Thread: 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
Attachment
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. +
Attachment
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