二、PostgreSQL数据库管理

1、配置文件

C:\Program Files\PostgreSQL\11\data路径下
1、postgresql.confpostgresql.auto.conf:通用配置,如内存分配。ALTER SYSTEM SQL命令会覆盖postgresql.auto.conf文件。
2、pg_hba.conf:控制访问安全,如允许哪些IP访问。
3、pg_ident.confpg_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

推荐阅读更多精彩内容