查找 Postgres 表的复制身份

Question

有没有一种方法可以查看Postgres表具有什么类型的复制身份，无论是使用pgAdmin还是通过查询？

- svakili

Answer 1

你可以查询 pg_class 系统目录：

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;

- Laurenz Albe

---

如果您使用默认的逻辑复制标识符，了解所有没有主键的表也非常有用-https://dba.stackexchange.com/questions/29932/how-can-i-list-all-tables-without-a-primary-key#answer-29933 - Greg0ry

如果您将表的复制标识设置为索引，则可以使用以下语句查找相关索引：SELECT indexrelid::regclass FROM pg_index WHERE indrelid='mytablename'::regclass AND indisreplident; - Pyrocks

Answer 2

查找多个表的复制标识。查询将列出表名及其复制标识状态。

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);

- Prabhu Kuppusamy