[google-cloud-sql-discuss] Re: No fuss strategy to migrate a CloudSQL PostgreSQL database to another GCP account, using replication and zero loss and downtime

Hi, alexhguerra,

Users sometimes want to migrate their (normal) relational database with "zero" downtime. While downtime can be reduced, migration cannot be done without any impact on applications (that is, zero downtime). Replication causes replication lag.

The instant the decision is made to "migrate" all applications from one replica to another, applications (and therefore customers) have to wait (that is, downtime) at least as long as the "replication lag" before using the new database. In practice, the downtime is a few orders of magnitude higher (minutes to hours) because:

* Database queries can take multiple seconds to complete and in flight queries must be completed or aborted at the time of migration.

* The database has to be "warmed up" if it has substantial buffer memory - common in large databases.

* If database shards have duplicate tables, some writes may need to be paused while the shards are being migrated.

* Applications must be stopped at source and restarted in GCP and connection to the GCP database instance must be established.

* Network routes to the applications must be rerouted. Based on how DNS entries are set up, this can take some time.

All of these can be reduced with some planning and "cost" (some operations not permitted for some time before/after migration).

More about: https://cloud.google.com/architecture/database-migration-concepts-principles-part-1?hl=en

On Wednesday, May 25, 2022 at 11:02:07 AM UTC-5 alexh...@gmail.com wrote:

Hello

We need to migrate a CloudSQL PostgreSQL 12 database from one GCP account to another, and currently there's no automatic / 'cloudy' solution offered by Google to perform this.

Its a critical online database that cant stop for a multitude of reasons

Im looking to implement the following process.

0- Setup primary as replica source, and increase WAL files retention time

1- take a PITR backup of primary

2- restore the PITR backup as a new database server

3- Make the necessary changes on the primary and standby to activate stream replication

4- Use standby as read replica to test the application for a short period of time to test if its allright.

5- switch off the primary and activate the app on the newly promoted standby to primary

May i ask if someone have a step by step procedure as reference for this?

Also, if choosing logical replication , is it possible to keep the PITR in the same way?

Thanks

Alexandre

A CloudSQL PostgreSQL database replicated to another GCP account, initially thru PITR restore, then using stream replication and zero downtime for switchover/takeover manually by the second one. 

Once the standby is up to date with the primary (LSN, and so on), it will take over as primary (or even standalone) , then to shut down the application, and point it to the standby now as primary 

I could see that i could use pglog

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/36030f3e-33e8-4ea2-a0cd-14ec1bdb6accn%40googlegroups.com.