数据库备份还原

pg_dump --no-owner --no-acl --encoding=UTF-8 -h localhost -p 1921 -U test_user -Fc database_1 > test.dump

pg_restore --no-owner --no-acl -h localhost -p 1921 -U test_user -d database_1  test.dump

创建用户

create user test_user with password '123456';

权限控制

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user;

查询所有表的记录数

select relname as TABLE_NAME, reltuples as rowCounts 
from pg_class where relkind = 'r' and relnamespace = 
     (select oid from pg_namespace where nspname='public') 
order by rowCounts desc;

查询slot的堆积量

SELECT slot_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 / 1024 AS wal_diff_gb
FROM pg_replication_slots;

修改自增id值

SELECT setval('table_name_id_seq', (SELECT MAX(id) FROM table_name));