On-Prem Postgres to GCP Cloud SQL Migration Using GCP DMS

In this article, we would discuss the Migration Strategy and create Migration Template for On-prem PostgreSQL to CloudSQL PostgreSQL.

Source and Destination:

Source - On Premises / Self-Managed PostgreSQL

Destination - GCP CloudSQL PostgreSQL

For ease of testing, We are assuming AWS EC2 “Ubuntu 22.04.1 LTS” Machine as the On-premise.

Source PostgreSQL Version - 13

Destination PostgreSQL Version - 14

Let’s validate if the source PostgreSQL is up and Running.

root@ip-172-31-40-4:~# ps -ef|grep post
postgres? ?16946? ? ? ?1? 0 12:20 ?? ? ? ? 00:00:00 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
postgres? ?16948? ?16946? 0 12:20 ?? ? ? ? 00:00:00 postgres: 13/main: checkpointer
postgres? ?16949? ?16946? 0 12:20 ?? ? ? ? 00:00:00 postgres: 13/main: background writer
postgres? ?16950? ?16946? 0 12:20 ?? ? ? ? 00:00:00 postgres: 13/main: walwriter
postgres? ?16951? ?16946? 0 12:20 ?? ? ? ? 00:00:00 postgres: 13/main: autovacuum launcher
postgres? ?16952? ?16946? 0 12:20 ?? ? ? ? 00:00:00 postgres: 13/main: stats collector
postgres? ?16953? ?16946? 0 12:20 ?? ? ? ? 00:00:00 postgres: 13/main: pglogical supervisor
postgres? ?16954? ?16946? 0 12:20 ?? ? ? ? 00:00:00 postgres: 13/main: logical replication launcher
root? ? ? ?16971? ? 1401? 0 12:20 pts/1? ? 00:00:00 /usr/lib/postgresql/13/bin/psql -h localhost -p 5432 -U postgres -d postgres
postgres? ?16972? ?16946? 0 12:20 ?? ? ? ? 00:00:00 postgres: 13/main: postgres postgres 127.0.0.1(58890) idle
postgres? ?17153? ?16946? 0 12:32 ?? ? ? ? 00:00:00 postgres: 13/main: postgres postgres 34.93.239.160(39854) idle
root? ? ? ?17162? ?17132? 0 12:33 pts/3? ? 00:00:00 grep --color=auto post
root@ip-172-31-40-4:~#        

We have created the Source Database with 15MB data. Maximum row count is ‘16044’ rows for 'Rental Table. We have Functions, Triggers and views and Sequences created in the Source Database.

dvdrental=# select count(*) from rental;
?count
-------
?16044
(1 row)

dvdrental=# select count(*) from payment;
?count
-------
?14596
(1 row)

dvdrental=# select * from rental limit 5;
?rental_id |? ? ?rental_date? ? ?| inventory_id | customer_id |? ? ?return_date? ? ?| staff_id |? ? ?last_update
-----------+---------------------+--------------+-------------+---------------------+----------+---------------------
? ? ? ? ?2 | 2005-05-24 22:54:33 |? ? ? ? ?1525 |? ? ? ? ?459 | 2005-05-28 19:40:33 |? ? ? ? 1 | 2006-02-16 02:30:53
? ? ? ? ?3 | 2005-05-24 23:03:39 |? ? ? ? ?1711 |? ? ? ? ?408 | 2005-06-01 22:12:39 |? ? ? ? 1 | 2006-02-16 02:30:53
? ? ? ? ?4 | 2005-05-24 23:04:41 |? ? ? ? ?2452 |? ? ? ? ?333 | 2005-06-03 01:43:41 |? ? ? ? 2 | 2006-02-16 02:30:53
? ? ? ? ?5 | 2005-05-24 23:05:21 |? ? ? ? ?2079 |? ? ? ? ?222 | 2005-06-02 04:33:21 |? ? ? ? 1 | 2006-02-16 02:30:53
? ? ? ? ?6 | 2005-05-24 23:08:07 |? ? ? ? ?2792 |? ? ? ? ?549 | 2005-05-27 01:32:07 |? ? ? ? 1 | 2006-02-16 02:30:53
(5 rows)

