PostgreSQL PCA必知必会

IT小Chen 2023-10-20

937

必知必会：

安装
配置文件
防火墙
客户端工具
数据库管理
体系结构
故障排查
备份和恢复
附录

PostgreSQL认证体系

PCA 认证专员
PCP 认证专家
PCM 认证大师

自由宽松的许可证，任何人都可以以任何目的免费使用、修改和分发PostgreSQL，不管是私用、商用还是学术研究目的。

PostgreSQL 的历史演进：

PostgreSQL 起源于加州大学分校，此分校有一个 stonebraker 教授，他在 1997 年的时候领导了关系数据库 ingres 的研究项目。
1982 年他离开了此分校，并将 ingres 商业化，使之成为了 RT 公司的一个产品，此后 RT 公司被 CA 公司收购。2004 年 CA 公司在开源许可下发布了 ingres release 3，此后 CA 公司继续开发 ingres。stonebraker 教授在此后返回 UCB 之后，又开始了一项叫做 post-ingres 计划，该计划就是解决基于关系模型的数据库管理系统的局限性，这就是 postgres 的开端（还不是现在的 PostgreSQL）。
1986 年开始，Stonebraker 教授发表了一系列论文，引入对象关系理念，探讨了新的数据库的结构设计和扩展设计。1986 年被视为 PostgreSQL 发展史的元年。
1988 年他提出 PostgreSQL 的第一个原型设计并发布第一个版本，此后又连续发布了两个版本，并在最后一个版本中改进了规则系统，增加了对多种存储系统的支持，并改进了查询引擎。
1993 年起，Postgres 用户越来越多，由于特性需求也急促增加，后来升级了第 4 个版本，之后 Postgres 项目正式停止。
1994 年，有两个研究生 Andrew yu 和 Jolly Chen 增加了对 SQL 语言的支持，建立Postgres95，并发行到了互联网。
1996 年，该计划重新命名为 PostgreSQL，许多世界各地数据库开发者以及志愿者参与进来，并发行了 PostgreSQL 第一个版本 6.0，自此之后 PostgreSQL 开始持续稳定的发布新版本，在新版本中有很多改进。
2008 年发布了 PostgreSQL8.0，从这个版本开始支持 Windows 操作系统。
2010 年底，PostgreSQL 发行了 9.0 版本。
2020 年 9 月份发布了 13 版本。
2021 年 9月份发布了14版本。
2022 年10月份发布了15版本。
2023 年9月份发布了16版本。

安装：

二进制安装

二进制安装包安装

(1)PostgreSQL 10.23 二进制安装方法参考我的ITPUB博客：

http://blog.itpub.net/29785807/viewspace-2932433/

(2)EDB不再为PostgreSQL 11及更高版本提供Linux安装程序(windows版本继续提供)，推荐用户使用源码编译安装。

源码编译安装

通过源码编译安装

• 重要的依赖：readline,flex,bison
• 三板斧：./configure && make && make install | install-world
• 他国是否切断pg源码

安装示例：

# 下载并解压：

wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz 
tar xf postgresql-13.3.tar.gz

# 安装依赖：

yum install -y bison flex readline-devel zlib-deve1 zlib zlib-devel gcc openssl-devel

# 创建用户并授权：

useradd postgres 
mkdir /PGCCC/postgresql -p

# 进行编译：

cd postgresql-13.3/ 
./configure --prefix=/PGCCC/postgresql --with-openssl
gmake world && gmake install-world

# 进行授权：

chown -R postgres. PGCCC/postgresql

# 配置环境变量：

vim etc/profile
export PATH=/PGCCC/postgresql/bin:$PATH
export PGDATA=/PGCCC/postgresql/data
source etc/profile

# 初始化：

su - postgres
initdb -D $PGDATA

# postgres 用户下使用 pg_ctl 进行启动：

pg_ctl -D $PGDATA start

# 使用 system 进行管理：

vim usr/lib/systemd/system/postgresql-13.service
[Unit]
Description=PostgreSQL database server
After=network.target
[Service] 
Type=forking 
User=postgres 
Group=postgres 
Environment=PGPORT=5432 
Environment=PGDATA=/PGCCC/postgresql/data/ 
OOMScoreAdjust=-1000 
ExecStart=/PGCCC/postgresql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300 
ExecStop=/PGCCC/postgresql/bin/pg_ctl stop -D ${PGDATA} -s -m fast 
ExecReload=/PGCCC/postgresql/bin/pg_ctl reload -D ${PGDATA} -s 
TimeoutSec=300 
[Install] 
WantedBy=multi-user.target

# 加载 system 配置：

systemctl daemon-reload

# 使用 systemctl 启动：

systemctl start postgresql-13.service 
systemctl enable postgresql-13.service

yum安装

通过yum安装

• 1）安装指南：

https://www.postgresql.org/download/linux/redhat/

• 2）安装RPM仓库

• yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

• 3）安装PG

• yum search postgresql && yum install postgresql

• 干净卸载

yum remove postgresql;rpm -e --nodeps `rpm -qa | grep postgresql` （一键删除所有的包)

安装示例：

yum 安装:

① 下载地址：

https://www.postgresql.org/download/linux/redhat/

② 系统优化：

# 关闭 selinux：

setenforce 0 
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' etc/selinux/config 
cat /etc/selinux/config

# 关闭防火墙：

systemctl status firewalld.service 
systemctl stop firewalld.service 
systemctl disable firewalld.service

# 关闭 NetworkManager：

systemctl stop NetworkManager 
systemctl disable NetworkManager

③ 安装数据库：

# 配置数据库的 yum 源：

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 创建用户

(也可以不创建 postgres 用户，yum 安装会自动生成 postgres 用户，su 切换用户后会显示-bash-4.2$)：

useradd postgres

# 安装数据库：

yum install -y postgresql13-server

# 修改环境变量：

vim etc/profile 
export PATH=/usr/pgsql-13/bin/:$PATH 
source etc/profile

# root 用户下初始化数据库：

/usr/pgsql-13/bin/postgresql-13-setup initdb

# 默认初始化数据库路径：

/var/lib/pgsql/13/data

# 启动数据库：

systemctl enable postgresql-13 
systemctl start postgresql-13

rpm安装

下载位置

https://yum.postgresql.org/rpmchart/
postgresql13 - PostgreSQL client programs and libraries
postgresql13-contrib - Contributed source and binaries distributed with PostgreSQL
postgresql13-libs - The shared libraries required for any PostgreSQL clients
postgresql13-server - The programs needed to create and run a PostgreSQL server
rpm -ivh postgresql13*

初始化数据库：

创建一个 Database Cluster（初始化实例）

– 以OS用户运行initdb 创建数据库实例.

• $ su - postgres
• $ initdb –D <data directory>
– -D <data directory> - Database cluster directory 指定数据库目录
– -U <super user> - Select the database super user name 指定数据库默认超级管理员用户
– -E <encoding> - Specify the database encoding 指定数据库编码
--n 错误后不清理文件 
– -W, --pwprompt 初始化时给数据库创建密码
– -X, --waldir=WALDIR 预写日志目录的位置
– --wal-segsize=SIZE WAL段的大小（单位是M）默认是16M,最大为1024M

