pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feed9.5 beta pg_upgrade documentation
9+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2015-09-01 9646d2fd62 c1564b3928 Use <substeps> in pg_upgrade's procedure
* 9.5 beta pg_upgrade documentation
@ 2015-08-21 20:27 Andy Colson <andy@squeakycode.net>
2015-08-21 20:44 ` Re: 9.5 beta pg_upgrade documentation Alvaro Herrera <alvherre@2ndquadrant.com>
2015-08-22 19:45 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-08-23 14:45 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
0 siblings, 3 replies; 9+ messages in thread
From: Andy Colson @ 2015-08-21 20:27 UTC (permalink / raw)
To: pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: 9.5 beta pg_upgrade documentation
2015-08-21 20:27 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
@ 2015-08-21 20:44 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2 siblings, 0 replies; 9+ messages in thread
From: Alvaro Herrera @ 2015-08-21 20:44 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-general; Bruce Momjian <bruce@momjian.us>
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Attachments:
pgupgrade-substeps.patch (text/x-diff, 592B) download
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index 072d5f4..ebc0d58 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -407,7 +407,7 @@ pg_upgrade.exe
upgrade them (before starting any servers):
</para>
- <procedure>
+ <substeps>
<step>
<title>Install the new PostgreSQL binaries on standby servers</title>
@@ -522,7 +522,7 @@ rsync --archive --delete --hard-links --size-only old_pgdata new_pgdata remote_d
</para>
</step>
- </procedure>
+ </substeps>
</step>
* Re: 9.5 beta pg_upgrade documentation
2015-08-21 20:27 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
@ 2015-08-22 19:45 ` Andy Colson <andy@squeakycode.net>
2 siblings, 0 replies; 9+ messages in thread
From: Andy Colson @ 2015-08-22 19:45 UTC (permalink / raw)
To: pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: 9.5 beta pg_upgrade documentation
2015-08-21 20:27 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
@ 2015-08-23 14:45 ` Andy Colson <andy@squeakycode.net>
2015-09-01 21:09 ` Re: 9.5 beta pg_upgrade documentation Bruce Momjian <bruce@momjian.us>
2 siblings, 1 reply; 9+ messages in thread
From: Andy Colson @ 2015-08-23 14:45 UTC (permalink / raw)
To: pgsql-general
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 to xfer from primary to standby ... but it would still work. Right?
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: 9.5 beta pg_upgrade documentation
2015-08-21 20:27 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-08-23 14:45 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
@ 2015-09-01 21:09 ` Bruce Momjian <bruce@momjian.us>
2015-09-01 23:56 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2015-09-01 21:09 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-general
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 to 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.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Attachments:
pg_upgrade.diff (text/x-diff, 2.4K) download
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
new file mode 100644
index ebc0d58..fcb0316
*** a/doc/src/sgml/ref/pgupgrade.sgml
--- b/doc/src/sgml/ref/pgupgrade.sgml
*************** NET STOP postgresql-9.0
*** 310,317 ****
</para>
<para>
! Streaming replication and log-shipping standby servers can remain running until
! a later step.
</para>
</step>
--- 310,330 ----
</para>
<para>
! Streaming replication and log-shipping standby servers can
! remain running until a later step.
! </para>
! </step>
!
! <step>
! <title>Verify standby servers</title>
!
! <para>
! If you are upgrading Streaming Replication and Log-Shipping standby
! servers, verify that the old standby servers are caught up by running
! <application>pg_controldata</> against the old primary and standby
! clusters. Verify that the <quote>Latest checkpoint location</>
! values match in all clusters. (There will be a mismatch if old
! standby servers were shut down before the old primary.)
</para>
</step>
*************** pg_upgrade.exe
*** 404,410 ****
If you have Streaming Replication (<xref
linkend="streaming-replication">) or Log-Shipping (<xref
linkend="warm-standby">) standby servers, follow these steps to
! upgrade them (before starting any servers):
</para>
<substeps>
--- 417,425 ----
If you have Streaming Replication (<xref
linkend="streaming-replication">) or Log-Shipping (<xref
linkend="warm-standby">) standby servers, follow these steps to
! upgrade them. You will not be running <application>pg_upgrade</>
! on the standby servers, but rather <application>rsync</>. Do not
! start any servers yet.
</para>
<substeps>
*************** pg_upgrade.exe
*** 447,464 ****
</para>
</step>
- <step>
- <title>Verify standby servers</title>
-
- <para>
- To prevent old standby servers from being modified, run
- <application>pg_controldata</> against the primary and standby
- clusters and verify that the <quote>Latest checkpoint location</>
- values match in all clusters. (This requires the standbys to be
- shut down after the primary.)
- </para>
- </step>
-
<step>
<title>Save configuration files</title>
--- 462,467 ----
* Re: 9.5 beta pg_upgrade documentation
2015-08-21 20:27 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-08-23 14:45 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-09-01 21:09 ` Re: 9.5 beta pg_upgrade documentation Bruce Momjian <bruce@momjian.us>
@ 2015-09-01 23:56 ` Andy Colson <andy@squeakycode.net>
2015-09-02 00:00 ` Re: 9.5 beta pg_upgrade documentation Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 9+ messages in thread
From: Andy Colson @ 2015-09-01 23:56 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-general
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 data to 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 get to step g (run rsync). I checked the rsync manual and don't see anything like this three directory argument thing you 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.
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: 9.5 beta pg_upgrade documentation
2015-08-21 20:27 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-08-23 14:45 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-09-01 21:09 ` Re: 9.5 beta pg_upgrade documentation Bruce Momjian <bruce@momjian.us>
2015-09-01 23:56 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
@ 2015-09-02 00:00 ` Bruce Momjian <bruce@momjian.us>
2015-09-02 01:46 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
0 siblings, 1 reply; 9+ messages in thread
From: Bruce Momjian @ 2015-09-02 00:00 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-general
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 get to step g (run rsync). I checked the rsync manual and don't see anything like this three directory argument thing you 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.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: 9.5 beta pg_upgrade documentation
2015-08-21 20:27 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-08-23 14:45 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-09-01 21:09 ` Re: 9.5 beta pg_upgrade documentation Bruce Momjian <bruce@momjian.us>
2015-09-01 23:56 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-09-02 00:00 ` Re: 9.5 beta pg_upgrade documentation Bruce Momjian <bruce@momjian.us>
@ 2015-09-02 01:46 ` Andy Colson <andy@squeakycode.net>
2015-09-02 02:12 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
0 siblings, 1 reply; 9+ messages in thread
From: Andy Colson @ 2015-09-02 01:46 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-general
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 get to step g (run rsync). I checked the rsync manual and don't see anything like this three directory argument thing you 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: 9.5 beta pg_upgrade documentation
2015-08-21 20:27 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-08-23 14:45 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-09-01 21:09 ` Re: 9.5 beta pg_upgrade documentation Bruce Momjian <bruce@momjian.us>
2015-09-01 23:56 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-09-02 00:00 ` Re: 9.5 beta pg_upgrade documentation Bruce Momjian <bruce@momjian.us>
2015-09-02 01:46 ` Re: 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
@ 2015-09-02 02:12 ` Andy Colson <andy@squeakycode.net>
0 siblings, 0 replies; 9+ messages in thread
From: Andy Colson @ 2015-09-02 02:12 UTC (permalink / raw)
To: pgsql-general
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 we get to step g (run rsync). I checked the rsync manual and don't see anything like this three directory argument thing you 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2015-09-02 02:12 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-21 20:27 9.5 beta pg_upgrade documentation Andy Colson <andy@squeakycode.net>
2015-08-21 20:44 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2015-08-22 19:45 ` Andy Colson <andy@squeakycode.net>
2015-08-23 14:45 ` Andy Colson <andy@squeakycode.net>
2015-09-01 21:09 ` Bruce Momjian <bruce@momjian.us>
2015-09-01 23:56 ` Andy Colson <andy@squeakycode.net>
2015-09-02 00:00 ` Bruce Momjian <bruce@momjian.us>
2015-09-02 01:46 ` Andy Colson <andy@squeakycode.net>
2015-09-02 02:12 ` Andy Colson <andy@squeakycode.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox