0

In PostgreSQL will the replica identity of a table in a read-only replica database be the same as the replica identity in the primary? I.e., if I run the following query on primary and replica databases will I always get the same result (assuming replication is caught up)?

SELECT CASE relreplident
          WHEN 'd' THEN 'default'
          WHEN 'n' THEN 'nothing'
          WHEN 'f' THEN 'full'
          WHEN 'i' THEN 'index'
       END AS replica_identity
FROM pg_class c
WHERE oid = 'mytable'::regclass;
2
  • Are you talking about streaming replication or logical replication? Apr 21, 2021 at 15:59
  • @LaurenzAlbe this is for streaming replication.
    – mjumbewu
    Apr 26, 2021 at 15:15

1 Answer 1

0

The query will return the same on both servers, because streaming replication keeps them physically identical.

"Replica identity" is a concept from logical replication, which is something quite different.

3
  • Thanks! In our case, we have the primary and replica set up on AWS RDS, and I'm assuming we're using streaming replication between them (based on the docs docs.aws.amazon.com/AmazonRDS/latest/UserGuide/…). We also have a CDC service reading a replication slot off of the primary, which is where my interest in the replica identity comes in, as I only have direct query access on the replica and want to be sure I'm getting the right values.
    – mjumbewu
    Apr 28, 2021 at 2:32
  • I see. Does my answer cover everything? Apr 28, 2021 at 2:35
  • I think so; thanks!
    – mjumbewu
    Apr 28, 2021 at 4:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.