修改端口号：

默认端口 5432

修改 postgresql.conf 中的监听和端口，确保为每个实例分配了唯一端口

• listen_addresses = '*' # what IP address(es) to listen on;
• port = 5436 # (change requires restart)

– 重启数据库

启动和关闭：

① 使用 system 进行管理：

systemctl start postgresql-13 
systemctl enable postgresql-13 
systemctl status postgresql-13 
systemctl stop postgresql-13

② 使用 postgresql 自带的命令进行管理：

pg_ctl start 
pg_ctl stop 
pg_ctl status 
pg_ctl -D $PGDATA stop -m smart 
pg_ctl -D $PGDATA stop -m fast 
pg_ctl -D $PGDATA stop -m immediate

启动实例

[postgres@cjc01 data]$ pg_ctl -D pg/data -l pg/log/pg.log start

③ 如果启停有问题可以查看日志：

vim var/lib/pgsql/13/data/log/postgresql-Mon.log

启停

– 使用pg_ctl (推荐)

• Start $PGHOME/pg_ctl –D $PGDATA start
• Reload $PGHOME/pg_ctl –D $PGDATA reload
• Stop $PGHOME/pg_ctl –D $PGDATA –m smart | fast | immediate stop
• Restart $PGHOME/pg_ctl –D $PGDATA –m smart | fast | immediate restart

– -m smart 等待客户端断开连接（默认）
– -m fast 回滚未完成的事务，断开客户端连接
– -m immediate 强行终止进程，数据库没有干净的关闭

– 使用Service方式 (el6)

• etc/init.d/postgresql-11.x start | stop | restart | reload
• Service postgresql-11.x start | stop | restart | reload

– 使用systemctl （el7）

• systemctl start | stop | restart | relaod postgresql-11.x.service

• 启停注意事项

– 启动和停止时，尽量一边观察日志一边操作 （eg. $ tailf logfile.csv）
– 可以通过预先设置环境变量，减少出错及方便日常操作
• PATH 必须指向正确的 bin 目录.
• PGDATA 必须指向正确的数据库实例目录.
• PGPORT 必须指向正确的数据库实例运行的端口.
• 修改 .bash_profile或/etc/profile文件设置变量
• 前台启动
– postgres -D $PGDATA
• 前台变后台启动
– postgres -d $PGDATA >logfile 2>&1 &
Postgres,pg_ctl都会读取$PGDATA变量，所以要设置好这个变量

查看进程：

– 在Linux上作为一个后台进程 ，在Windows上作为一个服务
• Linux中查看postgres进程
ps -aux | grep postgres
netstat -natp|grep post （查看进程占用的端口号）
netstat -a | grep PGSQL （查看进程占用的端口号）
ss -tnlp | grep post（查看进程占用的端口号）

• 查看进程相关信息的命令：

leof -p pid
strace -o output.txt -T -tt -e trace=all -p 28979 (strace能帮助你追踪到一个程序所执行的系统调用)

登录数据库：

psql工工具

psql -U postgres -d pgbouncer -h 192.168.1.220 -p 6632
psql -h tar1 -p 5432 -d zdb -U pgccc –W

• -U：以哪个用户登录
• -W：强制输入密码
• -h：主机名（IP地址）
• -p：端口号
• -d：登录哪个数据库

# 查看版本信息：

psql –version 
select version();

安装后注意事项：

① OS 用户不能是 root 或具有操作系统管理权限的账号。
② 进行容量估算和规划：
# 存储空间及读写能力。
# 内存、cpu、网卡宽带。
③ 安装集群需要确认 OS 用户 UID 和 GID。
④ 配置时间同步：
# 服务端配置：
yum -y install ntp 
vim /etc/ntp.conf 
# 给与本机所有权限 
restrict 127.0.0.1 
restrict ::1 
# 授权下述网段上所有的机器允许从 ntp 服务器上查询和同步时间 
restrict 10.0.0.0 mask 255.255.255.0 nomodify notrap

配置文件：

① 配置文件的位置：

$ PGDATA/postgresql.conf

# 参数文件存在才会进行加载

include_if_exists = ''

# 加载目录下的参数文件

include_dir = ''

# 无论参数文件是否存在都会加载

include = ''

② 配置文件详解：

postgresql.auto.conf

# alter system 修改后的参数配置，会覆盖 postgresql.conf 的值

recovery.conf

# pg12 已经不存在这个文件，已经将此文件的参数合并到了 postgresql.conf 中

pg_hba.conf

# 客户端认证配置文件

pg_ident.conf

# 客户端认证映射文件

配置文件的位置

– $PGDATA/postgresql.conf
• 开启参数include_dir = 'conf.d'可使用$PGDATA/.conf.d/custom.conf
• 开启参数include_if_exists = 'exists.conf'并存在可使用$PGDATA/exists.conf
• 开启参数#include = 'special.conf’可使用$PGDATA/special.conf
• 数据库启动时，会读取该文件
• 可手工编辑
– $PGDATA/postgresql.auto.conf
• 保存ALTER SYSTEM修改后的参数( ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, Values
set with ALTER SYSTEM will be effective after the next server configuration reload)
• 不要手动修改它
• postgresql.auto.conf 的值覆盖postgresql.conf 的值（Settings in postgresql.auto.conf override those in postgresql.conf. ）
– $PGDATA/recovery.conf（pg12已经不存在这个文件，recovery.conf 配置文件中的参数合并到 postgresql.conf）
– $PGDATA/pg_hba.conf
– $PGDATA/pg_ident.conf
• postgresql.conf 配置文件结构
– #注释
– key = value
• 支持的参数值类型
– 布尔、整数、浮点数、字符串、枚举
– include指令（允许嵌套）

全局参数修改

• 修改参数的方法

– 通过linux命令修改配置文件vim,echo,sed

– 通过ALTER SYSTEM命令修改全局配置:

– 启动时设置（不推荐，除非进入单用户模式）

• psql -c configparameter=newvalue

• 如何查看参数

– 查询pg_settings系统表：

- SELECT name,setting FROM pg_settings where name ~ ‘xxx’;
– select name,setting,unit,short_desc from pg_settings where name like 'work_mem%';
– SELECT current_setting(name);
– SELECT current_setting('work_mem');

– 通过show (show all)命令查看

• 使参数生效的几种方法

– SELECT pg_reload_conf();
– pg_ctl -D $PGDATA reload;
– etc/init.d/postgresql-11.x reload; (el6)
– systemctl reload service.postgresql-11.x (el7)

示例：

① 全局参数的修改：

alter system set port=5433;

② 查看参数配置：

select name,setting from pg_settings where name = 'port';
select current_setting('port');
show port;

③ 修改参数后，使参数生效的方法：

select pg_reload_conf();
pg_ctl -D $PGDATA restart

数据库默认值：

设置在一个特定数据库中成为默认值，示例：

ALTER DATABASE mydb SET geqo TO off;

这样将保存该设置（但不是立即设置它）。

在后续建立的到该数据库的连接中它将表现得像在会话开始后马上调用SET geqo TO off;。

注意用户仍然可以在该会话中更改这个设置，它只是默认值。

要撤消这样的设置，使用ALTER DATABASE dbname RESET varname。

非全局参数修改

• 设置和重置Database级别的配置

– ALTER DATABASE name SET configparameter { TO | = } { value | DEFAULT }
– ALTER DATABASE name RESET configuration

• 设置和重置Session级别的配置

– 通过SET命令设置当前Session的配置

• SET configparameter { TO | = } { value | 'value' | DEFAULT }
• SET configparameter TO DEFAULT;

– 更新pg_settings视图

• UPDATE pg_settings SET setting = new_value WHERE name = 'configparameter';
• UPDATE pg_settings SET setting = reset_val WHERE name = 'configparameter';

– 使用set_config函数更新会话配置

• SELECT set_config('configparameter',new_value,false);

• Role级别的配置

– ALTER ROLE name IN DATABASE database_name SET configparameter { TO | = } { value | DEFAULT }
– ALTER ROLE name IN DATABASE database_name RESET configparameter

示例：

① 库级别修改和重置：

alter database postgres set timezone = 'utf8';
alter database postgres reset timezone;

② session 级别修改和重置：

set timezone = 'utf8';
reset timezone;

③ role 级别的配置：

alter role postgres in database postgres set timezone = 'utf8';
alter role postgres in database postgres reset timezone;

PG防火墙

pg_hba格式

 TYPE  DATABASE  USER  ADDRESS  METHOD

• 由pg_hba.conf决定

– 哪些主机可以连接数据库实例

– 客户端使用什么连接方式和认证方式

• 连接方式(TYPE)：local,host,hostssl,hostnossl

• 认证方式：trust, reject, md5, password, scram, gss, sspi, ident, peer, pam, ldap, radius or cert

– 哪个数据库用户可以使用它

– 允许这个用户使用哪些数据库

– find -name "postgresql.conf"

• 七种格式

– # TYPE DATABASE USER ADDRESS METHOD
– local database user auth-method [auth-options]
– host database user address auth-method [auth-options]
– hostssl database user address auth-method [auth-options]
– hostnossl database user address auth-method [auth-options]
– host database user IP-address IP-mask auth-method [auth-options]
– hostssl database user IP-address IP-mask auth-method [auth-options]
– hostnossl database user IP-address IP-mask auth-method [auth-options]

第一列：连接方式

• local匹配使用Unix域套接字的连接。

– 如果没有TYPE为local的条目则不允许通过Unix域套接字连接

• host匹配使用 TCP/IP建立的连接，同时匹配SSL和非SSL连接

– 缺省安装只监听本地环回地址localhost的连接，不允许使用TCP/IP远程连接，启

用远程连接需要修改postgresql.conf中的listen_addresses参数。

• hostssl匹配必须是使用SSL的TCP/IP连接

– 客户端和服务器端都安装OpenSSL

– 编译PostgreSQL的时候指定configure参数--with-openssl打开SSL支持

– 在postgresql.conf中配置ssl = on

• hostnossl只匹配使用非SSL的TCP/IP连接

第二列：目标数据库

DATABASE 列标识该行设置对哪个数据库生效。

第三列：目标用户

USER 列标识该行设置对哪个数据库用户生效。

第四列：访问来源

ADDRESS 列标识该行设置对哪个 IP 地址或 IP 地址段生效。

第五列：认证方法

• trust

– 无条件地允许连接。

这种方法允许任何可以与PostgreSQL数据库服务器连接的用户以他们期望的任意PostgreSQL数据库用户身份登入,而不需要口令或者其他任何认证。

• reject

– 无条件拒绝连接。

常用于从一个组中“过滤出”特定主机,例如一个reject行可以阻塞一个特定的主机连接,而后面一行允许一个特定网络中的其余主机进行连。

• md5

– md5认证方式为双重md5加密

执行SCRAM-SHA-256或MD5认证来验证用户的口令。

• password口令认证

– password指明文密码，所以不能在非信任网络使用password方式

(Require the client to supply an unencrypted password for authentication.

Since the password is sent in clear text over the network, this should not be used on untrusted networks)。

• peer

– 从操作系统获得客户端的操作系统用户,并且检查它是否匹配被请求的数据库用户名。这只对本地连接可用。

• scram-sha-256

– Perform SCRAM-SHA-256 authentication to verify the user's password.。

postgresql10 中新增最安全的加密方式。

执行SCRAM-SHA-256认证来验证用户的口令

• ident

用户映射文件

pg_ident.conf 配置文件详细说明

例子：当 OS 用户是 zhang 时，允许它以数据库用户 postgres 进行连接：

vim pg_hba.conf
local all all ident
map=yingshe
vim pg_ident.conf
yingshe zhang postgres

– 其它认证方式

– https://www.postgresql.org/docs/current/static/auth-methods.html

示例：

如果要使所有可提供有效密码的用户均能从以 100 开头的地址连接到 PostgreSQL 实例中的任何数据库，则需要向 pg_hba.conf 文件添加与与下面的行内容类似的行：

host all all 100.0.0.0/8 md5

上述示例针对 IPV4 地址。如果需要配置数据库群集以接受具有 IPV6 地址的计算机的连接，那么格式应如下所示：

host all all fe60::61f9:1253:c522:9b92%10/128 md5

要允许所有具有以 fe60 开始的地址的计算机进行连接，应向 pg_hba.conf 文件添加以下行：

host all all fe60::0000:0000:0000:0000%10/8 md5

如果您的 PostgreSQL 数据库集群安装在具有 IPV6 地址的计算机上，而您想要使具有 IPV4 地址的计算机与其连接，则需要在 pg_hba.conf 文件中取消以下行的注释：

host all all ::1/128 md5

允许IP地址为10.10.56.17的所有用户可以通过MD5的密码验证方式连接主机上所有的数据库

# TYPE DATABASE USER ADDRESS METHOD
host all all 10.10.56.17/32 md5

即表示允许地址为 10.10.56.17 的用户 pgtest通过 MD5方式加密的密码方式连接主机上的 test 数据库

# TYPE DATABASE USER ADDRESS METHOD
host test pgtest 10.10.56.17/32 md5

即表示允许任意iP 通过用户名为 pgtest 和md5的密码验证方式连接主机上 test 的数据库

# TYPE DATABASE USER ADDRESS METHOD
host test pgtest 0.0.0.0/0 md5

表示任意IP地址的用户 pgtest 无需密码验证可直接连接访问该主机的 test 数据库

# TYPE DATABASE USER ADDRESS METHOD
host test pgtest 0.0.0.0/0 trust

客户端认证

客户端认证是由一个配置文件（通常名为pg_hba.conf并被存放在数据库集簇目录中）控制（HBA表示基于主机的认证）。

在initdb初始化数据目录时，它会安装一个默认的pg_hba.conf文件。

不过我们也可以把认证配置文件放在其它地方；

参阅hba_file配置参数。

口令认证方法：

scram-sha-256

使用的是一种挑战-响应的方案，可以防止在不可信连接上对口令的嗅探并且支持在服务器上以一种加密哈希的方式存放口令，因此被认为是安全的。

