Обсуждение: [ADMIN] Upgrade master/slave: rsync?
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).
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.
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
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
www.seiler.us
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
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.
Don Seiler
www.seiler.us
www.seiler.us
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
www.seiler.us
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