0

I have two servers A and B, server A is the publisher for table with primary key sequence. Before i switch to server B, I update the sequence in server B to be same as server A. Then I do the failover and make server B the publisher and server A the subscriber, the insertion works perfectly on server B, but replication fails on server A, although they both have the same value for:

SELECT last_value FROM orders_id_seq

The script to update the sequence on the new publisher:

import psycopg2

def check_and_move_sequence(table, source_conn, target_conn):
    with source_conn.cursor() as source_cursor:
        # Check if a sequence exists for the table
        source_cursor.execute(f"SELECT column_name, column_default FROM information_schema.columns WHERE table_name = '{table}' AND column_default LIKE 'nextval%'")
        sequence_data = source_cursor.fetchone()
        
        if sequence_data:
            sequence_column, sequence_name = sequence_data
            sequence_name = sequence_name.split("'")[1]
            # Get the maximum value of the primary key column in the table
            source_cursor.execute(f"SELECT MAX({sequence_column}) FROM {table}")
            max_val = source_cursor.fetchone()[0]
            
            # Generate SQL script to set sequence value
            set_sequence_sql = f"SELECT setval('{sequence_name}', {max_val});"
            
            # Execute the SQL script on the target connection
            with target_conn.cursor() as target_cursor:
                target_cursor.execute(set_sequence_sql)
                target_conn.commit()
Yassin Shanwany
  • 56
  • 5
  • "Replication fails": what are the error messages in the log? – Laurenz Albe Sep 01 '23 at 07:16
  • 2023-08-31 16:22:53.858 UTC [138107] LOG: logical replication table synchronization worker for subscription "orders_subscription", table "orders" has started 2023-08-31 16:22:53.978 UTC [138107] ERROR: duplicate key value violates unique constraint "orders_pkey" 2023-08-31 16:22:53.978 UTC [138107] DETAIL: Key (id)=(10) already exists. 2023-08-31 16:22:53.978 UTC [138107] CONTEXT: COPY orders, line 1 2023-08-31 16:22:53.982 UTC [108540] LOG: background worker "logical replication worker" (PID 138107) exited with exit code 1 – Yassin Shanwany Sep 01 '23 at 07:48
  • The inserting on the new publisher works fine, but still data are not replicated correctly unless I delete all the records from the table on the new subscriber. Then everything works fine. – Yassin Shanwany Sep 01 '23 at 07:52
  • If the data in the tables differ, that cannot work. This as expected. – Laurenz Albe Sep 01 '23 at 12:09

0 Answers0