这是当前提供的方法中最安全的一种，但是旧的客户端库不支持这种方法。

md5

方法md5使用一种自定义的安全性较低的挑战-响应机制。

它能防止口令嗅探并且防止口令在服务器上以明文存储，但是无法保护攻击者想办法从服务器上窃取了口令哈希的情况。

此外，现在认为MD5哈希算法对于确定攻击已经不再安全。

md5方法不能与db_user_namespace特性一起使用。

为了简化从md5方法到较新的SCRAM方法的转变，如果在pg_hba.conf中指定了md5但是用户在服务器上的口令是为SCRAM（见下文）加密的，则将自动选择基于SCRAM的认证。

password

方法password以明文形式发送口令，因此它对于口令“嗅探”攻击很脆弱。

如果可能应该尽量避免使用它。

不过，如果连接被SSL加密保护着，那么可以安全地使用password（不过如果依靠SSL，SSL证书认证可能是更好的选择）。

查看没有用SCRAM加密口令的用户

create user foo password 'foopassword';
select usename,passwd from pg_shadow where passwd not like 'SCRAM%' or passwd is null;
select usename,passwd from pg_shadow ;

查看现在的加密方法

postgres=# show password_encryption;

修改加密方法

postgres=# alter system set password_encryption = 'scram-sha-256';
postgres=# show password_encryption;

重新加载配置文件

postgres=# select pg_reload_conf();

修改密码

postgres=# alter user foo password ' 'Xzzp2008 ';
The password_encryption parameter decides how the ALTER USER statement will encrypt the password

修改pg_hba.conf 本地连接的method方法为scram-sha-256

# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
host all all 0.0.0.0/0 scram-sha-256
postgres=# select pg_reload_conf();

远程登录

psql -h 192.168.1.221 -p 5433 -d postgres -U foo -W

有一个插件，可以用于加强口令复杂度管理

passwordcheck
http://www.postgresql.org/docs/current/static/passwordcheck.html

客户端工具

• pgAdmin 4
– https://www.pgadmin.org
– yum  apt install pgadmin
• other
– Navicat , DBeaver ....

psql

客户端工具

– 连接数据库：
psql -h localhost -p 5432 database_name
– 获得psql的帮助：\?
– 获得语法的帮助：\h STATEMENT
– 在shell中执行命令：psql -c "STATEMENT"
– 通过psql执行sql文件：psql < f.sql
\? 获得 psql 的帮助
\h 获取语法的帮助
psql -c 在 shell 中执行命令
psql < 通过 psql 执行 sql 文件，用来恢复数据
\l 查看有哪些数据库
\c 用于切换数据库
\d 显示每个匹配关系（表，视图，索引，序列）的信息
\d [ pattern ] +
\d 后面跟一个表名，表示显示表结构定义
\d 后跟一个索引名，显示索引的信息
\d 后面跟一个视图名，显示视图信息
\timing on 显示SQL执行的时间
\timing off 关闭计时功能
\dn 列出所有的schema
\db 显示所有的表空间
\du\dg 列出所有的角色或者用户
\dp 显示权限分配情况
\x 行列互换显示
\set AUTOCOMMIT off将自动提交功能关闭

数据库管理

创建数据库

2种方法

1. Using CREATE DATABASE, an SQL command.
postgres=# CREATE DATABASE testdb;
2. Using createdb a command-line executable.
[postgres@tar1 pgdata]$ createdb testdb
createdb -U postgres test

当前角色自动成为该新数据库的拥有者。

以后删除这个数据库也是该拥有者的特权（同时还会删除其中的所有对象，即使那些对象有不同的拥有者）。

为其他用户创建数据库

CREATE DATABASE dbname OWNER rolename;

或者用于 shell 的

createdb -O rolename dbname

只有超级用户才被允许为其他人（即为一个你不是其成员的角色）创建一个数据库

将创建一个使用EUC_KR字符集和ko_KR区域的名为korean的数据库。

createdb -E EUC_KR -T template0 --lc-collate=ko_KR.euckr --lc-ctype=ko_KR.euckr korean

另外一种实现方法是使用 SQL 命令：

CREATE DATABASE korean WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;

• 登录数据库

psql testdb

postgres数据库：

由initdb命令在初始化数据存储区域时创建的，是第一个数据库。

template1数据库：

在数据库集簇初始化期间也会创建第二个数据库template1。

当在集簇中创建一个新数据库时，实际上就是克隆了template1。

这就意味着你对template1所做的任何修改都会体现在所有随后创建的数据库中。

因此应避免在template1中创建对象，除非你想把它们传播到每一个新创建的数据库中。

删除数据库

1. Using CREATE DATABASE, an SQL command.
postgres=# DROP DATABASE testdb;
2. Using createdb a command-line executable.
[postgres@tar1 pgdata]$ dropdb testdb

体系结构

数据库集群的概念

Database Cluster

• 一个数据库集群（database cluster）=一个数据库实例（简称“实例”）

• 由数据目录组成，目录中包含了所有的数据文件和配置文件

• 通过两种方式引用：

– 数据目录的位置

– 端口号

• 一个服务器可以管理多个数据库实例

• PostgreSQL多实例和MySQL多实例

• 进程树(pstree -p 13524)

内存结构

shared_buffers:共享内存

work_mem:当使用 order by 或 distinct 操作对元组仅从排序时会使用这部分内存

wal_buffer:wal 缓存

后台进程

进程查看

ps -ef |grep post

进程说明

background processes
background writer:
进程将shared buffer pool中的脏数据写到磁盘，检查点总能触发这个进程


checkpointer:
检查点会触发产生这个进程


autovacuum launcher:
为vacuum process周期性的调用autovacuum work processes。
autovacuum 的守护进程，周期性调用 autovacuum worker 进程。


autovacuum 的作用：
① 删除或重用无效元组的磁盘空间
② 更新数据统计信息，保证执行计划更优
③ 更新 visibility map，加速 index-only scans
④ 避免 XID 回卷造成的数据丢失


WAL writer
周期性的从wal buffer刷新数据到磁盘


statistics collector
收集统计信息进程，比如pg_stat_activity 和pg_stat_database的数据
收集统计信息进程（表和索引进行了多少次插入，更新，删除操作，磁盘块读写次数及行的读写次数)


logging collector (logger)
将错误信息写入到日志


archiver
将日志归档的进程.


postgremaster
监听。

物理结构

数据目录

base:表和索引文件存放目录
global:影响全局的系统表存放目录
pg_commit_ts:事务提交时间戳数据存放目录
pg_stat:统计子系统信息永久文件
pg_wal:事务日志（预写日志)

相关文件

PG_VERSION:版本号文件
pg_hba.conf:客户端认证控制文件
postgresql.conf:参数文件
postgresql.auto.conf:参数文件，只保存 ALTER SYSTEM 命令修改的参数，类似Oracle的spfile
postmaster.opts:记录服务器最后一次启动时使用的命令行参数
pg_ident.conf:控制 postgresql 用户名映射文件
postmaster.pid:记录数据库进程编号、PGDATA、端口等

表空间

