PostgreSQL篇

PG的逻辑架构为：instance -> database -> schema -> table。
和Oracle不同的是PG的用户不同于schema；
和MySQL不同的是，PG的database下还有schema。

实例启停与状态
1)启动
$ pg_ctl start -D /datadir -l /logdir/logfile start
-D xxx：指定实例的数据目录。若不指定-D，则使用环境变量PGDATA的值；
-l xxxx：指定实例的日志文件，默认在数据目录下的log目录；
2)停止
$ pg_ctl stop -D /datadir -m fast
-m xxxx：指定实例关闭模式，三种模式可选择。
smart表示等待所有连接断开后关闭实例；fast表示立即回滚所有连接中未提交的事务后断开连接，然后关闭实例；immediate立即关闭实例，有数据丢失风险。一般选择fast。
3)重启实例
$ pg_ctl restart -D /datadir -m fast
4)重新加载参数及配置文件
$ pg_ctl reload -D /datadir
实例会读取参数和配置文件中的修改，将不用重启就能生效的修改应用到实例，包括postgresql.conf、postgresql.auto.conf、pg_hba.conf、pg_ident.conf等文件。
5)查看实例状态
$ pg_ctl status -D /datadir
或者
$ pg_controldata -D /datadir

实例连接
1)使用psql工具连接实例
$ psql -h 192.168.1.1 -p 5432 -d postgres -U postgres
-h xxxxx：指定PG的IP地址或主机名，若不指定默认为本地socket；
-p xxxxx：指定实例端口，若不指定默认为5432；
-d xxxxx：指定要连接的database，若不指定默认为postgres；
-U xxxxx：指定连接实例的用户或角色，若不指定默认为当前操作系统用户名；

数据目录文件说明
1)目录
base：该目录包含数据库用户所创建的各个数据库，同时也包括postgres、template0和template1的pg_defaulttablespace。
global：包含集群范围的表的文件和全局控制信息等。
pg_commit_ts：包含事务提交时间戳数据的子目录。
pg_dynshmem：包含被动态共享内存子系统所使用文件的子目录。
pg_logical：包含用于逻辑复制的状态数据的子目录。
pg_multixact：包含多事务状态数据的子目录（用户共享的行锁）。
pg_notify：包含LISTEN/NOTIFY状态数据的子目录。
pg_repslot：包含复制槽数据的子目录。
pg_serial：包含已提交的可序列化事务信息的子目录。
pg_snapshots：包含到处的快照的子目录。
pg_stat：包含用于统计子系统的永久文件的子目录。
pg_stat_tmp：包含用于统计信息子系统临时文件的子目录。
pg_subtrans    ：包含子事务状态数据的子目录。
pg_tblspc：包含指向表空间的符号链接的子目录。
pg_twophase：用于预备事务状态文件的子目录。
pg_wal：保存WAL预写日志。
pg_xact    ：包含事务提交状态数据的子目录, 记录事务提交状态数据。

2)文件
current_logfiles：记录当前实例正在使用的日志文件。
postgresql.conf：实例的参数文件。
pg_hba.conf：客户端认证控制文件，用于配置用户、客户端IP等黑白名单和认证方式。其中，认证方式有trust(信任、无需密码)、md5(需要密码)、reject(拒绝连接)、ident(使用pg_ident.conf配置认证)。修改后执行pg_ctl reload即可生效。
pg_ident.conf：用来配置哪些操作系统用户可以映射为哪个数据库用户。
postgresql.auto.conf：参数文件，只保存alter system命令修改的参数。
postmaster.opts：记录服务器最近一次启动时使用的命令行参数。
postmaster.pid：记录进程的信息。
pg_version：pg数据库主版本号文件。

常用参数说明
PG实例的参数文件为数据目录下的postgresql.conf

序号	参数名	说明
1	listen_addresses	需要监听的IP地址，默认为localhost，*表示所有IP。
2	port	监听端口。
3	max_connections	最大连接数。
4	authentication_timeout	认证超时时间。
5	shared_buffers	共享内存大小。
6	temp_buffers	临时缓存大小。
7	work_mem	工作进程内存分配大小，用于排序、join等操作。
8	maintenance_work_mem	数据库维护进程（主要是vacuum进程）使用内存大小。
9	archive_mode	是否开启归档。
10	archive_command	归档命令。
11	log_statement	允许日志记录哪些SQL操作，有none(不记录)、ddl(只记录ddl语句)、mode(所有可能发生数据更改的语句)、all(所有语句)。
12	log_min_duration_statement	设置至少执行多长时间的SQL才会被记录，单位毫秒。

