數(shù)據(jù)庫備份還原

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

創(chuàng)建用戶

create user test_user with password '123456';

權(quán)限控制

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

查詢所有表的記錄數(shù)

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