0

I've set up a couple of (logical) replicated tables between two postgres instances. All SELECT, INSERT, UPDATE, DELETE Transactions are replicated as expected.

My use case is to restore/append a couple of thousands of rows out of a logical dump file (custom format, COPY or INSERT?)

TRUNCATE is not replicated...in my understanding (which also relies on the knowledge how other commercial DB vendors do it) because it bypasses the transaction log which is needed by the replication mechanism (this would be another question, if logical replication relies on WAL segments?)

As I cannot find any documentation about it, I would like to know if:

  • COPY bypasses the transaction log (WAL)?
  • COPY has any restrictions on logical replication so that in fact the use of INSERTs is mandatory in replicated environments?

P.S. Sorry if I am mixing up things here, I really would like to clear these topics up to better understand how they are working together

Jürgen Zornig
  • 1,174
  • 20
  • 48
  • As far as I know, COPY does **not** bypass WAL and all inserts will be replicated via logical replication. –  Feb 03 '21 at 09:27

1 Answers1

1

There are a few things to set right:

  • Logical replication supports TRUNCATE:

    Replication of TRUNCATE commands is supported

    This was introduced in v11

  • neither COPY nor TRUNCATE bypass WAL

  • COPY data have always been replicated with logical replication, like all other data modifying statements

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Sorry, forgot to add, that this is (at least parts of it) still v10. Thanks for the clarification, though, thats all I wanted to know. – Jürgen Zornig Feb 03 '21 at 09:37