1、配置文件
C:\Program Files\PostgreSQL\11\data路径下
1、postgresql.conf、postgresql.auto.conf：通用配置，如内存分配。ALTER SYSTEM SQL命令会覆盖postgresql.auto.conf文件。
2、pg_hba.conf：控制访问安全，如允许哪些IP访问。
3、pg_ident.conf：pg_hba.conf中权限控制信息的身份验证模式字段指定为ident时使用。
查询配置文件路径SQL语句：
SELECT name, setting FROM pg_settings WHERE category='File Locations';
2、连接管理
查询活动连接列表
SELECT * FROM pg_stat_activity order by backend_start desc;
终止连接
SELECT pg_terminate_backend(procid);
3、创建可登录角色
（1）创建具备登录权限的角色
创建具备登录权限的角色（不能创建数据库，默认永不过期）
CREATE ROLE zww LOGIN PASSWORD 'zww';
创建具备登录权限的角色（可创建数据库，默认永不过期）
CREATE ROLE zww LOGIN PASSWORD 'zww' CREATEDB;
查询所有角色
SELECT * FROM pg_roles;
查询角色也可在psql中输入\du
（2）创建具备超级用户权限的角色
创建具备超级用户权限的角色（默认永不过期）
CREATE ROLE zww LOGIN PASSWORD 'zww' SUPERUSER;
创建具备超级用户权限的角色（过期时间为2020-01-01 00:00:00）
CREATE ROLE zww LOGIN PASSWORD 'zww' SUPERUSER VALID UNTIL '2020-1-1 00:00';
4、创建组角色
创建组角色
CREATE ROLE xxx INHERIT;
将组角色权限授予成员角色
GRANT xxx TO zww;
临时扮演父角色，不修改session_user，修改current_user
SET ROLE xxx;
具备SUPERUSER权限用户扮演任意角色，，修改session_user和current_user
SET SESSION AUTHORIZATION xxx;
5、创建数据库
创建数据库，默认以template1库为模板
CREATE DATABASE mydb;
记住，任何时候不要对template0模板数据库做任何修改，要修改字符集编码和排序规则只能基于此模板进行。
以my_template_db为模板创建数据库
CREATE DATABASE mydb TEMPLATE my_template_db;
使用超级用户身份将my_template_db为设为模板数据库
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'my_template_db';
6、权限管理
（1）创建登录角色，创建数据库并设定其所有者
CREATE ROLE mydb_admin LOGIN PASSWORD 'something';
CREATE DATABASE mydb WITH owner = mydb_admin;
（2）授权
GRANT some_privilege TO some_role;
GRANT ALL ON ALL TABLES IN SECHEMA public TO mydb_admin WITH GRANT OPTION;
备注：
① 拥有权限者，且自身有GRANT时才能授予别人；
② 有些权限只有对象的所有者才能拥有，任何情况都不能授予别人。如DROP、ALTER；
③ ALL，一次性将某个对象的所有权限授予；
④ PUBLIC，指代所有角色
（3）回收权限
REVOKE EXECUTE ON ALL FUNCTIONS IN SECHEMA my_schema FROM public;
7、扩展包
（1）查询服务器上已安装的扩展
select * from pg_available_extensions;
（2）安装扩展包
CREATE EXTENSION funzzystrmatch;
或者在psql中以非交互方式执行：
psql -p 5432 -d mydb -c "CREATE EXTENSION funzzystrmatch;"
备注：执行psql命令，需要将C:\Program Files\PostgreSQL\11\bin加入环境变量中的Path路径
8、备份与恢复
（1）pg_dump备份指定库
--备份为自定义压缩格式（-F c）、备份为SQL文本（-F p）
pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f mydb.backup postgres
（2）pg_dumpall全库备份
只能备份为SQL文本，恢复比较耗时。建议每天仅使用pg_dumpall对角色和表空间定义等全局对象备份。
--仅备份角色和表空间定义
pg_dumpall -h localhost -p 5432 -U postgres -f myglobals.sql --globals-only
--仅备份角色定义
pg_dumpall -h localhost -p 5432 -U postgres -f myroles.sql --roles-only
（3）使用psql恢复SQL文本格式的数据备份
psql -U postgres -f myglobals.sql
（4）使用pg_restore进行恢复
①连接到postgres库，使用--create说明恢复出来的数据库名与备份时一致
pg_restore --dbname=postgres --create --jobs=4 --verbose mydb.backup
②先创建数据库，再连接该数据库进行恢复
CREATE DATABASE mydb;
pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup
③先创建数据库，再连接该数据库仅恢复结构
CREATE DATABASE mydb;
pg_restore --dbname=mydb --section=pre-data --jobs=4 mydb.backup
9、表空间
（1）概述
pg_default：用于存储所有的用户级数据；
pg_global：用于存储所有的系统级数据。
（2）创建表空间
CREATE TABLESPACE secondary LOCATION 'C:/pgdata_secondary'
（2）迁移对象表空间
--移动库的表空间
ALTER DATABASE mydb SET TABLESPACE secondary;
--移动表的表空间
ALTER TABLE mytable SET TABLESPACE secondary;
10、禁止的行为
（1）不要删除PostgreSQL系统文件
（2）不要把操作系统管理员权限授予PostgreSQL的系统账号（postgres）
（3）不要把shared_buffer缓存区设置得过大
（4）不要将PostgreSQL服务器的侦听端口设为一个已被其他程序占用的端口
参考资料
1、《PostgreSQL即学即用》, [美] Regina Obe Leo Hsu著, 丁奇鹏 译
转载请注明：作者gisxiaowei，首发简书 jianshu.com
