I just noticed I missed Sherrylyn's post.
I did some reading about Slony and believe it is would be useful if I had the time to dig in. As pointed out, it's not
anout-of-the box solution. It is included on the TODO list though. For now I can only dream of the 86 second down time.
Thanks
________________________________________
From: Sherrylyn Branchaw <sbranchaw@gmail.com>
Sent: Sunday, April 7, 2019 6:43 AM
To: senor
Cc: pgsql-general@lists.postgresql.org
Subject: Re: pg_upgrade --jobs
are there any shortcuts to upgrading that would circumvent exporting the entire schema?
By "shortcuts," do you mean you want to minimize the time and energy you put into the upgrade, or that you want to
minimizedatabase downtime? If you mean downtime, I was able to upgrade a customer-facing database with ~350,000 tables
fromPostgres 9.0 to 9.6 last year with only 86 seconds of downtime, using Slony, but I had to make many custom
modificationsto Slony and test thoroughly beforehand, and it was not for the faint of heart, the pressed for time, or
theinexperienced. There may be better ways (and if so, I would be curious to learn about them), but Slony was the tool
withwhich I was most familiar at the time.
This method does, of course, require exporting the entire schema, but because our only constraint was to minimize
customerdowntime, and the database was online while the schema was being exported, we didn't care how long it took.
Yourconstraints may be different.
For those reading: we do know that 350,000 tables is Doing It Wrong, and we're getting rid of them, but we decided
beingon an EOLed version of Postgres was worse and should be fixed first.
Sherrylyn