Re: Need suggestions about live migration from PG 9.2 to PG 13 - Mailing list pgsql-general

On Wed, Jul 7, 2021 at 3:22 AM Lucas <root@sud0.nz> wrote:
>
> Hello all,
>
> I'm currently working on a migration from PG 9.2 to PG 13 (RDS) and would like some suggestions, please.
>
> Our current database stack is:
>
> master (pg 9.2) --> slave (pg 9.2) --> slave (pg 9.2 - cascading replication)
>                          --> bucardo (ec2 instance) --> RDS (pg 13)
>
>
> The original plan was:
>
> Get bucardo replication working
> Deploy a test environment using the new PG 13 RDS database
> Use the test environment and test as much as possible, to make sure our application works with PG 13
> Test more
> Decide on a date to move all customers to the new database
> Stop the replication
>
> However, the business decided that's not the way they want to move forward. If we have issues it would impact our
customers,etc, even though we do have the test environment up and running and the application works with it. 
>
> Now, the business wants to move a few customers to RDS and leave the rest on PG 9.2... then gradually migrate them to
RDS.But they want customers data to be available in both databases, in case we need to move the customers back to 9.2
ifwe face any issues. So that means a bidirectional replication. 
>
> I am not comfortable with that, using Bucardo, and was hopping you guys could suggest an alternative solution? if
anybodyhas ever done something like this, could share their experiences? 

Note that PostgreSQL 9.2 has been end of life for almost 5 years by
now. If I were you I'd be a *lot* more worried about that than I would
be about Bucardo.

You're also not going to find many other solutions that want to talk
to an old unsupported postgresql, in particular one which doesn't
support logical decoding (which came in postgresql 9.4, also out of
support by now). Slony might be a choice -- it doesn't do
bidirectional, but you can replicate different
tables/schemas/databases in different directions in the same system.
But that assumes that your customers are not shared in the same table
-- if so, you're stuck with something more complex.

I don't believe Slony is supported with RDS though -- RDS is quite
limited in what options you can use.



> BTW - The DB is ~ 1.5TB so pg_upgrade is out of the question, as it takes ages.

As long as you run pg_upgrade in link mode, diong so on a 1.5TB
database is a very quick operation, normally completes in a couple of
minutes including the ANALYZE step. This is a "destructive operation",
so you can't go back if something goes wrong, but just keep an extra
standby node around to fail over to if everything blows up and you
have that covered.

The fact that pg_upgrade *doesn't* take ages to deal with medium size
databases and up is the main reason it *exists*.

--
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/