PostgreSQL日常运维命令总结分享

本文总结了PostgreSQL的日常运维命令，包括查看版本、数据库列表、字符集、连接数量、当前用户、数据库运行时间、表空间管理、用户和角色管理、数据库管理、表管理、索引管理、数据库启停、登录、函数、扩展、会话管理等。提供了相应的SQL命令示例，帮助用户高效管理PostgreSQL数据库。

1. 查看版本

postgres=# show server_version;
postgres=# select version();
postgres=# SELECT * FROM pg_catalog.pg_settings WHERE name = 'server_version';

2. 查看数据库列表及编码

postgres=# \l

3. 查看字符集

postgres=# \encoding

4. 查看数据库连接数量

postgres=# select datid, datname, pid, usename, state, client_addr, query from pg_stat_activity;
postgres=# SELECT COUNT(*) FROM pg_stat_activity;
postgres=# show max_connections;

5. 查询当前用户

postgres=# select * from current_user;
postgres=# SELECT SESSION_USER;
postgres=# select * from pg_user;

6. 查看数据库运行时间

postgres=# select pg_postmaster_start_time();
postgres=# SELECT age(now(), pg_postmaster_start_time()) AS uptime;

7. 查看当前用户表列表

postgres=# SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
postgres=# SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public';

8. 表空间管理

postgres=# \db
postgres=# SELECT pg_tablespace.spcname, pg_size_pretty(pg_tablespace_size(pg_tablespace.oid)) AS size FROM pg_tablespace;
postgres=# select pg_size_pretty(pg_tablespace_size('pg_global'));
postgres=# create tablespace test owner test location '/pgdb/data/test';
postgres=# drop tablespace test;

9. 查看所有 schema

postgres=# select * from information_schema.schemata;
postgres=# \dn

10. 查看数据库大小

postgres=# select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS total_size from pg_database;

11. 查看归档日志设置

postgres=# show archive_mode;
postgres=# show archive_command;

12. 查看锁信息

postgres=# SELECT * FROM pg_locks;

13. 查看表结构

postgres=# \d students
postgres=# SELECT column_name, data_type, character_maximum_length, is_nullable, column_default FROM information_schema.COLUMNS WHERE TABLE_NAME = 'students';

14. 用户管理

postgres=# create user test1 with password '123456';
postgres=# create user superwith password '123456' superuser;
postgres=# create schema test1 authorization test1;
postgres=# grant all on schema test1 to test1;
postgres=# grant usage on schema test1 to test;
postgres=# alter user superwith password '123123';
postgres=# drop user test;

15. 角色管理

postgres=# \du
postgres=# create role role2;
postgres=# drop role role1;

16. 数据库管理

postgres=# CREATE DATABASE test;
postgres=# DROP DATABASE test;
postgres=# \c test;
postgres=# \ds;

17. 表管理

postgres=# ANALYZE test;
postgres=# alter table test add c3 int;
postgres=# alter table test drop c3;
postgres=# alter table test alter column c3 type varchar(10);
postgres=# \x;

18. 索引管理

postgres=# REINDEX TABLE tablename;
postgres=# \di;

19. 数据库启停管理

[postgres@localhost ~]$ pg_ctl stop;
[postgres@localhost ~]$ pg_ctl start;

20. 数据库登录

[postgres@localhost ~]$ psql;
[postgres@localhost ~]$ psql -U postgres -p 5785 -d postgres -h 192.168.59.138;

21. 查看所有函数

postgres=# \df;
postgres=# SELECT proname, proargtypes, prosrc FROM pg_proc;

22. 查看数据库扩展

postgres=# \dx;

23. 切换工作路径

postgres=# \cd /pgdb;

24. 会话管理

postgres=# \conninfo;
postgres=# select * from pg_stat_activity where state != 'idle';

25. 显示 SQL 执行时间

postgres=# \timing;

26. 将查询结果输出到文件

postgres=# \o test.txt select * from demo1; \o;

27. 执行 SQL 脚本

postgres=# \i test.sql;

28. 序列管理

postgres=# CREATE SEQUENCE snc_seq INCREMENT BY 1 START WITH 1 NO MAXVALUE NO CYCLE CACHE 10;

29. 查看长事务

postgres=# SELECT extract(epoch FROM (clock_timestamp() - xact_start)) AS longtrans FROM pg_stat_activity WHERE state != 'idle';

30. 查找锁阻塞

postgres=# SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid FROM pg_locks blocked_locks JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype WHERE blocking_locks.pid != blocked_locks.pid;