postgresql日常运维 - 好文

1.启停 postgres –D $PGDATA start pg_ctl -D $PGDATA start 三种关机模式： pg_ctl –D
$PGDATA stop -m smart 不允许新连接，等待会话结束 pg_ctl –D $PGDATA stop –m fast
不允许新连接，等待子进程退出，终止备份 pg_ctl –D $PGDATA stop –m immediate 立即退出，下次启动需重放wal日志进行恢复
2. pg导数 :::::::::::::::::::::::导出 1）整库备份 pg_dump -d <dbname> -Fd -j 10 -Z 5 -v
-f /$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log 导出sql nohup pg_dump
-d <dbname> -Fc -Z 9 -s -v -f /$backdir/exp.sql & 2）只备份数据 pg_dump -d <dbname>
-Fd -j 10 -Z 5 -v -a -f /$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log
3）只备份结构 pg_dump -d <dbname> -Fd -Z 5 -v -s -f /$backdir/pgdump_$PGNAME_`date
+"%Y%m%d%H%M"` &>out.log 4）只备份特定schema pg_dump -d <dbname> -n <schemaname> -Fd
-j 10 -Z 5 -v -f /$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log
5）只备份特定表（单表不支持并行） pg_dump -d <dbname> -t <tablename> -Fd -Z 5 -v -f
/$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log 多个表 -t <tablename1> -t
<tablename2> ... [postgres@主机:<dbname>:port ~]$ pg_dump --help -F,
--format=c|d|t|p output file format (custom, directory, tar, plain text
(default)) -j, --jobs=NUM use this many parallel jobs to dump -Z,
--compress=0-9 compression level for compressed formats -v, --verbose verbose
mode -f, --file=FILENAME output file or directory name
:::::::::::::::::::::::恢复 1）整库恢复 nohup psql -d <dbname> < imp.sql & pg_restore
-d <dbname> -j 10 -v /$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log
2）恢复前删除已存在object pg_restore -d <dbname> -j 10 -v -c --if-exists
/$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log 3）只恢复数据 pg_restore -d
<dbname> -j 10 -v -a /$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log
4）只恢复结构 pg_restore -d <dbname> -v -s /$backdir/pgdump_$PGNAME_`date
+"%Y%m%d%H%M"` &>out.log 5）恢复特定schema pg_restore -d <dbname> -n <schemaname> -j
10 -v /$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log 6）恢复特定表（单表不支持并行）
pg_restore -d <dbname> -t <tablename> -v /$backdir/pgdump_$PGNAME_`date
+"%Y%m%d%H%M"` &>out.log 7）恢复特定函数 pg_restore -d <dbname> -P <functionname> -j
10 -v /$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log 8）不恢复权限
pg_restore -d <dbname> -j 10 -v -x /$backdir/pgdump_$PGNAME_`date
+"%Y%m%d%H%M"` &>out.log 9）替换表owner pg_restore -d <dbname> -t <tablename> -O
--role=devroot -v /$backdir/pgdump_$PGNAME_`date +"%Y%m%d%H%M"` &>out.log
:::::::::::::::::::::::导数注意 <1> 查看数据库大小 select pg_database_size('<dbname>');
<2> 索引的删除与重建当数据库较大时，需要删除索引和约束,恢复完成后再重建 :::::生成删除索引语句 select 'drop index'||
b.indexname from pg_tables a,pg_indexes b where a.tablename=b.tablename and
a.tableowner='<schemaname>'; :::::生成创建索引语句 select b.indexdef||';' from
pg_tables a,pg_indexes b where a.tablename=b.tablename and
a.tableowner='<schemaname>'; :::::生成删除主键语句 select 'alter table
'||t.tablename||' drop CONSTRAINT '||i.indexname||';' from pg_indexes i
,pg_tables t where i.schemaname=t.schemaname and i.tablename=t.tablename and
i.indexname like '%pk%' and t.tableowner='<schemaname>'; :::::生成添加主键语句 select
'alter table ' ||t.tablename||' add primary key using index '||i.indexname||';'
from pg_indexes i ,pg_tables t where i.schemaname=t.schemaname and
i.tablename=t.tablename and i.indexname like '%pk%' and
t.tableowner='<schemaname>'; :::::查询整库的索引大小并排序 SELECT c.relname,c2.relname,
c2.relpages*8/1024 as size_MB, indexdef||';' as index_def FROM pg_class c,
pg_class c2, pg_index i,pg_indexes iv WHERE c.oid = i.indrelid AND c2.oid =
i.indexrelid and c2.relname=iv.indexname ORDER BY c2.relpages*8 desc; <3> 删除整库
导入整库前，需删除数据库 DROP DATABASE <dbname>; 3.添加pg_pathman 注意，需要在每个<db>均升级 \l 查询数据库 \c
<dbname> 进入数据库 :::：添加插件 create extension pg_pathman; :::：升级插件 alter extension
pg_pathman UPDATE to '1.4'; SET pg_pathman.enable = t; ::::删除插件 drop extension
pg_pathman 4.查询会话 ::::正在连接的会话 select * from pg_stat_activity; select
usename,client_addr,waiting,state,pid from pg_stat_activity; select * from
pg_stat_activity where state='active'; ::::查看连接数 select count(*) from
pg_stat_activity; :::::生成批量结束会话语句 select 'SELECT
pg_terminate_backend('||pid||');' from pg_stat_activity; select 'kill ' || pid
from pg_stat_activity where query like '%SELECT message.roomid,
message.fromjid, message.tojid%'; 6.锁 :::::查询当前所有锁与对象的对应关系 select
a.locktype,a.pid,a.relation,a.mode,a.granted,b.relname from pg_locks a,pg_class
b where a.relation=b.oid; :::::查询锁定query，用户 select usename,query,client_addr,
query_start,pid,client_addr from pg_stat_activity where pid=15010; select query
from pg_stat_activity where pid=15008; :::::pg的锁类型 acesss share: 只与access
exclusive冲突，select 加锁，在用户select时不能做ddl操作 row share: 与exclusive和access
exclusive冲突，类似mysql共享意向锁，select for update/select for share 加锁 row exclusive:
与share/share row exclusive/exclusive/access
exclusive冲突，UPDATE/INSERT/DELETE加这种锁 share update exclusive:与row share/access
exclusive/share/share row exclusive/exclusive，vacuum(不带full)/anslyze/create
index concurr-ently share: 与row exclusive/share update exclusive/share row
exclusive/exclusive/access exclusive ，create index(不带concurrently选项语句)加锁 share
row exclusive: Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE,
SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode
protects a table against concurrent data changes, and is self-exclusive so that
only one session can hold it at a time. Acquired by CREATE TRIGGER and many
forms of ALTER TABLE (see ALTER TABLE). exclusive: Conflicts with the ROW
SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE,
EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode allows only concurrent
ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel
with a transaction holding this lock mode. Acquired by REFRESH MATERIALIZED
VIEW CONCURRENTLY. access exclusive: 类似于mysql的意向排他锁，为并发执行设计 Conflicts with
locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE
EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This
mode guarantees that the holder is the only transaction accessing the table in
any way. Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL,
and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many forms of
ALTER TABLE also acquire a lock at this level. This is also the default lock
mode for LOCK TABLE statements that do not specify a mode explicitly.
详见：https://www.postgresql.org/docs/9.5/static/explicit-locking.html 7.权限 \du+
查看用户权限及角色 pg_stats select * from pg_tables where tableowner='<schemaname>';
8.database操作 ::::重命名 ALTER DATABASE <dbname> RENAME TO <new_dbname>; ::::建库
create database <dbname> owner postgres; ::::删库 DROP DATABASE <dbname>; 9.
pg_hba.conf postgres的连接配置文件，标明允许什么用户连接，及连接权限 # TYPE DATABASE USER ADDRESS
METHOD local all all trust host all all ip/8 md5 注意，修改过后使用pg_ctl reload重新应用
10.xlog :::清除20天之前的xlog find $PGDATA/pg_xlog -mtime +20 -name "000000*"
xlog是pg的wal日志存储目录，相当于Oracle归档日志和redo日志的合体
如果删除掉正在应用的日志会导致数据库不一致，无法start,最好不要做这种操作 find /paic/pg6604/data/pg_xlog -mtime
+6 -name "000000*" -exec rm -rf {} \; 11.数据操作 ::::添加列 ALTER TABLE <table_name>
ADD <col_name> Character varying(2000) not null default 0; ::::改密码 ALTER USER
<user_name> WITH PASSWORD 'xxxx'; :::::执行sql脚本： \x \i a.sql :::::显示SQL执行时间：
\timing on :::::显示所有表名： \d :::::查看表定义： \d <表名> :::::只显示表： \dt ::::只显示索引： \di
::::只显示序列： \ds ::::只显示视图： \dv ::::显示Schema： \dn ::::显示表空间： \db ::::列出用户和角色：
\du或\dg ::::显示表权限分配情况： \dp <表名> ::::关闭自动提交 \set AUTOCOMMIT off ::::显示所有数据库： \l
::::进入某一个库： \c <数据库名> 12.查询占用空间 ::::查询数据表占用空间大小 SELECT table_schema || '.' ||
table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' ||
table_schema || '"."' || table_name || '"')) AS size FROM
information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema
|| '"."' || table_name || '"') DESC ; :::::查询一个索引大小 select
pg_size_pretty(pg_relation_size('indexname)) :::::查看一张表及此它上的索引总大小 select
pg_size_pretty(pg_total_relation_size('tablename')); 13.执行计划 explain (analyze
on, verbose on, buffers on, timing on, costs on) <query> 14.查看慢sql SELECT
query, calls, total_time, (total_time/calls) as average ,rows, 100.0 *
shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY average DESC LIMIT 10;

热门工具换一换