一、 PG数据库操作

#停止数据库

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

# 登录数据库

psql -D $PGDATA  -p 5432 -U postgres sncdb

# 查看控制文件

pg_controldata -D $PGDATA

# 列出数据库以及查看数据库编码

\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

# 查看更多帮助

\?

#创建序列

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 版本信息

查看是否开启归档

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'

# 查看会话状态

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

#取消当前会话查询

select pg_cancel_backend(pid);

# 杀会话

select pg_terminate_backend(pid);

或者 kill pid

不要用 kill -9 pid 的方式，会影响到其他已连接的会话 

# 查看表空间使用率

select * from pg_tablespace;

 df -h

# 查看数据库大小

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS SIZE FROM pg_database;

# 查看当前数据库 top 10 大的表

SELECT relname, pg_relation_size('public."' || relname || '"')/1024/1024 AS MB, relkind FROM pg_class WHERE relnamespace IN

(SELECT oid FROM pg_namespace WHERE nspname NOT IN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema')) AND relkind ='r' ORDER BY 2 DESC LIMIT 10 ;

# 查看当前数据库 top 10 对象

SELECT relname, pg_relation_size('public."' || relname || '"')/1024/1024 AS MB, relkind FROM pg_class WHERE relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOT IN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema')) ORDER BY 2 DESC LIMIT 10;

#收集统计信息（分析表）

analyze demo1;

# vacuum 表

vacuum demo1;

# vacuum 数据库

vacuum;

# vacuum 回收空间

vacuum full; ----- 会锁表 

# 查看统计信息

SELECT schemaname,

relname, n_live_tup, n_dead_tup, n_mod_since_analyze, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname='demo1';

# 查看 SQL 执行计划

explain select * from demo1; explain (analyze,verbose,costs,buffers,timing) select * from demo1;

注意，analyze 会执行 SQL 。

#pg_dump 备份数据库

pg_dump mydb> mydb.bak

#导出表结构

pg_dump -s -t demo1 mydb > demo1_metadata.sql

#导出表

pg_dump -h mdw -t demo1 mydb > demo1.sql

#只导出表数据

pg_dump -t demo1 -a mydb > demo1_data_only.sql

#还原

psql mydb -f demo1.sql

#pg_dumpall 备份整个数据库

pg_dumpall > db.out

备份恢复举例：

- 创建和备份 PostgreSQL 数据库：

createdb -U # 创建一个 PostgreSQL 数据库

pg_dump -U > # 备份一个 PostgreSQL 数据库到文件

- 还原 PostgreSQL 数据库：

createdb -U # 先创建一个空的 PostgreSQL 数据库

psql -U < # 从备份文件中还原 PostgreSQL 数据库