dvdrental=# select * from payment limit 5;
?payment_id | customer_id | staff_id | rental_id | amount |? ? ? ? payment_date
------------+-------------+----------+-----------+--------+----------------------------
? ? ? 17503 |? ? ? ? ?341 |? ? ? ? 2 |? ? ? 1520 |? ?7.99 | 2007-02-15 22:25:46.996577
? ? ? 17504 |? ? ? ? ?341 |? ? ? ? 1 |? ? ? 1778 |? ?1.99 | 2007-02-16 17:23:14.996577
? ? ? 17505 |? ? ? ? ?341 |? ? ? ? 1 |? ? ? 1849 |? ?7.99 | 2007-02-16 22:41:45.996577
? ? ? 17506 |? ? ? ? ?341 |? ? ? ? 2 |? ? ? 2829 |? ?2.99 | 2007-02-19 19:39:56.996577
? ? ? 17507 |? ? ? ? ?341 |? ? ? ? 2 |? ? ? 3130 |? ?7.99 | 2007-02-20 17:31:48.996577
(5 rows)

dvdrental=#
dvdrental=# \d rental
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?Table "public.rental"
? ? Column? ? |? ? ? ? ? ? Type? ? ? ? ? ? ?| Collation | Nullable |? ? ? ? ? ? ? ? ? Default
--------------+-----------------------------+-----------+----------+-------------------------------------------
?rental_id? ? | integer? ? ? ? ? ? ? ? ? ? ?|? ? ? ? ? ?| not null | nextval('rental_rental_id_seq'::regclass)
?rental_date? | timestamp without time zone |? ? ? ? ? ?| not null |
?inventory_id | integer? ? ? ? ? ? ? ? ? ? ?|? ? ? ? ? ?| not null |
?customer_id? | smallint? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ?| not null |
?return_date? | timestamp without time zone |? ? ? ? ? ?|? ? ? ? ? |
?staff_id? ? ?| smallint? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ?| not null |
?last_update? | timestamp without time zone |? ? ? ? ? ?| not null | now()
Indexes:
? ? "rental_pkey" PRIMARY KEY, btree (rental_id)
? ? "idx_fk_inventory_id" btree (inventory_id)
? ? "idx_unq_rental_rental_date_inventory_id_customer_id" UNIQUE, btree (rental_date, inventory_id, customer_id)
Foreign-key constraints:
? ? "rental_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
? ? "rental_inventory_id_fkey" FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id) ON UPDATE CASCADE ON DELETE RESTRICT
? ? "rental_staff_id_key" FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
Referenced by:
? ? TABLE "payment" CONSTRAINT "payment_rental_id_fkey" FOREIGN KEY (rental_id) REFERENCES rental(rental_id) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
? ? last_updated BEFORE UPDATE ON rental FOR EACH ROW EXECUTE FUNCTION last_updated()

dvdrental=#        

dvdrental=# alter user postgres with password '****';

ALTER ROLE

dvdrental=#

Migration Strategy:

Using GCP DMS - Full Load and CDC

Configuring the Source On-Premises PostgreSQL:

Install the pglogical package on the server.

Connect to the instance and set the following parameters, as needed:

1. Create pglogical Extension at source,