psql工具的使用
当使用psql工具连接到实例后，可使用以下快捷命令(常用)查询数据库的信息：
# 显示帮助说明
postgres=# \h 或 \help
# 退出
postgres=# \q 或 \quit
# 列举database
postgres=# \l
# 选择database
postgres=# \c databasename
# 列举当前database中所有的表
postgres=# \dt
# 查看某个表的表结构
postgres=# \d tablename 或 \d schema.tablename
# 查看客户端字符集
postgres=# \encoding
# 列举所有用户和角色
postgres=# \du
# 列举schema
postgres=# \dn
# 列举表空间
postgres=# \db
# 列举视图
postgres=# \dv
# 执行sql文件
postgres=# \i xxx.sql

1．database
创建database：
CREATE DATABASE dbname OWNER username TEMPLATE template0 TABLESPACE tablespace_name ENCODING 'UTF8';
其中，除dbname是必选外，其他选项皆有默认值。
修改database：
ALTER DATABASE dbname RENAME TO new_dbname; -- 重命名database
ALTER DATABASE dbname OWNER TO new_owner; -- 更改数据库属主
ALTER DATABASE dbname SET TABLESPACE new_tablespace；-- 更改默认表空间
删除database：
DROP DATABASE dbname;
查看database：
postgres=# \l
或者
select * from pg_database;

2．表空间
PG中的表空间实质是指操作系统的某个目录，创建表空间就是在pg_tblspc目录下创建一个链接文件指向操作系统目录。
创建表空间：
CREATE TABLESPACE tablespace_name OWNER username LOCALION 'path';
其中，除dbname是必选外，其他选项皆有默认值。
修改表空间：
ALTER TABLESPACE tablespace_name RENAME TO new_name; -- 重命名
ALTER TABLESPACE tablespace_name OWNER TO new_owner; -- 更改属主
删除表空间：
DROP TABLESPACE tablespace_name;
查看表空间：
postgres=# \db
或者
select * from pg_tablespace;

3．schema
创建schema：
CREATE SCHEMA schema_name AUTHORIZATION username;
其中AUTHORIZATION username指定该schema属于哪个用户，默认为当前用户。
修改schema：
ALTER SCHEMA schema_name RENAME TO new_name; -- 重命名
ALTER SCHEMA schema_name OWNER TO new_owner; -- 更改属主
删除schema：
DROP SCHEMA IF EXISTS schema_name CASCADE; -- 删除schema及其所拥有的所有对象

4．用户和权限
PG中user和role的概念基本等同，只是user默认有登录权限。实例的用户并不属于某个database，对该实例下的所有database都有效。
创建用户：
CREATE USER username WITH PASSWORD 'xxxxx'; -- CREATE USER默认有登录(login)权限
修改用户密码：
ALTER USER username WITH PASSWORD 'xxxxx' VALID UNTIL 'xxxx-xx-xx'; -- 其中，VALID UNTIL用于设置密码有效期，到xxxx-xx-xx失效
查看用户权限：
1)查看用户的系统权限
select * from pg_roles where rolname='username';
2)查看用户在某个表上的权限
select * from information_schema.table_privileges where grantee='username';

5．会话
1)查看当前所有进程信息
select * from pg_stat_activity;
2)查杀指定进程
select pid from pg_stat_activity;
select pg_terminate_backend(pid);
3)查看某个进程运行的SQL
select query from pg_stat_activity where pid = '12345';

6．锁
1)查看所锁信息
select * from pg_locks;
2)查看被锁的表、所类型以及锁表语句
select a.pid,b.relname,a.mode,c.query from pg_locks a,pg_class b,pg_stat_activity c where a.relation = b.oid and a.pid = c.pid;

7．执行计划
1)查看执行计划
explain select * from pg_tables;

8．慢SQL
如果要记录慢SQL，需要在postgresql.conf设置参数log_min_duration_statement，如log_min_duration_statement = 5000，表示执行时间超过5000毫秒的SQL语句将会被记录。
修改配置文件后，执行pg_ctl reload -D /datadir 即可生效。慢SQL同样被记录到普通日志中，一般位于数据目录下的log目录。