AWS Aurora Postgres to GCP Cloud SQL Postgres using External Replica With SSL

In this document, we will conduct a Proof of Concept on AWS Aurora Postgres to GCP Cloud SQL Postgres with External Replica and SSL connections.

This arrangement is suitable for DR purposes and does not rely on any intermediary services.

Source Server Details

Server Type : Amazon Aurora Postgres

Engine version : 11.13

Capacity type : Provisioned

SSL Config : rds.force_ssl Enabled

Public Access : Enabled

Target Server Details

Target Server : GCP CloudSQL Postgres

Engine version : PostgreSQL 11

Source Server Configurations

• Enable shared_preload_libraries

Go to AWS DB Cluster Parameter group and add 'pglogical' to shared_preload_libraries

• Enable rds.logical_replication

Go to AWS DB cluster Parameter group and enable rds.logical_replication

• Set wal_sender_timeout to 0

Go to AWS DB cluster Parameter group and set variable wal_sender_timeout to 0

• set max_replication_slots to number of databases

Set max_replication_slots according number of replication slots total required (as minimum), in this case need minimum 2 and we have set for 10

• set max_wal_senders to number of databases

Set max_wal_senders according number of wal senders slots total required (as minimum), in this case need minimum 2 and we have set for 10

• set max_worker_processes to number of databases

Its recommend to have max_worker_processes equals to number of slots

After above steps we have to take reboot aws instance in order to get effect.

Verify settings from psql command. using below commands.

cc_test_db=> show shared_preload_libraries ;
? ? ? ?shared_preload_libraries? ? ? ??
---------------------------------------
?rdsutils,pg_stat_statements,pglogical
(1 row)

cc_test_db=> show wal_level ;
?wal_level?
-----------
?logical
(1 row)

cc_test_db=> show wal_sender_timeout ;
?wal_sender_timeout?
--------------------
?0
(1 row)

cc_test_db=> show max_replication_slots ;
?max_replication_slots?
-----------------------
?10
(1 row)

cc_test_db=> show max_wal_senders ;
?max_wal_senders?
-----------------
?10
(1 row)

cc_test_db=> show max_worker_processes ;
?max_worker_processes?
----------------------
?8
(1 row)        

In this replication, setup can replicate all databases excluding system databases (rdsadmin, template0, template1)

• We have to enable pglogical to all databases we need to migrate. using below syntax.

CREATE EXTENSION IF NOT EXISTS pglogical ;        

• Create replication user

CREATE USER repl_usr WITH PASSWORD '<password>';        

• Provide required access in all databases

GRANT USAGE on SCHEMA public to repl_usr;
GRANT SELECT on ALL SEQUENCES in SCHEMA public to repl_usr;
GRANT SELECT on ALL TABLES in SCHEMA public to repl_usr;
GRANT USAGE on SCHEMA pglogical to PUBLIC;
GRANT SELECT on ALL TABLES in SCHEMA pglogical to repl_usr;        

• Provide Aurora replication access

GRANT rds_replication to repl_usr;        

Target Server Configurations

GCP Side we are configuring instance using Local terminal, First we need to authenticate user and set project in the terminal.

jistojose@jistojose jp_shell % gcloud auth login
jistojose@jistojose gcloud config set project PROJECT_ID        

Create source representation instance using curl.

To get rds certificate go to below link and download corresponding region file from below link (Using SSL/TLS to encrypt a connection to a DB instance - Amazon Relational Database Service )

From the file we have take certificate value as below.

Add certificate value to below configuration

ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
? ? ? ? ?--header 'Content-Type: application/json' \
? ? ? ? ?--data '{"name": "cc-test-master", "region": "asia-south1", "databaseVersion": "POSTGRES_11", "onPremisesConfiguration": { "hostPort": "jisto-test.cluster-c0e7ccisrmja.ap-southeast-1.rds.amazonaws.com:5432", "username": "repl_usr", "password": "<Password>", "caCertificate": "-----BEGIN CERTIFICATE----- <Value from certificate> -----END CERTIFICATE-----"}}' \
? ? ? ? ?-X POST https://sqladmin.googleapis.com/sql/v1beta4/projects/pg-dr-test/instances        

Once created we will get response as below.