root@ip-172-31-40-4:/etc/postgresql/13/main# psql -h localhost -p 5432 -U postgres -d postgres
psql (13.9 (Ubuntu 13.9-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \dx
? ? ? ? ? ? ? ? ?List of installed extensions
? Name? ?| Version |? ?Schema? ?|? ? ? ? ?Description
---------+---------+------------+------------------------------
?plpgsql | 1.0? ? ?| pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# create exte^C
postgres=# show shared_preload_libraries ;
?shared_preload_libraries
--------------------------
?pglogical
(1 row)

postgres=# create extension pglogical;
CREATE EXTENSION
postgres=# \dx
? ? ? ? ? ? ? ? ? ?List of installed extensions
? ?Name? ? | Version |? ?Schema? ?|? ? ? ? ? Description
-----------+---------+------------+--------------------------------
?pglogical | 2.4.2? ?| pglogical? | PostgreSQL Logical Replication
?plpgsql? ?| 1.0? ? ?| pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=#        

2. Set wal_level to logical.

To set this parameter, modify the ‘postgresql.conf’ file in the Data Directory.

postgres=# show wal_level ;
?wal_level
-----------
?logical
(1 row)

postgres=#        

3. Set wal_sender_timeout to 0.

To set this parameter, modify the ‘postgresql.conf’ file in the Data Directory.

Here 0 disables the timeout mechanism that’s used to terminate inactive replication connections.

postgres=# show wal_sender_timeout;
?wal_sender_timeout
--------------------
?0
(1 row)

postgres=#        

4. max_replication_slots defines the maximum number of replication slots the source instance can support. It must be set to at least the number of subscriptions expected to connect, plus some reserves for table synchronization.

For example, if there are 5 databases on the source instance and if there will be 2 migration jobs created for the source, then the number of replication slots must be at least 5 * 2 = 10, plus the number of replication slots already used by you.

To set this parameter, modify the ‘postgresql.conf’ file in the Data Directory.

postgres=# show max_replication_slots;
?max_replication_slots
-----------------------
?10
(1 row)

postgres=#        

5. max_wal_senders should be set to at least the same as max_replication_slots, plus the number of senders already used on your instance.

For example, if the max_replication_slots parameter is set to 10, and you’re already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12.

To set this parameter, run the ALTER SYSTEM SET max_wal_senders = #; command, where # represents the number of WAL sender processes running simultaneously.

6. max_worker_processes should be set to at least the same number of databases that Database Migration Service is going to migrate (which is all of the databases under the source instance), plus the number of max_worker_processes already used on your instance.

To set this parameter, modify the modify the ‘postgresql.conf’ file in the Data Directory.

postgres=# show max_wal_senders;
?max_wal_senders
-----------------
?12
(1 row)

postgres=#        

7. To apply the configuration changes for all the above steps, restart the source instance.

Configuring the GCP DMS:

1. Create a source connection profile

• Go to the Database Migration page in the Google Cloud Console.
• Go to the Connection profiles page and click on create profile.
• Select a Source database engine as `Postgresql’ from the drop-down list
• Enter a Connection profile name. This is used in the connection profile list as well as when an existing connection profile is selected in the creation of a migration job.
• Keep the auto-generated Connection profile ID.
• Enter hostname, port name, username and password to connect to the source database.
• Select the region and click on create.
• I have assigned Elastic Ip ‘3.7.225.168’ and modified the hostname accordingly later in the below picture.

2. Create a migration job

Go to the Migration jobs page in the Google Cloud Console.

• Click CREATE MIGRATION JOB at the top of the page.
• Provide a name for the migration job. Choose a friendly name that helps you identify the migration job. Do not include sensitive or personally identifiable information in the job name.
• Keep the auto-generated Migration job ID.
• Select the source database engine.
• Select the destination region for your migration. This is where the Database Migration Service instance is created, and should be selected based on the location of the services that need your data, such as Compute Engine instances and App Engine apps, and other services. Once chosen, this selection can’t be changed.
• Specify the migration job type: Continuous (snapshot + ongoing changes).
• Click SAVE & CONTINUE.

• Define a source

As we have already created a connection profile, then select it from the list of existing connection profiles.

Click SAVE & CONTINUE.

Create a Destination cloud sql instance

1. Provide a Destination instance ID (This will be you destination cloud sql instance name)

2. Provide an alphanumeric Root password for the destination Cloud SQL instance. This will be the password for the root administrator account in the instance.

You can either enter the password manually or click GENERATE to have the Database Migration Service for PostgreSQL create one for you automatically.

Tip: To see the password in clear text, click the Reveal icon. This icon looks like an eye.

3. Choose the database version for the destination instance from the list of supported Database Migration Service versions for the specified database engine.

4. Choose whether to connect to this instance via private or public IP address. It is recommended to use Private IP. If you are using Private IP then select the VPC name as well, if you are using public IP just select it and move on to set the machine type.

5. Select the machine type for the Cloud SQL instance. The disk size must be equal to or greater than the source database size, make sure automatic storage increase is enabled.

6. In the Create destination database window, click CREATE DESTINATION & CONTINUE to create the new instance. This may take up to several minutes.

Define connectivity method

From the Connectivity method drop-down menu, select a network connectivity method. This method defines how the newly created Cloud SQL instance will connect to the source database.

If you have selected public ip at the time of destination instance creation then use the connectivity method as IP allowlist.

3. If you have selected private IP then use the connectivity method as VPC Peering and provide the VPC name.

4. After selecting the network connectivity method and providing any additional information for the method, click CONFIGURE & CONTINUE.

Test and create the migration job

1. On this final step, review the summary of the migration job settings, source, destination, and connectivity method, and then test the validity of the migration job setup. If any issues are encountered, then you can modify settings. Not all settings are editable.

2. Click TEST JOB to verify that: It will test the below things:

The source has been configured based on the prerequisites.

The source and destination instances are able to communicate with each other.

Any updates to private or public IP addresses needed on the destination are done.

The migration job is valid, and the source and destination versions are compatible.

3. Click CREATE JOB to create the migration job.

4. After creating the job go to the database migration page and open a job created by you and click on start to start it.

Note: After starting the DMS job there should not be any DDL operations running on the source database. If you want to run DDL operation you need to run that on cloud sql instance first then on source database to avoid job failure or data sync failure.

Keep in mind: For tables in your source PostgreSQL database that don't have primary keys, only the initial snapshot and INSERT statements are migrated.

3. Monitoring

Once the job has been started after some time the status of job will be changes as below

And then once dump and restore completed job status will be changed as below

Keep monitoring the job, you can see lag between both the servers as below.

Also keep monitoring the storage usage by destination instance, that will give an idea of how much data has been migrated (in this snap till now 30 GB data has been migrated).

Once you see replication as 0 bytes for a long time, it means all the data has been migrated to the destination instance.

We can view the errors and Log Information from ‘Logs Explorer’ page.

4. Data Validation

Data Validation of Full Load,

We have migrated the On-prem to GCP PostgreSQL with 15MB data. Maximum row count is ‘16044’ rows for 'Rental Table.

(base) rajkmarnarendiran@RajKumarNarendiran dvdrental % psql -h 34.100.182.1 -p 5432 -U postgres -d dvdrental
Password for user postgres:
psql (14.5 (Homebrew), server 14.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

dvdrental=> \l+
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? List of databases
? ? ?Name? ? ? |? ? ? ? Owner? ? ? ? ?| Encoding |? Collate? ?|? ?Ctype? ? |? ? ? ? ? ? Access privileges? ? ? ? ? ? |? Size? ?| Tablespace |? ? ? ? ? ? ? ? Description
---------------+----------------------+----------+------------+------------+-----------------------------------------+---------+------------+--------------------------------------------
?cloudsqladmin | cloudsqladmin? ? ? ? | UTF8? ? ?| en_US.UTF8 | en_US.UTF8 |? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 8825 kB | pg_default |
?dvdrental? ? ?| cloudsqlexternalsync | UTF8? ? ?| C.UTF-8? ? | C.UTF-8? ? |? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 16 MB? ?| pg_default |
?postgres? ? ? | cloudsqlexternalsync | UTF8? ? ?| C.UTF-8? ? | C.UTF-8? ? |? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 9233 kB | pg_default | default administrative connection database
?template0? ? ?| cloudsqladmin? ? ? ? | UTF8? ? ?| en_US.UTF8 | en_US.UTF8 | =c/cloudsqladmin? ? ? ? ? ? ? ? ? ? ? ?+| 8609 kB | pg_default | unmodifiable empty database
? ? ? ? ? ? ? ?|? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? |? ? ? ? ? ? |? ? ? ? ? ? | cloudsqladmin=CTc/cloudsqladmin? ? ? ? ?|? ? ? ? ?|? ? ? ? ? ? |
?template1? ? ?| cloudsqlsuperuser? ? | UTF8? ? ?| en_US.UTF8 | en_US.UTF8 | =c/cloudsqlsuperuser? ? ? ? ? ? ? ? ? ?+| 8761 kB | pg_default | default template for new databases
? ? ? ? ? ? ? ?|? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? |? ? ? ? ? ? |? ? ? ? ? ? | cloudsqlsuperuser=CTc/cloudsqlsuperuser |? ? ? ? ?|? ? ? ? ? ? |
(5 rows)

dvdrental=> select count(*) from rental;
?count
-------
?16044
(1 row)

dvdrental=> select count(*) from payment;
?count
-------
?14596
(1 row)

dvdrental=> \conninfo
You are connected to database "dvdrental" as user "postgres" on host "34.100.182.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
dvdrental=> select * from rental limit 5;
?rental_id |? ? ?rental_date? ? ?| inventory_id | customer_id |? ? ?return_date? ? ?| staff_id |? ? ?last_update
-----------+---------------------+--------------+-------------+---------------------+----------+---------------------
? ? ? ? ?2 | 2005-05-24 22:54:33 |? ? ? ? ?1525 |? ? ? ? ?459 | 2005-05-28 19:40:33 |? ? ? ? 1 | 2006-02-16 02:30:53
? ? ? ? ?3 | 2005-05-24 23:03:39 |? ? ? ? ?1711 |? ? ? ? ?408 | 2005-06-01 22:12:39 |? ? ? ? 1 | 2006-02-16 02:30:53
? ? ? ? ?4 | 2005-05-24 23:04:41 |? ? ? ? ?2452 |? ? ? ? ?333 | 2005-06-03 01:43:41 |? ? ? ? 2 | 2006-02-16 02:30:53
? ? ? ? ?5 | 2005-05-24 23:05:21 |? ? ? ? ?2079 |? ? ? ? ?222 | 2005-06-02 04:33:21 |? ? ? ? 1 | 2006-02-16 02:30:53
? ? ? ? ?6 | 2005-05-24 23:08:07 |? ? ? ? ?2792 |? ? ? ? ?549 | 2005-05-27 01:32:07 |? ? ? ? 1 | 2006-02-16 02:30:53
(5 rows)

dvdrental=> select * from payment limit 5;
?payment_id | customer_id | staff_id | rental_id | amount |? ? ? ? payment_date
------------+-------------+----------+-----------+--------+----------------------------
? ? ? 17503 |? ? ? ? ?341 |? ? ? ? 2 |? ? ? 1520 |? ?7.99 | 2007-02-15 22:25:46.996577
? ? ? 17504 |? ? ? ? ?341 |? ? ? ? 1 |? ? ? 1778 |? ?1.99 | 2007-02-16 17:23:14.996577
? ? ? 17505 |? ? ? ? ?341 |? ? ? ? 1 |? ? ? 1849 |? ?7.99 | 2007-02-16 22:41:45.996577
? ? ? 17506 |? ? ? ? ?341 |? ? ? ? 2 |? ? ? 2829 |? ?2.99 | 2007-02-19 19:39:56.996577
? ? ? 17507 |? ? ? ? ?341 |? ? ? ? 2 |? ? ? 3130 |? ?7.99 | 2007-02-20 17:31:48.996577
(5 rows)

dvdrental=> \d rental
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?Table "public.rental"
? ? Column? ? |? ? ? ? ? ? Type? ? ? ? ? ? ?| Collation | Nullable |? ? ? ? ? ? ? ? ? Default
--------------+-----------------------------+-----------+----------+-------------------------------------------
?rental_id? ? | integer? ? ? ? ? ? ? ? ? ? ?|? ? ? ? ? ?| not null | nextval('rental_rental_id_seq'::regclass)
?rental_date? | timestamp without time zone |? ? ? ? ? ?| not null |
?inventory_id | integer? ? ? ? ? ? ? ? ? ? ?|? ? ? ? ? ?| not null |
?customer_id? | smallint? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ?| not null |
?return_date? | timestamp without time zone |? ? ? ? ? ?|? ? ? ? ? |
?staff_id? ? ?| smallint? ? ? ? ? ? ? ? ? ? |? ? ? ? ? ?| not null |
?last_update? | timestamp without time zone |? ? ? ? ? ?| not null | now()
Indexes:
? ? "rental_pkey" PRIMARY KEY, btree (rental_id)
? ? "idx_fk_inventory_id" btree (inventory_id)
? ? "idx_unq_rental_rental_date_inventory_id_customer_id" UNIQUE, btree (rental_date, inventory_id, customer_id)
Foreign-key constraints:
? ? "rental_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
? ? "rental_inventory_id_fkey" FOREIGN KEY (inventory_id) REFERENCES inventory(inventory_id) ON UPDATE CASCADE ON DELETE RESTRICT
? ? "rental_staff_id_key" FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
Referenced by:
? ? TABLE "payment" CONSTRAINT "payment_rental_id_fkey" FOREIGN KEY (rental_id) REFERENCES rental(rental_id) ON UPDATE CASCADE ON DELETE SET NULL
Triggers:
? ? last_updated BEFORE UPDATE ON rental FOR EACH ROW EXECUTE FUNCTION last_updated()

dvdrental=>        

Data Validation of CDC,

Source on-prem INSERT,

dvdrental=# insert into actor values(201,'Raj','Kumar','2022-05-26 14:47:57.62');
INSERT 0 1
dvdrental=# select * from actor order by actor_id desc limit 2;
?actor_id | first_name | last_name |? ? ? last_update
----------+------------+-----------+------------------------
? ? ? 201 | Raj? ? ? ? | Kumar? ? ?| 2022-05-26 14:47:57.62
? ? ? 200 | Thora? ? ? | Temple? ? | 2013-05-26 14:47:57.62
(2 rows)

dvdrental=# \conninfo
You are connected to database "dvdrental" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
dvdrental=#        

Destination GCP INSERT,

dvdrental=> select * from actor order by actor_id desc limit 2;
?actor_id | first_name | last_name |? ? ? last_update
----------+------------+-----------+------------------------
? ? ? 201 | Raj? ? ? ? | Kumar? ? ?| 2022-05-26 14:47:57.62
? ? ? 200 | Thora? ? ? | Temple? ? | 2013-05-26 14:47:57.62
(2 rows)

dvdrental=> \conninfo
You are connected to database "dvdrental" as user "postgres" on host "34.100.182.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
dvdrental=>        

Source on-prem UPDATE,

dvdrental=# update actor set last_name='Kumara' where actor_id=201;
UPDATE 1
dvdrental=# select * from actor order by actor_id desc limit 2;
?actor_id | first_name | last_name |? ? ? ? last_update
----------+------------+-----------+----------------------------
? ? ? 201 | Raj? ? ? ? | Kumara? ? | 2023-01-04 14:16:31.088698
? ? ? 200 | Thora? ? ? | Temple? ? | 2013-05-26 14:47:57.62
(2 rows)

dvdrental=#        

Destination GCP UPDATE,

dvdrental=> select * from actor order by actor_id desc limit 2;
?actor_id | first_name | last_name |? ? ? ? last_update
----------+------------+-----------+----------------------------
? ? ? 201 | Raj? ? ? ? | Kumara? ? | 2023-01-04 14:16:31.088698
? ? ? 200 | Thora? ? ? | Temple? ? | 2013-05-26 14:47:57.62
(2 rows)

dvdrental=> \conninfo
You are connected to database "dvdrental" as user "postgres" on host "34.100.182.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
dvdrental=>        

Source on-prem DELETE,

dvdrental=# select count(*) from actor;
?count
-------
? ?201
(1 row)

dvdrental=# delete from actor where actor_id=201;
DELETE 1
dvdrental=# select count(*) from actor;
?count
-------
? ?200
(1 row)

dvdrental=#        

Destination GCP DELETE,

dvdrental=> select count(*) from actor;
?count
-------
? ?200
(1 row)

dvdrental=> \conninfo
You are connected to database "dvdrental" as user "postgres" on host "34.100.182.1" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
dvdrental=>        

We have successfully tested CDC for DML Statements - INSERT, UPDATE and DELETE. All are working fine. We have Database Objects like Functions, Triggers and views and Sequences migrated to the Destination GCP Database from the Source On-prem database.

dvdrental=> \l+ ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? List of databases
? ? ?Name? ? ? |? ? ? ? Owner? ? ? ? ?| Encoding |? Collate? ?|? ?Ctype? ? |? ? ? ? ? ? Access privileges? ? ? ? ? ? |? Size? ?| Tablespace |? ? ? ? ? ? ? ? Description
---------------+----------------------+----------+------------+------------+-----------------------------------------+---------+------------+--------------------------------------------
?cloudsqladmin | cloudsqladmin? ? ? ? | UTF8? ? ?| en_US.UTF8 | en_US.UTF8 |? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 8825 kB | pg_default |
?dvdrental? ? ?| cloudsqlexternalsync | UTF8? ? ?| C.UTF-8? ? | C.UTF-8? ? |? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 16 MB? ?| pg_default |
?postgres? ? ? | cloudsqlexternalsync | UTF8? ? ?| C.UTF-8? ? | C.UTF-8? ? |? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| 9233 kB | pg_default | default administrative connection database
?template0? ? ?| cloudsqladmin? ? ? ? | UTF8? ? ?| en_US.UTF8 | en_US.UTF8 | =c/cloudsqladmin? ? ? ? ? ? ? ? ? ? ? ?+| 8609 kB | pg_default | unmodifiable empty database
? ? ? ? ? ? ? ?|? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? |? ? ? ? ? ? |? ? ? ? ? ? | cloudsqladmin=CTc/cloudsqladmin? ? ? ? ?|? ? ? ? ?|? ? ? ? ? ? |
?template1? ? ?| cloudsqlsuperuser? ? | UTF8? ? ?| en_US.UTF8 | en_US.UTF8 | =c/cloudsqlsuperuser? ? ? ? ? ? ? ? ? ?+| 8761 kB | pg_default | default template for new databases
? ? ? ? ? ? ? ?|? ? ? ? ? ? ? ? ? ? ? |? ? ? ? ? |? ? ? ? ? ? |? ? ? ? ? ? | cloudsqlsuperuser=CTc/cloudsqlsuperuser |? ? ? ? ?|? ? ? ? ? ? |
(5 rows)
dvdrental=>
        

dvdrental=> \d
? ? ? ? ? ? ? ? ? ? ? ? ? ?List of relations
?Schema |? ? ? ? ? ? Name? ? ? ? ? ? |? ?Type? ?|? ? ? ? Owner
--------+----------------------------+----------+----------------------
?public | actor_actor_id_seq? ? ? ? ?| sequence | cloudsqlexternalsync
?public | address_address_id_seq? ? ?| sequence | cloudsqlexternalsync
?public | category_category_id_seq? ?| sequence | cloudsqlexternalsync
?public | city_city_id_seq? ? ? ? ? ?| sequence | cloudsqlexternalsync
?public | country_country_id_seq? ? ?| sequence | cloudsqlexternalsync
?public | customer_customer_id_seq? ?| sequence | cloudsqlexternalsync
?public | film_film_id_seq? ? ? ? ? ?| sequence | cloudsqlexternalsync
?public | inventory_inventory_id_seq | sequence | cloudsqlexternalsync
?public | language_language_id_seq? ?| sequence | cloudsqlexternalsync
?public | payment_payment_id_seq? ? ?| sequence | cloudsqlexternalsync
?public | rental_rental_id_seq? ? ? ?| sequence | cloudsqlexternalsync
?public | staff_staff_id_seq? ? ? ? ?| sequence | cloudsqlexternalsync
?public | store_store_id_seq? ? ? ? ?| sequence | cloudsqlexternalsync
(13 rows)

dvdrental=> \df
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? List of functions
?Schema |? ? ? ? ? ? Name? ? ? ? ? ? | Result data type |? ? ? ? ? ? ? ? ? ? ? ? ?Argument data types? ? ? ? ? ? ? ? ? ? ? ? ?| Type
--------+----------------------------+------------------+---------------------------------------------------------------------+------
?public | _group_concat? ? ? ? ? ? ? | text? ? ? ? ? ? ?| text, text? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | func
?public | film_in_stock? ? ? ? ? ? ? | SETOF integer? ? | p_film_id integer, p_store_id integer, OUT p_film_count integer? ? ?| func
?public | film_not_in_stock? ? ? ? ? | SETOF integer? ? | p_film_id integer, p_store_id integer, OUT p_film_count integer? ? ?| func
?public | get_customer_balance? ? ? ?| numeric? ? ? ? ? | p_customer_id integer, p_effective_date timestamp without time zone | func
?public | group_concat? ? ? ? ? ? ? ?| text? ? ? ? ? ? ?| text? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | agg
?public | inventory_held_by_customer | integer? ? ? ? ? | p_inventory_id integer? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | func
?public | inventory_in_stock? ? ? ? ?| boolean? ? ? ? ? | p_inventory_id integer? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | func
?public | last_day? ? ? ? ? ? ? ? ? ?| date? ? ? ? ? ? ?| timestamp without time zone? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| func
?public | last_updated? ? ? ? ? ? ? ?| trigger? ? ? ? ? |? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?| func
?public | rewards_report? ? ? ? ? ? ?| SETOF customer? ?| min_monthly_purchases integer, min_dollar_amount_purchased numeric? | func
(10 rows)

dvdrental=> \dv
? ? ? ? ? ? ? ? ? ? ? ? ?List of relations
?Schema |? ? ? ? ? ? Name? ? ? ? ? ? | Type |? ? ? ? Owner
--------+----------------------------+------+----------------------
?public | actor_info? ? ? ? ? ? ? ? ?| view | cloudsqlexternalsync
?public | customer_list? ? ? ? ? ? ? | view | cloudsqlexternalsync
?public | film_list? ? ? ? ? ? ? ? ? | view | cloudsqlexternalsync
?public | nicer_but_slower_film_list | view | cloudsqlexternalsync
?public | sales_by_film_category? ? ?| view | cloudsqlexternalsync
?public | sales_by_store? ? ? ? ? ? ?| view | cloudsqlexternalsync
?public | staff_list? ? ? ? ? ? ? ? ?| view | cloudsqlexternalsync
(7 rows)


dvdrental=>s        

5. Promoting a migration

Stop all writes, running scripts and client connections to the source database. The downtime period begins here and promote the cloud sql instance as master by clicking on “PROMOTE”. Promotion means that the destination Cloud SQL instance is disconnected from the source, and is promoted from a replica instance to a primary instance.

Promote disconnects the source from the destination, and promotes the destination to be a writeable instance. It's recommended to stop writes to the source, and to wait until the replication delay is at zero before initiating promote.

Below is the Screenshot before Promotion,

Below is the Screenshot after Promotion,

Thus, we have completed the Migration from On-prem PostgreSQL to CloudSQL PostgreSQL successfully.

6. Known Errors you may face,

1. Failure connecting to the source database.

Failure connecting to the source database. Make sure the connectivity information on the connection profile is correct and the source database is reachable. Address the issues then Start the migration job
generic::unknown: unable to connect to source database server: failed to connect to on-premises database: connectWithTimeoutAndRetry timeout with error: dial tcp 3.109.60.201:5432: connect: connection timed out.        

When you get the above error you can resolve this using below steps.

Resolution:

Modify the ‘listen_addresses’ in ‘postgresql.conf’ file from ‘localhost’ to '*' in the source PostgreSQL on-premises database.

[postgres@ip-172-31-45-207 data]$ cat postgresql.conf |grep listen
listen_addresses = '*'		# what IP address(es) to listen on;
[postgres@ip-172-31-45-207 data]$        

Modify the Network Security Groups Inbound Rules in source On-prem to allow all port connection to the database from GCP DMS.

2. Source database must have pglogical extension installed.

The source database must have pglogical extension installed.
no pglogical extension installed on databases: dvdrental; postgres        

When you get the above error you can resolve this using below steps.

Resolution:

Create ‘pglogical’ extension at source on-prem database to resolve this error.

postgres=# show shared_preload_libraries;
?shared_preload_libraries
--------------------------
?pglogical
(1 row)

postgres=# create extension pglogical;
CREATE EXTENSION
postgres=# \dx
? ? ? ? ? ? ? ? ? ?List of installed extensions
? ?Name? ? | Version |? ?Schema? ?|? ? ? ? ? Description
-----------+---------+------------+--------------------------------
?pglogical | 2.4.2? ?| pglogical? | PostgreSQL Logical Replication
?plpgsql? ?| 1.0? ? ?| pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=#        

3. unknown column name actor_age

We would get this error if any column changes happened at the source database without modifying the destination during CDC phase.

• Note: After starting the DMS job there should not be any DDL operations running on the source database. If you want to run DDL operation you need to run that on cloud sql instance first then on source database to avoid job failure or data sync failure.

If you end up with this error, create the column in the destination CloudSQL and then the CDC will resume automatically.

Reference Links

Configure your source ?|? Database Migration Service ?|? Google Cloud

Known limitations ?|? Database Migration Service ?|? Google Cloud

Thanks @Rajkumar for the content