【PostgreSQL】PostgreSQL日常运维操作手册_pg运维手册

---

一、PG数据库操作

1.1、启停数据库

#停止数据库
pg_ctl stop  -D $PGDATA  -l $PGDATA / pglog .log

# 启动数据库
pg_ctl start -D  -D $PGDATA  -l $PGDATA / pglog .log

# 检查数据库状态
pg_ctl status -D -D $PGDATA  -l $PGDATA / pglog .log
1
2
3
4
5
6
7
8

1.2、数据库登录操作

# 登录数据库
psql -D $PGDATA  -p 5432 -U postgres sncdb

# 查看控制文件
pg_controldata -D $PGDATA
1
2
3
4
5

二、PG命令行常用操作

# 列出数据库以及查看数据库编码
\l [\list]

# 切换数据库
\c dbname usrname

# 列出当前数据库所有的表
\dt

# 列出当前数据库所有的索引
\di

# 列出 ROLE
\du

# 列出 SCHEMA
\dn

# 列出表空间
\db

# 列出所有的 function
\df

# 列出数据库扩展
\dx

# 列出当前数据库序列
\ds

# 切换工作路径
\cd /u01/soft

# 查看当前会话连接信息
\conninfo

# 显示 SQL 执行时间
\timing

# 列模式显示每个字段
\x

# 将查询结果输出到操作系统（Oracle spool 功能）
\o test.txt select * from demo1; \o

# 执行 SQL 脚本
\i test.sql

# 查看更多帮助
\?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

三、PG数据库对象操作

#创建序列
CREATE SEQUENCE snc_seq INCREMENT BY 1 START WITH 1 NO MAXvalue NO CYCLE CACHE 10;

# 查看序列
postgres=# select * from snc_seq ;

# 查看序列当前值
select currval(' snc_seq ');

# 获取序列下一个值
select nextval('snc_seq');

# 序列相关的表或者视图
select * from pg_sequence;
select * from pg_sequences;
select * from information_schema.sequences;

# 创建用户
create user sncadmin  with password 's nc dmin';
或者 create role s ncadmin  with password 's nc dmin' login;

# 创建和用户同名的 schema
mydb=# create schema s ncadmin  authorization s ncadmin  ;

# 将 schema 的所有权限给用户
postgres=# grant all on schema s ncadmin   to s ncadmin  ;

# 将schema 的所有权限给另外一个用户

grant usage on schema s ncadmin  to sncuser ;

# 修改表的 schema
alter table t_city  set schema s ncadmin  ;

# 创建超级用户
create user snc_pm  with password ' snc_pm ' superuser;

# 修改用户密码
alter snc_pm sa with password 'oracle';
或者 \password sa

# 禁止用户登录
alter user snc_pm  with nologin;

# 删除用户
drop owned by   snc_pm ;

# 创建表空间
create tablespace tbs_data owner pguser location '/u01/data/tbs1';

# 删除表空间
drop tablespace tbs_data;

# 表添加字段
alter table [tabname] add [column_name] [type];
alter table demo1 add c3 int;

# 表删除字段
alter table demo1 drop c3 ;

# 表更改字段
alter table demo1 alter column c3 type varchar(10);

# 查看索引 DDL
select oid from pg_class where relname ='ind_demo_id'; select pg_get_indexdef(18193);

# 查看视图 DDL
select pg_get_viewdef(view_oid);

# 查看约束 DDL
select pg_get_constraintdef(constraint_oid);

# 查看 function DDL
select pg_get_functiondef(func_oid);

# 查看触发器 DDL
select pg_get_triggerdef(trigger_oid);

# 将表 copy 到操作系统
copy t2 to '/u01/soft/t2.txt';

# 将数据拷贝到表
copy t2 from '/u01/soft/t2.csv' delimiter ',' csv header;

# 查询 PostgreSQL 版本信息：
psql -U postgres -c "SELECT version();" # 查询 PostgreSQL 版本信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

四、归档检查

-- 查看是否开启归档
show archive_command ;

-- 开启归档
-- （编辑$PGDATA/postgresql.conf ，需要重启数据库）
archive_command='DATE=`date +%Y%m%d`;DIR="/data/pgsql/arch/$DATE";(test -d $DIR || mkdir -p $DIR)&& cp %p $DIR/%f'
1
2
3
4
5
6

五、 数据库性能

# 查看会话状态
SELECT datname, usename, pid, client_hostname, backend_start, query_start, wait_event_type, STATE, backend_type FROM pg_stat_activity;

# 查看锁的信息
select * from pg_locks;

# 查看 blocker
SELECT datname, pid, usename, client_hostname, wait_event, STATE, query FROM pg_stat_activity WHERE pid IN (SELECT pid FROM pg_locks WHERE GRANTED<>'f' AND transactionid = (SELECT transactionid FROM pg_locks WHERE GRANTED= 'f'));

# 查看膨胀高的 top 10 表
SELECT relname AS TABLE_NAME, pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size, n_dead_tup, n_live_tup,
(n_dead_tup * 100 / (n_live_tup + n_dead_tup))AS dead_tup_ratio FROM pg_stat_user_tables WHERE n_dead_tup<>0 LIMIT 10;

# 查看当前数据库表的年龄
SELECT Current_database(), nspname, CASE relkind WHEN $$r$$ THEN $$ordinary table$$ WHEN $$t$$ THEN $$toast table$$ END AS relkind, relname, age(relfrozenxid), CASE WHEN ( substring(reloptions::text,$$autovacuum_freeze_max_age=(\d+)$$ )::int8) IS NOT NULL THEN (substring(reloptions::text,$$autovacuum_freeze_max_age=(\d+)$ $)::int8)-age(relfrozenxid) ELSE ( SELECT setting FROM pg_settings WHERE name=$$autovacuum_freeze_max_age$$)::int8 - age(relfrozenxid) END AS age_remain FROM pg_class t2 join pg_namespace t3 ON t2.relnamespace=t3.oid WHERE t2.relkind IN ($$t$$, $$r$$) and nspname not in ('pg_catalog','pg_toast','information_schema') ORDER BY age(relfrozenxid) DESC;

# freeze 表的年龄
vacuum freeze demo1;

# 查看数据库长事务
SELECT extract(epoch FROM (clock_timestamp() - xact_start)) AS longtrans,
extract(epoch FROM (clock_timestamp() - query_start)) AS longquery FROM pg_stat_activity WHERE 1=1 AND STATE <> 'idle' ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

六、杀session

#取消当前会话查询
select pg_cancel_backend(pid);

# 杀会话
select pg_terminate_backend(pid);
或者 kill pid
不要用 kill -9 pid 的方式，会影响到其他已连接的会话 
1
2
3
4
5
6
7