PostgreSQL最常用命令参考
2016-09-05 11:56:48 
阿炯
默认情况下，安装完成后，PostgreSQL服务器会自动在本机的5432端口开启。添加新用户和新数据库
初次安装后，默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是，同时还生成了一个名为postgres的Linux系统用户。下面，我们使用postgres用户，来生成其他用户和新数据库。好几种方法可以达到这个目的，这里介绍两种。
第一种方法，使用PostgreSQL控制台。
首先，新建一个Linux新用户，可以取你想要的名字，这里为freeoadbuser。
sudo adduser freeoadbuser
然后，切换到postgres用户。
sudo su - postgres
下一步，使用psql命令登录PostgreSQL控制台。
psql
这时相当于系统用户postgres以同名数据库用户的身份，登录数据库，这是不用输入密码的。如果一切正常，系统提示符会变为"postgres=#"，表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。
第一件事是使用\password命令，为postgres用户设置一个密码。
\password postgres
第二件事是创建数据库用户freeoadbuser(刚才创建的是Linux系统用户)，并设置密码。
CREATE USER freeoadbuser WITH PASSWORD 'password';
第三件事是创建用户数据库，这里为freeoadb，并指定所有者为freeoadbuser。
CREATE DATABASE freeoadb OWNER freeoadbuser;
第四件事是将freeoadb数据库的所有权限都赋予freeoadbuser，否则freeoadbuser只能登录控制台，没有任何数据库操作权限。
GRANT ALL PRIVILEGES ON DATABASE freeoadb to freeoadbuser;
最后，使用\q命令退出控制台(也可以直接按ctrl+D)。
\q
第二种方法，使用shell命令行。
添加新用户和新数据库，除了在PostgreSQL控制台内，还可以在shell命令行下完成。这是因为PostgreSQL提供了命令行程序createuser和createdb。还是以新建用户freeoadbuser和数据库freeoadb为例。
首先，创建数据库用户freeoadbuser，并指定其为超级用户。
sudo -u postgres createuser --superuser freeoadbuser
然后，登录数据库控制台，设置freeoadbuser用户的密码，完成后退出控制台。
sudo -u postgres psql
\password freeoadbuser
\q
接着，在shell命令行下，创建数据库freeoadb，并指定所有者为freeoadbuser。
sudo -u postgres createdb -O freeoadbuser freeoadb
登录数据库
添加新用户和新数据库以后，就要以新用户的名义登录数据库，这时使用的是psql命令。
psql -U freeoadbuser -d freeoadb -h 127.0.0.1 -p 5432
上面命令的参数含义如下：-U指定用户，-d指定数据库，-h指定服务器，-p指定端口。输入上面命令以后，系统会提示输入freeoadbuser用户的密码。输入正确，就可以登录控制台了。
psql命令存在简写形式。如果当前Linux系统用户，同时也是PostgreSQL用户，则可以省略用户名(-U参数的部分)。举例来说，我的Linux系统用户名为ruanyf，且PostgreSQL数据库存在同名用户，则我以ruanyf身份登录Linux系统后，可以直接使用下面的命令登录数据库，且不需要密码。
psql freeoadb
此时，如果PostgreSQL内部还存在与当前系统用户同名的数据库，则连数据库名都可以省略。比如，假定存在一个叫做ruanyf的数据库，则直接键入psql就可以登录该数据库。另外，如果要恢复外部数据，可以使用下面的命令。
psql freeoadb < freeoadb.sql
控制台命令
除了前面已经用到的\password命令(设置密码)和\q命令(退出)以外，控制台还提供一系列其他命令。
\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。
数据库操作
基本的数据库操作，就是使用一般的SQL语言。
# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
# 选择记录
SELECT * FROM user_tbl;
# 更新数据
UPDATE user_tbl set name = '李四' WHERE name = '张三';
# 删除记录
DELETE FROM user_tbl WHERE name = '李四' ;
# 添加栏位
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新结构
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名栏位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 删除栏位
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 删除表格
DROP TABLE IF EXISTS backup_tbl;
第三种方法之最常见操作指令：
连接数据库, 默认的用户和数据库是postgres
psql -U user -d dbname
切换数据库,相当于mysql的use dbname
\c dbname
列举数据库，相当于mysql的show databases
\l
列举表，相当于mysql的show tables
\dt
查看表结构，相当于desc tblname,show columns from tbname
\d tblname
创建数据库：
create database [数据库名];
创建数据库指定用户：
create database [数据库名] owner [用户名];
删除数据库：
drop database [数据库名];
*重命名一个表：
alter table [表名A] rename to [表名B];
*删除一个表：
drop table [表名];
*在已有的表里添加字段：
alter table [表名] add column [字段名] [类型];
*删除表中的字段：
alter table [表名] drop column [字段名];
*重命名一个字段：
alter table [表名] rename column [字段名A] to [字段名B];
*给一个字段设置缺省值：
alter table [表名] alter column [字段名] set default [新的默认值];
*去除缺省值：
alter table [表名] alter column [字段名] drop default;
在表中插入数据：
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);
修改表中的某行某列的数据：
update [表名] set [目标字段名]=[目标值] where [该行特征];
删除表中某行数据：
delete from [表名] where [该行特征];
delete from [表名];--删空整个表
创建表：
create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;);
\copyright 显示 PostgreSQL 的使用和发行条款
\encoding [字元编码名称]显示或设定用户端字元编码
\h [名称] SQL 命令语法上的说明，用 * 显示全部命令
\prompt [文本] 名称提示用户设定内部变量
\password [USERNAME] securely change the password for a user
\q 退出 psql
--------------------------------------------------------------
postgres一些查询命令操作
postgres下一些查询有时候是不同于其他数据库的。
(1)显示SQL语句执行时间
\timing on 退出数据库失效
(2)显示postgres中所有的表总数(包含数据库自带的)
select count(*) from pg_tables;
安装好数据库系统自带为58个。
(3)显示用户生成的数据库列表
\d|\d+
(4)显示某个表列属性
\d tablename
(5)删除表中所有数据
delete from tablename --写入日志
Truncate Table tablename --不写入日志
TRUNCATE TABLE
删除表中的所有行，而不记录单个行删除操作。
语法
TRUNCATE TABLE name
参数
name 是要截断的表的名称或要删除其全部行的表的名称。
注释 TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
示例
下例删除 authors 表中的所有数据。
TRUNCATE TABLE authors;
(6)显示当前用户
\c
(7)显示当前数据库所有用户
\du
via sql:
SELECT u.usename AS "User name",
u.usesysid AS "User ID", CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text) ELSE CAST('' AS pg_catalog.text) END AS "Attributes" FROM pg_catalog.pg_user u ORDER BY 1;
(8)创建用户
create user freeoa with password 'xxxxxx';
(9)更改用户密码
alter user freeoa with password 'yyyy';
(10)更改用户连接
\c database freeoa
(11)更改表名
更改表名称
alter table smallint rename to integer;
(12)更改列名
更改表列名
alter table integer rename column id to id1;
(13)显示日期
show datestyle;
(14)设置日期格式
set datestyle=MDY;
(15)打印详细出错信息
postgres=# \set VERBOSITY verbose
postgres=# create table (id );
ERROR: syntax error at or near "(" at character 14
STATEMENT: create table (id );
ERROR: 42601: syntax error at or near "("
LINE 1: create table (id );
^
LOCATION: scanner_yyerror, scan.l:1044
(16)更改列属性
postgres=# alter table a1 alter column id2 set DATA TYPE int;
(17)查看帮助信息
\h alter table
(18)列显示
\x
(19)创建函数
CREATE OR REPLACE FUNCTION f_value_single()
returns void AS $_$
DECLARE
BEGIN
for i in 1..2000000 loop
insert into abc values(1,'a','a');
end loop;
return;
END;
$_$ language plpgsql;
将表名作为入参
create or replace function count(varchar)
returns bigint
as
selectcount(∗)fromquoteident($1);
language 'sql';
(20)关闭数据库3种模式
SIGTERM
不再允许新的连接，但是允许所有活跃的会话正常完成他们的工作，只有在所有会话都结束任务后才关闭。这是智能关闭。
SIGINT
不再允许新的连接，向所有活跃服务器发送 SIGTERM(让它们立刻退出)，然后等待所有子进程退出并关闭数据库。这是快速关闭。
SIGQUIT
令 postgres 向所有子进程发送 SIGQUIT 并且立即退出(所有子进程也会立即退出)，而不会妥善地关闭数据库系统。这是立即关闭。这样做会导致下次启动时的恢复(通过重放 WAL 日志)。我们推荐只在紧急的时候使用这个方法。
SIGKILL
此选项尽量不要使用，这样会阻止服务器清理共享内存和信号灯资源，那样的话你只能在启动服务器之前自己手工做这件事。另外，SIGKILL 直接把 postgres 杀掉，而不会等它把信号中继给它的子进程，因此我们还需要手工杀掉每个独立子进程。
--------------------------------------------------------------
Postgresql常用命令
1.createdb 数据库名称
产生数据库
2.dropdb 数据库名称
删除数据库
3.CREATE USER 用户名称
创建用户
4.drop User 用户名称
删除用户
5.SELECT usename FROM pg_user;
查看系统用户信息:\du
7.SELECT version();
查看版本信息
8.psql 数据库名
打开psql交互工具
9.mydb=> \i basics.sql
\i 命令从指定的文件中读取命令。
10.COPY weather FROM '/home/freeoa/weather.txt';
批量将文本文件中内容导入到wether表
11.SHOW search_path;
显示搜索路径
12.创建用户
CREATE USER 用户名 WITH PASSWORD '密码'
13.创建模式
CREATE SCHEMA myschema;
14.删除模式
DROP SCHEMA myschema;
15.查看搜索模式
SHOW search_path;
16.设置搜索模式
SET search_path TO myschema,public;
17.创建表空间
create tablespace 表空间名称 location '文件路径';
18.显示默认表空间
show default_tablespace;
19.设置默认表空间
set default_tablespace=表空间名称;
20.指定用户登录
psql MTPS -u
21.显示当前系统时间
select now();
22.配置plpgsql语言
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
23.删除规则
DROP RULE name ON relation [ CASCADE | RESTRICT ]
输入
name
要删除的现存的规则．
relation
该规则应用的关系名字(可以有大纲修饰)．
CASCADE
自动删除依赖于此规则的对象。
RESTRICT
如果有任何依赖对象，则拒绝删除此规则。这个是缺省。
24.日期格式函数
select 'P'||to_char(current_date,'YYYYMMDD')||'01'
25.产生组
Create Group 组名称
26.修改用户归属组
Alter Group 组名称 add user 用户名称
26.为组赋值权限
Grant 操作 On 表名称 to group 组名称：
27.创建角色
Create Role 角色名称
28.删除角色
Drop Role 角色名称
29.获得当前postgresql版本
SELECT version();
30.在linux中执行计划任务
通过crontab执行
su root -c "psql -p 5433 -U developer MTPS -c'select test()'"
developer用户的密码存储于环境变量PGPASSWORD中。
31.查询表是否存在
select * from pg_statio_user_tables where relname='你的表名';
32.为用户复制SCHEMA权限
grant all on SCHEMA 作用域名称 to 用户名称
33.整个数据库导出
pg_dumpall -D -p 端口号 -h 服务器IP -U postgres(用户名) > /home/xiaop/all.bak
34.数据库备份恢复
psql -h 192.168.0.48 -p 5433 -U postgres
35.当前日期函数
current_date
36.返回第十条开始的5条记录
select * from tabname limit 5 offset 10;
37.为用户赋模式权限
Grant on schema developer to UDataHouse
38.将字符转换为日期时间
select to_timestamp('2010-10-21 12:31:22', 'YYYY-MM-DD hh24:mi:ss')
39.数据库备份
pg_dumpall -h 192.168.0.4 -p 5433 -U postgres >/DataBack/Postgresql2010012201.dmp
如8.1以后多次输入密码
40.\dn
查看schema
41.删除schema
drop schema _clustertest cascade;
42.导出表
./pg_dump -p 端口号 -U 用户 -t 表名称 -f 备份文件位置 数据库;
43.字符串操作函数
select distinct(split_part(ip,'.',1)||'.'||split_part(ip,'.',2)) from t_t_userip order by (split_part(ip,'.',1)||'.'||split_part(ip,'.',2));
44.删除表主键
alter table 表名 drop CONSTRAINT 主键名称;
45.创建表空间
create tablespace 空间名称 location '路径'
46.查看表结构
select * from information_schema.columns
./postgres -D /usr/local/src/data
or
./pg_ctl -D /usr/local/src/data -l logfile start
47.查看数据库大小
SELECT pg_size_pretty(pg_database_size('MTPS')) As fulldbsize;
48.查看数据库表大小
SELECT pg_size_pretty(pg_total_relation_size('developer.t_L_collectfile')) As fulltblsize,
pg_size_pretty(pg_relation_size('developer.t_L_collectfile')) As justthetblsize
49.设置执行超过指定秒数的sql语句输出到日志
log_min_duration_statement = 3
50.超过一定秒数sql自动执行执行计划
shared_preload_libraries = 'auto_explain'
custom_variable_classes = 'auto_explain'
auto_explain.log_min_duration = 4s
51.数据库备份
select pg_start_backup('backup baseline');
select pg_stop_backup();
recovery.conf
restore_command='cp /opt/buxlog/%f %p'
52.重建索引
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]
INDEX
重新建立声明了的索引。
TABLE
重新建立声明的表的所有索引。如果表有个从属的"TOAST"表，那么这个表也会重新索引。
DATABASE
重建当前数据库里的所有索引。 除非在独立运行模式下，会忽略在共享系统表上的索引（见下文）。
SYSTEM
在当前数据库上重建所有系统表上的索引。不会处理在用户表上的索引。 另外，除了是在单主机模式下，共享的系统表也会被忽略（见下文）。
name
需要重建索引的索引，表或者数据库的名称。 表和索引名可以有模式修饰。 目前，REINDEX DATABASE 和 REINDEX SYSTEM 只能重建当前数据库的索引， 因此其参数必须匹配当前数据库的名字。
FORCE
这是一个废弃的选项，如果声明，会被忽略。
54.数据字典查看表结构
SELECT column_name, data_type from information_schema.columns where table_name = 'blog_sina_content_train';
52.查看被锁定表
SELECT pg_class.relname AS table, pg_database.datname AS database, pid, mode, granted
FROM pg_locks, pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid
AND pg_locks.database = pg_database.oid;
53.查看客户端连接情况
SELECT client_addr ,client_port,waiting,query_start,current_query FROM pg_stat_activity;
54.常看数据库.conf配置
show all
55.修改数据库postgresql.conf参数
修改postgresql.conf内容
pg_ctl reload
56.回滚日志强制恢复
pg_resetxlog -f 数据库文件路径
idvalue | remark
----------+--------
33953557 | inser
57.当前日期属于一年中第几周
select EXTRACT(week from TIMESTAMP '2016-08-22');
58.显示最近执行命令
\s
59.导出数据库角色
/data/pgsql/bin/pg_dumpall -p 5432 -U postgres -r >/tmp/postgres_8.3_role.bak
60.修改sequence所有者
grant all on sequence名称 to 所有者;
61.修改sequence初始值
Alter SEQUENCE sequencename START value;
62.查看sequence当前值
SELECT currval('sequencename');
63.查看sequence下一值
SELECT nextval('sequencename');
64.设置sequence当前值
alter SEQUENCE sequencename restart with startvalue;
SELECT nextval('sequencename');
65.查询表结构
SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull
FROM pg_class c,pg_attribute a,pg_type t
WHERE c.relname=表名称and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid
66.将查询结果直接输出到文件
在psql中
\o 文件路径
select datname,rolname from pg_database a left outer join pg_roles b on a.datdba=b.oid;
\o
67.查询数据库所有则
select datname,rolname from pg_database a left outer join pg_roles b on a.datdba=b.oid;
68.结束正在执行的事务
SELECT * from pg_stat_activity;
select pg_cancel_backend('procpid');
69.结束session
SELECT * from pg_stat_activity;
select pg_terminate_backend('procpid');
70.postgresql取消转义字符功能
将postgresql.conf文件中的standard_conforming_strings设置为on
71.查询正在执行SQL
SELECT procpid, start, now() - start AS lap, current_query
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S
WHERE current_query <> '' ORDER BY lap DESC;
最常用的操作指令参考一名话总结
I. SQL 命令
ABORT — 退出当前事务
ALTER AGGREGATE — 修改一个聚集函数的定义
ALTER CONVERSION — 修改一个编码转换的定义
ALTER DATABASE — 修改一个数据库
ALTER DOMAIN — 改变一个域的定义
ALTER FUNCTION — 修改一个函数的定义
ALTER GROUP — 修改一个用户组
ALTER INDEX — 改变一个索引的定义
ALTER LANGUAGE — 修改一个过程语言的定义
ALTER OPERATOR — 改变一个操作符的定义
ALTER OPERATOR CLASS — 修改一个操作符表的定义
ALTER ROLE — 修改一个数据库角色
ALTER SCHEMA — 修改一个模式的定义
ALTER SEQUENCE — 更改一个序列生成器的定义
ALTER TABLE — 修改表的定义
ALTER TABLESPACE — 改变一个表空间的定义
ALTER TRIGGER — 改变一个触发器的定义
ALTER TYPE — 改变一个类型的定义
ALTER USER — 改变数据库用户帐号
ANALYZE — 收集与数据库有关的统计
BEGIN — 开始一个事务块
CHECKPOINT — 强制一个事务日志检查点
CLOSE — 关闭一个游标
CLUSTER — 根据一个索引对某个表集簇
COMMENT — 定义或者改变一个对象的评注
COMMIT — 提交当前事务
COMMIT PREPARED — 提交一个早先为两阶段提交准备好的事务
COPY — 在表和文件之间拷贝数据
CREATE AGGREGATE — 定义一个新的聚集函数
CREATE CAST — 定义一个用户定义的转换
CREATE CONSTRAINT TRIGGER — 定义一个新的约束触发器
CREATE CONVERSION — 定义一个新的的编码转换
CREATE DATABASE — 创建新数据库
CREATE DOMAIN — 定义一个新域
CREATE FUNCTION — 定义一个新函数
CREATE GROUP — 定义一个新的用户组
CREATE INDEX — 定义一个新索引
CREATE LANGUAGE — 定义一种新的过程语言
CREATE OPERATOR — 定义一个新的操作符
CREATE OPERATOR CLASS — 定义一个新的操作符表
CREATE ROLE — define a new database role
CREATE RULE — 定义一个新的重写规则
CREATE SCHEMA — 定义一个新的模式
CREATE SEQUENCE — 创建一个新的序列发生器
CREATE TABLE — 定义一个新表
CREATE TABLE AS — 从一条查询的结果中定义一个新表
CREATE TABLESPACE — 定义一个新的表空间
CREATE TRIGGER — 定义一个新的触发器
CREATE TYPE — 定义一个新的数据类型
CREATE USER — 创建一个新的数据库用户帐户
CREATE VIEW — 定义一个视图
DEALLOCATE — 删除一个准备好的查询
DECLARE — 定义一个游标
DELETE — 删除一个表中的行
DROP AGGREGATE — 删除一个用户定义的聚集函数
DROP CAST — 删除一个用户定义的类型转换
DROP CONVERSION — 删除一个用户定义的编码转换
DROP DATABASE — 删除一个数据库
DROP DOMAIN — 删除一个用户定义的域
DROP FUNCTION — 删除一个函数
DROP GROUP — 删除一个用户组
DROP INDEX — 删除一个索引
DROP LANGUAGE — 删除一个过程语言
DROP OPERATOR — 删除一个操作符
DROP OPERATOR CLASS — 删除一个操作符表
DROP ROLE — 删除一个数据库角色
DROP RULE — 删除一个重写规则
DROP SCHEMA — 删除一个模式
DROP SEQUENCE — 删除一个序列
DROP TABLE — 删除一个表
DROP TABLESPACE — 删除一个表空间
DROP TRIGGER — 删除一个触发器定义
DROP TYPE — 删除一个用户定义数据类型
DROP USER — 删除一个数据库用户帐号
DROP VIEW — 删除一个视图
END — 提交当前的事务
EXECUTE — 执行一个准备好的查询
EXPLAIN — 显示语句执行规划
FETCH — 用游标从查询中抓取行
GRANT — 定义访问权限
INSERT — 在表中创建新行
LISTEN — 监听一个通知
LOAD — 装载或重载一个共享库文件
LOCK — 明确地锁定一个表
MOVE — 重定位一个游标
NOTIFY — 生成一个通知
PREPARE — 创建一个准备好的查询
PREPARE TRANSACTION — 为当前事务做两阶段提交的准备
REINDEX — 重建索引
RELEASE SAVEPOINT — 删除一个前面定义的保存点
RESET — 把一个运行时参数值恢复为缺省值
REVOKE — 删除访问权限
ROLLBACK — 退出当前事务
ROLLBACK PREPARED — 取消一个早先为两阶段提交准备好的事务
ROLLBACK TO — 回滚到一个保存点
SAVEPOINT — 在当前事务里定义一个新的保存点
SELECT — 从表或视图中取出若干行
SELECT INTO — 从一个查询的结果中定义一个新表
SET — 改变运行时参数
SET CONSTRAINTS — 设置当前事务的约束检查模式
SET ROLE — set the current user identifier of the current session
SET SESSION AUTHORIZATION — 为当前会话设置会话用户标识符和当前用户标识符
SET TRANSACTION — 设置当前事务的特性
SHOW — 显示运行时参数的数值
START TRANSACTION — 开始一个事务块
TRUNCATE — 清空一个或者一堆表
UNLISTEN — 停止监听通知信息
UPDATE — 更新一个表中的行
VACUUM — 垃圾收集以及可选地分析一个数据库
II. 客户端应用
clusterdb — 对一个PostgreSQL数据库进行建簇
createdb — 创建一个新的 PostgreSQL 数据库
createlang — 定义一种新的 PostgreSQL 过程语言
createuser — 定义一个新的 PostgreSQL 用户帐户
dropdb — 删除一个现有 PostgreSQL 数据库
droplang — 删除一种 PostgreSQL 过程语言
dropuser — 删除一个 PostgreSQL 用户帐户
ecpg — 嵌入的 SQL C 预处理器
pg_config — 检索已安装版本的 PostgreSQL 的信息
pg_dump — 将一个PostgreSQL数据库抽出到一个脚本文件或者其它归档文件中
pg_dumpall — 抽出一个 PostgreSQL 数据库集群到脚本文件中
pg_restore — 从一个由 pg_dump 创建的备份文件中恢复 PostgreSQL 数据库。
psql — PostgreSQL 交互终端
vacuumdb — 收集垃圾并且分析一个PostgreSQL 数据库
III. PostgreSQL 服务器应用
initdb — 创建一个新的 PostgreSQL数据库集群
ipcclean — 从失效的PostgreSQL服务器中删除共享内存和信号灯
pg_controldata — 显示一个 PostgreSQL 集群的控制信息
pg_ctl — 启动，停止和重起 PostgreSQL
pg_resetxlog — 重置一个 PostgreSQL 数据库集群的预写日志以及其它控制内容
postgres — 以单用户模式运行一个 PostgreSQL服务器
postmaster — PostgreSQL多用户数据库服务器
--------------------------------------------------------------
DBA 应该掌握的百条命令
本节转自DBA学习之路(2026年7月)
前言
PostgreSQL 的运维逻辑与 MySQL、Oracle 有明显差异。很多刚接触 PostgreSQL 的 DBA，看到数据库连接数升高、表空间增长、SQL 卡顿或者主从延迟时，仍然沿用其他数据库的处理习惯：先看 CPU、再看磁盘、最后考虑重启。但 PostgreSQL 的很多生产问题，实际上都与长事务、MVCC 垃圾版本、锁等待、统计信息失真、WAL 堆积、复制槽未消费以及 Autovacuum 工作不充分有关。下面整理 100 条 PostgreSQL DBA 日常使用频率较高的命令，覆盖实例信息、连接会话、事务锁、SQL 性能、表与索引、Vacuum、WAL、主从复制、备份恢复和权限管理等核心场景。
本节主要面向 PostgreSQL v14—v18。不同版本的统计视图字段可能存在差异，执行前应先确认数据库版本。当前 PostgreSQL 官方文档的 Current 版本为 v18。
一、实例与基础信息
01、查看 PostgreSQL 版本
SELECT version();
返回 PostgreSQL 版本、编译器、操作系统架构等信息。
也可以只查看版本号：SHOW server_version;
02、查看服务器版本号
SELECT current_setting('server_version');
在脚本中使用 current_setting()，通常比解析 version() 的返回文本更方便。
03、查看当前数据库
SELECT current_database();
04、查看当前用户
SELECT current_user;
同时查看当前用户和会话用户：
SELECT current_user, session_user;
session_user 表示最初建立连接的用户，current_user 可能因 SET ROLE 等操作发生变化。
05、查看数据库服务器地址和端口
SELECT inet_server_addr(), inet_server_port();
在 VIP、负载均衡、读写分离和多实例环境中，可以用它确认当前连接到了哪台数据库。
06、查看客户端地址和端口
SELECT inet_client_addr(), inet_client_port();
07、查看数据库启动时间
SELECT pg_postmaster_start_time();
计算实例已经运行了多长时间：
SELECT now() - pg_postmaster_start_time() AS uptime;
08、查看当前时间和时区
SELECT now(), current_timestamp, current_setting('TimeZone');
09、查看数据目录
SHOW data_directory;
也可以查询：
SELECT current_setting('data_directory');
10、查看配置文件路径
SHOW config_file;
同时查看主要配置文件：
SELECT current_setting('config_file') AS config_file,
current_setting('hba_file') AS hba_file,
current_setting('ident_file') AS ident_file;
分别对应：
postgresql.conf
pg_hba.conf
pg_ident.conf
二、数据库、模式与对象
11、查看所有数据库
在 psql 中执行：\l
SQL 方式：
SELECT datname, datdba::regrole AS owner, encoding,
datcollate, datctype, datallowconn
FROM pg_database ORDER BY datname;
12、查看当前数据库大小
SELECT pg_size_pretty(pg_database_size(current_database()));
13、查看所有数据库大小
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS database_size
FROM pg_database WHERE datallowconn ORDER BY pg_database_size(datname) DESC;
14、查看当前数据库中的模式
在 psql 中：\dn
SQL 方式：
SELECT schema_name, schema_owner
FROM information_schema.schemata ORDER BY schema_name;
15、查看当前搜索路径
SHOW search_path;
search_path 会影响未指定 Schema 的对象解析顺序。
16、查看指定模式中的表
在 psql 中：
\dt public.*
SQL 方式：
SELECT schemaname, tablename, tableowner
FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;
17、查看表结构
在 psql 中：
\d public.table_name
查看更完整的信息：
\d+ public.table_name
\d+ 可以显示字段、索引、约束、访问方法、表大小和存储参数等信息。
18、查看视图
\dv
查看物化视图：\dm
SQL 方式：
SELECT schemaname, viewname, viewowner
FROM pg_views ORDER BY schemaname, viewname;
19、查看函数和存储过程
\df
查看更详细的信息：\df+
SQL 查询：
SELECT n.nspname AS schema_name, p.proname AS routine_name,
pg_get_function_identity_arguments(p.oid) AS arguments, p.prokind
FROM pg_proc p JOIN pg_namespace n
ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;
20、查看扩展
\dx
SQL 方式：
SELECT extname, extversion,extnamespace::regnamespace AS schema_name
FROM pg_extension ORDER BY extname;
三、连接与会话管理
21、查看当前活动会话
SELECT pid, usename, datname, client_addr, application_name,
state, backend_start, query_start, wait_event_type, wait_event, query
FROM pg_stat_activity ORDER BY query_start NULLS LAST;
pg_stat_activity 是 PostgreSQL 会话排查的核心视图。
22、查看当前连接数
SELECT count(*) AS current_connections FROM pg_stat_activity;
23、按数据库统计连接数
SELECT datname, count(*) AS connection_count
FROM pg_stat_activity GROUP BY datname ORDER BY connection_count DESC;
24、按用户统计连接数
SELECT usename, count(*) AS connection_count
FROM pg_stat_activity GROUP BY usename ORDER BY connection_count DESC;
25、按客户端地址统计连接数
SELECT client_addr, count(*) AS connection_count
FROM pg_stat_activity GROUP BY client_addr ORDER BY connection_count DESC;
26、查看最大连接数
SHOW max_connections;
查看为超级用户预留的连接数：
SHOW superuser_reserved_connections;
27、查看连接使用率
SELECT count(*) AS current_connections,
current_setting('max_connections')::int AS max_connections,
round(count(*) * 100.0 /current_setting('max_connections')::int,2
) AS usage_percent
FROM pg_stat_activity;
28、查看正在执行的 SQL
SELECT pid, usename, datname, client_addr, now() - query_start AS running_time,
wait_event_type, wait_event, query
FROM pg_stat_activity WHERE state = 'active'
AND pid <> pg_backend_pid() ORDER BY query_start;
29、查看执行超过 60 秒的 SQL
SELECT pid, usename, datname, client_addr,
now() - query_start AS running_time, wait_event_type, wait_event,query
FROM pg_stat_activity WHERE state = 'active'
AND query_start < now() - interval '60 seconds' ORDER BY query_start;
30、查看空闲连接
SELECT pid, usename, datname, client_addr, state,
now() - state_change AS idle_time, query
FROM pg_stat_activity WHERE state = 'idle' ORDER BY state_change;
31、查看空闲事务
SELECT pid, usename, datname, client_addr,
xact_start, now() - xact_start AS transaction_time, state, query
FROM pg_stat_activity WHERE state = 'idle in transaction'
ORDER BY xact_start;
idle in transaction 是 PostgreSQL 运维中必须重点关注的状态。会话虽然没有执行 SQL，但事务仍未结束，可能持有锁、阻止 Vacuum 清理垃圾版本，并导致表膨胀。
32、消正在执行的 SQL
SELECT pg_cancel_backend(12345);
pg_cancel_backend() 只取消当前 SQL，一般不会断开数据库连接。
33、终止数据库会话
SELECT pg_terminate_backend(12345);
终止连接后，该会话中的未提交事务会被回滚。
34、批量取消长时间 SQL
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity WHERE state = 'active'
AND query_start < now() - interval '30 minutes'
AND pid <> pg_backend_pid();
生产环境不要直接执行。建议先将查询结果中的会话逐个确认，再决定是否取消。
35、查看自己的后台进程 PID
SELECT pg_backend_pid();
四、事务、锁等待与阻塞
36、查看长事务
SELECT pid, usename, datname, client_addr, xact_start,
now() - xact_start AS transaction_age, state, wait_event_type, wait_event, query
FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;
37、查看超过 10 分钟的事务
SELECT pid, usename, datname, client_addr,
now() - xact_start AS transaction_age, state, query
FROM pg_stat_activity
WHERE xact_start < now() - interval '10 minutes' ORDER BY xact_start;
38、查看当前锁
SELECT pid, locktype, relation::regclass AS relation,
mode, granted, waitstart FROM pg_locks ORDER BY granted, pid;
39、查看正在等待的锁
SELECT pid, locktype, relation::regclass AS relation,
page, tuple, transactionid, mode, waitstart
FROM pg_locks WHERE NOT granted ORDER BY waitstart;
40、查看被谁阻塞
SELECT pid, pg_blocking_pids(pid) AS blocking_pids,
wait_event_type, wait_event, query FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
41、查看完整阻塞关系
SELECT
blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
now() - blocked.query_start AS blocked_duration,
blocked.query AS blocked_query,
blocker.pid AS blocker_pid,
blocker.usename AS blocker_user,
now() - blocker.query_start AS blocker_duration,
blocker.state AS blocker_state,
blocker.query AS blocker_query
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid
JOIN pg_stat_activity blocker
ON blocker.pid = bpid
ORDER BY blocked.query_start;
这条 SQL 可以直接建立等待会话与阻塞会话之间的关系。
42、查看阻塞其他会话的进程
SELECT DISTINCT
blocker.pid,
blocker.usename,
blocker.datname,
blocker.client_addr,
blocker.state,
blocker.xact_start,
blocker.query_start,
blocker.query
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bpid
JOIN pg_stat_activity blocker
ON blocker.pid = bpid;
43、终止阻塞源会话
SELECT pg_terminate_backend(12345);
终止前必须确认：
是否存在未提交事务；
回滚需要多长时间；
是否为关键业务连接；
是否会触发应用重试风暴；
是否还有更上游的阻塞源。
44、查看预备事务
SELECT * FROM pg_prepared_xacts;
两阶段提交环境中，长期未完成的预备事务可能持续持有锁。
45、查看数据库死锁数量
SELECT datname, deadlocks FROM pg_stat_database ORDER BY deadlocks DESC;
这里记录的是数据库启动以来或统计信息重置以来累计检测到的死锁数量。
五、SQL 性能与执行计划
46、查看估算执行计划
EXPLAIN SELECT * FROM public.table_name WHERE id = 100;
EXPLAIN 不会真正执行 SQL。
47、查看实际执行计划
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM public.table_name WHERE id = 100;
它会实际执行 SQL，并显示：实际耗时；实际返回行数；执行循环次数；Shared Buffer 命中；磁盘读取；临时文件读写。
对于 UPDATE、DELETE 和 INSERT，执行 EXPLAIN ANALYZE 会真正修改数据。生产环境中应放在事务中验证，并在确认后回滚。
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE public.table_name SET status = 1 WHERE id = 100;
ROLLBACK;
48、查看更完整的执行计划
EXPLAIN (ANALYZE,BUFFERS,WAL,VERBOSE,SETTINGS,SUMMARY)
SELECT * FROM public.table_name WHERE id = 100;
49、查看 JSON 格式执行计划
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM public.table_name WHERE id = 100;
JSON 格式更适合执行计划平台、自动化分析工具和程序解析。
50、安装 pg_stat_statements
首先需要在配置文件中加入：
shared_preload_libraries = 'pg_stat_statements'
重启数据库后，在目标数据库创建扩展：
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
pg_stat_statements 用于聚合记录 SQL 的执行次数、总耗时、平均耗时、返回行数和 I/O 等统计数据。该扩展需要在具体数据库中创建。
51、查看总耗时最高的 SQL
SELECT queryid, calls,
round(total_exec_time::numeric, 2) AS total_exec_ms,
round(mean_exec_time::numeric, 2) AS avg_exec_ms, rows,query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;
52、查看平均耗时最高的 SQL
SELECT queryid, calls,
round(mean_exec_time::numeric, 2) AS avg_exec_ms,
round(max_exec_time::numeric, 2) AS max_exec_ms,
rows,query
FROM pg_stat_statements WHERE calls >= 10
ORDER BY mean_exec_time DESC LIMIT 20;
53、查看执行次数最多的 SQL
SELECT queryid, calls,
round(total_exec_time::numeric, 2) AS total_exec_ms,
round(mean_exec_time::numeric, 2) AS avg_exec_ms,
query FROM pg_stat_statements ORDER BY calls DESC LIMIT 20;
54、查看读取数据块最多的 SQL
SELECT queryid, calls, shared_blks_read, shared_blks_hit,
temp_blks_read, temp_blks_written,query
FROM pg_stat_statements ORDER BY shared_blks_read DESC LIMIT 20;
55、查看临时文件消耗最高的 SQL
SELECT queryid, calls, temp_blks_read, temp_blks_written,
round(total_exec_time::numeric, 2) AS total_exec_ms, query
FROM pg_stat_statements WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC LIMIT 20;
56、查看 WAL 生成量最高的 SQL
SELECT queryid, calls, wal_records, wal_fpi,
pg_size_pretty(wal_bytes::bigint) AS wal_size, query
FROM pg_stat_statements ORDER BY wal_bytes DESC LIMIT 20;
适合分析批量更新、大事务以及 WAL 异常增长问题。
57、重置 pg_stat_statements
SELECT pg_stat_statements_reset();
重置前应确认是否还需要保留原有 SQL 性能基线。
58、查看数据库缓存命中率
SELECT datname, blks_read, blks_hit,
round(
blks_hit * 100.0 /
NULLIF(blks_hit + blks_read, 0), 2
) AS cache_hit_percent
FROM pg_stat_database
WHERE datname IS NOT NULL ORDER BY cache_hit_percent;
缓存命中率高并不代表 SQL 一定正常，还要结合执行计划、物理 I/O 延迟、工作集大小和访问模式判断。
59、查看表扫描情况
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables ORDER BY seq_tup_read DESC LIMIT 20;
60、查看统计信息最近更新时间
SELECT schemaname, relname,last_analyze,
last_autoanalyze, analyze_count, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY greatest(last_analyze, last_autoanalyze) NULLS FIRST;
六、表、索引与空间分析
61、查看表总大小
SELECT pg_size_pretty(
pg_total_relation_size('public.table_name')
) AS total_size;
总大小包括：表数据；索引；TOAST 数据；TOAST 索引。
62、分别查看表和索引大小
SELECT
pg_size_pretty(
pg_relation_size('public.table_name')
) AS table_size,
pg_size_pretty(
pg_indexes_size('public.table_name')
) AS index_size,
pg_size_pretty(
pg_total_relation_size('public.table_name')
) AS total_size;
63、查看最大的表
SELECT schemaname, relname,pg_size_pretty(
pg_total_relation_size(relid)
) AS total_size FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;
64、查看索引
在 psql 中：
\di public.*
SQL 方式：
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes WHERE schemaname = 'public'
ORDER BY tablename, indexname;
65、查看表的所有索引定义
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'table_name';
66、查看索引使用情况
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
idx_scan, idx_tup_read, idx_tup_fetch,
pg_size_pretty(
pg_relation_size(indexrelid)
) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;
67、查看未使用索引
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
idx_scan,pg_size_pretty(
pg_relation_size(indexrelid)
) AS index_size
FROM pg_stat_user_indexes WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
不能因为 idx_scan = 0 就直接删除索引，需要同时确认：
统计信息是否刚重置；
实例是否刚重启；
是否为唯一约束索引；
是否用于低频但关键的月末或年末任务；
是否被外键关联查询使用；
是否作为备用执行计划存在。
68、查看重复索引定义
SELECT indrelid::regclass AS table_name,
array_agg(indexrelid::regclass) AS indexes,
pg_get_indexdef(indexrelid) AS index_definition
FROM pg_index
GROUP BY indrelid, indkey, indclass, indcollation, indexprs, indpred,
pg_get_indexdef(indexrelid) HAVING count(*) > 1;
实际判断重复索引时，应重点比较索引列、顺序、排序方式、表达式和过滤条件，不能只比较索引名称。
69、查看无效索引
SELECT n.nspname AS schema_name, t.relname AS table_name, i.relname AS index_name
FROM pg_index x
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_class t ON t.oid = x.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE NOT x.indisvalid ORDER BY n.nspname, t.relname;
并发创建或重建索引失败后，可能留下无效索引。
70、在线创建索引
CREATE INDEX CONCURRENTLY idx_table_name_col
ON public.table_name(col_name);
CONCURRENTLY 可以降低创建索引期间对业务 DML 的阻塞，但执行时间通常更长，资源消耗也可能更高，而且不能在显式事务块中执行。
71、在线重建索引
REINDEX INDEX CONCURRENTLY public.idx_table_name_col;
普通 REINDEX 默认需要较强的表锁。在支持的版本中，生产环境通常优先评估 REINDEX CONCURRENTLY。
72、查看表的行数估算
SELECT relname, reltuples::bigint AS estimated_rows
FROM pg_class WHERE oid = 'public.table_name'::regclass;
这是统计信息中的估算值，并不是精确行数。
精确统计需要执行：
SELECT count(*) FROM public.table_name;
对于超大表，count(*) 可能执行很久并产生大量 I/O。
73、查看表的活跃与死亡元组
SELECT schemaname, relname, n_live_tup, n_dead_tup,
round(
n_dead_tup * 100.0 /
NULLIF(n_live_tup + n_dead_tup, 0), 2
) AS dead_tuple_percent
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
74、查看表膨胀相关指标
SELECT schemaname, relname, n_live_tup, n_dead_tup,
last_vacuum, last_autovacuum, vacuum_count, autovacuum_count,
pg_size_pretty(
pg_total_relation_size(relid)
) AS total_size
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
n_dead_tup 只是估算值，不能单独作为表膨胀比例。准确判断还需结合 pgstattuple、表文件大小、历史数据量和业务更新模型。
75、查看 TOAST 表大小
SELECT
c.oid::regclass AS table_name,
c.reltoastrelid::regclass AS toast_table,
pg_size_pretty(
pg_total_relation_size(c.reltoastrelid)
) AS toast_size
FROM pg_class c WHERE c.oid = 'public.table_name'::regclass;
七、Vacuum、Autovacuum 与统计信息
PostgreSQL 采用 MVCC。更新和删除通常不会立即覆盖原有行版本，而是产生可清理的死亡元组。因此，Vacuum 不是可有可无的“优化动作”，而是 PostgreSQL 日常运行机制的重要组成部分。官方文档也明确指出，PostgreSQL 数据库需要定期执行 Vacuum，大部分环境由 Autovacuum 自动完成。
76、手工执行 Vacuum
VACUUM public.table_name;
普通 VACUUM 清理可回收的死亡元组，使空间可以被后续数据复用，通常不会把表文件空间归还给操作系统。
77、执行 Vacuum Analyze
VACUUM (ANALYZE) public.table_name;
也可以写成：
VACUUM ANALYZE public.table_name;
它会先执行 Vacuum，再收集优化器统计信息。
78、显示 Vacuum 详细输出
VACUUM (VERBOSE, ANALYZE) public.table_name;
79、执行 Vacuum Full
VACUUM FULL public.table_name;
VACUUM FULL 会重写整张表，将可释放空间归还给操作系统，但需要强锁，并可能产生较高的 I/O 和额外临时空间需求。生产大表上不能把它当作常规维护命令。
80、单独收集统计信息
ANALYZE public.table_name;
指定字段：
ANALYZE public.table_name(col1, col2);
81、提高字段统计信息目标值
ALTER TABLE public.table_name
ALTER COLUMN col_name
SET STATISTICS 1000;
然后重新收集：
ANALYZE public.table_name;
适用于数据分布倾斜、默认统计信息粒度不足，导致优化器行数估算明显失真的字段。
82、查看 Autovacuum 配置
SELECT name, setting, unit, source
FROM pg_settings WHERE name LIKE 'autovacuum%'
ORDER BY name;
83、查看正在执行的 Vacuum
SELECT pid, datname, relid::regclass AS table_name, phase,
heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
index_vacuum_count, num_dead_item_ids
FROM pg_stat_progress_vacuum;
PostgreSQL 能够为 VACUUM、ANALYZE、CREATE INDEX、CLUSTER、COPY 和基础备份等操作提供进度视图。
84、查看 Autovacuum Worker
SELECT pid, datname, usename, backend_type,
query_start, wait_event_type, wait_event, query
FROM pg_stat_activity WHERE backend_type = 'autovacuum worker';
85、查看事务年龄和冻结风险
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY age(datfrozenxid) DESC;
查看表级冻结年龄：
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
age(c.relfrozenxid) AS xid_age
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
ORDER BY age(c.relfrozenxid) DESC LIMIT 20;
事务 ID 年龄过高可能导致数据库进入防止事务 ID 回卷的保护状态，是 PostgreSQL DBA 必须监控的指标。
八、WAL、检查点与归档
86、查看当前 WAL 位置
SELECT pg_current_wal_lsn();
87、查看 WAL 文件名
SELECT pg_walfile_name(pg_current_wal_lsn());
88、计算两个 WAL 位置的差值
sql、SELECT pg_size_pretty(
pg_wal_lsn_diff(
'0/5000000'::pg_lsn,
'0/4000000'::pg_lsn
));
89、查看 WAL 配置
SELECT name, setting, unit, source
FROM pg_settings WHERE name IN (
'wal_level',
'max_wal_size',
'min_wal_size',
'wal_buffers',
'wal_compression',
'checkpoint_timeout',
'checkpoint_completion_target',
'archive_mode',
'archive_command'
)
ORDER BY name;
90、查看 WAL 统计信息
SELECT * FROM pg_stat_wal;
常见字段包括：
wal_records
wal_fpi
wal_bytes
wal_buffers_full
wal_write
wal_sync
91、查看归档状态
SELECT * FROM pg_stat_archiver;
重点关注：
archived_count
failed_count
last_archived_wal
last_archived_time
last_failed_wal
last_failed_time
归档持续失败可能导致 pg_wal 目录不断增长。
92、手工切换 WAL
SELECT pg_switch_wal();
通常用于：测试归档链路；触发当前 WAL 文件归档；备份流程；恢复验证。
不应在高频循环中随意执行。
九、流复制与复制槽
93、查看当前节点是否处于恢复状态
SELECT pg_is_in_recovery();
返回：
false：通常为主库；
true：通常为物理备库。
94、查看主库复制状态
SELECT pid, usename, application_name, client_addr, state, sync_state,
sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
官方文档说明，主库可以通过 pg_stat_replication 查看 WAL Sender；备库可以通过 pg_stat_wal_receiver 查看 WAL Receiver。
95、计算各备库复制延迟
SELECT application_name, client_addr, state, sync_state,
pg_size_pretty(
pg_wal_lsn_diff(
pg_current_wal_lsn(),
replay_lsn
)
) AS replay_lag_bytes, replay_lag
FROM pg_stat_replication ORDER BY
pg_wal_lsn_diff(
pg_current_wal_lsn(),
replay_lsn
) DESC;
replay_lag 是时间维度，LSN 差值是 WAL 字节维度，两者应该结合分析。
96、查看备库 WAL 接收状态
SELECT * FROM pg_stat_wal_receiver;
97、查看备库回放位置和延迟时间
SELECT
pg_last_wal_receive_lsn() AS receive_lsn,
pg_last_wal_replay_lsn() AS replay_lsn,
pg_size_pretty(
pg_wal_lsn_diff(
pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn()
)
) AS receive_replay_gap,
now() - pg_last_xact_replay_timestamp() AS replay_delay;
需要注意：当主库长时间没有事务提交时，时间差值可能持续增大，并不一定代表复制正在延迟。
98、查看复制槽
SELECT slot_name, slot_type, database, active, active_pid,
restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size
FROM pg_replication_slots;
复制槽能够防止主库过早删除消费者尚未使用的 WAL。但如果复制槽长期不消费，主库可能持续保留 WAL，最终导致磁盘空间耗尽。
查看复制槽保留的 WAL 大小：
SELECT slot_name, slot_type, active,
pg_size_pretty(
pg_wal_lsn_diff(
pg_current_wal_lsn(),
restart_lsn
)
) AS retained_wal
FROM pg_replication_slots WHERE restart_lsn IS NOT NULL
ORDER BY
pg_wal_lsn_diff(
pg_current_wal_lsn(),
restart_lsn
) DESC;
十、备份、恢复与权限管理
99、使用 pg_dump 进行逻辑备份
备份单个数据库：
pg_dump \
-h 127.0.0.1 \
-p 5432 \
-U backup_user \
-F c \
-f appdb_$(date +%F).dump \
appdb
其中：
-F c：使用 Custom 格式；
-f：指定输出文件；
Custom 格式支持通过 pg_restore 选择对象并行恢复。
并行备份需要使用 Directory 格式：
pg_dump \
-h 127.0.0.1 \
-p 5432 \
-U backup_user \
-F d \
-j 8 \
-f appdb_dir \
appdb
恢复 Custom 格式备份：
createdb \
-h 127.0.0.1 \
-p 5432 \
-U postgres \
appdb_restore
pg_restore \
-h 127.0.0.1 \
-p 5432 \
-U postgres \
-d appdb_restore \
-j 8 \
appdb_2026-07-17.dump
备份全局对象：
pg_dumpall \
-h 127.0.0.1 \
-p 5432 \
-U postgres \
--globals-only \
> globals_$(date +%F).sql
PostgreSQL 官方将备份方式概括为 SQL Dump、文件系统级备份和连续归档三类。
物理基础备份：
pg_basebackup \
-h 10.0.0.10 \
-p 5432 \
-U repl_user \
-D /backup/base_$(date +%F) \
-Fp \
-Xs \
-P \
-R
pg_basebackup 可以对运行中的 PostgreSQL 集群创建基础备份，可用于时间点恢复，也可作为流复制备库的初始数据。
验证基础备份：
pg_verifybackup /backup/base_2026-07-17
pg_verifybackup 会根据 pg_basebackup 生成的备份清单验证基础备份完整性。
100、用户、角色与权限管理
查看所有角色：\du
SQL 方式：
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb,
rolcanlogin, rolreplication, rolconnlimit
FROM pg_roles ORDER BY rolname;
创建登录用户：
CREATE ROLE app_user LOGIN PASSWORD 'StrongPassword';
创建只读角色：
CREATE ROLE app_readonly NOLOGIN;
允许连接数据库：
GRANT CONNECT ON DATABASE appdb TO app_readonly;
授权使用 Schema：
GRANT USAGE ON SCHEMA public TO app_readonly;
授权读取现有表：
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
授权读取现有序列：
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;
配置以后新建表的默认权限：
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_readonly;
将只读角色授予具体用户：
GRANT app_readonly TO app_user;
查看表权限：\dp public.table_name
查看用户成员关系：
SELECT
member.rolname AS member_name,
role.rolname AS granted_role
FROM pg_auth_members m
JOIN pg_roles role
ON role.oid = m.roleid
JOIN pg_roles member
ON member.oid = m.member
ORDER BY member.rolname, role.rolname;
修改密码：
ALTER ROLE app_user PASSWORD 'NewStrongPassword';
禁止登录：
ALTER ROLE app_user NOLOGIN;
限制连接数量：
ALTER ROLE app_user CONNECTION LIMIT 20;
删除用户：
DROP ROLE app_user;
删除前需要确认该用户是否拥有对象或仍被授予权限。
补充：PostgreSQL DBA 常用的 psql 命令
除了 SQL，DBA 还需要熟悉 psql 自带的反斜杠命令。
\l 查看数据库。
\c appdb 切换数据库。
\dn 查看 Schema。
\dt 查看表。
\d+ public.table_name 查看表详细结构。
\di 查看索引。
\dv 查看视图。
\dm
查看物化视图。
\df 查看函数。
\du 查看角色。
\dx 查看扩展。
\x 切换扩展显示模式，查看宽表结果时非常实用。
\timing on 显示 SQL 执行时间。
\watch 2
每两秒重复执行上一条 SQL，适合实时观察连接数、复制延迟、Vacuum 进度等指标。
\o output.txt 将查询结果输出到文件。
\copy public.table_name TO '/tmp/table.csv' CSV HEADER
通过客户端导出 CSV。
\q 退出 psql。
PostgreSQL 故障排查的正确顺序
真正有价值的不是把这 100 条命令全部背下来，而是知道什么时候使用哪一类命令。当 PostgreSQL 业务出现卡顿时，可以按照下面的顺序排查。
101、第一步：检查连接和正在执行的 SQL
重点查看：SELECT * FROM pg_stat_activity;
确认是否存在：
连接数暴增；
长时间运行 SQL；
大量空闲连接；
idle in transaction；
相同 SQL 集中并发执行；
明显异常的等待事件。
102、第二步：检查长事务和锁等待
重点查看：
SELECT * FROM pg_locks WHERE NOT granted;
以及：
SELECT pid, pg_blocking_pids(pid), query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
很多 PostgreSQL 卡顿问题并不是 SQL 本身执行慢，而是 SQL 在等待另外一个长事务释放锁。
103、第三步：检查 SQL 执行计划和历史负载
当前 SQL 使用：
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;
历史 SQL 使用：
SELECT * FROM pg_stat_statements
ORDER BY total_exec_time DESC;
既要看单次执行很慢的 SQL，也要看单次不慢但执行次数极高的 SQL。
104、第四步：检查死亡元组和 Autovacuum
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
如果存在大量死亡元组，还要继续判断：
是否有长事务阻止清理；
Autovacuum 是否被关闭；
Autovacuum 参数是否过于保守；
表级 Autovacuum 参数是否合理；
是否存在持续高频更新；
是否出现事务 ID 冻结风险。
105、第五步：检查 WAL 和复制
主库检查：SELECT * FROM pg_stat_replication;
备库检查：SELECT * FROM pg_stat_wal_receiver;
复制槽检查：SELECT * FROM pg_replication_slots;
如果 pg_wal 目录持续增长，除了检查归档失败，还必须检查失效或长期不消费的复制槽。
106、第六步：再考虑参数和系统资源
只有确认连接、SQL、锁、事务、Vacuum、WAL 和复制状态后，才应该进一步检查：
shared_buffers
work_mem
maintenance_work_mem
effective_cache_size
max_connections
checkpoint_timeout
max_wal_size
autovacuum_max_workers
autovacuum_vacuum_scale_factor
参数调整不能替代 SQL 优化，也不能解决长事务、锁等待和应用连接管理问题。
小结
PostgreSQL DBA 与其他数据库 DBA 最大的区别之一，是必须真正理解 MVCC、Vacuum、WAL 和事务可见性机制。看到表空间增长，不能立即执行 VACUUM FULL；看到查询慢，不能只想着加索引；看到备库延迟，也不能只盯着时间字段。很多现象背后，可能是一个长期未提交事务、一条数据分布估算错误的 SQL、一个停止消费的复制槽，或者一次没有及时完成的 Autovacuum。
这 100 条命令覆盖了 PostgreSQL 日常运维的大部分基础入口，但命令只是工具。一个成熟 DBA 的核心能力，仍然是根据会话、锁、事务、执行计划、统计信息和 WAL 之间的关系，建立完整的故障因果链。