AWS DMS with PostgreSQL source¶

What’s Inside¶

Using a PostgreSQL database as an AWS DMS source

Prerequisites¶

First of all, activate the pglocical extension on your RDS PostgreSQL instance.

CREATE EXTENSION pglogical;
SELECT * FROM pg_catalog.pg_extension WHERE extname='pglogical';

SHOW shared_preload_libraries;
SELECT name, setting FROM pg_settings WHERE name in ('rds.logical_replication','shared_preload_libraries');

Data in Source¶

After that, connect to RDS PostgreSQL, and provision a little bunch of data.

DROP TABLE IF EXISTS foo CASCADE;
CREATE TABLE foo (id INT PRIMARY KEY, name TEXT, age INT, attributes JSONB);
INSERT INTO foo (id, name, age, attributes) VALUES (42, 'John', 30, '{"foo": "bar"}');
INSERT INTO foo (id, name, age, attributes) VALUES (43, 'Jane', 31, '{"baz": "qux"}');

Data in Target¶

cr> SELECT * FROM public.foo;

+---------------------------------------------------------------------+
| data                                                                |
+---------------------------------------------------------------------+
| {"age": 30, "attributes": {"foo": "bar"}, "id": 42, "name": "John"} |
| {"age": 31, "attributes": {"baz": "qux"}, "id": 43, "name": "Jane"} |
+---------------------------------------------------------------------+

UPDATE foo SET age=32 WHERE name='Jane';
UPDATE foo SET age=33 WHERE id=43;
UPDATE foo SET age=33 WHERE attributes->>'foo'='bar';
UPDATE foo SET attributes = jsonb_set(attributes, '{last_name}', '"Doe"', true) WHERE name='John';

DELETE FROM foo WHERE name='Jane';
DELETE FROM foo WHERE name='John';