PostgreSQL中的表空间允许数据库管理员在文件系统中定义用来存放表示数据库对象的文件的位置。

一旦被创建，表空间就可以在创建数据库对象时通过名称引用。

通过使用表空间，管理员可以控制一个PostgreSQL安装的磁盘布局。

这么做至少有两个用处。

首先，如果初始化集簇所在的分区或者卷用光了空间，而又不能在逻辑上扩展或者做别的什么操作，那么表空间可以被创建在一个不同的分区上，直到系统可以被重新配置。

其次，表空间允许管理员根据数据库对象的使用模式来优化性能。

例如，一个很频繁使用的索引可以被放在非常快并且非常可靠的磁盘上，如一种非常贵的固态设备。

同时，一个很少使用的或者对性能要求不高的存储归档数据的表可以存储在一个便宜但比较慢的磁盘系统上。

警告

即便是位于主要的 PostgreSQL 数据目录之外，表空间也是数据库集簇的一部分并且不能被视作数据文件的一个自治集合。

它们依赖于包含在主数据目录中的元数据，并且因此不能被附加到一个不同的数据库集簇或者单独备份。

类似地，如果丢失一个表空间（文件删除、磁盘失效等），数据库集簇可能会变成不可读或者无法启动。

把一个表空间放在一个临时文件系统（如一个内存虚拟盘）上会带来整个集簇的可靠性风险。

表空间分类：

默认表空间：pg_default ，是用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。对应文件系统目录$PADATA/base/。

pg_default表空间是template1和template0数据库的默认表空间（并且，因此也将是所有其他数据库的默认表空间，除非被一个CREATE DATABASE中的TABLESPACE子句覆盖）。

系统字典表表空间：pg_global，存放系统字典表，如pg_database、pg_authid、pg_tablespace等表以及它们的索引。对应文件系统目录$PADATA/global/。

自定义表空间：用户创建的表空间。对应文件系统目录$PADATA/pg_tblspc/，当手动创建表空间时，该目录下会自动生成一个软链接，指向表空间设定的路径。

其中pg_default和pg_global是在PG集群initdb之后默认创建的。

查询表空间

psql程序的\db元命令也可以用来列出现有的表空间。

SELECT spcname FROM pg_tablespace;
pg_default
pg_global

创建表空间

CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
CREATE TABLE foo(i int) TABLESPACE space1;

另外，还可以使用default_tablespace参数：

SET default_tablespace = space1;
CREATE TABLE foo(i int);

模板数据库

CREATE DATABASE实际上通过拷贝一个已有数据库进行工作。

默认情况下，它拷贝名为template1的标准系统数据库。

所以该数据库是创建新数据库的“模板”。

如果你为template1数据库增加对象，这些对象将被拷贝到后续创建的用户数据库中。

这种行为允许对数据库中标准对象集合的站点本地修改。

例如，如果你把过程语言PL/Perl安装到template1中，那么你在创建用户数据库后不需要额外的操作就可以使用该语言。

系统里还有名为template0的第二个标准系统数据库。

这个数据库包含和template1初始内容一样的数据，也就是说，只包含你的PostgreSQL版本预定义的标准对象。

在数据库集簇被初始化之后，不应该对template0做任何修改。

通过指示CREATE DATABASE使用template0取代template1进行拷贝，你可以创建一个“原始的”用户数据库（其中不存在用户定义的对象，并且系统对象没有被改变），它不会包含任何template1中的站点本地附加物。

这一点在恢复一个pg_dump转储时非常方便：转储脚本应该在一个原始的数据库中恢复以确保我们重建被转储数据库的正确内容，而不和任何现在可能已经被加入到template1中的附加物相冲突。

另一个从template0而不是template1复制的常见原因是，可以在复制template0时指定新的编码和区域设置，而一个template1的副本必须使用和它相同的设置。

这是因为的template1可能包含编码相关或区域相关的数据，而template0中没有。

要通过拷贝template0来创建一个数据库，使用：SQL 环境中的

CREATE DATABASE dbname TEMPLATE template0;

或者 shell 中的

createdb -T template0 dbname

可以创建额外的模板数据库，并且实际上可以通过将集簇中任意数据库指定为CREATE DATABASE的模板来从该数据库拷贝。

不过，我们必需明白，这个功能并不是设计作为一般性的“COPY DATABASE”功能。

主要的限制是当源数据库被拷贝时，不能有其他会话连接到它。

如果在CREATE DATABASE开始时存在任何其它连接，那么该命令将会失败。在拷贝操作期间，到源数据库的新连接将被阻止。

对于每一个数据库在pg_database中存在两个有用的标志：

datistemplate和datallowconn列。

datistemplate可以被设置来指示该数据库是不是要作为CREATE DATABASE的模板。

如果设置了这个标志，那么该数据库可以被任何有 CREATEDB权限的用户克隆；

如果没有被设置，那么只有超级用户和该数据库的拥有者可以克隆它。

如果datallowconn为假，那么将不允许与该数据库建立任何新的连接（但已有的会话不会因为把该标志设置为假而被中止）。

template0通常被标记为datallowconn = false来阻止对它的修改。

template0和template1通常总是被标记为datistemplate = true

注意

除了template1是CREATE DATABASE的默认源数据库名之外， template1和template0没有任何特殊的状态。

例如，我们可以删除template1然后从template0重新创建它而不会有任何不良效果。

如果我们不小心在template1中增加了一堆垃圾，那么我们就会建议做这样的操作（要删除template1，它必须有pg_database.datistemplate =false）。

当数据库集簇被初始化时，也会创建postgres数据库。

这个数据库用于做为用户和应用连接的默认数据库。

它只是 template1的一个拷贝，需要时可以删除并重建。

故障排查

• 查看操作系统错误日志

/var/log/message

系统启动后的信息和错误日志，是Red Hat Linux中最常用的日志之一

• 查看数据库错误日志

查看文件$PGDATA/log

默认情况下，一天产生一个日志

备份和恢复

物理备份：

pg_basebackup 系统级别的磁盘备份

归档模式，自动执行pg_start_backup()和pg_stop_backup()函数。15版本改名了。

备份速度比手动备份快。

全量备份

① 模拟环境：

# 创建数据：

create table t1(id int not null primary key,name varchar(20) not null);
insert into t1 values(1,'zhnagsan'),(2,'lisi');

进行备份：

pg_basebackup -h 10.0.0.62 -D PGCCC/backup -P -p 5432 -U postgres

③ 模拟数据库故障：

