Find Replica Identity for a Postgres table

Question

Is there a way to see what kind of replica identity a Postgres table has, whether using pgAdmin or through a query?

score 35 · Accepted Answer · answered Mar 19 '19 at 20:38

35

You can query the pg_class system catalog:

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
WHERE oid = 'mytablename'::regclass;

answered Mar 19 '19 at 20:38

Laurenz Albe

209,280
17
206
263

3

It's also useful to know all tables with no primary key if you use default logical replica identity - https://dba.stackexchange.com/questions/29932/how-can-i-list-all-tables-without-a-primary-key#answer-29933 – Greg0ry Oct 04 '19 at 06:00
2

If you set the replica identity of a table to an index you could find the relevant index with `SELECT indexrelid::regclass FROM pg_index WHERE indrelid='mytablename'::regclass AND indisreplident;` – Pyrocks Aug 30 '22 at 11:38

score 0 · Answer 2 · edited Jul 16 '23 at 01:59

Find replica identify for multiple tables. The query will list the tablename as its replica identity status.

SELECT oid::regclass,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
WHERE oid in ('public.testtable1'::regclass,'public.testtable1'::regclass);

Find Replica Identity for a Postgres table

2 Answers2