PostgreSQL 使用SQL

发布主题

设置发布为true

这个语句是针对 PostgreSQL 数据库中的逻辑复制功能中的逻辑发布（Logical Publication）进行设置的。

PostgreSQL 中，逻辑复制是一种基于逻辑日志的复制方法，允许将数据更改从一个数据库实例复制到另一个数据库实例，而不是通过传统的物理复制方式复制数据文件。逻辑发布是逻辑复制的一部分，用于定义要复制的数据对象。

update pg_publication set puballtables=true where pubname is not null;

创建发布主题例子：

-- 把所有表进行发布 也可指定

CREATE PUBLICATION "发布名" FOR ALL TABLES;

-- 指定表发布 创建发布名 for table代表含有哪些表被发布

create PUBLICATION "发布名" FOR TABLE

    表名,表名，表名...;

DROP PUBLICATION [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
-- 删除发布的表名
DROP PUBLICATION mypublication;

-- 查询所有发布的主题(postgreSQL中的数据库) 
select * from pg_publication; 
-- 查询 发布名 含哪些表发布 
select * from pg_catalog.pg_publication_tables ppt where pubname ='表名'; 
-- 查询所有发布表 
select * from pg_publication_tables; 

ALTER PUBLICATION — 修改发布的定义

ALTER PUBLICATION name ADD TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ] [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name

命令ALTER PUBLICATION可以更改发布的属性。

更改表的复制标识包含更新和删除的值

-- (如果一张表缺少主键，并且涉及到复制，就需要执行这条语句) 
-- 更改复制标识包含更新和删除之前值 
ALTER TABLE [表名] REPLICA IDENTITY FULL; 
-- 查看复制标识（为f标识说明设置成功） 
select relreplident from pg_class where relname='表名';

slot 插槽

查看solt使用情况 删除solt

-- 查看solt使用情况
SELECT * FROM pg_replication_slots; 
-- 删除solt zd_org_goods_solt为slot名
SELECT pg_drop_replication_slot('zd_org_goods_solt'); 

用户连接

查看用户连接数

-- 查询用户当前连接数 
select usename, count(*) from pg_stat_activity group by usename order by count(*) desc; 
-- 设置用户最大连接数 
alter role odps_etl connection limit 200;

分区

查询所属表的主表的分区表有哪些 并查看分区表的模式

-- 查询所属表的主表的分区表有哪些 并查看分区表的模式
SELECT parent.relname AS
                         partitioned_table,
       child.relname  AS partition,
       ns.nspname     AS schema
FROM pg_inherits
         JOIN
     pg_class AS parent ON parent.oid = pg_inherits.inhparent
         JOIN pg_class AS child ON child.oid
    = pg_inherits.inhrelid
         JOIN pg_namespace AS ns ON ns.oid = parent.relnamespace
WHERE parent.relkind =
      'p'
  -- ns.nspname 为你的模式名
  AND ns.nspname = 'your schema name'
  -- parent.relname 你的表名
  AND parent.relname = 'your table name'
ORDER BY parent.relname, child.relname;

判断分区。 查询分区表信息的 SQL

-- 查询表 是什么表类型，是否有子表，是否为一个分区表
SELECT relname, relkind, relhassubclass, relispartition
-- pg_class 表字段解释 如下表格
FROM pg_class
-- relname 你要查询的表名
WHERE relname = 'inp_order';

查询分区主表所属类型

SELECT c.relname AS table_name, p.partstrat AS partition_strategy
FROM pg_partitioned_table p
         JOIN pg_class c ON p.partrelid = c.oid
-- c.relname 为主表名称
WHERE c.relname = 'tablename'

查询分区键

SELECT pg_attribute.attname AS partition_key
FROM pg_attribute
         JOIN pg_partitioned_table ON pg_attribute.attrelid = pg_partitioned_table.partrelid
         JOIN pg_class ON pg_partitioned_table.partrelid = pg_class.oid
         JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
-- pg_class.relname 为表名         
WHERE pg_class.relname = 'tableName'
-- pg_namespace.nspname 为模式名  
  AND pg_namespace.nspname = 'schemaName'  
  AND pg_attribute.attnum = ANY (pg_partitioned_table.partattrs) 

删除 创建 分区

ALTER TABLE [tablename] DROP PARTITION '分区表名';

pg_class 表详细信息

名称	类型	引用	描述
oid	oid		行标识符
relname	name		表、索引、视图等的名字
relnamespace	oid	pg_namespace.oid	包含该关系的名字空间的OID
reltype	oid	pg_type.oid	可能存在的表行类型所对应数据类型的OID（对索引为0，索引没有pg_type项）
reloftype	oid	pg_type.oid	对于有类型的表，为底层组合类型的OID，对于其他所有关系为0
relowner	oid	pg_authid.oid	关系的拥有者
relam	oid	pg_am.oid	如果这是一个表或者索引，表示索引使用的访问方法（堆、B树、哈希等）
relfilenode	oid		该关系的磁盘文件的名字，0表示这是一个“映射”关系，其磁盘文件名取决于低层状态
reltablespace	oid	pg_tablespace.oid	该关系所存储的表空间。如果为0，使用数据库的默认表空间。（如果关系无磁盘文件时无意义）
relpages	int4		该表磁盘表示的尺寸，以页面计（页面尺寸为BLCKSZ）。这只是一个由规划器使用的估计值。它被VACUUM、ANALYZE以及一些DDL命令（如CREATE INDEX）所更新
reltuples	float4		表中的存活行数。这只是一个由规划器使用的估计值。它被VACUUM、ANALYZE以及一些DDL命令（如CREATE INDEX）所更新。
relallvisible	int4		在表的可见性映射表中被标记为全可见的页数。这只是一个由规划器使用的估计值。它被VACUUM、ANALYZE以及一些DDL命令（如CREATE INDEX）所更新
reltoastrelid	oid	pg_class.oid	与该表相关联的TOAST表的OID，如果没有则为0。TOAST表将大属性“线外”存储在一个二级表中
relhasindex	bool		如果这是一个表并且其上建有（或最近建有）索引则为真
relisshared	bool		如果该表在集簇中的所有数据库间共享则为真。只有某些系统目录（如pg_database）是共享的
relpersistence	char		p = 永久表，u = 无日志表， t = 临时表
relkind	char		r = 普通表， i = 索引， S = 序列， t = TOAST表， v = 视图， m = 物化视图， c = 组合类型， f = 外部表， p = 分区表， I = 分区索引
relnatts	int2		关系中用户列的数目（系统列不计算在内）。在pg_attribute中必须有这么多对应的项。另请参阅pg_attribute.attnum。
relchecks	int2		表上CHECK约束的数目，参见pg_constraint目录
relhasrules	bool		如果表有（或曾有）规则则为真，参见 pg_rewrite目录
relhastriggers	bool		如果表有（或曾有）触发器则为真目录
relhassubclass	bool		如果表或者索引有（或曾有）任何继承子女则为真
relrowsecurity	bool		如果表上启用了行级安全性则为真，参见 pg_policy目录
relforcerowsecurity	bool		如果行级安全性（启用时）也适用于表拥有者则为真，参见 pg_policy目录
relispopulated	bool		如果表已被填充则为真（对于所有关系该列都为真，但对于某些物化视图却不是）
relreplident	char		用来为行形成“replica identity”的列： d = 默认 (主键，如果存在), n = 无, f = 所有列 i = 索引的indisreplident被设置或者为默认
relispartition	bool		如果表或索引是一个分区，则为真
relrewrite	oid	pg_class.oid	对于在要求表重写的DDL操作期间被写入的新关系，这个域包含原始关系的OID，否则为0。那种状态仅在内部可见，对于一个用户可见的关系这个域应该从不包含不是0的值
relfrozenxid	xid		在此之前的所有事务ID在表中已经被替换为一个永久的（“冻结的”) 事务ID。这用于跟踪表是否需要被清理，以便阻止事务ID回卷或者允许pg_xact被收缩。如果该关系不是一个表则为0（InvalidTransactionId）
relminmxid	xid		在此之前的多事务ID在表中已经被替换为一个事务ID。这被用于跟踪表是否需要被清理，以阻止 多事务ID回卷或者允许pg_multixact被收缩。如果关系不是一个表则 为0（InvalidMultiXactId）
relacl	aclitem[]		访问权限
reloptions	text[]		访问方法相关的选项，以“keyword=value”字符串形式
relpartbound	pg_node_tree		如果表示一个分区（见relispartition），分区边界的内部表达

索引

查询索引

-- schemaname 指定模式名，tablename 指定表名
SELECT * FROM pg_indexes WHERE schemaname = '%s' and tablename= '%s'

pg_indexes 表详细信息

名字	类型	引用	描述
schemaname	name	pg_namespace.nspname	包含表和索引的模式的名字
tablename	name	pg_class.relname	此索引所服务的表的名字
indexname	name	pg_class.relname	索引的名字
tablespace	name	pg_tablespace.spcname	包含索引的表空间名字(如果是数据库缺省，则为 NULL)
indexdef	text		索引定义(一个重建的CREATE INDEX命令)

索引CRD（增加，查看，删除）

-- 查看索引
SELECT * FROM pg_indexes WHERE tablename='表名';
-- 或者
SELECT * FROM pg_statio_all_indexes WHERE relname='表名';
-- 创建索引
CREATE INDEX IF NOT EXISTS [表名]  ON  [索引名称]('字段'...);
-- 删除索引
DROP INDEX [索引名称];