clone_replica_steps.md

Clone Replica Steps (Did not work)

Part 1: Setup publisher (PG13)

[x] 1. Create publications

Let's create publications for all tables

CREATE PUBLICATION pub_all_table for all tables;

[x] 2. Create replication slots¹

SELECT pg_create_logical_replication_slot('pub_all_table', 'pgoutput');

Part 2: Setup subscriber (PG16)

[x] 3. Clone the PG13 database cluster.

[x] 4. Capture the LSN in the cloned DB

SELECT * FROM aurora_volume_logical_start_lsn();

[x] 5. The replication slots & publication tables in the new subscriber DB are relics of cloning the PG13 DB, so we can safely drop them from the table.

select * from pg_replication_slots;
select pg_drop_replication_slot(slot_name) from pg_replication_slots;
select * from pg_replication_slots;
DROP PUBLICATION pub_all_table;

[x] 6. Upgrade our PG13 clone to PG16.

1. Modify
2. Update version in top select
3. Update parameter groups
4. Don’t forget to apply immediately

This upgrade takes ~30 mins to complete, during which we can run the following command in the source DB (PG13) to find the replication lag between our two DBs.

SELECT 
  now() AS CURRENT_TIME,
  slot_name,
  active,
  active_pid,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS diff_size,
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS diff_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

[x] 7. Create a subscription in PG13

Note: Create but do not enable the subscription yet!

  CREATE SUBSCRIPTION sub_all_table 
  CONNECTION 'host=HOST_HERE dbname=instant port=5432 user=instant_admin password=CHANGE_ME' 
  PUBLICATION pub_all_table WITH ( copy_data = false, 
  create_slot = false, enabled = false, connect = true, 
  slot_name = 'pub_all_table'); 

  select * from pg_subscription;

[x] 8. Capture Replication Origin³

SELECT * FROM pg_replication_origin;

[x] 9. Advance Replication Origin to the LSN number captured earlier.

  SELECT pg_replication_origin_advance('----','CHANGEME');

[x] 10. Finally, with the advanced replication origin, we can enable subscription in PG13, which should start to reduce the replication lag between the databases.

ALTER SUBSCRIPTION sub_all_table ENABLE;

The replication lag now is decreasing.

[x] 11. Restart the replica database

[ ] 12. Check that all the transactions synced

On instant-aurora-1

select max(id) from transactions;
select count(*) from transactions where id < :max-id;

On replica:

select count(*) from transactions where id < :max-id;

[ ] 13. Run vacuum analyze

vacuum (verbose, analyze, full);

Based on this https://www.postgresql.org/message-id/CAA4eK1K3vjj7o-x8AbDvrF_O989enWcb_2_aWorBWj1%2B_dYx5w%40mail.gmail.com it sounds plausible something could have ran on the clone which wrote to WAL before you're able to capture the LSN (I suppose maybe auto vacuum could cause this)

So the LSN on the clone advanced a bit from different WAL changes than the origin

It's possible AWS has some automation that did something (maybe they reset the password of the RDS service account they use when an instance is restored, for example)

I also think you lost the 13 rows because you got the lsn in the cloned PG13, instead you could have captured the lsn in the same transaction you created the replication slot. something like

begin
# [x] 2. Create replication slots¹
SELECT pg_create_logical_replication_slot('pub_all_table', 'pgoutput');
SELECT * FROM aurora_volume_logical_start_lsn();
commit

I also think you lost the 13 rows because you got the lsn in the cloned PG13, instead you could have captured the lsn in the same transaction you created the replication slot. something like

begin
# [x] 2. Create replication slots¹
SELECT pg_create_logical_replication_slot('pub_all_table', 'pgoutput');
SELECT * FROM aurora_volume_logical_start_lsn();
commit

Since the replication slot is created then the database is cloned from a disk image, the db can still process transactions in between the slot creation and the disk snapshot. You'd need to be able to roll the clone back--not sure if this is possible or not on AWS with PITR