{

"kind": "sql#operation",

"targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/pg-dr-test/instances/cc-test-master",

"status": "DONE",

"user": "*****",

"insertTime": "2023-08-12T07:27:02.455Z",

"endTime": "2023-08-12T07:27:02.457Z",

"operationType": "CREATE",

"name": "40448b90-54b8-43db-8b61-d6340000002f",

"targetId": "cc-test-master",

"selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/pg-dr-test/operations/40448b90-54b8-43db-8b61-d6340000002f",

"targetProject": "pg-dr-test"

}

• Then we need to create cloud sql replication instance using below command.

ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
? ? ? ? ?--header 'Content-Type: application/json' \
? ? ? ? ?--data '{"settings": { "tier": "db-custom-2-7680", "dataDiskSizeGb": "20", "ipConfiguration": { "privateNetwork": "projects/pg-dr-test/global/networks/pg-dr-test-vpc"}, "availabilityType": "REGIONAL"}, "masterInstanceName": "cc-test-master", "region": "asia-south1", "databaseVersion": "POSTGRES_11", "name": "cc-test"}' \
? ? ? ? ?-X POST https://sqladmin.googleapis.com/sql/v1beta4/projects/pg-dr-test/instances        

Note : Once its created wait for instance status become stopped from modifying.

After above step instance created, we can reset password for default user postgres using console.

• Next step is add connectivity (if not present), We can get external ip address from GCP cloud sql. like below.

• We have to white list these ip’s in aws network in order to have connectivity between source and Target.

• In this step we can ensure connectivity between source and target using curl command below.

ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
? ? ?--header 'Content-Type: application/json' \
? ? ?--data '{ "syncMode": "EXTERNAL_SYNC_MODE_UNSPECIFIED"}' \
? ? ?-X POST https://sqladmin.googleapis.com/sql/v1beta4/projects/pg-dr-test/instances/cc-test/verifyExternalSyncSettings        

Output of above command should be like below

{

"kind": "sql#externalSyncSettingErrorList"

}

General error regarding connectivity.

If any error like below then we have to check connectivity.

{ "kind": "sql#externalSyncSettingErrorList",

"errors": [ { "kind": "sql#externalSyncSettingError",

"type": "CONNECTION_FAILURE",

"detail": "generic::unknown: unable to connect to source database server: failed to connect to on-premises database \"postgres\": connectWithTimeoutAndRetry timeout with error: dial tcp 3.0.83.251:5432: connect: connection timed out" }]}

Starting replication

We can start replication if connectivity is good enough.

ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
? ? ?--header 'Content-Type: application/json' \
? ? ?--data '{ "syncMode": "ONLINE"}' \
? ? ?-X POST https://sqladmin.googleapis.com/sql/v1beta4/projects/pg-dr-test/instances/cc-test/startExternalSync
        

Output will be like below

{

"kind": "sql#operation",

"targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/pg-dr-test/instances/cc-test",

"status": "PENDING",

"user": "*******",

"insertTime": "2023-08-12T08:44:38.704Z",

"operationType": "START_EXTERNAL_SYNC",

"name": "21701359-2740-4e95-b0e8-ff800000002f",

"targetId": "cc-test",

"selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/pg-dr-test/operations/21701359-2740-4e95-b0e8-ff800000002f",

"targetProject": "pg-dr-test"

}

Once replication started we can able to see data is populated in database. Also Instance in GCP Cloud SQL dashboard will be available status like below.

Also we can verify replication slot running on Source database (AWS) with status 't' using below.

cc_test_db=> select * from pg_replication_slots ;
? ? ? ? ? ? ? ? ? ?slot_name? ? ? ? ? ? ? ? ? ? |? ? ? plugin? ? ? | slot_type | datoid |? database? | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn?
------------------------------------------------+------------------+-----------+--------+------------+-----------+--------+------------+------+--------------+-------------+---------------------
?pgl_postgres_cloudsql1712cc6_cloudsqle2983f6? ?| pglogical_output | logical? ?|? 14007 | postgres? ?| f? ? ? ? ?| t? ? ? |? ? ? 32161 |? ? ? |? ? ? ? 22044 | 0/42962D0? ?| 0/4296AA0
?pgl_cc_test_db_cloudsqlf960373_cloudsqle2983f6 | pglogical_output | logical? ?|? 16407 | cc_test_db | f? ? ? ? ?| t? ? ? |? ? ? 32162 |? ? ? |? ? ? ? 22044 | 0/42962D0? ?| 0/4296AA0
(2 rows)        

Reference Links : About replicating from an external server ?|? Cloud SQL for PostgreSQL ?|? Google Cloud