pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUpgrade master/slave: rsync?
8+ messages / 4 participants
[nested] [flat]
* Upgrade master/slave: rsync?
@ 2017-09-12 21:57 Don Seiler <don@seiler.us>
2017-09-13 16:13 ` Re: Upgrade master/slave: rsync? Guillaume Lelarge <guillaume@lelarge.info>
2017-09-13 16:24 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 8+ messages in thread
From: Don Seiler @ 2017-09-12 21:57 UTC (permalink / raw)
To: pgsql-admin
Planning steps to upgrade our 9.2 DB to the latest stable 9.6 using
pg_upgrade. I've gone through the steps for a primary/standalone DB and
they work well. Looking into how then to upgrade the standby, the
documentation says that *the data_dir from the master should be rsync'ed to
each slave*.
Is this truly the case? Having to copy all the data from the primary will
be a huge issue for our larger databases. I had hoped we'd just need to
upgrade the binaries, copy the old data dir to the new data dir, update
conf files as needed and turn it back on (similar to Oracle standby
upgrade).
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Upgrade master/slave: rsync?
2017-09-12 21:57 Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
@ 2017-09-13 16:13 ` Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 0 replies; 8+ messages in thread
From: Guillaume Lelarge @ 2017-09-13 16:13 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin
2017-09-12 23:57 GMT+02:00 Don Seiler <don@seiler.us>:
> Planning steps to upgrade our 9.2 DB to the latest stable 9.6 using
> pg_upgrade. I've gone through the steps for a primary/standalone DB and
> they work well. Looking into how then to upgrade the standby, the
> documentation says that *the data_dir from the master should be rsync'ed
> to each slave*.
>
> Is this truly the case?
>
It is.
Having to copy all the data from the primary will be a huge issue for our
> larger databases. I had hoped we'd just need to upgrade the binaries, copy
> the old data dir to the new data dir, update conf files as needed and turn
> it back on (similar to Oracle standby upgrade).
>
>
--
Guillaume.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Upgrade master/slave: rsync?
2017-09-12 21:57 Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
@ 2017-09-13 16:24 ` Stephen Frost <sfrost@snowman.net>
2017-09-13 16:50 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
1 sibling, 1 reply; 8+ messages in thread
From: Stephen Frost @ 2017-09-13 16:24 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin
Don,
* Don Seiler (don@seiler.us) wrote:
> Planning steps to upgrade our 9.2 DB to the latest stable 9.6 using
> pg_upgrade. I've gone through the steps for a primary/standalone DB and
> they work well. Looking into how then to upgrade the standby, the
> documentation says that *the data_dir from the master should be rsync'ed to
> each slave*.
>
> Is this truly the case? Having to copy all the data from the primary will
> be a huge issue for our larger databases. I had hoped we'd just need to
> upgrade the binaries, copy the old data dir to the new data dir, update
> conf files as needed and turn it back on (similar to Oracle standby
> upgrade).
There is a method to avoid having to copy the data from the primary, but
it requires using --link mode with pg_upgrade and it's a bit of an
involved process that requires very carefully following the steps and
doing additional validation.
A discussion around exactly this is currenlty ongoing as to how we can
improve the documentation on this method. You can find that discussion
here:
https://www.postgresql.org/message-id/flat/VisenaEmail.10.2b4049e43870bd16.15d898d696f%40tc7-visena#...
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Upgrade master/slave: rsync?
2017-09-12 21:57 Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 16:24 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
@ 2017-09-13 16:50 ` Don Seiler <don@seiler.us>
2017-09-13 16:53 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 8+ messages in thread
From: Don Seiler @ 2017-09-13 16:50 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
Thanks, that's a nice peek inside the sausage factory. Obviously it would
be real nice if pg_upgrade could work it's voodoo on the standby without
having to rsync everything. Seems like most agree there so I'll leave it at
that.
In the meantime I'll look at using the --link method and rsync. Probably
should be using --link method on a larger database anyway to save time and
disk space?
Don.
On Wed, Sep 13, 2017 at 11:24 AM, Stephen Frost <sfrost@snowman.net> wrote:
> Don,
>
> * Don Seiler (don@seiler.us) wrote:
> > Planning steps to upgrade our 9.2 DB to the latest stable 9.6 using
> > pg_upgrade. I've gone through the steps for a primary/standalone DB and
> > they work well. Looking into how then to upgrade the standby, the
> > documentation says that *the data_dir from the master should be rsync'ed
> to
> > each slave*.
> >
> > Is this truly the case? Having to copy all the data from the primary will
> > be a huge issue for our larger databases. I had hoped we'd just need to
> > upgrade the binaries, copy the old data dir to the new data dir, update
> > conf files as needed and turn it back on (similar to Oracle standby
> > upgrade).
>
> There is a method to avoid having to copy the data from the primary, but
> it requires using --link mode with pg_upgrade and it's a bit of an
> involved process that requires very carefully following the steps and
> doing additional validation.
>
> A discussion around exactly this is currenlty ongoing as to how we can
> improve the documentation on this method. You can find that discussion
> here:
>
> https://www.postgresql.org/message-id/flat/VisenaEmail.
> 10.2b4049e43870bd16.15d898d696f%40tc7-visena#VisenaEmail.10.
> 2b4049e43870bd16.15d898d696f@tc7-visena
>
> Thanks!
>
> Stephen
>
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Upgrade master/slave: rsync?
2017-09-12 21:57 Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 16:24 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
2017-09-13 16:50 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
@ 2017-09-13 16:53 ` Stephen Frost <sfrost@snowman.net>
2017-09-13 16:56 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
0 siblings, 1 reply; 8+ messages in thread
From: Stephen Frost @ 2017-09-13 16:53 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin
Don,
On these mailing lists, we really prefer it if people wouldn't
"top-post", instead add your comments in-line and eliminate text that
isn't relevant.
* Don Seiler (don@seiler.us) wrote:
> In the meantime I'll look at using the --link method and rsync. Probably
> should be using --link method on a larger database anyway to save time and
> disk space?
Yes, it's much more efficient, but I strongly recommend taking a backup
as close to before the upgrade as possible, being extremely careful
throughout the process, plan for a good bit of time above what the
actual commands take to run, and make sure that you have *tested* your
backup and *restore* process well and have strong confidence in it.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Upgrade master/slave: rsync?
2017-09-12 21:57 Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 16:24 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
2017-09-13 16:50 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 16:53 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
@ 2017-09-13 16:56 ` Don Seiler <don@seiler.us>
2017-09-13 20:01 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
0 siblings, 1 reply; 8+ messages in thread
From: Don Seiler @ 2017-09-13 16:56 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
On Wed, Sep 13, 2017 at 11:53 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
> On these mailing lists, we really prefer it if people wouldn't
> "top-post", instead add your comments in-line and eliminate text that
> isn't relevant.
>
Sorry, I've tried to reply in-line in my previous replies to the pgsql
lists but sometimes I forget. Mea culpa.
> Yes, it's much more efficient, but I strongly recommend taking a backup
> as close to before the upgrade as possible, being extremely careful
> throughout the process, plan for a good bit of time above what the
> actual commands take to run, and make sure that you have *tested* your
> backup and *restore* process well and have strong confidence in it.
>
All good advice, thanks again!
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Upgrade master/slave: rsync?
2017-09-12 21:57 Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 16:24 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
2017-09-13 16:50 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 16:53 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
2017-09-13 16:56 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
@ 2017-09-13 20:01 ` Don Seiler <don@seiler.us>
2017-09-19 19:41 ` Re: Upgrade master/slave: rsync? Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: Don Seiler @ 2017-09-13 20:01 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin
Thinking more on this in my situation. The databases I've inherited have
the default system tables in the default tablespaces under $PGDATA
(/var/lib/pgsql/9.2/data), but then also has most of the app-specific data
on separate tablespaces outside of the data directory (eg various
directories under /opt/postgres). Of course this also means the various
symlinks to these from $PGDATA/pg_tblspc. For these, pg_upgrade will create
a new directory (PG_9.6_201608131) with hard-links (assuming I used the
--link option) to the old PG_9.2_201204301 directory, and then add symlinks
from the new 9.6 $PGDATA directory to the tablespace location.
I'm just trying to walk through this in my head. I'd need to run a similar
rsync command for each of these. For example, for tablespace foo with
location /opt/postgres/foo:
cd /opt/postgres/foo
rsync --archive --delete --hard-links --size-only PG_9.2_201204301
PG_9.6_201608131 standby.host.com:/opt/postgres/foo
And then rinse & repeat for any others similar tablespaces for each
standby/slave?
Another question. I have one slave in the same datacenter as the master,
the other slaves are in the DR data center across the country. I'm assuming
I could sync those DR slaves from the local slave? That way I can get the
master back online and serving requests as soon as the local slave is
successfully upgraded rather than waiting for rsync over the WAN? And then
one DR slave could be copied from the other so I only have to do the WAN
rsync once (especially if it's a downstream standby in a cascading
scenario)?
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Upgrade master/slave: rsync?
2017-09-12 21:57 Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 16:24 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
2017-09-13 16:50 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 16:53 ` Re: Upgrade master/slave: rsync? Stephen Frost <sfrost@snowman.net>
2017-09-13 16:56 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 20:01 ` Re: Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
@ 2017-09-19 19:41 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 8+ messages in thread
From: Bruce Momjian @ 2017-09-19 19:41 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-admin
On Wed, Sep 13, 2017 at 03:01:50PM -0500, Don Seiler wrote:
> Thinking more on this in my situation. The databases I've inherited have the
> default system tables in the default tablespaces under $PGDATA (/var/lib/pgsql/
> 9.2/data), but then also has most of the app-specific data on separate
> tablespaces outside of the data directory (eg various directories under /opt/
> postgres). Of course this also means the various symlinks to these from $PGDATA
> /pg_tblspc. For these, pg_upgrade will create a new directory
> (PG_9.6_201608131) with hard-links (assuming I used the --link option) to the
> old PG_9.2_201204301 directory, and then add symlinks from the new 9.6 $PGDATA
> directory to the tablespace location.
>
> I'm just trying to walk through this in my head. I'd need to run a similar
> rsync command for each of these. For example, for tablespace foo with location
> /opt/postgres/foo:
>
> cd /opt/postgres/foo
> rsync --archive --delete --hard-links --size-only PG_9.2_201204301
> PG_9.6_201608131 standby.host.com:/opt/postgres/foo
Yes.
> And then rinse & repeat for any others similar tablespaces for each standby/
> slave?
>
> Another question. I have one slave in the same datacenter as the master, the
> other slaves are in the DR data center across the country. I'm assuming I could
> sync those DR slaves from the local slave? That way I can get the master back
> online and serving requests as soon as the local slave is successfully upgraded
> rather than waiting for rsync over the WAN? And then one DR slave could be
> copied from the other so I only have to do the WAN rsync once (especially if
> it's a downstream standby in a cascading scenario)?
Yes.
I think you will find answers to these questions in the updated
pg_upgrade docs that were backpatched to 9.5:
https://www.postgresql.org/docs/9.6/static/pgupgrade.html
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2017-09-19 19:41 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-09-12 21:57 Upgrade master/slave: rsync? Don Seiler <don@seiler.us>
2017-09-13 16:13 ` Guillaume Lelarge <guillaume@lelarge.info>
2017-09-13 16:24 ` Stephen Frost <sfrost@snowman.net>
2017-09-13 16:50 ` Don Seiler <don@seiler.us>
2017-09-13 16:53 ` Stephen Frost <sfrost@snowman.net>
2017-09-13 16:56 ` Don Seiler <don@seiler.us>
2017-09-13 20:01 ` Don Seiler <don@seiler.us>
2017-09-19 19:41 ` Bruce Momjian <bruce@momjian.us>
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