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()