#停止数据库 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 <username> <database-name> # 创建一个 PostgreSQL 数据库 pg_dump -U <username> <database-name> > <backup-file> # 备份一个 PostgreSQL 数据库到文件 - 还原 PostgreSQL 数据库： createdb -U <username> <database-name> # 先创建一个空的 PostgreSQL 数据库 psql -U <username> <database-name> < <backup-file> # 从备份文件中还原 PostgreSQL 数据库