A tested, step-by-step walkthrough — how PostgreSQL logical replication works internally, and the four traps that catch people at cutover.
Why use PostgreSQL's own replication?
PostgreSQL has had built-in logical replication since version 10. It streams committed row changes from a publisher to a subscriber over a normal database connection, and it works perfectly well when the subscriber is Amazon RDS for PostgreSQL or Aurora PostgreSQL.
For PostgreSQL-to-PostgreSQL moves this is worth considering first. The AWS documentation makes the same point:
"When you migrate from a database engine other than PostgreSQL to a PostgreSQL database, AWS DMS is almost always the best migration tool to use. But when you are migrating from a PostgreSQL database to a PostgreSQL database, PostgreSQL tools can be more effective."
What you get:
- Near-zero downtime. The bulk copy and the change stream both run while the source stays fully available. Your only outage is the cutover itself — seconds, if you drive it well.
- Native fidelity. No intermediate type system. PostgreSQL-specific types, collations, and extensions arrive as themselves.
- No extra moving parts. No service in your data path, no IAM roles, no additional cost.
What you take on: schema, sequences, roles, monitoring, and validation are all yours. That is the whole trade, and the rest of this post is about doing your half properly.
There is a section at the end on when a managed service is the better call.
Everything below was executed, not sketched. I ran it against two PostgreSQL 18.6 clusters (source on port 5501, target on 5502) with a 26,000-row schema, live write traffic during the migration, and a full cutover. The output shown is real. The RDS-specific configuration step (step 2) was validated separately against a live RDS for PostgreSQL 15.19 instance. I verified the replica-identity behaviour on both PostgreSQL 15.19 and 18.6 — it is not version-specific.
How PostgreSQL logical replication actually works
Understanding the mechanism makes the traps obvious rather than surprising. This is all standard, documented PostgreSQL behaviour — you can watch it yourself with log_replication_commands = on.
SOURCE (publisher) TARGET (subscriber)
────────────────── ───────────────────
wal_level = logical wal_level = logical ← yes, the target too
CREATE PUBLICATION ────────────────────────► CREATE SUBSCRIPTION
│
◄────────────────────────────────┤ subscriber connects to publisher
│
for each table, the subscriber: │
1. creates a temporary replication slot ◄────┤ SNAPSHOT 'use'
2. COPY <table> TO STDOUT ◄────┤ ← this is where data moves
3. streams changes from the snapshot's LSN ◄────┤ catch up writes during the copy
4. drops the temporary slot ◄────┤
│
one durable slot streams ongoing changes ◄────────┘ pgoutput
Three consequences drive everything else:
The subscriber connects to the publisher, not the other way round. Your RDS instance needs network reachability to the source on port 5432, and the source credentials are stored inside the target's pg_subscription catalog. Plan security groups and credential rotation accordingly.
Slot creation yields a snapshot and its LSN atomically. When PostgreSQL creates a logical slot it must find a consistent point — an LSN where every in-flight transaction has committed or aborted. With SNAPSHOT 'use', the COPY then runs inside exactly that snapshot, and streaming resumes from that same LSN. This is what makes the handoff from bulk copy to change streaming lossless: no gap, no duplication. You do not have to engineer it, but you should know it is what you are relying on.
Peak slot usage is 1 + N, not 1. During initial sync PostgreSQL creates one transient slot per table being synchronised (named pg_<suboid>_sync_<relid>_<sysid>), alongside the durable slot for ongoing changes. Size max_replication_slots for the peak, not the steady state.
Prerequisites
| Requirement | |
|---|---|
| Source | PostgreSQL 10+ (self-managed on EC2, on-premises, or another cloud) |
| Target | RDS for PostgreSQL or Aurora PostgreSQL, same or higher major version |
| Network | Target must reach source on 5432. Source must reach target on 5432. |
| Privileges | A source role with REPLICATION and SELECT; on the target, rds_superuser |
Step 0 — Audit the source before you touch anything
This is the single highest-value five minutes of the whole project. Find every table without a primary key.
SELECT n.nspname || '.' || c.relname AS table_without_pk
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.oid AND i.indisprimary);
On my test database:
table_without_pk
------------------
public.audit_log
Also inventory what logical replication will not bring, because you will need to handle each one:
-- sequences: never replicated, must be synced at cutover
SELECT schemaname, sequencename, last_value FROM pg_sequences;
-- large objects: invisible to logical replication entirely
SELECT count(*) FROM pg_largeobject_metadata;
-- extensions: confirm each is supported on RDS/Aurora
SELECT extname, extversion FROM pg_extension;
Step 1 — Configure the source
# postgresql.conf
wal_level = logical # requires a restart
max_replication_slots = 10 # >= number of subscriptions; see note below
max_wal_senders = 12 # max_replication_slots + other standbys
wal_sender_timeout = 0 # AWS recommendation: disables the idle timeout
Those values follow AWS's documented recommendations for logical replication. If your source is itself RDS or Aurora, set rds.logical_replication = 1 instead — same static-parameter-plus-reboot dance as step 2.
wal_level is static, so this needs a restart. Verify:
SELECT current_setting('wal_level'); -- logical
Sizing
max_replication_slots. AWS suggests one per subscription. Be aware the true peak is higher: during initial sync PostgreSQL creates one transient slot per table being synchronised, on top of the durable slot. Leave headroom rather than sizing to exactly the number of subscriptions.
Allow the target to connect, in pg_hba.conf:
host appdb repl <target-cidr>/32 scram-sha-256
host replication repl <target-cidr>/32 scram-sha-256
Step 2 — Configure the target
This step catches almost everyone. The subscriber also needs logical replication enabled — not just the source. Without it you get a hard failure.
On RDS or Aurora, rds.logical_replication is a static parameter, and default parameter groups cannot be edited. So you need a custom group and a reboot:
aws rds create-db-parameter-group \
--db-parameter-group-name pg-logical \
--db-parameter-group-family postgres15 \
--description "logical replication enabled"
aws rds modify-db-parameter-group \
--db-parameter-group-name pg-logical \
--parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot"
aws rds modify-db-instance \
--db-instance-identifier my-target \
--db-parameter-group-name pg-logical --apply-immediately
aws rds reboot-db-instance --db-instance-identifier my-target
For Aurora, put the parameter in a DB cluster parameter group and reboot the writer.
Check ParameterApplyStatus rather than assuming — it reads pending-reboot until you actually reboot. Then confirm inside the database:
SELECT name, setting FROM pg_settings
WHERE name IN ('wal_level', 'rds.logical_replication');
name | setting
-------------------------+---------
rds.logical_replication | on
wal_level | logical
Step 3 — Create the replication role on the source
CREATE ROLE repl WITH LOGIN REPLICATION PASSWORD '<strong-password>';
GRANT USAGE ON SCHEMA public TO repl;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl;
Grant SELECT in every schema you intend to migrate. Missing column privileges cause failures partway through the initial copy.
Step 4 — Migrate global objects, then the schema
Logical replication carries neither DDL nor global objects. Tables must exist on the target before a subscription can populate them, and roles must exist before the schema dump can assign ownership and grants to them.
4a. Global objects (roles, tablespaces)
AWS states the problem plainly:
"The
pg_dumputility only takes the backup of the single database at any point of time. It doesn't back up global objects such as users and groups. To migrate global objects, you need to use a combination ofpg_dumpalland psql."
The documented procedure:
pg_dumpall --host <source> --globals-only --no-role-passwords \
--username postgres > globals_only.sql
psql --host <target> --dbname appdb --username postgres -f globals_only.sql
--no-role-passwords is not optional for RDS/Aurora PG, and the reason is specific:
"Because the Amazon RDS for PostgreSQL and Aurora PostgreSQL
rds_superuserrole doesn't have permission on thepg_authidtable, it's important to use--no-role-passwordswithpg_dumpall."
Two consequences to plan for:
- You must set role passwords separately on the target, since they were deliberately excluded.
- Review the file before applying it. The RDS master user is a member of
rds_superuser, not a true superuser, so statements grantingSUPERUSER,REPLICATION, orBYPASSRLSwill fail. Strip those attributes or drop those roles from the script.
Related, if your source is RDS or Aurora and pg_dump cannot see everything:
"For running the
pg_dumputility, you need to have SELECT permission on all database objects. By default,rds_superuserdoesn't have SELECT permission on all objects. As a workaround, grant SELECT permission to therds_superuserrole or grant permissions of other users tords_superuserby running:GRANT <user> TO <rds_super_user>;"
4b. Schema
pg_dump --host <source> --schema-only --dbname appdb --username postgres > schema_only.sql
psql --host <target> --username postgres --dbname appdb -f schema_only.sql
Verify the target has the tables and zero rows:
target table: audit_log
target table: customers
target table: orders
customers=0 orders=0 audit_log=0
For a small database you can stream it and skip the intermediate file entirely, which AWS also recommends:
pg_dump --host <source> --username postgres appdb | psql --host <target> --username postgres appdb
Why not --no-owner --no-acl?
You will see these flags recommended widely on the internet. They do not appear in AWS's documented procedures, and it is worth understanding why before you reach for them.
They suppress ALTER ... OWNER TO and GRANT/REVOKE, which avoids errors when the target lacks the roles. But that is treating the symptom. I tested all three paths against the same table, owned by app_owner with SELECT granted to app_reader:
SOURCE: owner = app_owner
grants = app_owner=arwdDxtm/app_owner app_reader=r/app_owner
(A) restore WITH --no-owner --no-acl
0 errors
owner = postgres ← ownership silently reassigned
grants = (none) ← every privilege silently dropped
(B) restore WITHOUT the flags, roles missing on target
ERROR: role "app_owner" does not exist
ERROR: role "app_reader" does not exist
(C) restore WITHOUT the flags, after step 4a
0 errors
owner = app_owner ← preserved
grants = app_owner=arwdDxtm/app_owner app_reader=r/app_owner
Path (A) produces a clean log and a wrong database. Your application roles arrive with no privileges at all, and you discover it later as permission-denied errors from the application rather than as errors during the migration.
Do step 4a, then omit the flags. That is path (C), and it is what the AWS procedure amounts to.
Optional: faster restore for very large tables
For a big initial load, AWS recommends deferring index and constraint creation:
"Dropping foreign keys, primary keys, and indexes before restore and adding them after restore can drastically reduce migration time."
You can approximate this by splitting the dump — restore --section=pre-data before the subscription, and --section=post-data after the initial sync completes. Be aware of the interaction with the primary-key requirement below: a table needs its replica identity in place before it starts replicating changes, so if you defer primary keys you must complete the initial sync before the change stream matters.
Also useful from the same source: --jobs should be at most the number of vCPUs on the target, and pg_dump opens jobs + 1 connections, so check max_connections.
Step 5 — Create the publication
CREATE PUBLICATION appdb_pub FOR ALL TABLES;
Prefer an explicit table list if you want control over scope:
CREATE PUBLICATION appdb_pub FOR TABLE customers, orders, audit_log;
FOR ALL TABLES is simpler but has a sharp edge covered in the traps below.
The primary-key trap, live
The instant that publication exists, PostgreSQL starts refusing UPDATE and DELETE on any published table with no replica identity. Here is my source database, immediately after:
UPDATE audit_log : ERROR: cannot update table "audit_log" because it does not have
a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
DELETE audit_log : ERROR: cannot delete from table "audit_log" because it does not have
a replica identity and publishes deletes
INSERT audit_log : (succeeded — no error)
UPDATE customers : (succeeded — it has a primary key)
Read that carefully. The error lands on your application's own writes to the source database. Not in a migration tool, not in a log you are watching. And INSERT still works, so an insert-heavy workload looks perfectly healthy while update paths break.
The fix, in preference order:
Add a real primary key. Best, and the only option that carries no performance penalty.
REPLICA IDENTITY FULL. AWS documents this as the supported workaround:"It's important to have a replication identity (primary key or unique index) for tables that are part of the publication in the logical replication. If the table doesn't have a primary key or a unique index, you can set replication identity to full (
ALTER TABLE <table name> REPLICA IDENTITY FULL) where the entire row acts as a primary key."Understand the cost before choosing it. It writes every column of the old row to WAL, and because the subscriber has no index to match on, it must scan to find each row. AWS puts it directly: "You can replicate tables with no primary or unique keys, but updates and deletes on those tables are slow on the subscriber." Fine for a low-churn table; painful for a hot one.
REPLICA IDENTITY USING INDEXwith a unique,NOT NULLindex. Keeps the subscriber's lookups indexed.
A non-unique index does not qualify. For my append-mostly log table, option 2 is the pragmatic choice — apply it on both sides:
-- source AND target
ALTER TABLE audit_log REPLICA IDENTITY FULL;
UPDATE audit_log now : (succeeded)
DELETE audit_log now : (succeeded)
Step 6 — Create the subscription
CREATE SUBSCRIPTION appdb_sub
CONNECTION 'host=source-host port=5432 dbname=appdb user=repl password=<password>'
PUBLICATION appdb_pub
WITH (copy_data = true);
copy_data = true is what triggers the initial bulk copy. This one statement does the entire data migration.
Watch per-table progress. srsubstate = 'r' means ready and streaming:
SELECT c.relname, r.srsubstate, r.srsublsn
FROM pg_subscription_rel r
JOIN pg_class c ON c.oid = r.srrelid
ORDER BY 1;
relname | srsubstate | srsublsn
------------+------------+------------
audit_log | r | 0/220B090
customers | r | 0/220B090
orders | r | 0/220B100
States progress i (initialising) → d (copying data) → s (synchronised) → r (ready). Row counts after initial sync:
TARGET: customers=5000 orders=20000 audit_log=1000
SOURCE: customers=5000 orders=20000 audit_log=1000
Note on credentials. The connection string — password included — is stored in
pg_subscription.subconninfoand is readable by any superuser on the target. If you havelog_statement = allenabled, it is also written to your logs in cleartext. Use a dedicated replication role, and rotate it after cutover.
Optional — a faster bulk load for large databases
Skip this section unless your initial sync is too slow. It is more moving parts in exchange for parallelism.
copy_data = true is convenient but the subscriber copies one table per worker, and there is no way to parallelise within a table. For a database with one dominant table, that single-threaded COPY sets your floor. Parallel pg_dump -j can be considerably faster.
The problem to solve is consistency: if you dump the data separately, at what point does the change stream start, so that nothing is lost or duplicated? PostgreSQL answers this by letting slot creation export a snapshot.
Note the distinction — the SQL function does not export one:
SELECT * FROM pg_create_logical_replication_slot('sqlslot','pgoutput');
-- slot_name | lsn
-- sqlslot | 0/1C0C660 ← no snapshot
Only the replication protocol command can. Connect with replication=database to source database (RDS/APG supported as well) and keep that session open:
psql "host=source port=5432 user=postgres dbname=appdb replication=database"
CREATE_REPLICATION_SLOT bulkslot LOGICAL pgoutput (SNAPSHOT 'export');
slot_name | consistent_point | snapshot_name | output_plugin
bulkslot | 0/1C0C978 | 0000007E-00000002-1 | pgoutput
Now dump the data inside that snapshot, in parallel, from another shell:
pg_dump -h source -d appdb --data-only --snapshot='0000007E-00000002-1' -Fd -j 4 -f /dump
pg_restore -h target -d appdb -j 4 /dump
Then attach a subscription to the slot you already created, telling it not to copy:
CREATE SUBSCRIPTION mysub
CONNECTION 'host=source port=5432 dbname=appdb user=repl password=<pw>'
PUBLICATION mypub
WITH (copy_data = false, create_slot = false, slot_name = 'bulkslot');
I tested this with 500 rows written after the snapshot was taken, to confirm the handoff is exact:
source rows at snapshot time : 1000
rows written after snapshot : 500
source rows total : 1500
target rows after snapshot dump : 1000 ← the snapshot state, not 1500
target rows after CDC catch-up : 1500
duplicate ids : 0
missing ids : 0
The dump saw exactly the snapshot state, and the change stream supplied precisely the rows the dump did not have.
Two things will bite you here.
The snapshot lives only as long as the session that exported it. Close that replication=database connection and the snapshot is gone — pg_dump then fails with ERROR: snapshot "..." does not exist. I hit this the first time by running the steps from separate shells. The connection must stay open for the entire duration of the dump, which for a large database can be hours.
And with create_slot = false you now own that slot. If you abandon the attempt, drop it by hand — see trap 4.
For most migrations copy_data = true is the right choice. Reach for this only when you have measured the initial sync and it is genuinely the bottleneck.
Step 7 — Verify change data capture
Do not trust the plumbing; prove it. Generate real traffic on the source — inserts, updates and deletes — then compare.
SOURCE: customers=5300 orders=20900 audit_log=889
TARGET: customers=5300 orders=20900 audit_log=889
The three monitoring queries worth putting on a dashboard:
-- SOURCE: is the slot alive, and how much WAL is it pinning?
SELECT slot_name, plugin, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;
slot_name | plugin | active | retained_wal
-----------+----------+--------+--------------
appdb_sub | pgoutput | t | 56 bytes
-- SOURCE: how far behind is the subscriber?
SELECT confirmed_flush_lsn,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS outstanding
FROM pg_replication_slots;
retained_wal is the number that can take your source database down. If the subscriber stalls, that grows without bound until the source disk fills. Alarm on it.
confirmed_flush_lsn is what the subscriber has durably applied — not merely what was sent. It is the correct field to drain against at cutover.
Step 8 — Cut over
The ordering here is not cosmetic. Two steps are load-bearing:
- Sequences must be read after writes stop. Read them earlier and the value you copy is already stale.
- The subscription must be dropped after the drain, or you discard in-flight changes.
8a. Quiesce the source
Stop the application. Then make sure it cannot reconnect — stopping a service does not close connections that are already open:
ALTER DATABASE appdb CONNECTION LIMIT 0;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE datname = 'appdb' AND pid <> pg_backend_pid() AND usename <> 'postgres';
8b. Drain
Freeze the source LSN, then wait for the subscriber to confirm it. Comparing against a frozen LSN rather than a live pg_current_wal_lsn() is what makes this terminate:
SELECT pg_current_wal_lsn(); -- note this value, e.g. 0/22CF070
SELECT pg_wal_lsn_diff('0/22CF070', confirmed_flush_lsn) FROM pg_replication_slots;
-- repeat until 0
source WAL frozen at 0/22CF070
attempt 1: outstanding bytes=0
8c. Reconcile with exact counts
Use count(*). Do not use n_live_tup or reltuples — they are planner estimates and will disagree by thousands under load.
customers source=5300 target=5300 OK
orders source=20900 target=20900 OK
audit_log source=889 target=889 OK
8d. Sync the sequences
Logical replication never replicates sequence values. This is the trap that silently corrupts an otherwise perfect migration. Before syncing, my target looked like this:
SOURCE sequences: TARGET sequences:
customers_id_seq = 5300 customers_id_seq = (never used)
orders_id_seq = 20900 orders_id_seq = (never used)
Every row arrived, but the target's sequences never moved. Repoint your application now and it immediately issues primary keys that already exist.
Read last_value from the source and apply setval on the target:
-- on the SOURCE
SELECT schemaname || '.' || sequencename, last_value FROM pg_sequences;
-- on the TARGET, for each
SELECT setval('public.customers_id_seq', 5300 + 1000, true);
public.customers_id_seq: source=5300 -> target setval=6300
public.orders_id_seq: source=20900 -> target setval=21900
Two details matter:
- The margin (1000 here) covers values already handed out by
nextval()into other sessions' caches but never committed. Those do not appear inlast_valueyet are already spoken for. Size it to your peak concurrency. is_called = truemakes the nextnextval()returnvalue + 1. Withfalse, the sequence re-issues the value itself.
8e. Promote
-- on the TARGET
DROP SUBSCRIPTION appdb_sub;
Then verify on the source that the slot is gone:
SELECT count(*) FROM pg_replication_slots; -- must be 0
NOTICE: dropped replication slot "appdb_sub" on publisher
source slots remaining = 0
DROP SUBSCRIPTION drops the publisher's slot too, provided the source is reachable. If it is not, the drop hangs — detach it first:
ALTER SUBSCRIPTION appdb_sub SET (slot_name = NONE);
DROP SUBSCRIPTION appdb_sub;
-- then on the source:
SELECT pg_drop_replication_slot('appdb_sub');
Finally drop the publication:
DROP PUBLICATION appdb_pub;
8f. Verify the new primary
subscriptions=0
in_recovery=false
target issued new customer id = 6301 (source high-water mark was 5300)
PASS: no primary-key collision possible
That last check is the one that proves the sequence sync worked. Insert a row on the target and confirm the generated key is strictly above the source's high-water mark.
The four traps, summarised
1. Tables without a primary key. Once a publication exists, source UPDATE/DELETE are rejected. The failure surfaces in your application, not in any migration tool, and INSERT keeps working so partial workloads mask it. Audit first (step 0).
2. Sequences are not replicated. Every sequence on the target sits at its dump-time value. Sync with setval after writes stop and before repointing the application.
3. DDL is not replicated. A column or table added on the source never appears on the target, with no error anywhere. AWS is explicit about the remedy:
"Any DDL changes aren't replicated. To avoid any interruption in replication, DDL changes such as any changes on table definition should be done at both sides at the same time."
There is a specific version of this worth calling out. With FOR ALL TABLES, a newly created source table is added to the publication automatically — but the subscriber ignores it until you run:
ALTER SUBSCRIPTION appdb_sub REFRESH PUBLICATION;
In testing I saw a new table with 500 rows on the source and 0 on the target, with no error on either side.
3b. Some object types cannot be replicated at all. Per AWS: "Views, materialized views, partition root tables, or foreign tables can't be replicated." These come across in the schema dump (step 4b) as definitions, but no rows will flow into a materialized view and you cannot subscribe a partition root. Plan to refresh materialized views on the target after cutover, and replicate partitioned tables by targeting the leaf partitions.
Large objects (pg_largeobject) are also invisible to logical replication. If you use lo_* functions, migrate that data separately with pg_dump or move off large objects first.
4. An abandoned replication slot will fill your source disk. This is the most damaging way to get this wrong, because it takes down the database you were migrating away from. An inactive logical slot retains WAL indefinitely. Always confirm zero slots after cutover, and alarm on retained_wal during the migration.
Also plan rollback before you cut over. Once the slot is dropped, changes on the target are tracked nowhere. Your options are reverse replication configured in advance — which requires primary keys on every table — or a target snapshot as a fixed recovery point.
When a managed service is the better call
Self-managed replication is not always the right answer. AWS Database Migration Service is the better choice when any of these apply.
You need built-in data validation. This is the biggest functional gap in the approach described here. Nothing in PostgreSQL logical replication verifies that the target matches the source — you get sync state, not content verification. DMS replication tasks include row-level validation that runs continuously and reports mismatches. If equivalence has to be proven for audit or regulatory sign-off, either budget for building that yourself or use a service that does it.
Note that not every DMS migration type includes validation. AWS documents that homogeneous data migrations, specifically, do not:
"Homogeneous data migrations don't provide a built-in tool for data validation."
So check which DMS migration type you are choosing against your validation requirement.
You need to reshape data in flight. Renaming, retyping, filtering rows or columns during migration. Logical replication is faithful by design — it gives you the same rows, not different ones.
Your source is not PostgreSQL. Logical replication is PostgreSQL-to-PostgreSQL only.
You would rather not own the operational surface. Slot monitoring, WAL retention alarms, cutover orchestration and reconciliation are all yours in the self-managed approach. That is real work, and a managed service absorbing it is a legitimate reason to choose one.
One thing that is not a differentiator: downtime. Any change-data-capture-based migration is bounded by the same cutover window, because the bulk load and the change stream both run with the source available. Do not choose between these options on downtime; choose on validation needs, transformation needs, and how much operational surface you want to own.
A note if you go the DMS route with a PostgreSQL source
DMS replication tasks do not use pgoutput. Per the AWS documentation, DMS creates its replication slot with pglogical if that extension is present on your source, and falls back to test_decoding otherwise. You can pin it with the PluginName endpoint setting.
This matters for the primary-key problem discussed above, because the remedies are not the same. AWS documents the constraint directly:
"REPLICA IDENTITY FULL is supported with a logical decoding plugin, but isn't supported with a pglogical plugin."
In other words, if your pipeline ends up on pglogical, REPLICA IDENTITY FULL will not rescue a table without a primary key — only a real primary key will. Worth knowing before you pick a remedy for that audit table.
Reference: the whole thing
-- SOURCE: wal_level = logical, restart, then
CREATE ROLE repl WITH LOGIN REPLICATION PASSWORD '<pw>';
GRANT USAGE ON SCHEMA public TO repl;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl;
ALTER TABLE <pk_less_table> REPLICA IDENTITY FULL; -- per step 0 audit
CREATE PUBLICATION appdb_pub FOR ALL TABLES;
# SCHEMA first
pg_dump -h source -d appdb --schema-only | psql -h target -d appdb
-- TARGET: rds.logical_replication = 1, reboot, then
ALTER TABLE <pk_less_table> REPLICA IDENTITY FULL;
CREATE SUBSCRIPTION appdb_sub
CONNECTION 'host=source port=5432 dbname=appdb user=repl password=<pw>'
PUBLICATION appdb_pub WITH (copy_data = true);
-- wait for every row: srsubstate = 'r'
SELECT c.relname, r.srsubstate FROM pg_subscription_rel r
JOIN pg_class c ON c.oid = r.srrelid;
-- CUTOVER
-- 1. stop the application; ALTER DATABASE appdb CONNECTION LIMIT 0;
-- 2. drain: wait until pg_wal_lsn_diff(<frozen lsn>, confirmed_flush_lsn) = 0
-- 3. reconcile: count(*) per table, both sides
-- 4. sequences: setval(seq, source_last_value + margin, true) on the target
-- 5. DROP SUBSCRIPTION appdb_sub; then verify source slots = 0
-- 6. repoint the application
Every command in this post was executed, not sketched. Tested on PostgreSQL 18.6 — two clusters, 26,000 rows across three tables, live write traffic during the migration, and a full cutover including sequence synchronisation. The replica-identity behaviour was additionally verified on PostgreSQL 15.19, so it is not version-specific. The RDS parameter-group procedure in step 2 was validated against a live Amazon RDS for PostgreSQL 15.19 instance.
