I've configured two servers with redundancy setup using pcsd configuration.
Both machines consists of Postgres 10 and logical replication. Used below steps for logical replication setup.
- Took PG Dump on Server1 using pg_dump command.
- Restored it on Server2 with postgres 10 using pg_restore.
- Made changes in pg_hba.conf and postgres.conf files.
- Used below commands for setup of logical replication.
CREATE PUBLICATION my_publication FOR ALL TABLES;
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=Server1 port=5432 password=postgres user=postgres dbname=database1'
PUBLICATION my_publication WITH (copy_data = false);
After above steps I could see services are running fine on both the systems(Redundant systems). But from the logs I could see below error messages.
...
2020-01-08 15:14:08.551 EET >LOG: logical replication apply worker for subscription "my_subscription" has started
2020-01-08 15:14:08.559 EET >ERROR: duplicate key value violates unique constraint "pk_xyz_instance"
2020-01-08 15:14:08.559 EET >DETAIL: Key (xyz_instance_id)=(103) already exists.
2020-01-08 15:14:08.560 EET >LOG: worker process: logical replication worker for subscription 23176 (PID 7411) exited with exit code 1
....
As I need earlier data of Server1, I took dump and restored it on other and using copy_data as false to avoid duplication.
After every switchover of services from Server1 to Server2 or vice versa, these unique constraint violation errors are seen on Server2 (where services are inactive state)
Is there anything I'm missing here in setup of replication using PostgreSQL 10.11?
Is copy_data flag not working as I expected?