Resolving Replica Identity Issues After Dropping Logical Replication

Recently, one of our clients encountered a perplexing issue with their PostgreSQL database. They had been using logical replication and had configured replica identity instead of using the primary key. When they decided to decommission the logical replication setup, they followed the standard procedure: dropping the subscription and publication.

Everything seemed fine initially, but they soon discovered that issues persisted even after the logical replication was completely removed. The database continued to behave as if replication constraints were still in place.

Understanding Replica Identity

Before diving into the solution, let’s clarify what replica identity is and why it matters.

In logical replication, the replica identity determines which columns are included in the WAL (Write-Ahead Log) for UPDATE and DELETE operations. This is crucial because the subscriber needs to identify which rows to update or delete on the target database.

PostgreSQL offers four replica identity options:

• DEFAULT: Uses the primary key (if one exists).
• USING INDEX: Uses a specified unique index.
• FULL: Includes all columns in the WAL.
• NOTHING: No information is logged (only works for INSERT operations).

In our client’s case, they had configured REPLICA IDENTITY FULL instead of relying on the default primary key behavior. This meant that every UPDATE and DELETE operation was logging all column values to the WAL, significantly increasing the write-ahead log size and impacting performance.

Why it occurs?

Dropping a logical replication setup doesn’t automatically reset the replica identity configuration.

When you execute:

DROP SUBSCRIPTION subscription_name;
DROP PUBLICATION publication_name;

You’re removing the replication infrastructure, but the table-level replica identity setting remains intact. The table continues to maintain the FULL replica identity configuration, which can cause:

• Significant performance overhead: All column values are written to WAL for every UPDATE/DELETE.
• Excessive WAL generation: Much larger WAL files than necessary.
• Unnecessary storage consumption.

The Solution

• Identify the tables using replica identity:

SELECT c.relname AS table_name,
       CASE c.relreplident
            WHEN 'd' THEN 'DEFAULT (Primary Key)'
            WHEN 'i' THEN 'USING INDEX'
            WHEN 'f' THEN 'FULL'
       END AS replica_identity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'          -- only regular tables
  AND c.relreplident <> 'n'    -- exclude tables with NOTHING
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')  -- skip system schemas
ORDER BY n.nspname, c.relname;
   table_name   |   replica_identity
----------------+-----------------------
 collation_test | DEFAULT (Primary Key)
 test           | FULL
 test1          | DEFAULT (Primary Key)
 toast_test     | DEFAULT (Primary Key)
(4 rows)

This SQL query will list all user tables that have a valid replica identity (DEFAULT, USING INDEX, or FULL).

• Reset the replica identity to its default state:

ALTER TABLE <your_table_name> REPLICA IDENTITY DEFAULT;

This command restores the table to use the primary key for replica identity (if one exists), or removes the replica identity constraint entirely if no primary key is present.

• Verify the change : You can use the same first SQL query and see the change from FULL or USING INDEX to DEFAULT

Key Takeaways

1. Replica identity persists: Dropping logical replication doesn’t reset replica identity settings. You must explicitly alter the tables.
2. Check before you leave: When decommissioning logical replication, always audit your tables and reset replica identity configurations as part of your cleanup process.
3. Document your changes: Keep track of which tables had custom replica identity settings during logical replication setup, so you know which ones to reset during teardown.
4. Plan ahead: If possible, try to avoid setting a custom replica identity — instead, use the primary key.If not,when you’re setting up logical replication temporarily, document the cleanup steps including replica identity resets in your runbook.

Best Practices for Logical Replication Cleanup

When removing logical replication from your environment, follow this checklist:

• Drop all subscriptions on subscriber databases.
• Drop all publications on publisher databases.
• Reset replica identity on all tables that were part of replication.
• Remove replication slots if they still exist.
• Update your monitoring and alerting to remove replication-specific checks.

Conclusion

Logical replication is a powerful feature in PostgreSQL, but it requires careful setup and teardown. The replica identity configuration is a table-level setting that outlives the replication infrastructure itself. By understanding this behavior and including replica identity cleanup in your decommissioning process, you can avoid this kind of issues and maintain a clean database environment.

Remember: always verify that your tables are back to their intended state after any major configuration change. A few minutes of verification can save hours of troubleshooting down the line.

Have you encountered similar issues with PostgreSQL logical replication? Share your experiences in the comments below!