I am trying to set up logical replication between an on-premise Postgresql 12 database to an Azure Flexible Postgres (15) server. I know that logical replication is able to work across postgres versions.

I stop the ‘source’ database, and run pg_dump to backup the database. I restore that database to the Azure Flexible Server without problems. I create a publication on the source database, exporting all tables. I create a subscription on the Azure Flexible Server to subscribe to the publication just mentioned. I made sure that the replication account has read access to the source database.

I had issues originally with the firewall between the two systems, but those have been resolved.

The create subscription command is accepted. Initially, the log on the source server showed an issue with ‘not enough replication slots’, so I increased the replication slots to 75, and that message goes away.

2024-05-01 20:59:56 UTC-6632ad4c.5303-ERROR: could not create replication slot “pg_1212556_sync_845538_7353346566421700674”: ERROR: all replication slots are in usenHINT: Free one or increase max_replication_slots

Now, I’ve tried it 2 ways: #1 – copy only the schema (no data). And #2 – full database backup. In the first case, the replication never starts. I’ve even run the REFRESH PUBLICATION to see if that would kickstart it.

In the 2nd case, the source shows lots of ‘client disconnects’, while the destination shows ‘duplicate record ids’. It looks like they 2 sides cannot agree on where to start the replication.

2024-05-01 20:59:56 UTC-6632ad4c.5305-LOG: logical replication table synchronization worker for subscription “rmove_munge_sub”, table “ms_blackout” has started
2024-05-01 20:59:56 UTC-6632ad4c.5305-ERROR: duplicate key value violates unique constraint “ms_blackout_pkey””,”detail”: “Key (id)=(1) already exists.”,”context”: “COPY ms_blackout, line 1″,”schema_name”: “public”,”table_name”: “ms_blackout”,”constraint_name”: “ms_blackout_pkey
2024-05-01 20:59:56 UTC-661ff4c0.1-LOG: background worker “logical replication worker” (PID 21253) exited with exit code 1

Any suggestions appreciated!

I have tried both using a schema only backup, and a full data backup.