CODEWITHSUNDEEP
postgresqldatabase-replicationpostgresql-11
P_Ar
P_Ar

Reputation: 439

PostgreSQL Streaming Replication - Table Dropped on Primary server

In a Primary-Standby streaming replication on PostgreSQL 11, if a table is dropped from the Primary server, however the same table is being accessed by a user/app on the stand by (for Read Only purposes) what will happen?

Will the standby server wait for the WAL to be received and executed and throw an error to the user that the table does not exist anymore or will it output the desired result set?

Please let me know

Thanks

Upvotes: 1

Views: 608

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246013

This would cause a lock replication conflict.

AccessExclusive locks (such locks are taken by DROP TABLE) are replicated to the standby server. When the standby wants to replay the lock, it will detect a conflict with a running query that uses the table. It will then delay replication for at most max_standby_streaming_delay, and if the reading transaction is not done by then, it will be canceled, so that replication can proceed.

See my blog for details.

Upvotes: 1

Related Questions