pg_ctl -D usr/local/pgsql/data/ stop
rm -rf usr/local/pgsql/data/*

④ 进行数据恢复：

cd usr/local/pgsql/backup/
tar xf base.tar.gz -C ../data

全备结合时间点进行恢复：

# 创建归档目录：

mkdir -p PGCCC/archive
chown -R postgres. PGCCC/

# 开启归档，并重启数据库：

wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p PGCCC/archive/%f'

# wal_level 参数说明：

· minimal 记录 wal 最少，记录数据库异常关闭需要恢复的 wal 外，其它操作都不记录。

· replica 在 minimal 的基础上还支持 wal 归档、复制和备库中启用只读查询等操作所需的 wal 信息。

· logical 记录 wal 日志信息最多，包含了支持逻辑解析（10 版本的新特性，逻辑复制使用这种模式）所需的 wal，此参数包含了 minimal 和 replica 所有的记录。

# 重启数据库：

pg_ctl restart

# 执行全量备份:

pg_basebackup -D pgccc/backup -P -p 5432 -U postgres

# 如果此时的数据有变化，也可以执行增量备份:

pg_receivewal -D pgccc/backup/ -p 5432

# 插入数据：

create table a(id int);
insert into a values (2);
select current_timestamp;
select pg_switch_wal();checkpoint;

# 模拟数据丢失：

drop table a;

# 启动备份实例，进行数据恢复：

vim postgresql.conf
recovery_target_time = '2021-07-03 11:55:41.543904+08'
restore_command='cp pgccc/archive/%f %p'
recovery_target_action = 'promote'

# 创建备库标签：

touch recovery.signal

# 启动数据库：

pg_ctl -D pgccc/backup start

# 关闭读模式：

select pg_wal_replay_resume();

全量结合还原点进行恢复

# 创建归档目录：

mkdir -p pgccc/archive
chown -R postgres. pgccc/

# 开启归档，并重启数据库：

wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p pgccc/archive/%f'

# 重启数据库：

pg_ctl restarat

# 执行全量备份:

pg_basebackup -h 10.0.0.62 -D PGCCC/backup -P -p 5432 -U postgres

如果有变化的数据，可以执行增量备份:

pg_receivewal -D pgccc/backup/ -p 5432

# 插入数据：

create table a(id int);
insert into a values (2);

# 创建还原点：

select pg_create_restore_point('huanyuan');

# 模拟数据丢失：

drop table a;

# 启动备份实例，进行数据恢复：

vim postgresql.conf
recovery_target_name = 'huanyuan'
restore_command='cp pgccc/archive/%f %p'
recovery_target_action = 'promote'
touch recovery.signal

# 启动数据库：

pg_ctl -D pgccc/backup start

# 关闭读模式：

select pg_wal_replay_resume();

全备结合指定事务进行恢复：

# 创建归档目录：

mkdir -p pgccc/archive
chown -R postgres. pgccc/

# 开启归档，并重启数据库：

wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p pgccc/archive/%f'

# 重启数据库：

pg_ctl restarat

# 执行全量备份:

pg_basebackup -h 10.0.0.62 -D PGCCC/backup -P -p 5432 -U postgres

# 如果有变化的数据，可以执行增量备份:

pg_receivewal -D pgccc/backup/ -p 5432

# 插入数据：

create table a(id int);
insert into a values (2);
select pg_current_xact_id();
select pg_switch_wal();checkpoint;

# 模拟数据丢失：

drop table a;

# 启动备份实例，进行数据恢复：

vim postgresql.conf
recovery_target_xid = '487'
restore_command='cp pgccc/archive/%f %p'
recovery_target_action = 'promote'
touch recovery.signal

# 启动数据库：

pg_ctl -D pgccc/backup start

# 关闭读模式：

select pg_wal_replay_resume();

全备结合 LSN 号码进行恢复：

# 创建归档目录：

mkdir -p pgccc/archive
chown -R postgres. pgccc/

# 开启归档，并重启数据库：

wal_level = 'replica'
archive_mode = 'on'
archive_command = 'cp %p pgccc/archive/%f'

# 重启数据库：

pg_ctl restarat

# 执行全量备份:

pg_basebackup -h 10.0.0.62 -D PGCCC/backup -P -p 5432 -U postgres

# 如果有变化的数据，可以执行增量备份:

pg_receivewal -D pgccc/backup/ -p 5432

# 插入数据：

create table a(id int);
insert into a values (2);
select pg_current_wal_lsn();
select pg_switch_wal();checkpoint;

# 模拟数据丢失：

drop table a;

# 启动备份实例，进行数据恢复：

vim postgresql.conf
recovery_target_lsn = '0/4011BF8'
restore_command='cp pgccc/archive/%f %p'
recovery_target_action = 'promote'
touch recovery.signal

# 启动数据库：

pg_ctl -D pgccc/backup start

# 关闭读模式：

select pg_wal_replay_resume();

逻辑备份：

1.psql，用于从纯SQL脚本文件进行恢复(文本文件）使用pg_dump创建，

2.pg_restore，用于从用pg_dump创建的.tar文件、目录或自定义格式进行恢复。

（如果在创建备份文件时选择自定义、目录或存档格式，则需要使用pg_restore恢复数据库)

pg_dump

pg_dump 的使用：

① 备份及恢复库下所有的数据：

# 备份：

pg_dump -U postgres test >test.sql

# 恢复：

psql
create database test;
psql -U postgres test < test.sql

# 指定格式进行备份：

# 备份：

pg_dump -Fc -U postgres test >test.dmp
pg_dump -d jdbc -h localhost -p 5432 -U postgres -f home/sql/test.dmp jdbc

# 恢复：

psql
create database test;
pg_restore -d test test.dmp

② 备份库下某个模式所有的表：

# 备份：

pg_dump -U postgres -t 'schema1.t*' test >test.sql

# 恢复：

psql -U postgres test < test.sql

恢复数据到bk01数据库

psql -U postgres -f postgres.sql bk01

③ 备份单个表：

# 例如备份 test 库下的 zhang 表：

# 备份：

pg_dump -t 表名 库名 >dump.sql

# 恢复：

create database test;
psql -U postgres test < dump.sql

pg_dumpall

pg_dump每次只转储一个数据库，而且它不会转储关于角色或表空间（因为它们是集簇范围的）的信息。

为了支持方便地转储一个数据库集簇的全部内容，提供了pg_dumpall程序。

pg_dumpall备份一个给定集簇中的每一个数据库，并且也保留了集簇范围的数据，如角色和表空间定义。

# 全库级别备份：

# 备份：

pg_dumpall -U postgres > dumpall.sql

# 恢复：

psql -U postgres < dumpall.sql

导出pg_dump的选项说明

导出（ pg_dump ）

-U to specify which user will connect to the PostgreSQL database server.
-W or --password will force pg_dump to prompt for a password before connecting to the server.
-F is used to specify the format of the output file, which can be one of the following
（导出的4种格式）:
p – plain-text SQL script
c – custom-format archive (支持压缩)
d – directory-format archive (支持压缩)
t – tar-format archive(不支持压缩)

pg_dump自带压缩功能

导入的2种方式：

导入

1.psql for restoring from a plain SQL script file（文本文件） created with pg_dump,
2.pg_restore for restoring from a .tar file, directory, or custom format created with pg_dump.
（If you choose custom, directory, or archive format when creating a backup file, then you will need to use pg_restore in order torestore your database:

pg_dumpall

pg_dumpall 是一个用于写出（"转储"）一个数据库集群里的所有 PostgreSQL 数据库到一个脚本文件的工具。

pg_dumpall调用pg_dump。

pg_dumpall还转储出所有数据库公用的全局对象。这些信息目前包括数据库用户和组，以及适用于整个数据库的访问权限。

pg_dumpall无法转储"大对象"，因为 pg_dump无法把这样的对象转储到纯文本文件中。

如果你的数据库里有大对象，那么你应该使用 pg_dump 的非文本输出格式之一转储它们。

pg_dumpall > all_DB

附录

常见命令参数说明

1.pg_basebackup

[postgres@cjc01 ~]$ pg_basebackup --help
pg_basebackup takes a base backup of a running PostgreSQL server.




Usage:
  pg_basebackup [OPTION]...




Options controlling the output:
  -D, --pgdata=DIRECTORY receive base backup into directory
  -F, --format=p|t       output format (plain (default), tar)
  -r, --max-rate=RATE    maximum transfer rate to transfer data directory
                         (in kB/s, or use suffix "k" or "M")
  -R, --write-recovery-conf
                         write configuration for replication
  -T, --tablespace-mapping=OLDDIR=NEWDIR
                         relocate tablespace in OLDDIR to NEWDIR
      --waldir=WALDIR    location for the write-ahead log directory
  -X, --wal-method=none|fetch|stream
                         include required WAL files with specified method
  -z, --gzip             compress tar output
  -Z, --compress=0-9     compress tar output with given compression level




General options:
  -c, --checkpoint=fast|spread
                         set fast or spread checkpointing
  -C, --create-slot      create replication slot
  -l, --label=LABEL      set backup label
  -n, --no-clean         do not clean up after errors
  -N, --no-sync          do not wait for changes to be written safely to disk
  -P, --progress         show progress information
  -S, --slot=SLOTNAME    replication slot to use
  -v, --verbose          output verbose messages
  -V, --version          output version information, then exit
      --manifest-checksums=SHA{224,256,384,512}|CRC32C|NONE
                         use algorithm for manifest checksums
      --manifest-force-encode
                         hex encode all file names in manifest
      --no-estimate-size do not estimate backup size in server side
      --no-manifest      suppress generation of backup manifest
      --no-slot          prevent creation of temporary replication slot
      --no-verify-checksums
                         do not verify checksums
  -?, --help             show this help, then exit




Connection options:
  -d, --dbname=CONNSTR   connection string
  -h, --host=HOSTNAME    database server host or socket directory
  -p, --port=PORT        database server port number
  -s, --status-interval=INTERVAL
                         time between status packets sent to server (in seconds)
  -U, --username=NAME    connect as specified database user
  -w, --no-password      never prompt for password
  -W, --password         force password prompt (should happen automatically)




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

2.pg_restore

[postgres@cjc01 ~]$ pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.




Usage:
  pg_restore [OPTION]... [FILE]




General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name (- for stdout)
  -F, --format=c|d|t       backup file format (should be automatic)
  -l, --list               print summarized TOC of the archive
  -v, --verbose            verbose mode
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit




Options controlling the restore:
  -a, --data-only              restore only the data, no schema
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 create the target database
  -e, --exit-on-error          exit on error, default is to continue
  -I, --index=NAME             restore named index
  -j, --jobs=NUM               use this many parallel jobs to restore
  -L, --use-list=FILENAME      use table of contents from this file for
                               selecting/ordering output
  -n, --schema=NAME            restore only objects in this schema
  -N, --exclude-schema=NAME    do not restore objects in this schema
  -O, --no-owner               skip restoration of object ownership
  -P, --function=NAME(args)    restore named function
  -s, --schema-only            restore only the schema, no data
  -S, --superuser=NAME         superuser user name to use for disabling triggers
  -t, --table=NAME             restore named relation (table, view, etc.)
  -T, --trigger=NAME           restore named trigger
  -x, --no-privileges          skip restoration of access privileges (grant/revoke)
  -1, --single-transaction     restore as a single transaction
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security
  --if-exists                  use IF EXISTS when dropping objects
  --no-comments                do not restore comments
  --no-data-for-failed-tables  do not restore data of tables that could not be
                               created
  --no-publications            do not restore publications
  --no-security-labels         do not restore security labels
  --no-subscriptions           do not restore subscriptions
  --no-tablespaces             do not restore tablespace assignments
  --section=SECTION            restore named section (pre-data, data, or post-data)
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership




Connection options:
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before restore




The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified
multiple times to select multiple objects.




If no input file name is supplied, then standard input is used.




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

3.pg_dump

[postgres@cjc01 ~]$ pg_dump --help
pg_dump dumps a database as a text file or to other formats.




Usage:
  pg_dump [OPTION]... [DBNAME]




General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   show this help, then exit




Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -e, --extension=PATTERN      dump the specified extension(s) only
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=PATTERN         dump the specified schema(s) only
  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=PATTERN          dump the specified table(s) only
  -T, --exclude-table=PATTERN  do NOT dump the specified table(s)
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --include-foreign-data=PATTERN
                               include data of foreign tables on foreign
                               servers matching PATTERN
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --no-tablespaces             do not dump tablespace assignments
  --no-toast-compression       do not dump TOAST compression methods
  --no-unlogged-table-data     do not dump unlogged table data
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  --quote-all-identifiers      quote all identifiers, even if not key words
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership




Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump




If no database name is supplied, then the PGDATABASE environment
variable value is used.




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

4.pg_dumpall

[postgres@cjc01 ~]$ pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.




Usage:
  pg_dumpall [OPTION]...




General options:
  -f, --file=FILENAME          output file name
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit




Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -g, --globals-only           dump only global objects, no databases
  -O, --no-owner               skip restoration of object ownership
  -r, --roles-only             dump only roles, no databases or tablespaces
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in the dump
  -t, --tablespaces-only       dump only tablespaces, no databases or roles
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --exclude-database=PATTERN   exclude databases whose name matches PATTERN
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-role-passwords          do not dump passwords for roles
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-sync                    do not wait for changes to be written safely to disk
  --no-tablespaces             do not dump tablespace assignments
  --no-toast-compression       do not dump TOAST compression methods
  --no-unlogged-table-data     do not dump unlogged table data
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  --quote-all-identifiers      quote all identifiers, even if not key words
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership




Connection options:
  -d, --dbname=CONNSTR     connect using connection string
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump




If -f/--file is not used, then the SQL script will be written to the standard
output.




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

5.createdb

[postgres@cjc01 ~]$ createdb --help
createdb creates a PostgreSQL database.




Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]




Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit




Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database




By default, a database with the same name as the current user is created.




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

6.dropdb

[postgres@cjc01 bin]$ dropdb --help
dropdb removes a PostgreSQL database.




Usage:
  dropdb [OPTION]... DBNAME




Options:
  -e, --echo                show the commands being sent to the server
  -f, --force               try to terminate other connections before dropping
  -i, --interactive         prompt before deleting anything
  -V, --version             output version information, then exit
  --if-exists               don't report error if database doesn't exist
  -?, --help                show this help, then exit




Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt
  --maintenance-db=DBNAME   alternate maintenance database




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

7.initdb

[postgres@cjc01 ~]$ initdb --help
initdb initializes a PostgreSQL database cluster.




Usage:
  initdb [OPTION]... [DATADIR]




Options:
  -A, --auth=METHOD         default authentication method for local connections
      --auth-host=METHOD    default authentication method for local TCP/IP connections
      --auth-local=METHOD   default authentication method for local-socket connections
 [-D, --pgdata=]DATADIR     location for this database cluster
  -E, --encoding=ENCODING   set default encoding for new databases
  -g, --allow-group-access  allow group read/execute on data directory
  -k, --data-checksums      use data page checksums
      --locale=LOCALE       set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
                            set default locale in the respective category for
                            new databases (default taken from environment)
      --no-locale           equivalent to --locale=C
      --pwfile=FILE         read password for the new superuser from file
  -T, --text-search-config=CFG
                            default text search configuration
  -U, --username=NAME       database superuser name
  -W, --pwprompt            prompt for a password for the new superuser
  -X, --waldir=WALDIR       location for the write-ahead log directory
      --wal-segsize=SIZE    size of WAL segments, in megabytes




Less commonly used options:
  -d, --debug               generate lots of debugging output
      --discard-caches      set debug_discard_caches=1
  -L DIRECTORY              where to find the input files
  -n, --no-clean            do not clean up after errors
  -N, --no-sync             do not wait for changes to be written safely to disk
      --no-instructions     do not print instructions for next steps
  -s, --show                show internal settings
  -S, --sync-only           only sync data directory




Other options:
  -V, --version             output version information, then exit
  -?, --help                show this help, then exit




If the data directory is not specified, the environment variable PGDATA
is used.




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

8.createuser

[postgres@cjc01 bin]$ createuser --help
createuser creates a new PostgreSQL role.




Usage:
  createuser [OPTION]... [ROLENAME]




Options:
  -c, --connection-limit=N  connection limit for role (default: no limit)
  -d, --createdb            role can create new databases
  -D, --no-createdb         role cannot create databases (default)
  -e, --echo                show the commands being sent to the server
  -g, --role=ROLE           new role will be a member of this role
  -i, --inherit             role inherits privileges of roles it is a
                            member of (default)
  -I, --no-inherit          role does not inherit privileges
  -l, --login               role can login (default)
  -L, --no-login            role cannot login
  -P, --pwprompt            assign a password to new role
  -r, --createrole          role can create new roles
  -R, --no-createrole       role cannot create roles (default)
  -s, --superuser           role will be superuser
  -S, --no-superuser        role will not be superuser (default)
  -V, --version             output version information, then exit
  --interactive             prompt for missing role name and attributes rather
                            than using defaults
  --replication             role can initiate replication
  --no-replication          role cannot initiate replication
  -?, --help                show this help, then exit




Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as (not the one to create)
  -w, --no-password         never prompt for password
  -W, --password            force password prompt




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

9.dropuser

[postgres@cjc01 bin]$ dropuser --help
dropuser removes a PostgreSQL role.




Usage:
  dropuser [OPTION]... [ROLENAME]




Options:
  -e, --echo                show the commands being sent to the server
  -i, --interactive         prompt before deleting anything, and prompt for
                            role name if not specified
  -V, --version             output version information, then exit
  --if-exists               don't report error if user doesn't exist
  -?, --help                show this help, then exit




Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as (not the one to drop)
  -w, --no-password         never prompt for password
  -W, --password            force password prompt




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

10.pg_ctl

[postgres@cjc01 bin]$ pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.




Usage:
  pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]
  pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-p PATH] [-c]
  pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-c]
  pg_ctl reload     [-D DATADIR] [-s]
  pg_ctl status     [-D DATADIR]
  pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl logrotate  [-D DATADIR] [-s]
  pg_ctl kill       SIGNALNAME PID




Common options:
  -D, --pgdata=DATADIR   location of the database storage area
  -s, --silent           only print errors, no informational messages
  -t, --timeout=SECS     seconds to wait when using -w option
  -V, --version          output version information, then exit
  -w, --wait             wait until operation completes (default)
  -W, --no-wait          do not wait until operation completes
  -?, --help             show this help, then exit
If the -D option is omitted, the environment variable PGDATA is used.




Options for start or restart:
  -c, --core-files       allow postgres to produce core files
  -l, --log=FILENAME     write (or append) server log to FILENAME
  -o, --options=OPTIONS  command line options to pass to postgres
                         (PostgreSQL server executable) or initdb
  -p PATH-TO-POSTGRES    normally not necessary




Options for stop or restart:
  -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"




Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown (default)
  immediate   quit without complete shutdown; will lead to recovery on restart




Allowed signal names for kill:
  ABRT HUP INT KILL QUIT TERM USR1 USR2




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

11.psql

[postgres@cjc01 ~]$ psql --help
psql is the PostgreSQL interactive terminal.




Usage:
  psql [OPTION]... [DBNAME [USERNAME]]




General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "postgres")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set psql variable NAME to VALUE
                           (e.g., -v ON_ERROR_STOP=1)
  -V, --version            output version information, then exit
  -X, --no-psqlrc          do not read startup file (~/.psqlrc)
  -1 ("one"), --single-transaction
                           execute as a single transaction (if non-interactive)
  -?, --help[=options]     show this help, then exit
      --help=commands      list backslash commands, then exit
      --help=variables     list special variables, then exit




Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)




Output format options:
  -A, --no-align           unaligned table output mode
      --csv                CSV (Comma-Separated Values) table output mode
  -F, --field-separator=STRING
                           field separator for unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           record separator for unaligned output (default: newline)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator for unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator for unaligned output to zero byte




Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)




For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

12.pg_receivewal

[postgres@cjc01 data]$ pg_receivewal --help
pg_receivewal receives PostgreSQL streaming write-ahead logs.




Usage:
  pg_receivewal [OPTION]...




Options:
  -D, --directory=DIR    receive write-ahead log files into this directory
  -E, --endpos=LSN       exit after receiving the specified LSN
      --if-not-exists    do not error if slot already exists when creating a slot
  -n, --no-loop          do not loop on connection lost
      --no-sync          do not wait for changes to be written safely to disk
  -s, --status-interval=SECS
                         time between status packets sent to server (default: 10)
  -S, --slot=SLOTNAME    replication slot to use
      --synchronous      flush write-ahead log immediately after writing
  -v, --verbose          output verbose messages
  -V, --version          output version information, then exit
  -Z, --compress=0-9     compress logs with given compression level
  -?, --help             show this help, then exit




Connection options:
  -d, --dbname=CONNSTR   connection string
  -h, --host=HOSTNAME    database server host or socket directory
  -p, --port=PORT        database server port number
  -U, --username=NAME    connect as specified database user
  -w, --no-password      never prompt for password
  -W, --password         force password prompt (should happen automatically)




Optional actions:
      --create-slot      create a new replication slot (for the slot's name see --slot)
      --drop-slot        drop the replication slot (for the slot's name see --slot)




Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

###chenjuchao 20231013###

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL PCA必知必会

必知必会：

二进制安装

源码编译安装

启动和关闭：

非全局参数修改

pg_hba格式

第二列：目标数据库

第三列：目标用户

第四列：访问来源

第五列：认证方法

创建数据库

删除数据库

数据库集群的概念

内存结构

后台进程

故障排查

备份和恢复

物理备份：

pg_dump

附录

1.pg_basebackup

评论