Search code examples
postgresqlreplication

Is there any way to truncate when using logical replication in postgresql 10?

Is it possible to clone a truncate in postgresql version 10.1 or 9.2?

Solution

  • Note: From v11 on, logical replication can replicate TRUNCATE. This answer is only for old, unsupported versions.

    I assume that 9.2 is a typo, because that old and unsupported release does not have logical replication.

    Also, using 10.1 is dangerous. Upgrading to the latest minor release is simple and painless and will fix bugs that can lead to data corruption.

    TRUNCATE is not replicated by logical replication in v10. So you have to do it yourself on both systems:

    • Start a transaction on the primary server.

    • TRUNCATE the table on the primary and wait until replication for the table has caught up.

    • TRUNCATE the table on the standby.

    • COMMIT the transaction on the primary.