PostgreSQL To AWS S3 And Google Cloud Storage

​

Scenario

​

Demo

​

Prerequisites

​

Postgres Peer

1. Enable logical decoding in PostgreSQL. Ensure that the following settings/GUCs are properly configured:
   1. wal_level: logical
   2. max_wal_senders: >1
   3. max_replication_slots: 4
2. Enable replication access for a PostgreSQL user - ALTER USER pg_user REPLICATION;
3. Ensure that both tables have primary keys. Composite primaries are also fine. If not, make sure your tables have REPLICA IDENTITY FULL.
4. If you are using PostgreSQL on the cloud, below links capture how to enable logical replication for each cloud:
   1. AWS RDS and Aurora PostgreSQL
   2. Azure Database for PostgreSQL - Flexible Server
   3. GCP Cloud SQL PostgreSQL

​

S3 peer

1. Ensure you have an existing bucket to use.
2. For PeerDB to access the S3 Peer, you can either use an existing AWS user or create a new AWS user.
3. Create access keys for that user through AWS Console or AWS CLI or AWS API
4. For the same user, create and attach a policy as below using JSON editor. Sharing AWS docs for reference. PeerDB requires s3:ListAllMyBuckets, s3:GetObject, s3:PutObject, s3:ListBucket and s3:DeleteObject on that bucket.
      {
          "Version": "2012-10-17",
          "Statement": [
              {
                  "Effect": "Allow",
                  "Action": "s3:ListAllMyBuckets",
                  "Resource": "*"
              },
              {
                  "Effect": "Allow",
                  "Action": [
                      "s3:GetObject",
                      "s3:PutObject",
                      "s3:ListBucket",
                      "s3:DeleteObject"
                  ],
                  "Resource": [
                      "arn:aws:s3:::<your-bucket-name>",
                      "arn:aws:s3:::<your-bucket-name>/*"
                  ]
              }
          ]
      }
   

​

GCS peer

1. Ensure you have an existing bucket to use.
2. For GCS, you must create a HMAC key-pair and use this for ACCESS_KEY_ID and SECRET_ACCESS_KEY in the Docker compose file.

​

Step 1: Add PostgreSQL and S3/GCS Peers

-- Connect to PeerDB
psql "port=9900 host=localhost password=peerdb"

-- Add PostgreSQL and Event Hubs peers
CREATE PEER postgres_peer FROM PostgreSQL (...);
CREATE PEER s3_peer FROM S3 (...);
-- Or, CREATE PEER gcs_peer FROM S3 (...);

​

Step 2: Real-Time CDC from PostgreSQL to S3/GCS by creating a MIRROR

​

Create MIRROR using UI

​

Create MIRROR using SQL

CREATE MIRROR IF NOT EXISTS <mirror-name>
FROM <postgres-peer-name> TO <storage-peer-name>
WITH TABLE MAPPING(
    <source-schema>.<table>:<destination_name>,
    ... -- Repeat as required for multiple tables
)
WITH(
    max_batch_size = <number>,
    publication_name = '<publication-name>'
);

CREATE MIRROR IF NOT EXISTS test_mirror_interop
FROM test_pg_peer TO test_gcs_peer
WITH TABLE MAPPING(
    schema1.table1:dest_table1,
    schema1.table2:dest_table2
    -- Add more tables as required
)
WITH(
    max_batch_size = 300000,
    publication_name = 'test_publication'
);

• mirror-name: Desired name for the mirror.
• postgres-peer-name: Name of the PostgreSQL peer.
• storage-peer-name: Name of the S3/GCS peer.
• max_batch_size: Maximum number of records in a batch.
• publication_name: Name of the publication.

​

Step 3: Monitor the MIRROR

​

Step 5: DROP MIRROR

-- drop the mirror
DROP MIRROR test_mirror_interop;