PostgreSQL Administration
大约 106 分钟
PostgreSQL Administration
注意：
此文档对应的 PostgreSQL 版本为 15.5。
概述
PostgreSQL 是一个开源的对象-关系型数据库管理系统（ORDBMS）。
- 开源且自由：PostgreSQL 是一款开源项目，许可证的灵活性使得用户可以以任何目的免费使用、修改和分发 PostgreSQL。
- 强大的数据模型：PostgreSQL 支持的数据类型包括数值、字符串、日期/时间、布尔型、数组、JSON、XML 等，可以方便地满足各种数据存储需求。
- 可扩展性和定制化：PostgreSQL 提供了丰富的扩展机制，比如可以通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等来扩展。
- 事务支持和并发控制：支持 ACID 事务特性，同时采用多版本并发控制（MVCC）技术，实现高并发读写操作。
- 安全性和数据完整性：提供了灵活的认证和授权机制，支持 SSL 加密传输。同时提供了完整性约束、触发器和外键等功能，确保数据的完整性和一致性。
在体系结构上，PostgreSQL 基于客户端-服务器模型，包括客户端、服务器、后台进程和共享缓冲区等核心组件。
在功能上，PostgreSQL支持复杂 SQL 查询（包括联接、子查询、聚合函数等）、全文搜索、地理空间数据处理等。它也允许定义各种数据完整性约束，保证数据的完整和一致。
部署
操作系统
PostgreSQL 支持多种操作系统，这里选择 CentOS 7，安装好操作系统后进行以下配置。
修改主机名：
[root@localhost ~]# hostnamectl set-hostname postgresql
关闭防火墙：
[root@postgresql ~]# systemctl stop firewalld
[root@postgresql ~]# systemctl disable firewalld
关闭 SELinux：
[root@postgresql ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
[root@postgresql ~]# init 6
配置地址映射：
[root@postgresql ~]# echo "192.168.92.161 postgresql" >> /etc/hosts
[root@postgresql ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.92.161 postgresql
[root@postgresql ~]# ping postgresql
调整内核参数：
[root@postgresql ~]# vi /etc/sysctl.conf
vm.overcommit_memory=2
[root@postgresql ~]# sysctl -p
vm.overcommit_memory = 2
调整资源限制：
[root@postgresql ~]# vi /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
* soft nproc 65535
* hard nproc 65535
二进制安装
在线安装
连接到公网，安装 PostgreSQL：
[root@postgresql ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@postgresql ~]# yum install -y epel-release
[root@postgresql ~]# yum install -y postgresql15-server
[root@postgresql ~]# /usr/pgsql-15/bin/postgresql-15-setup initdb
[root@postgresql ~]# systemctl enable postgresql-15
[root@postgresql ~]# systemctl start postgresql-15
[root@postgresql ~]# ps -ef | grep postgre
postgres 1757 1 0 16:53 ? 00:00:00 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
postgres 1759 1757 0 16:53 ? 00:00:00 postgres: logger
postgres 1760 1757 0 16:53 ? 00:00:00 postgres: checkpointer
postgres 1761 1757 0 16:53 ? 00:00:00 postgres: background writer
postgres 1763 1757 0 16:53 ? 00:00:00 postgres: walwriter
postgres 1764 1757 0 16:53 ? 00:00:00 postgres: autovacuum launcher
postgres 1765 1757 0 16:53 ? 00:00:00 postgres: logical replication launcher
root 1769 1454 0 16:54 pts/0 00:00:00 grep --color=auto postgre
[root@postgresql ~]# su - postgres
-bash-4.2$ psql -V
psql (PostgreSQL) 15.5
-bash-4.2$ psql
psql (15.5)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
使用二进制安装默认将数据存放在 /var/lib/pgsql 目录下，可以在初始化时使用自定义目录。
[root@postgresql ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[root@postgresql ~]# yum install -y epel-release
[root@postgresql ~]# yum install -y postgresql15-server
[root@postgresql ~]# mkdir -p /pg/data
[root@postgresql ~]# chown -R postgres:postgres /pg
[root@postgresql ~]# su - postgres
-bash-4.2$ /usr/pgsql-15/bin/initdb -D /pg/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /pg/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-15/bin/pg_ctl -D /pg/data/ -l logfile start
启动数据库：
-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /pg/data/ -l logfile start
waiting for server to start.... done
server started
关闭数据库：
-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /pg/data/ -l logfile stop
waiting for server to shut down.... done
server stopped
调整服务配置：
[root@postgresql ~]# vi /usr/lib/systemd/system/postgresql-15.service
Environment=PGDATA=/pg/data/
[root@postgresql ~]# systemctl daemon-reload
[root@postgresql ~]# systemctl start postgresql-15
[root@postgresql ~]# systemctl enable postgresql-15
[root@postgresql ~]# systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; disabled; vendor preset: disabled)
Active: active (running) since Wed 2023-11-15 10:27:56 CST; 6s ago
Docs: https://www.postgresql.org/docs/15/static/
Process: 9422 ExecStartPre=/usr/pgsql-15/bin/postgresql-15-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 9427 (postmaster)
CGroup: /system.slice/postgresql-15.service
├─9427 /usr/pgsql-15/bin/postmaster -D /pg/data/
├─9429 postgres: logger
├─9430 postgres: checkpointer
├─9431 postgres: background writer
├─9433 postgres: walwriter
├─9434 postgres: autovacuum launcher
└─9435 postgres: logical replication launcher
Nov 15 10:27:56 postgresql systemd[1]: Starting PostgreSQL 15 database server...
Nov 15 10:27:56 postgresql postmaster[9427]: 2023-11-15 10:27:56.479 CST [9427] LOG: redirecting log output to logging colle...rocess
Nov 15 10:27:56 postgresql postmaster[9427]: 2023-11-15 10:27:56.479 CST [9427] HINT: Future log output will appear in direc..."log".
Nov 15 10:27:56 postgresql systemd[1]: Started PostgreSQL 15 database server.
Hint: Some lines were ellipsized, use -l to show in full.
配置 postgres 用户环境变量：
[root@postgresql ~]# su - postgres
-bash-4.2$ vi .pgsql_profile
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
-bash-4.2$ source .pgsql_profile
[postgres@postgresql ~]$ vi .bash_profile
export PGPORT=5432
export PGHOME=/usr/pgsql-15
export PGDATA=/pg/data
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
[postgres@postgresql ~]$ source .bash_profile
离线安装
如果服务器不能访问公网，则需要先从官网网站下载对应版本的安装包以及在阿里云下载依赖包 libzstd.x86_64 0:1.5.5-1.el7，然后上传到服务器：
[root@postgresql ~]# ll
-rw-r--r-- 1 root root 299372 Nov 15 11:08 libzstd-1.5.5-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 1646456 Nov 15 11:11 postgresql15-15.5-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 root root 291024 Nov 15 11:11 postgresql15-libs-15.5-1PGDG.rhel7.x86_64.rpm
-rw-r--r-- 1 root root 6122888 Nov 15 11:11 postgresql15-server-15.5-1PGDG.rhel7.x86_64.rpm
[root@postgresql ~]# yum localinstall -y libzstd-1.5.5-1.el7.x86_64.rpm postgresql15-15.5-1PGDG.rhel7.x86_64.rpm postgresql15-libs-15.5-1PGDG.rhel7.x86_64.rpm postgresql15-server-15.5-1PGDG.rhel7.x86_64.rpm
初始化数据库：
[root@postgresql ~]# mkdir -p /pg/data
[root@postgresql ~]# chown -R postgres:postgres /pg
[root@postgresql ~]# su - postgres
-bash-4.2$ /usr/pgsql-15/bin/initdb -D /pg/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /pg/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-15/bin/pg_ctl -D /pg/data/ -l logfile start
启动数据库：
-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /pg/data/ -l logfile start
waiting for server to start.... done
server started
关闭数据库：
-bash-4.2$ /usr/pgsql-15/bin/pg_ctl -D /pg/data/ -l logfile stop
waiting for server to shut down.... done
server stopped
调整服务配置：
[root@postgresql ~]# vi /usr/lib/systemd/system/postgresql-15.service
Environment=PGDATA=/pg/data/
[root@postgresql ~]# systemctl daemon-reload
[root@postgresql ~]# systemctl start postgresql-15
[root@postgresql ~]# systemctl enable postgresql-15
[root@postgresql ~]# systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2023-11-15 11:28:13 CST; 8s ago
Docs: https://www.postgresql.org/docs/15/static/
Main PID: 1707 (postmaster)
CGroup: /system.slice/postgresql-15.service
├─1707 /usr/pgsql-15/bin/postmaster -D /pg/data/
├─1709 postgres: logger
├─1710 postgres: checkpointer
├─1711 postgres: background writer
├─1713 postgres: walwriter
├─1714 postgres: autovacuum launcher
└─1715 postgres: logical replication launcher
Nov 15 11:28:13 postgresql systemd[1]: Starting PostgreSQL 15 database server...
Nov 15 11:28:13 postgresql postmaster[1707]: 2023-11-15 11:28:13.482 CST [1707] LOG: redirecting log output to logging colle...rocess
Nov 15 11:28:13 postgresql postmaster[1707]: 2023-11-15 11:28:13.482 CST [1707] HINT: Future log output will appear in direc..."log".
Nov 15 11:28:13 postgresql systemd[1]: Started PostgreSQL 15 database server.
Hint: Some lines were ellipsized, use -l to show in full.
配置 postgres 用户环境变量：
[root@postgresql ~]# su - postgres
-bash-4.2$ vi .pgsql_profile
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
-bash-4.2$ source .pgsql_profile
[postgres@postgresql ~]$ vi .bash_profile
export PGPORT=5432
export PGHOME=/usr/pgsql-15
export PGDATA=/pg/data
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
[postgres@postgresql ~]$ source .bash_profile
源码安装
创建组和用户：
[root@postgresql ~]# groupadd postgres
[root@postgresql ~]# useradd -g postgres postgres
创建目录：
[root@postgresql ~]# mkdir -p /pg/data
[root@postgresql ~]# mkdir /pg/software
[root@postgresql ~]# chown -R postgres:postgres /pg
安装依赖：
[root@postgresql ~]# yum install -y cmake make zlib zlib-devel readline readline-devel python36 ncurses-devel flex perl perl-ExtUtils-Embed perl-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel tcl tcl-devel openldap openldap-devel python python-devel gcc gcc-c++ rsync icu libicu libicu-devel e2fsprogs-devel uuid uuid-devel libuuid-devel lz4 lz4-devel epel-release bison bison-devel gettext gettext-devel systemd-devel net-tools
配置环境变量：
[root@postgresql ~]# su - postgres
[postgres@postgresql ~]$ vi .bash_profile
export PGPORT=5432
export PGHOME=/pg/software
export PGDATA=/pg/data
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
[postgres@postgresql ~]$ source .bash_profile
在官方网站下载源码包，编译安装 PostgreSQL：
[root@postgresql ~]# su - postgres
[postgres@postgresql ~]$ tar -xvzf postgresql-15.5.tar.gz
[postgres@postgresql ~]$ cd postgresql-15.5
[postgres@postgresql postgresql-15.5]$ ./configure --prefix=/pg/software/
[postgres@postgresql postgresql-15.5]$ make
[postgres@postgresql postgresql-15.5]$ make install
初始化数据库：
[postgres@postgresql postgresql-15.5]$ initdb -D $PGDATA
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /pg/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /pg/data -l logfile start
其中：
- 初始化时指定存放数据的目录必须为空目录。
- 初始化时会调整数据目录的权限为
700。 - 可以使用
-W选项为数据库超级用户postgres（与当前操作系统帐户相同的名称）指定密码。
启动数据库：
[postgres@postgresql postgresql-15.5]$ pg_ctl -D /pg/data -l logfile start
waiting for server to start.... done
server started
使用 psql 访问数据库，使用默认用户名 postgres，访问默认数据库 postgres：
[postgres@postgresql postgresql-15.5]$ psql
psql (15.5)
Type "help" for help.
postgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
可以看到，在初始化后，包含默认数据库 postgres，以及 template0 和 template1 这两个模板数据库。
关闭数据库：
[postgres@postgresql postgresql-15.5]$ pg_ctl -D /pg/data -l logfile stop
waiting for server to shut down.... done
server stopped
配置服务：
[root@postgresql ~]# vi /usr/lib/systemd/system/postgresql-15.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. It is recommended to use systemd
# "dropin" feature; i.e. create file with suffix .conf under
# /etc/systemd/system/postgresql-15.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit postgresql-15"
# Look at systemd.unit(5) manual page for more info.
# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.
# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-15-setup.
[Unit]
Description=PostgreSQL 15 database server
Documentation=https://www.postgresql.org/docs/15/static/
After=syslog.target
After=network-online.target
[Service]
#Type=notify
Type=forking
User=postgres
Group=postgres
# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.
# Location of database directory
Environment=PGDATA=/pg/data/
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
#ExecStartPre=/pg/software/bin/postgresql-15-check-db-dir ${PGDATA}
ExecStart=/pg/software/bin/pg_ctl start -D ${PGDATA} -w -t 300 -s
ExecStop=/pg/software/bin/pg_ctl stop -D ${PGDATA} -m fast -s
ExecReload=/pg/software/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0
# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0
TimeoutStopSec=1h
[Install]
WantedBy=multi-user.target
[root@postgresql ~]# systemctl daemon-reload
[root@postgresql ~]# systemctl start postgresql-15
[root@postgresql ~]# systemctl enable postgresql-15
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-15.service to /usr/lib/systemd/system/postgresql-15.service.
[root@postgresql ~]# systemctl status postgresql-15
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2023-11-23 09:14:35 CST; 1h 47min ago
Docs: https://www.postgresql.org/docs/15/static/
Process: 951 ExecStart=/pg/software/bin/pg_ctl start -D ${PGDATA} -w -t 300 -s (code=exited, status=0/SUCCESS)
Main PID: 958 (postgres)
CGroup: /system.slice/postgresql-15.service
├─ 958 /pg/software/bin/postgres -D /pg/data
├─1043 postgres: logger
├─1049 postgres: checkpointer
├─1050 postgres: background writer
├─1055 postgres: walwriter
├─1056 postgres: autovacuum launcher
└─1057 postgres: logical replication launcher
Nov 23 09:14:34 postgresql systemd[1]: Starting PostgreSQL 15 database server...
Nov 23 09:14:35 postgresql pg_ctl[951]: 2023-11-23 09:14:35.518 CST [958] LOG: redirecting log output to logging collector process
Nov 23 09:14:35 postgresql pg_ctl[951]: 2023-11-23 09:14:35.518 CST [958] HINT: Future log output will appear in directory "log".
Nov 23 09:14:35 postgresql systemd[1]: Started PostgreSQL 15 database server.
配置
修改参数
安装完成后，需要根据实际环境配置参数。假设主机内存为 32 GB，需要修改的参数如下：
listen_addresses = '*'
logging_collector = on
max_connections = 300
shared_buffers = 8GB
effective_cache_size = 24GB
work_mem = 8MB
maintenance_work_mem = 2GB
wal_buffers = 16MB
min_wal_size = 2GB
max_wal_size = 8GB
可以直接修改配置文件 $PGDATA/postgresql.conf，或者在 psql 中使用以下命令修改，此时会将修改的参数写入到 $PGDATA/postgresql.auto.conf 文件中，以覆盖 $PGDATA/postgresql.conf 中的参数：
ALTER SYSTEM SET listen_addresses = '*';
ALTER SYSTEM SET logging_collector = 'on';
ALTER SYSTEM SET max_connections = '300';
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '8MB';
ALTER SYSTEM SET maintenance_work_mem = '2GB';
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET min_wal_size = '2GB';
ALTER SYSTEM SET max_wal_size = '8GB';
重启数据库：
[root@postgresql ~]# systemctl restart postgresql-15.service
修改密码
如果在初始化数据库时没有使用 -W 选项为用户 postgres（与当前操作系统帐户相同的名称）指定密码，则在允许远程访问数据库前，需要为其指定密码：
postgres=# alter user postgres with password '123456';
访问控制
安装完成后，默认只能从服务器访问数据库，需要修改认证配置文件 $PGDATA/pg_hba.conf，在最后添加：
[postgres@postgresql ~]$ vi /pg/data/pg_hba.conf
host all all 0.0.0.0/0 md5
然后重启数据库：
[root@postgresql ~]# systemctl restart postgresql-15.service
这样就可以从任意地址远程访问数据库了。
大页内存
如果分配给 PostgreSQL 的 shared_buffers 超过了 8 GB，建议为其配置大页内存。
关闭数据库：
[postgres@postgresql ~]$ pg_ctl -D /pg/data -l logfile stop
waiting for server to shut down.... done
server stopped
计算大页数量，这里假设 shared_buffers = 8192MB：
[postgres@postgresql ~]$ postgres -D $PGDATA -C shared_memory_size_in_huge_pages
4200
调整内存参数：
[root@postgresql ~]# vi /etc/sysctl.conf
vm.nr_hugepages=4200
[root@postgresql ~]# sysctl -p
[root@postgresql ~]# cat /proc/meminfo | grep -i hugepages
AnonHugePages: 4096 kB
HugePages_Total: 4200
HugePages_Free: 4200
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
调整资源限制，这里假设服务器内存有 16GB，postgres 用户最大锁定内存为 10GB（10485760 KB）：
[root@postgresql ~]# vi /etc/security/limits.conf
postgres soft memlock 10485760
postgres hard memlock 10485760
[root@postgresql ~]# su - postgres
[postgres@postgresql ~]$ ulimit -a | grep "max locked memory"
max locked memory (kbytes, -l) 10485760
重启服务器：
[root@postgresql ~]# init 6
启动数据库（如果没有配置自动启动）：
[root@postgresql ~]# su - postgres
[postgres@postgresql postgresql-15.5]$ pg_ctl -D /pg/data -l logfile start
waiting for server to start.... done
server started
查看大页内存使用情况：
[root@postgresql ~]# cat /proc/meminfo | grep -i hugepages
AnonHugePages: 10240 kB
HugePages_Total: 4200
HugePages_Free: 4105
HugePages_Rsvd: 4105
HugePages_Surp: 0
Hugepagesize: 2048 kB
架构
PostgreSQL 的架构与 Oracle 的架构类似，可分为：
- 实例（Instance）：一组使用共享内存（Shared Memory）进行通信的后端进程（Backend Process）和辅助进程（Auxiliary Process）
- 内存结构（Memory）
- 进程结构（Process）
- 数据库（Database）：存储结构
整体架构图如下：
内存结构
内存包括：
- 共享内存（Shared Memory）：由实例所有进程使用。
- 本地内存（Local Memory）：每个后端进程分配给自己使用。
共享内存
共享内存（Shared Memory）包括：
- 共享缓冲区（Shared Buffers）
- 日志缓冲区（WAL Buffers）
- 提交日志区（Commit Logs）
共享缓冲区
共享缓冲区（Shared Buffers）类似于 Oracle 的数据库缓冲区缓存（Database Buffer Cache）和共享池（Shared Pool），用于存放从数据文件中读取的数据块，到内存页中。
使用参数 shared_buffers 指定共享缓冲区大小，默认为 128 MB。建议设置为内存的 25%，不超过
内存的 40%。如果设置了较大的 shared_buffers，则需要相应增大 max_wal_size。
postgres=# show shared_buffers;
shared_buffers
----------------
128MB
(1 row)
日志缓冲区
日志缓冲区（WAL Buffers）类似于 Oracle 的重做日志缓冲区（Redo Log Buffer），用于存放数据库修改信息。是一个环形区域，循环写入。
使用参数 wal_buffers 指定日志缓冲区大小，默认为 -1 表示自动设置为 shared_buffers 的 1/32，通常为 16 MB。如果自动设置的值太大或者太小，建议根据实际业务情况进行调整。
postgres=# show wal_buffers;
wal_buffers
-------------
4MB
(1 row)
提交日志区
提交日志区（Commit Logs）用于存放事务状态。默认为 shared_buffers 的 1/512，最大为 128 个页，最小为 4 个页。
本地内存
本地内存（Local Memory）包括：
- 工作区（Work Memory）
- 维护工作区（Maintenance Work Memory）
- 临时缓冲区（Temporary Buffers）
工作区
工作区（Work Memory）用于单个会话的 ORDER BY，DISTINCT，MERGE JOIN 及 HASH JOIN 等操作。
使用参数 work_mem 指定单个会话的工作区大小，默认为 4 MB，当内存使用量超过这个值时，会写入到磁盘临时文件。对于需要大量排序或哈希操作的查询，建议增大该参数。
postgres=# show work_mem;
work_mem
----------
4MB
(1 row)
维护工作区
维护工作区（Maintenance Work Memory）用于维护操作（如 VACUUM、CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY）。
使用参数 maintenance_work_mem 指定单个会话的维护工作区大小，默认为 64 MB。较大的值可以提高 VACUUM 和还原数据的性能。
postgres=# show maintenance_work_mem;
maintenance_work_mem
----------------------
64MB
(1 row)
临时缓冲区
临时缓冲区（Temporary Buffers）仅用于访问临时表。
使用参数 temp_buffers 指定单个会话的临时缓冲区大小，默认为 8 MB。当需要访问较大的临时表时，合理地设置 temp_buffers 可以显著提升性能。
postgres=# show temp_buffers;
temp_buffers
--------------
8MB
(1 row)
进程结构
PostgreSQL 使用了一个多进程的体系结构来管理其操作，包括：
- Postgres：实例的第一个进程，默认监听端口 5432，该进程启动和管理辅助进程（Auxiliary Processes），并根据客户端请求创建后端进程（Backend Processes）。如果使用二进制安装，则使用
postgres命令的软连接postmaster。 - Auxiliary Process：辅助进程，处理特定后台任务的进程，包括：
- Background Writer：后台写进程，将共享缓冲区中的脏页写入到磁盘。类似于 Oracle 的 DBWn 进程。
- Checkpointer：检查点进程，执行检查点，以确保该检查点之前所有共享缓冲区中修改的数据都会被写入到磁盘，同时会将检查点记录写入到 WAL 日志中。类似于 Oracle 的 CKPT 进程。
- Autovacuum Launcher：是 Autovacuum 的守护进程，负责周期性地调度 Autovacuum Worker 进程执行 Vacuum 操作。
- WAL Writer：将 WAL 日志从日志缓冲区写入到磁盘。类似于 Oracle 的 LGWR 进程。
- WAL Archiver：将 WAL 日志文件进行归档，用于数据库恢复。类似于 Oracle 的 ARCn 进程。
- Logger：将数据库事件信息写入到日志文件，在指定的时间间隔里对日志文件进行轮换。
- Backend Processes：后端进程，用于处理客户端会话请求，参数
max_connections指定客户端的最大数量。类似于 Oracle 的服务器进程。 - Background worker：后台工作进程，用于执行特定任务而动态启动的进程，例如 Logical Replication Launcher （逻辑复制启动器进程）。这些工作进程可以在不干扰主服务器操作的情况下执行一些维护或特定的任务。通过后台工作进程，可以实现对数据库的一些优化和维护操作，提高数据库的性能和稳定性。
[root@postgresql ~]# ps -ef | grep postgres
postgres 958 1 0 09:14 ? 00:00:00 /pg/software/bin/postgres -D /pg/data
postgres 1043 958 0 09:14 ? 00:00:00 postgres: logger
postgres 1049 958 0 09:14 ? 00:00:00 postgres: checkpointer
postgres 1050 958 0 09:14 ? 00:00:00 postgres: background writer
postgres 1055 958 0 09:14 ? 00:00:00 postgres: walwriter
postgres 1056 958 0 09:14 ? 00:00:00 postgres: autovacuum launcher
postgres 1057 958 0 09:14 ? 00:00:00 postgres: logical replication launcher
postgres 1917 958 0 11:16 ? 00:00:00 postgres: postgres postgres [local] idle
存储结构
在安装 PostgreSQL 时，使用 initdb 命令在磁盘上初始化的数据库存储区域，称之为数据库集群（Database Cluster 或者 Catalog Cluster），是在一个数据库服务器上由单个实例管理的多个数据库（Database）集合。初始化时会默认创建 template1 和 template0 这两个模板数据库以及 postgres 这个可供应用程序使用的数据库。postgres 默认有一个名称为 public 的模式（Schema），可以在模式下创建表（Table），索引（Index）等数据库对象。同时为了更好的管理各个数据库中的对象，通过使用表空间（Tablespace）在文件系统中指定数据库对象对应文件的存储目录，初始化时会创建 template1 和 template0 数据库的默认表空间 pg_default 以及用于共享系统目录（Shared System Catalogs）的 pg_global 表空间，分别对应 $PGDATA/base 目录和 $PGDATA/global 目录。
整个存储结构图如下：
默认情况下，PostgreSQL 的所有文件都存放在 $PGDATA 目录下：
[postgres@postgresql ~]$ ll $PGDATA
total 72
drwx------ 7 postgres postgres 59 Nov 24 17:20 base
-rw------- 1 postgres postgres 44 Dec 8 13:55 current_logfiles
drwx------ 2 postgres postgres 4096 Dec 8 13:56 global
drwx------ 2 postgres postgres 4096 Dec 8 13:55 log
drwx------ 2 postgres postgres 6 Nov 16 14:09 pg_commit_ts
drwx------ 2 postgres postgres 6 Nov 16 14:09 pg_dynshmem
-rw------- 1 postgres postgres 4858 Nov 17 16:04 pg_hba.conf
-rw------- 1 postgres postgres 1636 Nov 16 14:09 pg_ident.conf
drwx------ 4 postgres postgres 68 Dec 8 14:00 pg_logical
drwx------ 4 postgres postgres 36 Nov 16 14:09 pg_multixact
drwx------ 2 postgres postgres 6 Nov 16 14:09 pg_notify
drwx------ 2 postgres postgres 6 Nov 16 14:09 pg_replslot
drwx------ 2 postgres postgres 6 Nov 16 14:09 pg_serial
drwx------ 2 postgres postgres 6 Nov 16 14:09 pg_snapshots
drwx------ 2 postgres postgres 6 Dec 8 13:55 pg_stat
drwx------ 2 postgres postgres 6 Nov 16 14:09 pg_stat_tmp
drwx------ 2 postgres postgres 18 Nov 16 14:09 pg_subtrans
drwx------ 2 postgres postgres 19 Nov 28 13:49 pg_tblspc
drwx------ 2 postgres postgres 6 Nov 16 14:09 pg_twophase
-rw------- 1 postgres postgres 3 Nov 16 14:09 PG_VERSION
drwx------ 3 postgres postgres 92 Nov 20 13:27 pg_wal
drwx------ 2 postgres postgres 18 Nov 16 14:09 pg_xact
-rw------- 1 postgres postgres 161 Nov 21 10:55 postgresql.auto.conf
-rw------- 1 postgres postgres 29463 Nov 16 14:09 postgresql.conf
-rw------- 1 postgres postgres 42 Dec 8 13:55 postmaster.opts
-rw------- 1 postgres postgres 65 Dec 8 13:55 postmaster.pid
在该目录下包含多个子目录和配置文件，主要有：
| Item | Description |
|---|---|
PG_VERSION | A file containing the major version number of PostgreSQL |
base | Subdirectory containing per-database subdirectories |
current_logfiles | File recording the log file(s) currently written to by the logging collector |
global | Subdirectory containing cluster-wide tables, such as pg_database |
pg_commit_ts | Subdirectory containing transaction commit timestamp data |
pg_dynshmem | Subdirectory containing files used by the dynamic shared memory subsystem |
pg_logical | Subdirectory containing status data for logical decoding |
pg_multixact | Subdirectory containing multitransaction status data (used for shared row locks) |
pg_notify | Subdirectory containing LISTEN/NOTIFY status data |
pg_replslot | Subdirectory containing replication slot data |
pg_serial | Subdirectory containing information about committed serializable transactions |
pg_snapshots | Subdirectory containing exported snapshots |
pg_stat | Subdirectory containing permanent files for the statistics subsystem |
pg_stat_tmp | Subdirectory containing temporary files for the statistics subsystem |
pg_subtrans | Subdirectory containing subtransaction status data |
pg_tblspc | Subdirectory containing symbolic links to tablespaces |
pg_twophase | Subdirectory containing state files for prepared transactions |
pg_wal | Subdirectory containing WAL (Write Ahead Log) files |
pg_xact | Subdirectory containing transaction commit status data |
postgresql.auto.conf | A file used for storing configuration parameters that are set by ALTER SYSTEM |
postmaster.opts | A file recording the command-line options the server was last started with |
postmaster.pid | A lock file recording the current postmaster process ID (PID), cluster data directory path, postmaster start timestamp, port number, Unix-domain socket directory path (could be empty), first valid listen_address (IP address or *, or empty if not listening on TCP), and shared memory segment ID (this file is not present after server shutdown) |
数据库
类似于 Oracle 的 CDB/PDB，一个 PostgreSQL 数据库集群包含多个数据库。各个数据库在逻辑和物理上都是独立的，一个数据库对应文件系统上的一个目录。相关数据库对象存储在各个数据库的不同模式中。但是有一些集群级别的对象，例如角色，数据库和表空间名称存储在 pg_global 表空间。
客户端访问 PostgreSQL 时，必须在连接请求中指定数据库名称，且一个连接只能访问一个数据库。因为只能连接到数据库后才能进行创建数据库等操作（先有鸡还是先有蛋问题），故在使用 initdb 命令初始化时会默认创建 postgres 这个可供客户端连接的数据库。
直接使用 psql 命令时默认连接到 postgres 数据库，也可以在连接时使用 -d 选项指定要连接的数据库。
[postgres@postgresql ~]$ psql
psql (15.5)
Type "help" for help.
postgres=#
[postgres@postgresql ~]$ psql -d postgres
psql (15.5)
Type "help" for help.
postgres=#
除了 postgres 数据库外，在数据库集群初始化期间还会创建 template1 和 template0 这两个模板数据库。创建新的数据库时会默认基于 template1 进行克隆。如果将对象添加到 template1 中，这些对象将被复制到随后创建的数据库中。而 template0 与初始 template1 的内容一致，仅包含预定义的标准对象。如果 template1 中包含了自定义对象，而又需要创建一个不包含自定义对象的数据库，此时就可以基于 template0 创建。
在 psql 中使用 \l 命令查看集群中的数据库：
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
还可以查询 pg_database 表查看集群中的数据库：
postgres=# select oid,datname,datistemplate,datallowconn from pg_database;
oid | datname | datistemplate | datallowconn
-----+-----------+---------------+--------------
5 | postgres | f | t
1 | template1 | t | t
4 | template0 | t | f
(3 rows)
其中：
oid：对象标识符，对应文件系统的目录。datistemplate：是否为模板数据库，如果为t，则任何具有CREATEDB权限的用户都可以克隆此数据库，否则只有超级用户和数据库所有者才能克隆。datallowconn：是否允许连接，如果为f，则不允许与该数据库建立连接。
创建数据库
使用 CREATE DATABASE 创建数据库。
语法：
CREATE DATABASE name
[ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ STRATEGY [=] strategy ] ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ ICU_LOCALE [=] icu_locale ]
[ ICU_RULES [=] icu_rules ]
[ LOCALE_PROVIDER [=] locale_provider ]
[ COLLATION_VERSION = collation_version ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ]
[ OID [=] oid ]
例子：创建数据库
postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
(4 rows)
postgres=# select oid,datname,datistemplate,datallowconn from pg_database;
oid | datname | datistemplate | datallowconn
-------+-----------+---------------+--------------
5 | postgres | f | t
16392 | testdb | f | t
1 | template1 | t | t
4 | template0 | t | f
(4 rows)
默认会在 $PGDATA/base 目录下面创建 16392 文件夹：
[postgres@postgresql ~]$ ll $PGDATA/base
total 48
drwx------ 2 postgres postgres 8192 Nov 24 09:47 1
drwx------ 2 postgres postgres 8192 Nov 24 17:13 16392
drwx------ 2 postgres postgres 8192 Nov 16 14:09 4
drwx------ 2 postgres postgres 8192 Nov 24 14:02 5
以 testdb 为模板，复制该数据库以创建新的数据库：
postgres=# CREATE DATABASE newtestdb TEMPLATE testdb;
CREATE DATABASE
postgres=# select oid,datname,datistemplate,datallowconn from pg_database;
oid | datname | datistemplate | datallowconn
-------+-----------+---------------+--------------
5 | postgres | f | t
16392 | testdb | f | t
1 | template1 | t | t
4 | template0 | t | f
16393 | newtestdb | f | t
(5 rows)
注意：
在复制数据库时，作为模板库的源库，不能有其他会话连接。如果在启动
CREATE DATABASE时有其他会话连接到源库，则会失败，且在复制期间，不能与源库建立新的连接。
修改数据库
使用 ALTER DATABASE 修改数据库。
语法：
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
ALLOW_CONNECTIONS allowconn
CONNECTION LIMIT connlimit
IS_TEMPLATE istemplate
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name REFRESH COLLATION VERSION
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL
其中：
- 第一个命令修改数据库的设置。
- 第二个命令修改数据库的名称。
- 第三个命令修改数据库的所有者。
- 第四个命令修改数据库的默认表空间。
- 其余命令修改数据库的参数。
例子：修改数据库名称
postgres=# ALTER DATABASE newtestdb RENAME TO newdb;
ALTER DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
newdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
(5 rows)
使用 ALTER DATABASE 可以修改针对数据库的参数，对后续连接的会话生效，只有超级用户或者数据库所有者才能修改。
例子：关闭数据库 newdb 中的索引扫描
postgres=# ALTER DATABASE newdb SET enable_indexscan TO off;
ALTER DATABASE
postgres=# \c newdb
You are now connected to database "newdb" as user "postgres".
newdb=# show enable_indexscan;
enable_indexscan
------------------
off
(1 row)
例子：将修改的配置还原为默认值
newdb=# ALTER DATABASE newdb RESET enable_indexscan;
ALTER DATABASE
newdb=# \c
You are now connected to database "newdb" as user "postgres".
newdb=# show enable_indexscan;
enable_indexscan
------------------
on
(1 row)
删除数据库
使用 DROP DATABASE 删除数据库。
语法：
DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [, ...] ) ]
where option can be:
FORCE
- 只有超级用户或者数据库所有者才能删除数据库。
- 删除数据库将删除数据库中包含的所有对象以及文件系统中的数据目录。
- 当连接到要删除的数据库时，无法执行删除操作。需要连接到
postgres或者其他数据库执行删除。 - 如果数据库上存在用户连接，则无法执行删除操作，可以使用
FORCE选项尝试终止与数据库的连接，但不能终止与复制和订阅相关连接。 - 数据库的删除无法撤消。
例子：删除数据库
postgres=# DROP DATABASE newdb;
DROP DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
(4 rows)
模式
创建数据库会默认创建一个名称为 public 的模式（Schema），可以在模式下创建表（Table），索引（Index）等数据库对象，也就是说数据库对象逻辑上是存放在模式中，但物理上没有与之对应的文件系统目录。
可以在数据库中创建多个模式，以对应不同的业务需求，方便分组管理。各个模式中的对象名称可以一样，通过在对象名称前面加上模式名称前缀以区分不同的对象。
在 psql 中使用 \dn 命令查看当前数据库的模式：
postgres=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
创建模式
与 Oracle 创建用户即会创建一个与之同名的模式不一样，PostgreSQL 需要单独创建模式。
使用 CREATE SCHEMA 创建模式。
语法：
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
where role_specification can be:
user_name
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
注意模式名称不能以 pg_ 开头。
例子：创建模式并指定其所有者
postgres=# CREATE USER stone WITH PASSWORD 'stone';
CREATE ROLE
postgres=# CREATE SCHEMA hr AUTHORIZATION stone;
CREATE SCHEMA
postgres=# \dn
List of schemas
Name | Owner
--------+-------------------
hr | stone
public | pg_database_owner
(2 rows)
修改模式
使用 ALTER SCHEMA 修改模式。
语法：
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
例子：修改模式的所有者
postgres=# CREATE USER hr WITH PASSWORD 'hr';
CREATE ROLE
postgres=# ALTER SCHEMA hr OWNER TO hr;
ALTER SCHEMA
postgres=# \dn
List of schemas
Name | Owner
--------+-------------------
hr | hr
public | pg_database_owner
(2 rows)
删除模式
使用 DROP SCHEMA 修改模式。
语法：
DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
其中：
RESTRICT：默认选项，拒绝删除包含对象的模式。适用于不包含对象的模式。CASCADE：级联删除模式，模式包含的对象以及依赖这些对象的所有对象。
只有超级用户或者模式所有者才能删除模式。
例子：级联删除模式
postgres=# DROP SCHEMA hr CASCADE;
DROP SCHEMA
postgres=# \dn
List of schemas
Name | Owner
--------+-------------------
public | pg_database_owner
(1 row)
搜索路径
在 Oracle 中，当使用某个用户连接到数据库时，直接访问某个对象，那么肯定就是访问该用户对应模式下的对象，如果要访问指定模式下的对象，则需要在对象名称前面增加模式名称前缀。而在 PostgreSQL 中，通过使用搜索路径，可以避免在对象名称前面增加模式名称前缀，从而简化访问指定模式下的对象。
显示当前搜索路径：
postgres=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
搜索路径默认包含 2 个模式，以逗号分隔，其中：
"$user"：表示与当前用户同名的模式。public：表示public模式。
当查找对象时，PostgreSQL 会依次在这些模式中进行查找，返回第一个匹配的对象；当创建对象时，如果没有指定模式名称，会在第一个存在的模式中创建。
可以根据实际使用情况来调整 search_path，但最佳实践是创建与用户同名的模式，这样对象就会被创建在对应的模式中，而不会都创建在 public 模式中。
除了 public 和用户创建的模式之外，每个数据库还有一个 pg_catalog 模式，其中包含系统表和所有内置数据类型、函数和运算符。 该模式默认隐式的位于搜索路径的最前面，以确保始终可以查找到这些内置对象。
表空间
在 PostgreSQL 中，使用表空间为数据库对象（数据库，表以及索引）指定文件系统上的存储目录，在该目录下存放数据库对象对应的数据文件。也就是说表空间是与文件系统上的目录一一对应，以提供更灵活和可扩展的数据存储管理。
初始化数据库集群时会自动创建 template1 和 template0 数据库的默认表空间 pg_default 以及用于共享系统目录（Shared System Catalogs）的 pg_global 表空间，分别对应 $PGDATA/base 目录和 $PGDATA/global 目录。
在 psql 中使用 \db 命令查看表空间：
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
(2 rows)
对应的文件系统目录为：
[postgres@postgresql ~]$ ll -d $PGDATA/base
drwx------ 6 postgres postgres 46 Nov 25 22:43 /pg/data/base
[postgres@postgresql ~]$ ll -d $PGDATA/global
drwx------ 2 postgres postgres 4096 Nov 26 17:20 /pg/data/global
如果创建数据库时使用 template1（默认）或者 template0 作为模板数据库，则 pg_default 也是新建数据库的默认表空间。此后，连接到该数据库创建表或者索引时，如果不指定表空间，将使用数据库的默认表空间。
可以在创建数据库，表或者索引时指定表空间：
CREATE TABLE foo(i int) TABLESPACE space1;
也可以设置 default_tablespace 参数为后续语句指定默认表空间：
SET default_tablespace = space1;
CREATE TABLE foo(i int);
创建表空间
使用 CREATE TABLESPACE 创建表空间。
语法：
CREATE TABLESPACE tablespace_name
[ OWNER { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory'
[ WITH ( tablespace_option = value [, ... ] ) ]
其中：
- 表空间名称不能以
pg_开头。 - 只有超级用户才能创建表空间。
- 用于表空间的目录必须存在且为空，所有者为 PostgreSQL 系统用户，且为绝对路径名。
- 可以设置的表空间参数有
seq_page_cost，random_page_cost，effective_io_concurrency和maintenance_io_concurrency。 - 由于创建的表空间与文件系统目录之间通过符号链接关联，故只能在支持符号链接的系统上创建表空间。
- 不能在事务块中执行
CREATE TABLESPACE语句。
例子：先使用 postgres 操作系统用户创建目录，再创建表空间
[postgres@postgresql ~]$ mkdir $PGDATA/app
[postgres@postgresql ~]$ ll -d $PGDATA/app
drwxr-xr-x 2 postgres postgres 6 Nov 26 21:10 /pg/data/app
postgres=# CREATE TABLESPACE app_tbs LOCATION '/pg/data/app';
WARNING: tablespace location should not be inside the data directory
CREATE TABLESPACE
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+--------------
app_tbs | postgres | /pg/data/app
pg_default | postgres |
pg_global | postgres |
(3 rows)
postgres=# select * from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16392 | app_tbs | 10 | |
(3 rows)
在表空间 app_tbs 对应的目录下 /pg/data/app 创建了一个目录 PG_15_202209061（以 PostgreSQL 版本命名）：
[postgres@postgresql ~]$ ll /pg/data/app
total 0
drwx------ 2 postgres postgres 6 Nov 26 21:13 PG_15_202209061
与此同时，在 $PADATA/pg_tblspc 目录下创建一个名称为表空间 oid 的符号链接，指向表空间对应的目录：
[postgres@postgresql ~]$ ll $PGDATA/pg_tblspc
total 0
lrwxrwxrwx 1 postgres postgres 12 Nov 26 21:13 16392 -> /pg/data/app
默认情况下，执行 CREATE TABLESPACE 语句的用户为该表空间的所有者，也可以使用 OWNER 选项指定所有者。为方便管理，建议统一使用 postgres 超级用户创建表空间。
对于普通用户，需要授予表空间上的对象创建权限才能使用该表空间。为用户 hr 授予表空间 app_tbs 上的使用权限：
postgres=# GRANT CREATE ON TABLESPACE app_tbs TO hr;
GRANT
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+--------------+---------------------+---------+---------+-------------
app_tbs | postgres | /pg/data/app | postgres=C/postgres+| | 0 bytes |
| | | hr=C/postgres | | |
pg_default | postgres | | | | 30 MB |
pg_global | postgres | | | | 555 kB |
(3 rows)
使用用户 postgres 创建数据库 appdb 并指定默认表空间为 app_tbs，然后连接该数据库，创建模式 hr 并指定所有者为 hr：
postgres=# CREATE DATABASE appdb TABLESPACE app_tbs;
CREATE DATABASE
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------+---------+------------+--------------------------------------------
appdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | | 7693 kB | app_tbs |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | | 7597 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +| 7441 kB | pg_default | unmodifiable empty database
| | | | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +| 7693 kB | pg_default | default template for new databases
| | | | | | | postgres=CTc/postgres | | |
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | | 7693 kB | pg_default |
(5 rows)
postgres=# select oid,datname,datistemplate,datallowconn,dattablespace from pg_database;
oid | datname | datistemplate | datallowconn | dattablespace
-------+-----------+---------------+--------------+---------------
5 | postgres | f | t | 1663
16388 | testdb | f | t | 1663
1 | template1 | t | t | 1663
4 | template0 | t | f | 1663
16393 | appdb | f | t | 16392
(5 rows)
postgres=# \c appdb
You are now connected to database "appdb" as user "postgres".
appdb=# CREATE SCHEMA hr AUTHORIZATION hr;
CREATE SCHEMA
appdb=# \dn
List of schemas
Name | Owner
--------+-------------------
hr | hr
public | pg_database_owner
(2 rows)
使用用户 hr 连接到 appdb，就会默认将表创建在 hr 模式下以及 app_tbs 表空间中：
appdb=# \c - hr
You are now connected to database "appdb" as user "hr".
appdb=> CREATE TABLE t1(id int);
CREATE TABLE
appdb=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
hr | t1 | table | hr
(1 row)
appdb=> SELECT * FROM pg_tables WHERE tablename='t1';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
hr | t1 | hr | | f | f | f | f
(1 row)
这里使用了数据库的默认表空间 app_tbs，故 tablespace 显示为空。
修改表空间
使用 ALTER TABLESPACE 创建表空间。
语法：
ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] )
其中：
- 使用
RENAME重命名表空间。 - 使用
OWNER TO修改表空间所有者。 - 使用
SET修改表空间参数seq_page_cost，random_page_cost，effective_io_concurrency和maintenance_io_concurrency。 - 使用
RESET重置表空间参数为默认值。
删除表空间
使用 DROP TABLESPACE 删除表空间。
语法：
DROP TABLESPACE [ IF EXISTS ] name
- 表空间只能由其所有者或超级用户删除。
- 必须删除表空间中所有数据库对象，然后才能删除表空间。
- 删除表空间时，同时会删除文件系统中对应的表空间子目录。
数据类型
PostgreSQL 有很多数据类型，具体如下表：
| Name | Aliases | Description |
|---|---|---|
bigint | int8 | signed eight-byte integer |
bigserial | serial8 | autoincrementing eight-byte integer |
bit [ (n) ] | fixed-length bit string | |
bit varying [ (n) ] | varbit [ (n) ] | variable-length bit string |
boolean | bool | logical Boolean (true/false) |
box | rectangular box on a plane | |
bytea | binary data (“byte array”) | |
character [ (n) ] | char [ (n) ] | fixed-length character string |
character varying [ (n) ] | varchar [ (n) ] | variable-length character string |
cidr | IPv4 or IPv6 network address | |
circle | circle on a plane | |
date | calendar date (year, month, day) | |
double precision | float8 | double precision floating-point number (8 bytes) |
inet | IPv4 or IPv6 host address | |
integer | int, int4 | signed four-byte integer |
interval [ fields ] [ (p) ] | time span | |
json | textual JSON data | |
jsonb | binary JSON data, decomposed | |
line | infinite line on a plane | |
lseg | line segment on a plane | |
macaddr | MAC (Media Access Control) address | |
macaddr8 | MAC (Media Access Control) address (EUI-64 format) | |
money | currency amount | |
numeric [ (p, s) ] | decimal [ (p, s) ] | exact numeric of selectable precision |
path | geometric path on a plane | |
pg_lsn | PostgreSQL Log Sequence Number | |
pg_snapshot | user-level transaction ID snapshot | |
point | geometric point on a plane | |
polygon | closed geometric path on a plane | |
real | float4 | single precision floating-point number (4 bytes) |
smallint | int2 | signed two-byte integer |
smallserial | serial2 | autoincrementing two-byte integer |
serial | serial4 | autoincrementing four-byte integer |
text | variable-length character string | |
time [ (p) ] [ without time zone ] | time of day (no time zone) | |
time [ (p) ] with time zone | timetz | time of day, including time zone |
timestamp [ (p) ] [ without time zone ] | date and time (no time zone) | |
timestamp [ (p) ] with time zone | timestamptz | date and time, including time zone |
tsquery | text search query | |
tsvector | text search document | |
txid_snapshot | user-level transaction ID snapshot (deprecated; see pg_snapshot) | |
uuid | universally unique identifier | |
xml | XML data |
数字类型
根据所表示的数字范围和精度，数字类型包括：
| Name | Storage Size | Description | Range |
|---|---|---|---|
smallint | 2 bytes | small-range integer | -32768 to +32767 |
integer | 4 bytes | typical choice for integer | -2147483648 to +2147483647 |
bigint | 8 bytes | large-range integer | -9223372036854775808 to +9223372036854775807 |
decimal | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
numeric | variable | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point |
real | 4 bytes | variable-precision, inexact | 6 decimal digits precision |
double precision | 8 bytes | variable-precision, inexact | 15 decimal digits precision |
smallserial | 2 bytes | small autoincrementing integer | 1 to 32767 |
serial | 4 bytes | autoincrementing integer | 1 to 2147483647 |
bigserial | 8 bytes | large autoincrementing integer | 1 to 9223372036854775807 |
其中 smallserial，serial 和 bigserial 并不是真正的数据类型，而是使用序列实现的用于方便表示字段唯一的标识（类似于 MySQL 中的 AUTO_INCREMENT）。
例如，以下字段定义：
CREATE TABLE tablename (
colname SERIAL
);
等价于：
CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
货币类型
货币类型存储具有固定小数精度的货币金额。
| Name | Storage Size | Description | Range |
|---|---|---|---|
money | 8 bytes | currency amount | -92233720368547758.08 to +92233720368547758.07 |
小数精度由参数 lc_monetary 决定，默认为 en_US.UTF-8：
postgres=# show lc_monetary;
lc_monetary
-------------
en_US.UTF-8
(1 row)
货币类型字段接收整型，浮点型及货币格式（例如 '$1,000.00'）输入，根据语言环境决定输出格式。
postgres=# select 123.45678::money;
money
---------
$123.46
(1 row)
postgres=# set lc_monetary='zh_CN.UTF-8';
SET
postgres=# select 123.45678::money;
money
----------
￥123.46
(1 row)
不要使用浮点类型处理资金以避免出现圆整错误。
字符类型
字符类型包括：
| Name | Description |
|---|---|
character varying(n), varchar(n) | variable-length with limit |
character(n), char(n) | fixed-length, blank padded |
text | variable unlimited length |
其中：
n应该大于 0，不超过 10485760。char(n)和varchar(n)表示可以存储长度不超过n字符（而不是字节）的字符串，如果字符串长度超过n，则会报错，除非多余的字符都是空格，此时字符串将会被截取为最大长度。如果字符串长度小于n，char将使用空格填充。char不指定长度，等价于char(1)。varchar不指定长度，表示任意长度。text存储任何长度字符串。- 将
char值转换为其他字符串类型时，将删除尾随空格。 - 数据库自动压缩长字符串以减少磁盘占用。
- 最多可以存储 1 GB 字符串。
- 大多数情况下，建议使用
text或者varchar。
例子：使用字符类型
CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- (1)
a | char_length
------+-------------
ok | 2
CREATE TABLE test2 (b varchar(5));
INSERT INTO test2 VALUES ('ok');
INSERT INTO test2 VALUES ('good ');
INSERT INTO test2 VALUES ('too long');
ERROR: value too long for type character varying(5)
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
SELECT b, char_length(b) FROM test2;
b | char_length
-------+-------------
ok | 2
good | 5
too l | 5
日期时间类型
日期时间类型包括：
| Name | Storage Size | Description | Low Value | High Value | Resolution |
|---|---|---|---|---|---|
timestamp [ (p) ] [ without time zone ] | 8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond |
timestamp [ (p) ] with time zone | 8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond |
date | 4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day |
time [ (p) ] [ without time zone ] | 8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond |
time [ (p) ] with time zone | 12 bytes | time of day (no date), with time zone | 00:00:00+1559 | 24:00:00-1559 | 1 microsecond |
interval [ fields ] [ (p) ] | 16 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond |
其中：
time，timestamp和interval可以使用p（0 到 6）指定秒的精度。interval的fields可以是：
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
- 如果
interval同时指定了fields和p，因为p仅适用于秒，故fields必须包含SECOND。
日期时间输入
任何日期或时间文本输入都需要用单引号括起来。
语法：
type [ (p) ] 'value'
日期类型输入示例：
| Example | Description |
|---|---|
| 1999-01-08 | ISO 8601; January 8 in any mode (recommended format) |
| January 8, 1999 | unambiguous in any datestyle input mode |
| 1/8/1999 | January 8 in MDY mode; August 1 in DMY mode |
| 1/18/1999 | January 18 in MDY mode; rejected in other modes |
| 01/02/03 | January 2, 2003 in MDY mode; February 1, 2003 in DMY mode; February 3, 2001 in YMD mode |
| 1999-Jan-08 | January 8 in any mode |
| Jan-08-1999 | January 8 in any mode |
| 08-Jan-1999 | January 8 in any mode |
| 99-Jan-08 | January 8 in YMD mode, else error |
| 08-Jan-99 | January 8, except error in YMD mode |
| Jan-08-99 | January 8, except error in YMD mode |
| 19990108 | ISO 8601; January 8, 1999 in any mode |
| 990108 | ISO 8601; January 8, 1999 in any mode |
| 1999.008 | year and day of year |
| J2451187 | Julian date |
| January 8, 99 BC | year 99 BC |
时间类型输入示例：
| Example | Description |
|---|---|
04:05:06.789 | ISO 8601 |
04:05:06 | ISO 8601 |
04:05 | ISO 8601 |
040506 | ISO 8601 |
04:05 AM | same as 04:05; AM does not affect value |
04:05 PM | same as 16:05; input hour must be <= 12 |
04:05:06.789-8 | ISO 8601, with time zone as UTC offset |
04:05:06-08:00 | ISO 8601, with time zone as UTC offset |
04:05-08:00 | ISO 8601, with time zone as UTC offset |
040506-08 | ISO 8601, with time zone as UTC offset |
040506+0730 | ISO 8601, with fractional-hour time zone as UTC offset |
040506+07:30:00 | UTC offset specified to seconds (not allowed in ISO 8601) |
04:05:06 PST | time zone specified by abbreviation |
2003-04-12 04:05:06 America/New_York | time zone specified by full name |
时区输入示例：
| Example | Description |
|---|---|
PST | Abbreviation (for Pacific Standard Time) |
America/New_York | Full time zone name |
PST8PDT | POSIX-style time zone specification |
-8:00:00 | UTC offset for PST |
-8:00 | UTC offset for PST (ISO 8601 extended format) |
-800 | UTC offset for PST (ISO 8601 basic format) |
-8 | UTC offset for PST (ISO 8601 basic format) |
zulu | Military abbreviation for UTC |
z | Short form of zulu (also in ISO 8601) |
默认时区由参数 TimeZone 指定：
postgres=# show TimeZone;
TimeZone
---------------
Asia/Shanghai
(1 row)
时间戳输入示例：
TIMESTAMP '2004-10-19 10:23:54'
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
特殊日期时间输入：
| Input String | Valid Types | Description |
|---|---|---|
epoch | date, timestamp | 1970-01-01 00:00:00+00 (Unix system time zero) |
infinity | date, timestamp | later than all other time stamps |
-infinity | date, timestamp | earlier than all other time stamps |
now | date, time, timestamp | current transaction's start time |
today | date, timestamp | midnight (00:00) today |
tomorrow | date, timestamp | midnight (00:00) tomorrow |
yesterday | date, timestamp | midnight (00:00) yesterday |
allballs | time | 00:00:00.00 UTC |
日期时间输出
输出格式有以下 4 种：
| Style Specification | Description | Example |
|---|---|---|
ISO | ISO 8601, SQL standard | 1997-12-17 07:37:16-08 |
SQL | traditional style | 12/17/1997 07:37:16.00 PST |
Postgres | original style | Wed Dec 17 07:37:16 1997 PST |
German | regional style | 17.12.1997 07:37:16.00 PST |
默认为 ISO，由参数 datestyle 指定：
postgres=# show datestyle;
DateStyle
-----------
ISO, MDY
(1 row)
在 SQL 和 POSTGRES 样式中，可以使用参数 datestyle 指定年月日显示顺序：
datestyle Setting | Input Ordering | Example Output |
|---|---|---|
SQL, DMY | day/month/year | 17/12/1997 15:37:16.00 CET |
SQL, MDY | month/day/year | 12/17/1997 07:37:16.00 PST |
Postgres, DMY | day/month/year | Wed 17 Dec 07:37:16 1997 PST |
间隔输入
间隔类型的语法为 interval [ fields ] [ (p) ]。
其中 interval 可以写为：
[@] quantity unit [quantity unit...] [direction]
其中：
quantity：为一个数字。unit：为时间单位，包括microsecond，millisecond，second，minute，hour，day，week，month，year，decade，century，millennium，及这些单位的缩写和复数形式。缩写有：
| Abbreviation | Meaning |
|---|---|
| Y | Years |
| M | Months (in the date part) |
| W | Weeks |
| D | Days |
| H | Hours |
| M | Minutes (in the time part) |
| S | Seconds |
- 可以直接指定天、小时、分钟和秒，而无需明确的单位标记。例如，
'1 12:59:10'与'1 day 12 hours 59 min 10 sec'相同。 - 以用破折号指定年份和月份的组合，例如
'200-10'与'200 years 10 months'相同。
其中 fields 可以是：
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND
间隔输入示例：
| Example | Description |
|---|---|
1-2 | SQL standard format: 1 year 2 months |
3 4:05:06 | SQL standard format: 3 days 4 hours 5 minutes 6 seconds |
1 year 2 months 3 days 4 hours 5 minutes 6 seconds | Traditional Postgres format: 1 year 2 months 3 days 4 hours 5 minutes 6 seconds |
P1Y2M3DT4H5M6S | ISO 8601 “format with designators”: same meaning as above |
P0001-02-03T04:05:06 | ISO 8601 “alternative format”: same meaning as above |
间隔输出
间隔类型的输出格式有 4 种：
sql_standardpostgrespostgres_verboseiso_8601
默认为 postgres，由参数 intervalstyle 指定：
postgres=# show IntervalStyle;
IntervalStyle
---------------
postgres
(1 row)
使用 SET intervalstyle 进行设置。
间隔输出示例：
| Style Specification | Year-Month Interval | Day-Time Interval | Mixed Interval |
|---|---|---|---|
sql_standard | 1-2 | 3 4:05:06 | -1-2 +3 -4:05:06 |
postgres | 1 year 2 mons | 3 days 04:05:06 | -1 year -2 mons +3 days -04:05:06 |
postgres_verbose | @ 1 year 2 mons | @ 3 days 4 hours 5 mins 6 secs | @ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago |
iso_8601 | P1Y2M | P3DT4H5M6S | P-1Y-2M3DT-4H-5M-6S |
布尔类型
布尔类型 boolean：
| Name | Storage Size | Description |
|---|---|---|
boolean | 1 byte | state of true or false |
布尔常量可以在 SQL 查询中用关键字 TRUE 、FALSE 和 NULL 表示。
当要向布尔类型字段输入 true 时，可以指定以下字符串：
trueyeson1
当要向布尔类型字段输入 false 时，可以指定以下字符串：
falsenooff0
布尔类型字段的输出为 t 或者 f。例如：
CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
a | b
---+---------
t | sic est
f | non est
SELECT * FROM test1 WHERE a;
a | b
---+---------
t | sic est
网络地址类型
PostgreSQL 提供了以下数据类型存储 IPv4，IPv6 和 MAC 地址：
| Name | Storage Size | Description |
|---|---|---|
cidr | 7 or 19 bytes | IPv4 and IPv6 networks |
inet | 7 or 19 bytes | IPv4 and IPv6 hosts and networks |
macaddr | 6 bytes | MAC addresses |
macaddr8 | 8 bytes | MAC addresses (EUI-64 format) |
相比使用纯文本类型来存储网络地址，以上类型提供了输入错误检查及相关运算符和函数。
inet 不会判断网络地址与掩码是否匹配，而 cidr 会判断网络地址与掩码是否匹配。
postgres=# select inet'192.168.92.161/24';
inet
-------------------
192.168.92.161/24
(1 row)
postgres=# select cidr'192.168.92.161/24';
ERROR: invalid cidr value: "192.168.92.161/24"
LINE 1: select cidr'192.168.92.161/24';
^
DETAIL: Value has bits set to right of mask.
postgres=# select cidr'192.168.92.161/32';
cidr
-------------------
192.168.92.161/32
(1 row)
约束
除了为字段指定数据类型以限制字段只能存储该类型的数据外，还可以为字段指定约束以限制字段可以存储的数据。
约束可以定义在字段级别和表级别。如果要对多个字段指定约束，则只能在表级定义。如果未指定约束名称，则将自动生成一个名称。
检查约束
指定一个布尔表达式，插入或更新的值需要满足该表达式。使用 CHECK 关键字定义。
例子：为字段指定检查约束
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
例子：使用 CONSTRAINT 关键字为约束指定名称
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
例子：在表级指定约束
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0 AND price > discounted_price)
);
例子：为表级约束指定名称
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);
非空约束
非空约束不允许字段包含 NULL 值，只能在字段级别定义。使用 NOT NULL 关键字定义，等同于 CHECK (column_name IS NOT NULL)。
例子：为字段指定非空约束
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
例子：同时为字段指定非空约束和检查约束
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0)
);
唯一约束
唯一约束指定一个或者多个字段的值唯一。使用 UNIQUE 关键字定义。
创建唯一约束将自动在约束列上创建唯一 B 树索引。
唯一约束默认可以包含 NULL 值，即两个 NULL 值默认不相等（NULLS DISTINCT），也可以使用 NULLS NOT DISTINCT 更改此默认行为。
例子：为字段指定唯一约束
CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric
);
例子：在表级为单个字段指定唯一约束
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);
例子：在表级为多个字段指定唯一约束
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
例子：为唯一约束指定名称
CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);
例子：为字段指定唯一约束，并指定 NULL 值相等
CREATE TABLE products (
product_no integer UNIQUE NULLS NOT DISTINCT,
name text,
price numeric
);
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE NULLS NOT DISTINCT (product_no)
);
主键约束
主键约束指定一个或者多个字段的值唯一且非空。使用 PRIMARY KEY 关键字定义。
创建主键约束将自动在约束列上创建唯一 B 树索引并标记字段非空。
一个表最多只能有一个主键，建议为每个表都创建主键。
例子：为字段指定主键约束
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE products (
product_no integer UNIQUE NOT NULL,
name text,
price numeric
);
例子：为多个字段指定主键约束
CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
外键约束
外键约束用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性，以保证从表外键约束字段的数据必须来自于主表的被引用字段或者为空。
例如有以下作为主表的产品表：
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
如果需要确保订单表中的产品必须真实存在，则可以在该表的 product_no 字段上创建外键约束，引用产品表中的 product_no 字段（该字段必须为主表的主键或者唯一键）：
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
注意：
不能在临时表和永久表之间定义外键约束。
如果在产品表中 product_no 字段为主键，则外键约束可以省略主表字段，简写为：
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products,
quantity integer
);
可以在表级为多个字段创建外键约束：
CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
主表和从表也可以是同一张表，用于具有层级关系的数据：
CREATE TABLE tree (
node_id integer PRIMARY KEY,
parent_id integer REFERENCES tree,
name text,
...
);
一个表也可以包含多个外键约束，引用多个表的数据：
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);
CREATE TABLE order_items (
product_no integer REFERENCES products,
order_id integer REFERENCES orders,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
为从表的列增加外键约束后，删除主表记录或者更新主表被引用字段时，为确保引用完整性，可以为外键约束增加删除（ ON DELETE）或更新（ON UPDATE）选项：
NO ACTION：删除主表记录或者更新主表被引用字段将报错，不允许执行该操作，这是默认选项。对于延迟约束，将在约束检查时报错。RESTRICT：与NO ACTION相同，适用于不可延迟约束。CASCADE：级联删除或更新从表中的数据。SET NULL：将从表的外键约束字段置为空，只适用于ON DELETE。SET DEFAULT：将从表的外键约束字段置为默认值，只适用于ON DELETE。如果默认值不为空，则必须与主表被引用字段的某个值匹配。
应根据业务情况，选项合适的选项创建外键：
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
对于 SET NULL 和 SET DEFAULT，可以指定外键约束字段中的某个字段：
CREATE TABLE tenants (
tenant_id integer PRIMARY KEY
);
CREATE TABLE users (
tenant_id integer REFERENCES tenants ON DELETE CASCADE,
user_id integer NOT NULL,
PRIMARY KEY (tenant_id, user_id)
);
CREATE TABLE posts (
tenant_id integer REFERENCES tenants ON DELETE CASCADE,
post_id integer NOT NULL,
author_id integer,
PRIMARY KEY (tenant_id, post_id),
FOREIGN KEY (tenant_id, author_id) REFERENCES users ON DELETE SET NULL (author_id)
);
注意：
删除主表记录或者更新主表被引用字段都会扫描从表的匹配数据，故建议在从表外键上创建索引。
表
在关系型数据库中，数据是存储在表中。与 MySQL 的表为索引组织表不同，PostgreSQL 的表与 Oracle 一样，为堆表。
表由列（也称为字段）组成，每列有名称和数据类型。数据以行（也称为记录）的形式存入表中。
表包含的列数量是有限制的，根据数据类型不同，最多可以有 250 到 1600 列。
创建表
使用 CREATE TABLE 创建表。
语法：
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
OF type_name [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
PARTITION OF parent_table [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and like_option is:
{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
and partition_bound_spec is:
IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
exclude_element in an EXCLUDE constraint is:
{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
referential_action in a FOREIGN KEY/REFERENCES constraint is:
{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }
其中：
- 表名和字段名以字母开头，可以使用数字和下划线，默认不应超过 63 个字节，否则会被截断（其他对象名也一样）。表名不能与同一模式下的其他表，视图，索引，序列，物化视图，外部表及数据类型同名。
- 可以在表名前面增加模式名前缀以创建在指定的模式中，否则创建在当前模式中。临时表位于特殊模式中，不能在临时表前面增加模式名前缀。
常用选项有：
TEMPORARY或TEMP：创建临时表。由于 Autovacuum Daemon 无法对临时表进行 VACUUM 和 ANALYZE，如果在复杂查询中使用到了临时表，建议在向临时表灌入数据后手动执行 ANALYZE。使用ON COMMIT来控制临时表行为，包括：PRESERVE ROWS：默认行为，事务结束后保留临时表中的数据。DELETE ROWS：事务结束后会清除（TRUNCATE）临时表中所有记录。DROP：事务结束后会删除临时表。
UNLOGGED：指定对表的 DML 操作不写 WAL。虽然性能较好，但如果数据库崩溃或者非正常关闭，则该表会被自动截断（TRUNCATE）。同时该表也不会被复制到备库。COMPRESSION：为字段指定压缩方法，仅用于可变长度数据类型以及字段的存储模式为main或extended。支持的压缩方法有pglz和lz4（编译时需使用--with-lz4选项） 。还可以指定为default表示使用参数default_toast_compression指定的压缩方法。COLLATE：为字段指定排序规则。如果未指定，则使用字段数据类型的默认排序规则。CONSTRAINT：为字段或者表指定约束。如果要对多个字段指定约束，则只能在表级定义。如果未指定约束名称，则将自动生成一个名称。约束包括：NULL：默认设置，允许字段包含 NULL 值。NOT NULL：非空约束，不允许字段包含 NULL 值，只能在字段级别定义，等同于CHECK (column_name IS NOT NULL)。CHECK：检查约束，指定一个布尔表达式，插入或更新的值需要满足该表达式。UNIQUE：唯一约束，指定一个或者多个字段的值唯一。PRIMARY KEY：主键约束，指定一个或者多个字段的值唯一且非空。REFERENCES：外键约束，维护从表（Child Table）和主表（Parent Table）之间的引用完整性。
NOT DEFERRABLE：每个命令执行完成后立即检查约束，默认动作。DEFERRABLE：表示该约束是可延迟验证的。适用于UNIQUE，PRIMARY KEY，EXCLUDE和REFERENCES约束。包括以下选项：INITIALLY IMMEDIATE：默认，立即验证，执行完一个语句后就进行验证。INITIALLY DEFERRED：在事务结束时检查。
DEFAULT：字段默认值，必须与字段类型匹配，不能是表的其他字段或子查询。如果没有为字段指定默认值，则默认值为空。LIKE：复制表，包括字段名称，数据类型及非空约束。可以使用EXCLUDING（默认）或者INCLUDING来排除或者包含指定选项，包括：COMMENTS：字段，约束和索引的注释。COMPRESSION：字段的压缩方法。CONSTRAINTS：字段的CHECK约束。DEFAULTS：字段的默认值。INDEXES：索引，PRIMARY KEY，UNIQUE和EXCLUDE约束。STATISTICS：扩展统计信息。STORAGE：存储设置。ALL：所有选项。
角色
与 Oracle 和 MySQL 不同，PostgreSQL 使用角色（Role）管理数据库权限。角色是可以拥有数据库对象并具有数据库权限的实体，可以把数据库对象的各种权限授予给角色。
角色可以作为：
- 用户（User），具有登录权限的角色。
- 组（Group），包含其他角色的角色。
初始化数据库集群时会自动创建与操作系统用户同名的超级用户 postgres，必须先使用 postgres 超级用户这个初始角色来创建更多的角色。
在 psql 中使用 \du 命令查看角色：
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
hr | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
stone | | {}
还可以查询 pg_roles 系统表查看角色：
postgres=# select rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcanlogin,rolreplication,rolconnlimit from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------
pg_database_owner | f | t | f | f | f | f | -1
pg_read_all_data | f | t | f | f | f | f | -1
pg_write_all_data | f | t | f | f | f | f | -1
pg_monitor | f | t | f | f | f | f | -1
pg_read_all_settings | f | t | f | f | f | f | -1
pg_read_all_stats | f | t | f | f | f | f | -1
pg_stat_scan_tables | f | t | f | f | f | f | -1
pg_read_server_files | f | t | f | f | f | f | -1
pg_write_server_files | f | t | f | f | f | f | -1
pg_execute_server_program | f | t | f | f | f | f | -1
pg_signal_backend | f | t | f | f | f | f | -1
pg_checkpoint | f | t | f | f | f | f | -1
postgres | t | t | t | t | t | t | -1
hr | f | t | f | f | t | f | -1
stone | f | t | f | f | t | f | -1
(15 rows)
其中以 pg_ 开头的角色为预定义角色，具体如下：
| Role | Allowed Access |
|---|---|
| pg_read_all_data | Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to. |
| pg_write_all_data | Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to. |
| pg_read_all_settings | Read all configuration variables, even those normally visible only to superusers. |
| pg_read_all_stats | Read all pg_stat_* views and use various statistics related extensions, even those normally visible only to superusers. |
| pg_stat_scan_tables | Execute monitoring functions that may take ACCESS SHARE locks on tables, potentially for a long time. |
| pg_monitor | Read/execute various monitoring views and functions. This role is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables. |
| pg_database_owner | None. Membership consists, implicitly, of the current database owner. |
| pg_signal_backend | Signal another backend to cancel a query or terminate its session. |
| pg_read_server_files | Allow reading files from any location the database can access on the server with COPY and other file-access functions. |
| pg_write_server_files | Allow writing to files in any location the database can access on the server with COPY and other file-access functions. |
| pg_execute_server_program | Allow executing programs on the database server as the user the database runs as with COPY and other functions which allow executing a server-side program. |
| pg_checkpoint | Allow executing the CHECKPOINT command. |
| pg_use_reserved_connections | Allow use of connection slots reserved via reserved_connections. |
| pg_create_subscription | Allow users with CREATE permission on the database to issue CREATE SUBSCRIPTION. |
创建角色
使用 CREATE ROLE 创建角色。
语法：
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
其中：
SUPERUSER | NOSUPERUSER：角色是否为超级用户，默认为NOSUPERUSER。必须使用超级才能创建超级用户。CREATEDB | NOCREATEDB：角色是否可以创建数据库，默认为NOCREATEDB。CREATEROLE | NOCREATEROLE：角色是否可以管理其他角色（包括创建，修改，删除，授权，回收权限等），默认为NOCREATEROLE。INHERIT | NOINHERIT：角色是否可以继承其成员角色权限，默认为INHERIT，但不会继承LOGIN，SUPERUSER，CREATEDB和CREATEROLE属性。LOGIN | NOLOGIN：角色是否可以登录，默认为NOLOGIN，使用CREATE USER创建的角色登录选项默认为LOGIN。REPLICATION | NOREPLICATION：角色是否为复制角色，默认为NOREPLICATION。BYPASSRLS | NOBYPASSRLS：角色是否绕过行级别安全 （RLS） 策略，默认为NOBYPASSRLS。CONNECTION LIMIT connlimit：可以登录的角色最大并发连接数，默认为-1表示无限制。对超级用户不生效。PASSWORD 'password' | PASSWORD NULL：对可以登录的角色设置密码。如果未指定密码或显式设置为NULL，则用户的身份验证将失败。VALID UNTIL 'timestamp'：指定角色密码到期时间，默认永久有效。IN ROLE role_name [, ...]：将该角色授予给其他角色，建议使用GRANT语句。ROLE role_name [, ...]：将其他角色授予给该角色，建议使用GRANT语句。
例子：使用超级用户 postgres 登录后创建角色，可以创建数据库和管理角色
postgres=# CREATE ROLE admin WITH CREATEDB CREATEROLE;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Create role, Create DB, Cannot login | {}
hr | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
stone | | {}
例子：创建用户，指定密码及密码到期时间
postgres=# CREATE ROLE guest WITH LOGIN PASSWORD '123456' VALID UNTIL '2023-12-31';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Create role, Create DB, Cannot login | {}
guest | Password valid until 2023-12-31 00:00:00+08 | {}
hr | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
stone | | {}
修改角色
使用 ALTER ROLE 修改角色。
语法：
ALTER ROLE role_specification [ WITH ] option [ ... ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
ALTER ROLE name RENAME TO new_name
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
where role_specification can be:
role_name
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
其中：
- 第一个命令修改角色的属性，超级用户可以修改任何角色的属性，普通角色只能修改自己的密码。
- 第二个命令修改角色的名称，超级用户可以修改任何角色的名称。
- 第三个命令修改角色会话的默认参数，可以用于所有数据库，也可以使用
IN DATABASE指定数据库。如果使用ALL，则针对所有角色。如果使用ALL及IN DATABASE，则等价于ALTER DATABASE ... SET ...。
例子：修改 admin 角色属性，使其不具有 CREATEROLE
postgres=# ALTER ROLE admin NOCREATEROLE;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Create DB, Cannot login | {}
guest | Password valid until 2023-12-31 00:00:00+08 | {}
hr | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
stone | | {}
例子：修改 guest 用户密码
postgres=# ALTER ROLE guest WITH PASSWORD 'guest';
ALTER ROLE
例子：修改 guest 用户密码永不过期
postgres=# ALTER ROLE guest VALID UNTIL 'infinity';
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Create DB, Cannot login | {}
guest | Password valid until infinity | {}
hr | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
stone | | {}
例子：修改角色参数
postgres=# ALTER ROLE admin SET maintenance_work_mem = '1GB';
ALTER ROLE
删除角色
使用 DROP ROLE 修改角色。
语法：
DROP ROLE [ IF EXISTS ] name [, ...]
- 使用超级用户删除超级用户及非超级用户角色，可以使用具有
CREATEROLE权限及被授予ADMIN OPTION的角色删除非超级用户角色。 - 在删除角色前，必须删除角色拥有的对象或者将这些对象重新授予给其他角色，并回收授予给其他对象的任意权限。此时可以使用
REASSIGN OWNED和DROP OWNED命令。 - 如果角色还包括其他成员角色，删除该角色不会影响其他角色。
例子：授予 guest 用户 testdb 数据库上的权限，删除 guest 前需要先回收权限以解除权限依赖
postgres=# GRANT ALL ON DATABASE testdb to guest;
GRANT
postgres=# DROP ROLE guest;
ERROR: role "guest" cannot be dropped because some objects depend on it
DETAIL: privileges for database testdb
postgres=# REVOkE ALL ON DATABASE testdb FROM guest;
REVOKE
postgres=# DROP ROLE guest;
DROP ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Create DB, Cannot login | {}
hr | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
stone | | {}
例子：创建用户 test，并连接到该用户创建表，删除该用户前需要先将创建的对象删除或者赋予给其他用户以解除对象依赖
postgres=# CREATE USER test;
CREATE ROLE
postgres=# GRANT ALL ON SCHEMA public TO test;
GRANT
postgres=# \c - test
You are now connected to database "postgres" as user "test".
postgres=> create table t2(id int);
CREATE TABLE
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# DROP ROLE test;
ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
owner of table t2
postgres=# ALTER TABLE t2 OWNER TO postgres;
ALTER TABLE
postgres=# DROP ROLE test;
ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
postgres=# REVOKE ALL ON SCHEMA public FROM test;
REVOKE
postgres=# DROP ROLE test;
DROP ROLE
例子：使用 REASSIGN OWNED 将要用户的对象赋予给其他用户，然后再删除用户
postgres=# CREATE USER test;
CREATE ROLE
postgres=# GRANT ALL ON SCHEMA public TO test;
GRANT
postgres=# \c - test
You are now connected to database "postgres" as user "test".
postgres=> create table t3(id int);
CREATE TABLE
postgres=> create table t4(id int);
CREATE TABLE
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# DROP ROLE test;
ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
owner of table t3
owner of table t4
postgres=# REASSIGN OWNED BY test TO postgres;
REASSIGN OWNED
postgres=# DROP ROLE test;
ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
postgres=# REVOKE ALL ON SCHEMA public FROM test;
REVOKE
postgres=# DROP ROLE test;
DROP ROLE
例子：使用 DROP OWNED 将要用户的对象都删除，然后再删除用户
postgres=# CREATE USER test;
CREATE ROLE
postgres=# GRANT ALL ON SCHEMA public TO test;
GRANT
postgres=# \c - test
You are now connected to database "postgres" as user "test".
postgres=> create table t5(id int);
CREATE TABLE
postgres=> create table t6(id int);
CREATE TABLE
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# DROP ROLE test;
ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
owner of table t5
owner of table t6
postgres=# DROP OWNED BY test;
DROP OWNED
postgres=# DROP ROLE test;
DROP ROLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | t2 | table | postgres
public | t3 | table | postgres
public | t4 | table | postgres
(4 rows)
组角色
在 PostgreSQL 中，组角色是一种特殊的角色，它可以包含其他角色作为成员。组角色通常用于管理一组用户的权限，以便可以将相同的权限集授予多个用户，从而简化权限管理。例如有一组用户需要访问相同的数据库和表，可以创建一个组角色，并为这些用户添加这个组角色。然后可以将权限授予该组角色，而不需要单独为每个用户授予权限。
创建组角色的语法与创建角色一样，但一般不包含 LOGIN 属性，然后就可以使用 GRANT 和 REVOKE 命令为用户增加和删除组角色。
GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;
例子：创建组角色 manager，为用户 stone 添加这个组角色
postgres=# CREATE ROLE managers;
CREATE ROLE
postgres=# GRANT managers TO stone;
GRANT ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+------------
admin | Create DB, Cannot login | {}
hr | | {}
managers | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
stone | | {managers}
用户 stone 将会自动继承组角色 manager 的普通权限，但不会继承角色的 LOGIN，SUPERUSER，CREATEDB 和 CREATEROLE 这几个被认为是特殊权限的属性。如果要使用这些特殊权限，可以使用 SET ROLE manager 命令将自己的角色临时性“变成”该组角色，然后使用 SET ROLE NONE 命令恢复原始权限状态。
权限
对象的创建者拥有对象的所有权限，不同的对象有不同的权限。
权限及其适用的对象：
| Privilege | Abbreviation | Applicable Object Types |
|---|---|---|
SELECT | r (“read”) | LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column |
INSERT | a (“append”) | TABLE, table column |
UPDATE | w (“write”) | LARGE OBJECT, SEQUENCE, TABLE, table column |
DELETE | d | TABLE |
TRUNCATE | D | TABLE |
REFERENCES | x | TABLE, table column |
TRIGGER | t | TABLE |
CREATE | C | DATABASE, SCHEMA, TABLESPACE |
CONNECT | c | DATABASE |
TEMPORARY | T | DATABASE |
EXECUTE | X | FUNCTION, PROCEDURE |
USAGE | U | DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE |
SET | s | PARAMETER |
ALTER SYSTEM | A | PARAMETER |
对象可用权限及检查权限的 psql 命令：
| Object Type | All Privileges | Default PUBLIC Privileges | psql Command |
|---|---|---|---|
DATABASE | CTc | Tc | \l |
DOMAIN | U | U | \dD+ |
FUNCTION or PROCEDURE | X | X | \df+ |
FOREIGN DATA WRAPPER | U | none | \dew+ |
FOREIGN SERVER | U | none | \des+ |
LANGUAGE | U | U | \dL+ |
LARGE OBJECT | rw | none | \dl+ |
PARAMETER | sA | none | \dconfig+ |
SCHEMA | UC | none | \dn+ |
SEQUENCE | rwU | none | \dp |
TABLE (and table-like objects) | arwdDxt | none | \dp |
| Table column | arwx | none | \dp |
TABLESPACE | C | none | \db+ |
TYPE | U | U | \dT+ |
其中：
- 默认将数据库的
CONNECT和TEMPORARY权限授予给PUBLIC。 - 默认将函数和存储过程的
EXECUTE权限授予给PUBLIC。 - 默认将语言和数据类型（包括域）的
USAGE权限授予给PUBLIC。 - 对于其他对象，默认没有权限授予给
PUBLIC。 - 可以使用
ALTER DEFAULT PRIVILEGES命令覆盖这些默认权限设置。
在 psql 中使用 \dp 查看权限：
postgres=# \c appdb
You are now connected to database "appdb" as user "postgres".
appdb=# \dn
List of schemas
Name | Owner
--------+-------------------
hr | hr
public | pg_database_owner
(2 rows)
appdb=# \c - hr
You are now connected to database "appdb" as user "hr".
appdb=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
hr | t1 | table | hr
(1 row)
appdb=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
hr | t1 | table | | |
(1 row)
最开始 Access privileges 字段为空，表示对象有默认权限，包括所有者的所有权限及与对象相关的 PUBLIC 权限。
授予权限
使用 GRANT 授予权限。
语法：
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { SET | ALTER SYSTEM } [, ... ] | ALL [ PRIVILEGES ] }
ON PARAMETER configuration_parameter [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT role_name [, ...] TO role_specification [, ...]
[ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
[ GRANTED BY role_specification ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
可以将以上 GRANT 命令分为两类：
- 将数据库对象的权限授予给一个或多个角色。
- 将角色授予给角色。
当对数据库对象进行授权操作：
- 其中
TO PUBLIC表示将该角色所拥有的权限授予给所有角色，包括后续创建的角色，数据库中所创建的角色都会继承PUBLIC组角色的权限。故任何特定角色的权限包括直接授予的权限，授予其包含角色的权限以及授予给PUBLIC的权限。 - 如果指定了
WITH GRANT OPTION，则权限接收者可以将权限授予给其他人。但权限接收者为PUBLIC时不能指定WITH GRANT OPTION。 - 对象所有者默认有对象的所有权限，故无需向对象所有者授权。
- 只有对象所有者才有删除对象及更改对象定义的权限，此权限不能授予及回收，但可以通过组角色继承。
- 权限包括：
SELECTINSERTUPDATEDELETETRUNCATEREFERENCESTRIGGERCREATECONNECTTEMPORARYEXECUTEUSAGESETALTER SYSTEMTEMP或者TEMPORARYALL PRIVILEGES或者ALL
FUNCTION包括普通函数、聚合函数和窗口函数，ROUTINE包括普通函数、聚合函数和窗口函数及存储过程。
当对角色进行授权操作：
ADMIN选项设置为TRUE表示允许接收者将收到的角色授予给其他角色，或者从其他角色回收，默认为FALSE。超级用户可以就角色授予给其他角色，或者从其他角色回收角色。INHERIT选项设置为TRUE表示接收者将继承所授予角色的权限，设置为FALSE表示接收者将不继承所授予角色的权限。如果没有指定该子句，如果所授予角色被设置为INHERIT，那么默认为TRUE；如果所授予角色被设置为NOINHERIT，那么默认为FALSE。参考创建角色。SET选项设置为TRUE表示允许接收者使用SET ROLE命令更改角色，适用于组角色，默认为TRUE。- 要创建由其他角色拥有的对象或将现有对象的所有权授予另一个角色，必须能够使用
SET ROLE切换到该角色，否则使用ALTER ... OWNER或者CREATE DATABASE ... OWNER命令时会失败。但是，如果用户继承了某个角色的权限却不能够使用SET ROLE切换到该角色，可能通过操作该角色拥有的对象来获得对该角色的完全访问权限（例如可以重新定义现有函数以充当特洛伊木马）。因此，如果用户要继承某个角色的权限但不能使用SET ROLE切换到该角色，则该角色不应拥有任何 SQL 对象。 - 不能将角色授予给
PUBLIC。
注意：
- 超级用户可以访问所有对象，但尽可能使用对象所有者执行授予和回收权限命令。
- 如果使用超级用户执行授予和回收权限命令，实际上是由对象所有者执行的。
- 可以使用组角色的成员（组角色的接收者）或者在对象上有
WITH GRANT OPTION的角色执行对象授予和回收权限命令，实际上是由对象所有者执行的。例如角色g1拥有表t1，角色又被授予给用户u1，则u1可以授予t1的权限给u2。
例子：授予权限
先切换到表所属的数据库和用户
postgres=# \c appdb
You are now connected to database "appdb" as user "postgres".
appdb=# \dn
List of schemas
Name | Owner
--------+-------------------
hr | hr
public | pg_database_owner
(2 rows)
appdb=# \c - hr
You are now connected to database "appdb" as user "hr".
appdb=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
hr | t1 | table | hr
(1 row)
appdb=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
hr | t1 | table | | |
(1 row)
最开始 Access privileges 字段为空，表示对象有默认权限，包括所有者的所有权限及与对象相关的 PUBLIC 权限。
将对表 t1 的查询权限授予给 PUBLIC：
appdb=> GRANT SELECT ON t1 TO PUBLIC;
GRANT
appdb=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
hr | t1 | table | hr=arwdDxt/hr +| |
| | | =r/hr | |
(1 row)
对数据库对象执行第一个 GRANT 或 REVOKE 语句将实例化默认权限（hr=arwdDxt/hr），然后根据语句进行修改。Column privileges 字段只显示非默认权限。
将对表 t1 的所有权限授予给 stone，并允许 stone 转授权：
appdb=> GRANT ALL ON t1 TO stone WITH GRANT OPTION;
GRANT
appdb=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------------+-------------------+----------
hr | t1 | table | hr=arwdDxt/hr +| |
| | | =r/hr +| |
| | | stone=a*r*w*d*D*x*t*/hr | |
(1 row)
其中 * 表示带有 WITH GRANT OPTION。
将对表 t1 的字段 id 的查询权限授予给角色 managers：
appdb=> GRANT SELECT (id) ON t1 TO managers;
GRANT
appdb=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------------+-------------------+----------
hr | t1 | table | hr=arwdDxt/hr +| id: +|
| | | =r/hr +| managers=r/hr |
| | | stone=a*r*w*d*D*x*t*/hr | |
(1 row)
回收权限
使用 REVOKE 回收权限。
语法：
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { SET | ALTER SYSTEM } [, ...] | ALL [ PRIVILEGES ] }
ON PARAMETER configuration_parameter [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
REVOKE [ { ADMIN | INHERIT | SET } OPTION FOR ]
role_name [, ...] FROM role_specification [, ...]
[ GRANTED BY role_specification ]
[ CASCADE | RESTRICT ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
- 由于任何特定角色的权限包括直接授予的权限，授予其包含角色的权限以及授予给
PUBLIC的权限，如果仅回收直接授予的权限，则有可能该权限还存在与包含的角色中或者PUBLIC中。 - 如果指定了
GRANT OPTION FOR，则仅取消级联授予，而不回收权限，否则同时回收权限并取消级联授予。 - 如果存在级联权限，则需要使用
CASCADE进行回收，否则失败。 - 回收对表的权限时，表字段上的权限也会被自动回收。如果对表授予了相关权限，对列回收相同的权限将不起作用。
- 用户只能回收自己授予的权限。
- 可以使用组角色的成员（组角色的接收者）或者在对象上有
WITH GRANT OPTION的角色执行对象授予和回收权限命令，实际上是由对象所有者执行的。例如角色g1拥有表t1，角色又被授予给用户u1，则u1可以回收由角色g1授予该表t1的权限。
例子：回收权限
回收授予给角色 managers 对表 t1 的字段 id 的查询权限：
appdb=> REVOKE SELECT (id) ON t1 FROM managers;
REVOKE
appdb=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------------+-------------------+----------
hr | t1 | table | hr=arwdDxt/hr +| |
| | | =r/hr +| |
| | | stone=a*r*w*d*D*x*t*/hr | |
(1 row)
回收授予给 stone 对表 t1 的所有权限：
appdb=> REVOKE ALL ON t1 FROM stone;
REVOKE
appdb=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
hr | t1 | table | hr=arwdDxt/hr +| |
| | | =r/hr | |
(1 row)
回收授予给 PUBLIC 对表 t1 的查询权限：
appdb=> REVOKE SELECT ON t1 FROM PUBLIC;
REVOKE
appdb=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
hr | t1 | table | hr=arwdDxt/hr | |
(1 row)
修改默认权限
使用 ALTER DEFAULT PRIVILEGES 修改默认权限。
语法：
ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTIONS | ROUTINES }
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTIONS | ROUTINES }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPES
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ USAGE | CREATE | ALL [ PRIVILEGES ] }
ON SCHEMAS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
使用 ALTER DEFAULT PRIVILEGES 为将来创建的对象设置权限，不会影响现有的对象。当前只能为模式，表（包括视图和外表），序列，函数和存储过程，类型（包括域）设置权限。
例子：使用 ALTER DEFAULT PRIVILEGES 将某个模式下表的查询权限授予给某个只读用户，即使后续在该模式下增加表，也不需要再次进行授权了。
appdb=> \conninfo
You are connected to database "appdb" as user "hr" via socket in "/tmp" at port "5432".
appdb=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+------+-------------------
(0 rows)
appdb=> ALTER DEFAULT PRIVILEGES IN SCHEMA hr GRANT SELECT ON TABLES TO stone;
ALTER DEFAULT PRIVILEGES
appdb=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
-------+--------+-------+-------------------
hr | hr | table | stone=r/hr
(1 row)
参数
与 Oracle 参数类似，PostgreSQL 也有很多用于配置数据库的参数。
设置参数
参数名和值
- 参数名不区分大小写。
- 参数值有以下 5 种类型：
- Boolean：值可以是
on，off，true，false，yes，no，1，0（不区分大小写）。 - String：将值括在单引号中，如果值是简单的数字或标识符，则通常可以省略引号。
- Numeric：可以是整数和浮点数。
- Numeric with Unit：值必须写成字符串（带引号），单位名称区分大小写，数值和单位之间可以有空格。有效的内存单位为
B，kB，MB，GB和TB；有效的时间单位为us，ms，s，min，h，d。 - Enumerated：枚举类型的参数值是一组限定的值，设置方式与字符串参数一样，参数值不区分大小写。
- Boolean：值可以是
通过配置文件设置参数
通过编辑配置文件 $PGDATA/postgresql.conf 是设置参数的最基本方法。
文件内容如下：
[postgres@postgresql ~]$ cat $PGDATA/postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()". Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: B = bytes Time units: us = microseconds
# kB = kilobytes ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# TB = terabytes h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - TCP settings -
# see "man tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
#tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds;
# 0 selects the system default
#client_connection_check_interval = 0 # time between checks for client
# disconnection while running queries;
# 0 for never
# - Authentication -
#authentication_timeout = 1min # 1s-600s
#password_encryption = scram-sha-256 # scram-sha-256 or md5
#db_user_namespace = off
# GSSAPI using Kerberos
#krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
#krb_caseins_users = off
# - SSL -
#ssl = off
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'
#ssl_crl_file = ''
#ssl_crl_dir = ''
#ssl_key_file = 'server.key'
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = off
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 128MB # min 128kB
# (change requires restart)
#huge_pages = try # on, off, or try
# (change requires restart)
#huge_page_size = 0 # zero for system default
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
#work_mem = 4MB # min 64kB
#hash_mem_multiplier = 2.0 # 1-1000.0 multiplier on hash table work_mem
#maintenance_work_mem = 64MB # min 1MB
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
#logical_decoding_work_mem = 64MB # min 64kB
#max_stack_depth = 2MB # min 100kB
#shared_memory_type = mmap # the default is the first option
# supported by the operating system:
# mmap
# sysv
# windows
# (change requires restart)
dynamic_shared_memory_type = posix # the default is usually the first option
# supported by the operating system:
# posix
# sysv
# windows
# mmap
# (change requires restart)
#min_dynamic_shared_memory = 0MB # (change requires restart)
# - Disk -
#temp_file_limit = -1 # limits per-process temp file space
# in kilobytes, or -1 for no limit
# - Kernel Resources -
#max_files_per_process = 1000 # min 64
# (change requires restart)
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0 # 0-100 milliseconds (0 disables)
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 2 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
#bgwriter_flush_after = 512kB # measured in pages, 0 disables
# - Asynchronous Behavior -
#backend_flush_after = 0 # measured in pages, 0 disables
#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
#maintenance_io_concurrency = 10 # 1-1000; 0 disables prefetching
#max_worker_processes = 8 # (change requires restart)
#max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
#max_parallel_maintenance_workers = 2 # taken from max_parallel_workers
#max_parallel_workers = 8 # maximum number of max_worker_processes that
# can be used in parallel operations
#parallel_leader_participation = on
#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate
# (change requires restart)
#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#wal_level = replica # minimal, replica, or logical
# (change requires restart)
#fsync = on # flush data to disk for crash safety
# (turning this off can cause
# unrecoverable data corruption)
#synchronous_commit = on # synchronization level;
# off, local, remote_write, remote_apply, or on
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux and FreeBSD)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_log_hints = off # also do full page writes of non-critical updates
# (change requires restart)
#wal_compression = off # enables compression of full-page writes;
# off, pglz, lz4, zstd, or on
#wal_init_zero = on # zero-fill new WAL files
#wal_recycle = on # recycle WAL files
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#wal_writer_flush_after = 1MB # measured in pages, 0 disables
#wal_skip_threshold = 2MB
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_timeout = 5min # range 30s-1d
#checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB # measured in pages, 0 disables
#checkpoint_warning = 30s # 0 disables
max_wal_size = 1GB
min_wal_size = 80MB
# - Prefetching during recovery -
#recovery_prefetch = try # prefetch pages referenced in the WAL?
#wal_decode_buffer_size = 512kB # lookahead window used for prefetching
# (change requires restart)
# - Archiving -
#archive_mode = off # enables archiving; off, on, or always
# (change requires restart)
#archive_library = '' # library to use to archive a logfile segment
# (empty string indicates archive_command should
# be used)
#archive_command = '' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
# - Archive Recovery -
# These are only used in recovery mode.
#restore_command = '' # command to use to restore an archived logfile segment
# placeholders: %p = path of file to restore
# %f = file name only
# e.g. 'cp /mnt/server/archivedir/%f %p'
#archive_cleanup_command = '' # command to execute at every restartpoint
#recovery_end_command = '' # command to execute at completion of recovery
# - Recovery Target -
# Set these only when performing a targeted recovery.
#recovery_target = '' # 'immediate' to end recovery as soon as a
# consistent state is reached
# (change requires restart)
#recovery_target_name = '' # the named restore point to which recovery will proceed
# (change requires restart)
#recovery_target_time = '' # the time stamp up to which recovery will proceed
# (change requires restart)
#recovery_target_xid = '' # the transaction ID up to which recovery will proceed
# (change requires restart)
#recovery_target_lsn = '' # the WAL LSN up to which recovery will proceed
# (change requires restart)
#recovery_target_inclusive = on # Specifies whether to stop:
# just after the specified recovery target (on)
# just before the recovery target (off)
# (change requires restart)
#recovery_target_timeline = 'latest' # 'current', 'latest', or timeline ID
# (change requires restart)
#recovery_target_action = 'pause' # 'pause', 'promote', 'shutdown'
# (change requires restart)
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Servers -
# Set these on the primary and on any standby that will send replication data.
#max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
#max_replication_slots = 10 # max number of replication slots
# (change requires restart)
#wal_keep_size = 0 # in megabytes; 0 disables
#max_slot_wal_keep_size = -1 # in megabytes; -1 disables
#wal_sender_timeout = 60s # in milliseconds; 0 disables
#track_commit_timestamp = off # collect timestamp of transaction commit
# (change requires restart)
# - Primary Server -
# These settings are ignored on a standby server.
#synchronous_standby_names = '' # standby servers that provide sync rep
# method to choose sync standbys, number of sync standbys,
# and comma-separated list of application_name
# from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
# - Standby Servers -
# These settings are ignored on a primary server.
#primary_conninfo = '' # connection string to sending server
#primary_slot_name = '' # replication slot on sending server
#promote_trigger_file = '' # file name whose presence ends recovery
#hot_standby = on # "off" disallows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
#wal_receiver_create_temp_slot = off # create temp slot if primary_slot_name
# is not set
#wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
#wal_receiver_timeout = 60s # time that receiver waits for
# communication from primary
# in milliseconds; 0 disables
#wal_retrieve_retry_interval = 5s # time to wait before retrying to
# retrieve WAL after a failed attempt
#recovery_min_apply_delay = 0 # minimum delay for applying changes during recovery
# - Subscribers -
# These settings are ignored on a publisher.
#max_logical_replication_workers = 4 # taken from max_worker_processes
# (change requires restart)
#max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_async_append = on
#enable_bitmapscan = on
#enable_gathermerge = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_incremental_sort = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_memoize = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_parallel_append = on
#enable_parallel_hash = on
#enable_partition_pruning = on
#enable_partitionwise_join = off
#enable_partitionwise_aggregate = off
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#parallel_setup_cost = 1000.0 # same scale as above
#parallel_tuple_cost = 0.1 # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB
#jit_above_cost = 100000 # perform JIT compilation if available
# and query more expensive than this;
# -1 disables
#jit_inline_above_cost = 500000 # inline small functions if query is
# more expensive than this; -1 disables
#jit_optimize_above_cost = 500000 # use expensive JIT optimizations if
# query is more expensive than this;
# -1 disables
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0
# - Other Planner Options -
#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#jit = on # allow JIT compilation
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
#plan_cache_mode = auto # auto, force_generic_plan or
# force_custom_plan
#recursive_worktable_factor = 10.0 # range 0.001-1000000
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, jsonlog, syslog, and
# eventlog, depending on platform.
# csvlog and jsonlog require
# logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr, jsonlog,
# and csvlog into log files. Required
# to be on for csvlogs and jsonlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#syslog_sequence_numbers = on
#syslog_split_messages = on
# This is only relevant when logging to eventlog (Windows):
# (change requires restart)
#event_source = 'PostgreSQL'
# - When to Log -
#log_min_messages = warning # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
#log_min_error_statement = error # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
#log_min_duration_sample = -1 # -1 is disabled, 0 logs a sample of statements
# and their durations, > 0 logs only a sample of
# statements running at least this number
# of milliseconds;
# sample fraction is determined by log_statement_sample_rate
#log_statement_sample_rate = 1.0 # fraction of logged statements exceeding
# log_min_duration_sample to be logged;
# 1.0 logs all such statements, 0.0 never logs
#log_transaction_sample_rate = 0.0 # fraction of transactions whose statements
# are logged regardless of their duration; 1.0 logs all
# statements from all transactions, 0.0 never logs
#log_startup_progress_interval = 10s # Time between progress updates for
# long-running startup operations.
# 0 disables the feature, > 0 indicates
# the interval in milliseconds.
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_autovacuum_min_duration = 10min # log autovacuum activity;
# -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#log_checkpoints = on
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose messages
#log_hostname = off
log_line_prefix = '%m [%p] ' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %b = backend type
# %p = process ID
# %P = process ID of parallel group leader
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %n = timestamp with milliseconds (as a Unix epoch)
# %Q = query ID (0 if none or not computed)
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_recovery_conflict_waits = off # log standby recovery conflict waits
# >= deadlock_timeout
#log_parameter_max_length = -1 # when logging statements, limit logged
# bind-parameter values to N bytes;
# -1 means print in full, 0 disables
#log_parameter_max_length_on_error = 0 # when logging an error, limit logged
# bind-parameter values to N bytes;
# -1 means print in full, 0 disables
#log_statement = 'none' # none, ddl, mod, all
#log_replication_commands = off
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'Asia/Shanghai'
#------------------------------------------------------------------------------
# PROCESS TITLE
#------------------------------------------------------------------------------
#cluster_name = '' # added to process titles if nonempty
# (change requires restart)
#update_process_title = on
#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------
# - Cumulative Query and Index Statistics -
#track_activities = on
#track_activity_query_size = 1024 # (change requires restart)
#track_counts = on
#track_io_timing = off
#track_wal_io_timing = off
#track_functions = none # none, pl, all
#stats_fetch_consistency = cache
# - Monitoring -
#compute_query_id = auto
#log_statement_stats = off
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_vacuum_insert_threshold = 1000 # min number of row inserts
# before vacuum; -1 disables insert
# vacuums
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_vacuum_insert_scale_factor = 0.2 # fraction of inserts over table
# size before insert vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
# before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#search_path = '"$user", public' # schema names
#row_security = on
#default_table_access_method = 'heap'
#default_tablespace = '' # a tablespace name, '' uses the default
#default_toast_compression = 'pglz' # 'pglz' or 'lz4'
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0 # in milliseconds, 0 is disabled
#lock_timeout = 0 # in milliseconds, 0 is disabled
#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
#idle_session_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_table_age = 150000000
#vacuum_freeze_min_age = 50000000
#vacuum_failsafe_age = 1600000000
#vacuum_multixact_freeze_table_age = 150000000
#vacuum_multixact_freeze_min_age = 5000000
#vacuum_multixact_failsafe_age = 1600000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
#gin_pending_list_limit = 4MB
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'Asia/Shanghai'
#timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are
# Default
# Australia (historical usage)
# India
# You can create your own file in
# share/timezonesets/.
#extra_float_digits = 1 # min -15, max 3; any value >0 actually
# selects precise output mode
#client_encoding = sql_ascii # actually, defaults to database
# encoding
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Shared Library Preloading -
#local_preload_libraries = ''
#session_preload_libraries = ''
#shared_preload_libraries = '' # (change requires restart)
#jit_provider = 'llvmjit' # JIT library to use
# - Other Defaults -
#dynamic_library_path = '$libdir'
#gin_fuzzy_search_limit = 0
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
#max_pred_locks_per_transaction = 64 # min 10
# (change requires restart)
#max_pred_locks_per_relation = -2 # negative values mean
# (max_pred_locks_per_transaction
# / -max_pred_locks_per_relation) - 1
#max_pred_locks_per_page = 2 # min 0
#------------------------------------------------------------------------------
# VERSION AND PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#standard_conforming_strings = on
#synchronize_seqscans = on
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off # terminate session on any error?
#restart_after_crash = on # reinitialize after backend crash?
#data_sync_retry = off # retry or panic on failure to fsync
# data?
# (change requires restart)
#recovery_init_sync_method = fsync # fsync, syncfs (Linux 5.8+)
#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------
# These options allow settings to be loaded from files other than the
# default postgresql.conf. Note that these are directives, not variable
# assignments, so they can usefully be given more than once.
#include_dir = '...' # include files ending in '.conf' from
# a directory, e.g., 'conf.d'
#include_if_exists = '...' # include file only if it exists
#include = '...' # include file
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
其中：
- 每一行设置一个参数，格式为
参数名 = 参数值，会忽略=前后的空格以及文件中的空行。 - 不是简单标识符或数字的参数值必须用单引号引起来。
- 如果文件包含同一参数的多个条目，则最后一个条目有效。
- 通过执行操作系统命令
pg_ctl reload或者 SQL 函数pg_reload_conf()向主服务器进程发送 SIGHUP 信号，会重新读取配置文件，以便当前会话也使用新值（会话完成当前正在执行的的客户端命令后）。但某些参数只能重启服务器才能生效。
除了 $PGDATA/postgresql.conf，PostgreSQL 数据目录还包含一个 postgresql.auto.conf 文件，其格式与 postgresql.conf 相同。但不要手动编辑该文件，而是会将 ALTER SYSTEM 命令修改的参数写入到该文件中。postgresql.auto.conf 文件中的参数会覆盖 postgresql.conf 文件中的参数。
[postgres@postgresql ~]$ cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
可以使用 pg_file_settings 视图查看配置文件中设置的参数情况：
postgres=# select * from pg_file_settings;
sourcefile | sourceline | seqno | name | setting | applied | error
-------------------------------+------------+-------+----------------------------+--------------------+---------+-------
/pg/data/postgresql.conf | 65 | 1 | max_connections | 100 | t |
/pg/data/postgresql.conf | 127 | 2 | shared_buffers | 128MB | t |
/pg/data/postgresql.conf | 150 | 3 | dynamic_shared_memory_type | posix | t |
/pg/data/postgresql.conf | 241 | 4 | max_wal_size | 1GB | t |
/pg/data/postgresql.conf | 242 | 5 | min_wal_size | 80MB | t |
/pg/data/postgresql.conf | 444 | 6 | log_destination | stderr | t |
/pg/data/postgresql.conf | 451 | 7 | logging_collector | on | t |
/pg/data/postgresql.conf | 457 | 8 | log_directory | log | t |
/pg/data/postgresql.conf | 459 | 9 | log_filename | postgresql-%a.log | t |
/pg/data/postgresql.conf | 463 | 10 | log_rotation_age | 1d | t |
/pg/data/postgresql.conf | 465 | 11 | log_rotation_size | 0 | t |
/pg/data/postgresql.conf | 468 | 12 | log_truncate_on_rotation | on | t |
/pg/data/postgresql.conf | 559 | 13 | log_line_prefix | %m [%p] | t |
/pg/data/postgresql.conf | 597 | 14 | log_timezone | Asia/Shanghai | t |
/pg/data/postgresql.conf | 711 | 15 | datestyle | iso, mdy | t |
/pg/data/postgresql.conf | 713 | 16 | timezone | Asia/Shanghai | t |
/pg/data/postgresql.conf | 727 | 17 | lc_messages | en_US.UTF-8 | t |
/pg/data/postgresql.conf | 729 | 18 | lc_monetary | en_US.UTF-8 | t |
/pg/data/postgresql.conf | 730 | 19 | lc_numeric | en_US.UTF-8 | t |
/pg/data/postgresql.conf | 731 | 20 | lc_time | en_US.UTF-8 | t |
/pg/data/postgresql.conf | 734 | 21 | default_text_search_config | pg_catalog.english | t |
/pg/data/postgresql.auto.conf | 3 | 22 | listen_addresses | * | t |
(22 rows)
其中：
sourcefile表示配置文件的完整路径名。sourceline表示该参数在配置文件中的行数。seqno表示序号。name表示参数名。setting表示参数值。applied字段为t表示该参数设置成功。error字段如果不为空，则为参数设置失败的原因。
如果配置文件有语法错误或无效的参数名称，则服务器将不会应用其中的任何设置，所有 applied 字段都将为 f，此时会有一行或多行具有非空 error 字段以指示错误。
通过 SQL 命令设置参数
可以使用以下 SQL 命令设置参数：
ALTER SYSTEM：设置整个数据库集群的参数，全局设置。会将修改的参数写入到$PGDATA/postgresql.auto.conf文件中，以覆盖$PGDATA/postgresql.conf中的参数。将参数设置为DEFAULT或使用RESET，则会从$PGDATA/postgresql.auto.conf文件中移除对应条目，使用RESET ALL从$PGDATA/postgresql.auto.conf文件中移除所有配置条目。设置完成后，通过执行操作系统命令pg_ctl reload或者 SQL 函数pg_reload_conf()向主服务器进程发送 SIGHUP 信号，会重新加载配置文件以生效。对于只能在服务器启动时设置的参数，只能在下次重启时生效。
语法：
ALTER SYSTEM SET configuration_parameter { TO | = } { value [, ...] | DEFAULT }
ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL
ALTER DATABASE：设置某个数据库的参数，覆盖全局设置。仅在启动新的数据库会话时应用。不能使用此命令设置在数据库运行时不能修改的参数。ALTER ROLE：设置某个用户的参数，覆盖数据库设置和全局设置。仅在启动新的数据库会话时应用。不能使用此命令设置在数据库运行时不能修改的参数。
可以在客户端使用 SQL 命令查看和修改当前会话参数值：
- 使用
SHOW命令或者current_setting(setting_name text)函数查看参数当前值。
SHOW configuration_parameter | SERVER_VERSION | IS_SUPERUSER | SERVER_ENCODING | LC_COLLATE | LC_CTYPE
SHOW ALL
- 使用
SET命令或者set_config(setting_name, new_value, is_local)函数可为当前会话修改参数，不影响其他会话。类似于 MySQL 中的SET SESSION或SET @@SESSION。SET LOCAL仅对当前事务生效。
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { value | 'value' | LOCAL | DEFAULT }
还可以使用系统视图 pg_settings 查看和修改当前会话参数值：
- 查询此视图类似于使用
SHOW ALL，但提供了更多详细信息。 - 使用
UPDATE语句更新该视图的setting字段，相当于使用SET命令。例如：
SET configuration_parameter TO DEFAULT;
等同于：
UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';
查询 pg_settings 视图查看当前会话参数设置：
postgres=# select name,setting,unit,context from pg_settings;
name | setting | unit | context
----------------------------------------+--------------------------------+------+-------------------
allow_in_place_tablespaces | off | | superuser
allow_system_table_mods | off | | superuser
application_name | psql | | user
archive_cleanup_command | | | sighup
archive_command | (disabled) | | sighup
archive_library | | | sighup
archive_mode | off | | postmaster
archive_timeout | 0 | s | sighup
array_nulls | on | | user
authentication_timeout | 60 | s | sighup
autovacuum | on | | sighup
autovacuum_analyze_scale_factor | 0.1 | | sighup
autovacuum_analyze_threshold | 50 | | sighup
autovacuum_freeze_max_age | 200000000 | | postmaster
autovacuum_max_workers | 3 | | postmaster
autovacuum_multixact_freeze_max_age | 400000000 | | postmaster
autovacuum_naptime | 60 | s | sighup
autovacuum_vacuum_cost_delay | 2 | ms | sighup
autovacuum_vacuum_cost_limit | -1 | | sighup
autovacuum_vacuum_insert_scale_factor | 0.2 | | sighup
autovacuum_vacuum_insert_threshold | 1000 | | sighup
autovacuum_vacuum_scale_factor | 0.2 | | sighup
autovacuum_vacuum_threshold | 50 | | sighup
autovacuum_work_mem | -1 | kB | sighup
backend_flush_after | 0 | 8kB | user
backslash_quote | safe_encoding | | user
backtrace_functions | | | superuser
bgwriter_delay | 200 | ms | sighup
bgwriter_flush_after | 64 | 8kB | sighup
bgwriter_lru_maxpages | 100 | | sighup
bgwriter_lru_multiplier | 2 | | sighup
block_size | 8192 | | internal
bonjour | off | | postmaster
bonjour_name | | | postmaster
bytea_output | hex | | user
check_function_bodies | on | | user
checkpoint_completion_target | 0.9 | | sighup
checkpoint_flush_after | 32 | 8kB | sighup
checkpoint_timeout | 300 | s | sighup
checkpoint_warning | 30 | s | sighup
client_connection_check_interval | 0 | ms | user
client_encoding | UTF8 | | user
client_min_messages | notice | | user
cluster_name | | | postmaster
commit_delay | 0 | | superuser
commit_siblings | 5 | | user
compute_query_id | auto | | superuser
config_file | /pg/data/postgresql.conf | | postmaster
constraint_exclusion | partition | | user
cpu_index_tuple_cost | 0.005 | | user
cpu_operator_cost | 0.0025 | | user
cpu_tuple_cost | 0.01 | | user
cursor_tuple_fraction | 0.1 | | user
data_checksums | off | | internal
data_directory | /pg/data | | postmaster
data_directory_mode | 0700 | | internal
data_sync_retry | off | | postmaster
DateStyle | ISO, MDY | | user
db_user_namespace | off | | sighup
deadlock_timeout | 1000 | ms | superuser
debug_assertions | off | | internal
debug_discard_caches | 0 | | superuser
debug_pretty_print | on | | user
debug_print_parse | off | | user
debug_print_plan | off | | user
debug_print_rewritten | off | | user
default_statistics_target | 100 | | user
default_table_access_method | heap | | user
default_tablespace | | | user
default_text_search_config | pg_catalog.english | | user
default_toast_compression | pglz | | user
default_transaction_deferrable | off | | user
default_transaction_isolation | read committed | | user
default_transaction_read_only | off | | user
dynamic_library_path | $libdir | | superuser
dynamic_shared_memory_type | posix | | postmaster
effective_cache_size | 524288 | 8kB | user
effective_io_concurrency | 1 | | user
enable_async_append | on | | user
enable_bitmapscan | on | | user
enable_gathermerge | on | | user
enable_hashagg | on | | user
enable_hashjoin | on | | user
enable_incremental_sort | on | | user
enable_indexonlyscan | on | | user
enable_indexscan | on | | user
enable_material | on | | user
enable_memoize | on | | user
enable_mergejoin | on | | user
enable_nestloop | on | | user
enable_parallel_append | on | | user
enable_parallel_hash | on | | user
enable_partition_pruning | on | | user
enable_partitionwise_aggregate | off | | user
enable_partitionwise_join | off | | user
enable_seqscan | on | | user
enable_sort | on | | user
enable_tidscan | on | | user
escape_string_warning | on | | user
event_source | PostgreSQL | | postmaster
exit_on_error | off | | user
external_pid_file | | | postmaster
extra_float_digits | 1 | | user
force_parallel_mode | off | | user
from_collapse_limit | 8 | | user
fsync | on | | sighup
full_page_writes | on | | sighup
geqo | on | | user
geqo_effort | 5 | | user
geqo_generations | 0 | | user
geqo_pool_size | 0 | | user
geqo_seed | 0 | | user
geqo_selection_bias | 2 | | user
geqo_threshold | 12 | | user
gin_fuzzy_search_limit | 0 | | user
gin_pending_list_limit | 4096 | kB | user
hash_mem_multiplier | 2 | | user
hba_file | /pg/data/pg_hba.conf | | postmaster
hot_standby | on | | postmaster
hot_standby_feedback | off | | sighup
huge_page_size | 0 | kB | postmaster
huge_pages | try | | postmaster
ident_file | /pg/data/pg_ident.conf | | postmaster
idle_in_transaction_session_timeout | 0 | ms | user
idle_session_timeout | 0 | ms | user
ignore_checksum_failure | off | | superuser
ignore_invalid_pages | off | | postmaster
ignore_system_indexes | off | | backend
in_hot_standby | off | | internal
integer_datetimes | on | | internal
IntervalStyle | postgres | | user
jit | on | | user
jit_above_cost | 100000 | | user
jit_debugging_support | off | | superuser-backend
jit_dump_bitcode | off | | superuser
jit_expressions | on | | user
jit_inline_above_cost | 500000 | | user
jit_optimize_above_cost | 500000 | | user
jit_profiling_support | off | | superuser-backend
jit_provider | llvmjit | | postmaster
jit_tuple_deforming | on | | user
join_collapse_limit | 8 | | user
krb_caseins_users | off | | sighup
krb_server_keyfile | | | sighup
lc_collate | en_US.UTF-8 | | internal
lc_ctype | en_US.UTF-8 | | internal
lc_messages | en_US.UTF-8 | | superuser
lc_monetary | en_US.UTF-8 | | user
lc_numeric | en_US.UTF-8 | | user
lc_time | en_US.UTF-8 | | user
listen_addresses | * | | postmaster
lo_compat_privileges | off | | superuser
local_preload_libraries | | | user
lock_timeout | 0 | ms | user
log_autovacuum_min_duration | 600000 | ms | sighup
log_checkpoints | on | | sighup
log_connections | off | | superuser-backend
log_destination | stderr | | sighup
log_directory | log | | sighup
log_disconnections | off | | superuser-backend
log_duration | off | | superuser
log_error_verbosity | default | | superuser
log_executor_stats | off | | superuser
log_file_mode | 0600 | | sighup
log_filename | postgresql-%Y-%m-%d_%H%M%S.log | | sighup
log_hostname | off | | sighup
log_line_prefix | %m [%p] | | sighup
log_lock_waits | off | | superuser
log_min_duration_sample | -1 | ms | superuser
log_min_duration_statement | -1 | ms | superuser
log_min_error_statement | error | | superuser
log_min_messages | warning | | superuser
log_parameter_max_length | -1 | B | superuser
log_parameter_max_length_on_error | 0 | B | user
log_parser_stats | off | | superuser
log_planner_stats | off | | superuser
log_recovery_conflict_waits | off | | sighup
log_replication_commands | off | | superuser
log_rotation_age | 1440 | min | sighup
log_rotation_size | 10240 | kB | sighup
log_startup_progress_interval | 10000 | ms | sighup
log_statement | none | | superuser
log_statement_sample_rate | 1 | | superuser
log_statement_stats | off | | superuser
log_temp_files | -1 | kB | superuser
log_timezone | Asia/Shanghai | | sighup
log_transaction_sample_rate | 0 | | superuser
log_truncate_on_rotation | off | | sighup
logging_collector | on | | postmaster
logical_decoding_work_mem | 65536 | kB | user
maintenance_io_concurrency | 10 | | user
maintenance_work_mem | 65536 | kB | user
max_connections | 100 | | postmaster
max_files_per_process | 1000 | | postmaster
max_function_args | 100 | | internal
max_identifier_length | 63 | | internal
max_index_keys | 32 | | internal
max_locks_per_transaction | 64 | | postmaster
max_logical_replication_workers | 4 | | postmaster
max_parallel_maintenance_workers | 2 | | user
max_parallel_workers | 8 | | user
max_parallel_workers_per_gather | 2 | | user
max_pred_locks_per_page | 2 | | sighup
max_pred_locks_per_relation | -2 | | sighup
max_pred_locks_per_transaction | 64 | | postmaster
max_prepared_transactions | 0 | | postmaster
max_replication_slots | 10 | | postmaster
max_slot_wal_keep_size | -1 | MB | sighup
max_stack_depth | 2048 | kB | superuser
max_standby_archive_delay | 30000 | ms | sighup
max_standby_streaming_delay | 30000 | ms | sighup
max_sync_workers_per_subscription | 2 | | sighup
max_wal_senders | 10 | | postmaster
max_wal_size | 1024 | MB | sighup
max_worker_processes | 8 | | postmaster
min_dynamic_shared_memory | 0 | MB | postmaster
min_parallel_index_scan_size | 64 | 8kB | user
min_parallel_table_scan_size | 1024 | 8kB | user
min_wal_size | 80 | MB | sighup
old_snapshot_threshold | -1 | min | postmaster
parallel_leader_participation | on | | user
parallel_setup_cost | 1000 | | user
parallel_tuple_cost | 0.1 | | user
password_encryption | scram-sha-256 | | user
plan_cache_mode | auto | | user
port | 5432 | | postmaster
post_auth_delay | 0 | s | backend
pre_auth_delay | 0 | s | sighup
primary_conninfo | | | sighup
primary_slot_name | | | sighup
promote_trigger_file | | | sighup
quote_all_identifiers | off | | user
random_page_cost | 4 | | user
recovery_end_command | | | sighup
recovery_init_sync_method | fsync | | sighup
recovery_min_apply_delay | 0 | ms | sighup
recovery_prefetch | try | | sighup
recovery_target | | | postmaster
recovery_target_action | pause | | postmaster
recovery_target_inclusive | on | | postmaster
recovery_target_lsn | | | postmaster
recovery_target_name | | | postmaster
recovery_target_time | | | postmaster
recovery_target_timeline | latest | | postmaster
recovery_target_xid | | | postmaster
recursive_worktable_factor | 10 | | user
remove_temp_files_after_crash | on | | sighup
restart_after_crash | on | | sighup
restore_command | | | sighup
row_security | on | | user
search_path | "$user", public | | user
segment_size | 131072 | 8kB | internal
seq_page_cost | 1 | | user
server_encoding | UTF8 | | internal
server_version | 15.5 | | internal
server_version_num | 150005 | | internal
session_preload_libraries | | | superuser
session_replication_role | origin | | superuser
shared_buffers | 65536 | 8kB | postmaster
shared_memory_size | 551 | MB | internal
shared_memory_size_in_huge_pages | 276 | | internal
shared_memory_type | mmap | | postmaster
shared_preload_libraries | | | postmaster
ssl | off | | sighup
ssl_ca_file | | | sighup
ssl_cert_file | server.crt | | sighup
ssl_ciphers | none | | sighup
ssl_crl_dir | | | sighup
ssl_crl_file | | | sighup
ssl_dh_params_file | | | sighup
ssl_ecdh_curve | none | | sighup
ssl_key_file | server.key | | sighup
ssl_library | | | internal
ssl_max_protocol_version | | | sighup
ssl_min_protocol_version | TLSv1.2 | | sighup
ssl_passphrase_command | | | sighup
ssl_passphrase_command_supports_reload | off | | sighup
ssl_prefer_server_ciphers | on | | sighup
standard_conforming_strings | on | | user
statement_timeout | 0 | ms | user
stats_fetch_consistency | cache | | user
superuser_reserved_connections | 3 | | postmaster
synchronize_seqscans | on | | user
synchronous_commit | on | | user
synchronous_standby_names | | | sighup
syslog_facility | local0 | | sighup
syslog_ident | postgres | | sighup
syslog_sequence_numbers | on | | sighup
syslog_split_messages | on | | sighup
tcp_keepalives_count | 0 | | user
tcp_keepalives_idle | 0 | s | user
tcp_keepalives_interval | 0 | s | user
tcp_user_timeout | 0 | ms | user
temp_buffers | 1024 | 8kB | user
temp_file_limit | -1 | kB | superuser
temp_tablespaces | | | user
TimeZone | Asia/Shanghai | | user
timezone_abbreviations | Default | | user
trace_notify | off | | user
trace_recovery_messages | log | | sighup
trace_sort | off | | user
track_activities | on | | superuser
track_activity_query_size | 1024 | B | postmaster
track_commit_timestamp | off | | postmaster
track_counts | on | | superuser
track_functions | none | | superuser
track_io_timing | off | | superuser
track_wal_io_timing | off | | superuser
transaction_deferrable | off | | user
transaction_isolation | read committed | | user
transaction_read_only | off | | user
transform_null_equals | off | | user
unix_socket_directories | /tmp | | postmaster
unix_socket_group | | | postmaster
unix_socket_permissions | 0777 | | postmaster
update_process_title | on | | superuser
vacuum_cost_delay | 0 | ms | user
vacuum_cost_limit | 200 | | user
vacuum_cost_page_dirty | 20 | | user
vacuum_cost_page_hit | 1 | | user
vacuum_cost_page_miss | 2 | | user
vacuum_defer_cleanup_age | 0 | | sighup
vacuum_failsafe_age | 1600000000 | | user
vacuum_freeze_min_age | 50000000 | | user
vacuum_freeze_table_age | 150000000 | | user
vacuum_multixact_failsafe_age | 1600000000 | | user
vacuum_multixact_freeze_min_age | 5000000 | | user
vacuum_multixact_freeze_table_age | 150000000 | | user
wal_block_size | 8192 | | internal
wal_buffers | 2048 | 8kB | postmaster
wal_compression | off | | superuser
wal_consistency_checking | | | superuser
wal_decode_buffer_size | 524288 | B | postmaster
wal_init_zero | on | | superuser
wal_keep_size | 0 | MB | sighup
wal_level | replica | | postmaster
wal_log_hints | off | | postmaster
wal_receiver_create_temp_slot | off | | sighup
wal_receiver_status_interval | 10 | s | sighup
wal_receiver_timeout | 60000 | ms | sighup
wal_recycle | on | | superuser
wal_retrieve_retry_interval | 5000 | ms | sighup
wal_segment_size | 16777216 | B | internal
wal_sender_timeout | 60000 | ms | user
wal_skip_threshold | 2048 | kB | user
wal_sync_method | fdatasync | | sighup
wal_writer_delay | 200 | ms | sighup
wal_writer_flush_after | 128 | 8kB | sighup
work_mem | 4096 | kB | user
xmlbinary | base64 | | user
xmloption | content | | user
zero_damaged_pages | off | | superuser
(352 rows)
其中 context 按照修改参数的难易程度分为：
internal：内部参数，不能直接修改，其中一些参数可以通过编译时或者初始化时指定。postmaster：修改这些参数需要重启数据库才能生效，一般位于配置文件postgresql.conf中或者在启动数据库时在命令行指定。sighup：修改配置文件postgresql.conf中的这些参数后无需重启数据库，通过执行操作系统命令pg_ctl reload或者 SQL 函数pg_reload_conf()向主服务器进程发送 SIGHUP 信号，重新加载配置文件以生效。主服务器进程会将 SIGHUP 信息转发给其他子进程，以便子进程也获取到新的参数值。superuser-backend：修改配置文件postgresql.conf中的这些参数后无需重启数据库，但连接用户必须是超级用户或者被授予合适的SET权限的用户。backend：修改配置文件postgresql.conf中的这些参数后无需重启数据库，任何用户都可以为其会话进行修改。superuser：只有超级用户或者有SET权限的用户可以使用SET命令为当前会话修改这些参数，不影响其他会话。user：任何用户都可以使用SET命令为当前会话修改这些参数，不影响其他会话。
管理配置文件内容
在配置文件 postgresql.conf 中，可以使用如下指令引入其他配置文件的内容：
include：指定一个配置文件。include_if_exists：指定一个配置文件，如果该文件不存在，不会报错。include_dir：指定一个目录，会引入该目录下以.conf后缀名的文件。
在有多个数据库服务器时，往往有许多参数的配置是相同的，可以将相同的参数写入到一个配置文件中，使用以上指令引入，以便管理。
include 'shared.conf'
include 'memory.conf'
include 'server.conf'
访问控制
PostgreSQL 使用配置文件 $PGDATA/pg_hba.conf 控制客户端身份验证。
[postgres@postgresql ~]$ cat $PGDATA/pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file. A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostgssenc DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnogssenc DATABASE USER ADDRESS METHOD [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type:
# - "local" is a Unix-domain socket
# - "host" is a TCP/IP socket (encrypted or not)
# - "hostssl" is a TCP/IP socket that is SSL-encrypted
# - "hostnossl" is a TCP/IP socket that is not SSL-encrypted
# - "hostgssenc" is a TCP/IP socket that is GSSAPI-encrypted
# - "hostnogssenc" is a TCP/IP socket that is not GSSAPI-encrypted
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
# keyword does not match "replication". Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof. In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches. It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask. A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts. Instead of a CIDR-address, you
# can write "samehost" to match any of the server's own IP addresses,
# or "samenet" to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
# Note that "password" sends passwords in clear text; "md5" or
# "scram-sha-256" are preferred since they send encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE. The available options depend on the different
# authentication methods -- refer to the "Client Authentication"
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted. Quoting one of the keywords
# "all", "sameuser", "samerole" or "replication" makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the server receives a
# SIGHUP signal. If you edit the file on a running system, you have to
# SIGHUP the server for the changes to take effect, run "pg_ctl reload",
# or execute "SELECT pg_reload_conf()".
#
# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records. In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.
# CAUTION: Configuring the system for local "trust" authentication
# allows any local user to connect as any PostgreSQL user, including
# the database superuser. If you do not trust all your local users,
# use another authentication method.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all 0.0.0.0/0 md5
文件中的每一行记录对应一条规则（忽略注释和空行），每条规则包括：
TYPE：连接类型，包括：local：使用 Unix 域套接字的连接。host：使用 TCP/IP 的连接。hostssl：使用 SSL 加密的 TCP/IP 连接。hostnossl：只匹配没有使用 SSL 加密的 TCP/IP 连接。hostgssenc：使用 GSSAPI 加密的 TCP/IP 连接。hostnogssenc：只匹配没有使用 GSSAPI 加密的 TCP/IP 连接。
DATABASE：匹配的数据库，可以是一个数据库名称或多个以逗号分隔的数据库名称，也可以是：all：匹配所有数据库。sameuser：匹配与请求用户同名的数据库。samerole：匹配与请求用户所拥有角色同名的数据库。replication：匹配物理复制连接而不是逻辑复制连接。@filename：匹配指定文件中的数据库名。
USER：指定可访问数据库的用户名，可以是一个用户名或多个以逗号分隔的用户名，也可以是：all：匹配所有用户。@filename：匹配指定文件中的用户名。
ADDRESS：指定主机名（域名解析）或者 IP 地址（必须带掩码），可以是以.开头的主机名或者 IP 地址范围，也可以是：all：匹配任何 IP 地址。0.0.0.0/0：匹配所有 IPv4 地址。::0/0：匹配所有 IPv6 地址。samehost：匹配服务器自身的 IP 地址。samenet：匹配服务器直接连接到的任何子网中的任何地址。
METHOD：指定当连接与此记录匹配时要使用的身份验证方法，包括：trust：无条件允许连接，无需密码或其他身份验证。reject：无条件拒绝连接。md5：使用 MD5 验证用户的密码。scram-sha-256：使用 SCRAM-SHA-256 验证用户的密码。password：客户端密码是通过网络以明文形式发送的，不要使用此种方式。
PostgreSQL 会按照从上到下的顺序进行连接匹配。通常应该将较小的访问范围和较弱的身份验证方法放在前面，将较大的访问范围和较强的身份验证方法放在后面。
PostgreSQL 会在启动时或者主服务器进程收到 SIGHUP 信号时读取 pg_hba.conf 文件。如果在数据库运行时修改了此文件，通过执行操作系统命令 pg_ctl reload 或者 SQL 函数 pg_reload_conf() 向主服务器进程发送 SIGHUP 信号以重新读取此文件。
使用系统视图 pg_hba_file_rules 查看配置文件 pg_hba.conf 的设置：
postgres=# select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+-------
89 | local | {all} | {all} | | | trust | |
91 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
93 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
96 | local | {replication} | {all} | | | trust | |
97 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
98 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
100 | host | {all} | {all} | 0.0.0.0 | 0.0.0.0 | md5 | |
(7 rows)
其中：
error：如果不为空，则为无法处理此行的错误信息。
注意：
要连接到特定数据库，用户不仅必须通过
pg_hba.conf检查，而且必须具有对数据库的CONNECT权限。相比通过在
pg_hba.conf中配置规则限制某些用户连接数据库，通过授予/回收CONNECT权限来控制访问更容易一些。
配置 pg_hba.conf 的一些示例：
# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
# The same using local loopback TCP/IP connections.
#
# TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 trust
# The same as the previous line, but using a separate netmask column
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all 127.0.0.1 255.255.255.255 trust
# The same over IPv6.
#
# TYPE DATABASE USER ADDRESS METHOD
host all all ::1/128 trust
# The same using a host name (would typically cover both IPv4 and IPv6).
#
# TYPE DATABASE USER ADDRESS METHOD
host all all localhost trust
# Allow any user from any host with IP address 192.168.93.x to connect
# to database "postgres" as the same user name that ident reports for
# the connection (typically the operating system user name).
#
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.168.93.0/24 ident
# Allow any user from host 192.168.12.10 to connect to database
# "postgres" if the user's password is correctly supplied.
#
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.168.12.10/32 scram-sha-256
# Allow any user from hosts in the example.com domain to connect to
# any database if the user's password is correctly supplied.
#
# Require SCRAM authentication for most users, but make an exception
# for user 'mike', who uses an older client that doesn't support SCRAM
# authentication.
#
# TYPE DATABASE USER ADDRESS METHOD
host all mike .example.com md5
host all all .example.com scram-sha-256
# In the absence of preceding "host" lines, these three lines will
# reject all connections from 192.168.54.1 (since that entry will be
# matched first), but allow GSSAPI-encrypted connections from anywhere else
# on the Internet. The zero mask causes no bits of the host IP address to
# be considered, so it matches any host. Unencrypted GSSAPI connections
# (which "fall through" to the third line since "hostgssenc" only matches
# encrypted GSSAPI connections) are allowed, but only from 192.168.12.10.
#
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.54.1/32 reject
hostgssenc all all 0.0.0.0/0 gss
host all all 192.168.12.10/32 gss
# Allow users from 192.168.x.x hosts to connect to any database, if
# they pass the ident check. If, for example, ident says the user is
# "bryanh" and he requests to connect as PostgreSQL user "guest1", the
# connection is allowed if there is an entry in pg_ident.conf for map
# "omicron" that says "bryanh" is allowed to connect as "guest1".
#
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.0.0/16 ident map=omicron
# If these are the only three lines for local connections, they will
# allow local users to connect only to their own databases (databases
# with the same name as their database user name) except for administrators
# and members of role "support", who can connect to all databases. The file
# $PGDATA/admins contains a list of names of administrators. Passwords
# are required in all cases.
#
# TYPE DATABASE USER ADDRESS METHOD
local sameuser all md5
local all @admins md5
local all +support md5
# The last two lines above can be combined into a single line:
local all @admins,+support md5
# The database column can also use lists and file names:
local db1,db2,@demodbs all md5
控制文件
类似于 Oracle 中的控制文件，PostgreSQL 也有控制文件，文件路径名称为：$PGDATA/global/pg_control。
[postgres@postgresql ~]$ ll $PGDATA/global/pg_control
-rw------- 1 postgres postgres 8192 Dec 11 13:51 /pg/data/global/pg_control
控制文件是一个大小为 8 KB 的二进制文件，包括初始化信息，WAL 及检查点信息以及一些配置信息，不能直接读取，可以使用 pg_controldata 命令查看内容：
[postgres@postgresql ~]$ pg_controldata
pg_control version number: 1300
Catalog version number: 202209061
Database system identifier: 7301938315432349147
Database cluster state: in production
pg_control last modified: Mon 11 Dec 2023 01:51:11 PM CST
Latest checkpoint location: 0/2CD0E70
Latest checkpoint's REDO location: 0/2CD0E38
Latest checkpoint's REDO WAL file: 000000010000000000000002
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:788
Latest checkpoint's NextOID: 16424
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 716
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 788
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Mon 11 Dec 2023 01:51:11 PM CST
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce: fdcb8f1e790823aa9e645a0250fb53a45f009767189a01744c89469afe00aec4
其中：
pg_control version number：控制文件版本号。Catalog version number：系统表版本号，格式为yyyymmddN。Database system identifier：数据库系统编号，包含了创建数据库的时间戳和initdb时初始化的进程号。
postgres=# SELECT to_timestamp(((7301938315432349147>>32) & (2^32 -1)::bigint));
to_timestamp
------------------------
2023-11-16 14:09:57+08
(1 row)
Database cluster state：数据库集群状态，包括：starting up：数据库正在启动。shut down： 数据库主库正常关闭。shut down in recovery：数据库备库正常关闭。shutting down：关闭数据库时正在进行 Checkpoint。in crash recovery：数据库在进行实例恢复。in archive recovery：数据库备库正常启动后。in production：数据库主库正常启动后。
pg_control last modified：最后修改控制文件的时间。
不能手动修改该文件，也不能单独备份和恢复该文件，只能与数据库一起备份和恢复。
如果控制文件损坏或者丢失，使用 pg_resetwal 命令进行重建。
语法：
pg_resetwal [ -f | --force ] [ -n | --dry-run ] [option...] [ -D | --pgdata ]datadir
pg_resetwal命令会清除 WAL， 并重置存储在pg_control中的一些控制信息。当数据库由于控制文件损坏而无法启动时，仅作为最后的恢复方案。- 运行
pg_resetwal命令后，数据库可能包含不一致的数据，应立即转储数据，运行initdb并还原数据库，然后检查并修复不一致数据。 - 出于安全原因，
pg_resetwal不使用环境变量PGDATA，必须在命令行上指定数据目录。
使用 pg_resetwal 命令进行重建控制文件，需要指定 4 个参数：
-l：指定下一个 WAL 段文件的名称。在$PGDATA/pg_wal目录下找到编号最大的日志文件，加 1 即可。
[postgres@postgresql ~]$ ll $PGDATA/pg_wal
total 32768
-rw------- 1 postgres postgres 16777216 Dec 11 13:51 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Nov 20 13:22 000000010000000000000003
drwx------ 2 postgres postgres 6 Nov 20 13:27 archive_status
当前编号最大的日志文件为 000000010000000000000003，则 -l 参数可以为 000000010000000000000004。
-O：指定下一个多事务偏移量。在$PGDATA/pg_multixact/members目录下找到数值最大的文件名，加 1 然后乘以 52352（0xCC80），转换为 16 进制。
[postgres@postgresql ~]$ ll $PGDATA/pg_multixact/members
total 8
-rw------- 1 postgres postgres 8192 Nov 16 14:09 0000
当前数值最大的文件名为 0000，加 1 然后乘以 52352，转换为 16 进制后为 0xCC80。
-m：指定下一个和最旧的多事务 ID。下一个多事务 ID 可以通过在$PGDATA/pg_multixact/offsets目录中数值最大的文件名，加 1 然后乘以 65536（0x10000）；最旧的多事务 ID 可以通过在$PGDATA/pg_multixact/offsets目录中数值最小的文件名，乘以 65536（0x10000），如果目录中数值最小的文件名为0000，则最旧的多事务 ID 计算结果为 0，此时取下一个多事务 ID 的值。文件名为十六进制，因此最简单的方法是以十六进制形式指定选项值并附加四个零。
[postgres@postgresql ~]$ ll $PGDATA/pg_multixact/offsets
total 8
-rw------- 1 postgres postgres 8192 Dec 11 13:51 0000
当前数值最大的文件名为 0000，简单的方式是文件名加 1，然后后面添加 0000，即 0x10000,0x10000。
-x：指定下一个事务 ID。在$PGDATA/pg_xact目录下找到数值最大的文件名，加 1 然后乘以 1048576 （0x100000）。注意文件名为十六进制。
[postgres@postgresql ~]$ ll $PGDATA/pg_xact
total 8
-rw------- 1 postgres postgres 8192 Dec 11 13:51 0000
当前数值最大的文件名为 0000，加 1 然后乘以 1048576（0x100000） ，转换为 16 进制后为 0x100000。
注意：
- 不能在数据库运行时，执行
pg_resetwal命令。- 如果在数据目录中找到锁文件
$PGDATA/postmaster.pid，将拒绝执行pg_resetwal命令。- 如果数据库已崩溃且留下了锁文件
$PGDATA/postmaster.pid，则需要确认没有数据库进程在运行，再手动删除该锁文件以便运行pg_resetwal命令。pg_resetwal命令仅适用于相同主版本的数据库服。
例子：重建控制文件
关闭数据库：
[root@postgresql ~]# systemctl stop postgresql-15.service
重命名原控制文件：
[postgres@postgresql ~]$ cd $PGDATA/global
[postgres@postgresql global]$ mv pg_control pg_control.bak
创建一个名为 pg_control 的空文件：
[postgres@postgresql global]$ touch pg_control
使用 pg_resetwal 命令重建 pg_control：
[postgres@postgresql global]$ pg_resetwal -l 000000010000000000000004 -O 0xCC80 -m 0x10000,0x10000 -x 0x100000 -D $PGDATA
pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it
Guessed pg_control values:
pg_control version number: 1300
Catalog version number: 202209061
Database system identifier: 7311636494740060602
Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: off
Latest checkpoint's NextXID: 0:3
Latest checkpoint's NextOID: 10000
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 3
Latest checkpoint's oldestXID's DB: 0
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 0
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Values to be changed:
First log segment after reset: 000000010000000000000004
NextMultiXactId: 65536
OldestMultiXid: 65536
OldestMulti's DB: 0
NextMultiOffset: 52352
NextXID: 1048576
OldestXID: 3
OldestXID's DB: 0
If these values seem acceptable, use -f to force reset.
确认控制文件参数无误后加上 -f 写入到控制文件：
[postgres@postgresql global]$ pg_resetwal -l 000000010000000000000004 -O 0xCC80 -m 0x10000,0x10000 -x 0x100000 -D $PGDATA -f
pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it
Write-ahead log reset
启动数据库：
[root@postgresql ~]# systemctl start postgresql-15.service
[root@postgresql ~]# systemctl status postgresql-15.service
● postgresql-15.service - PostgreSQL 15 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-15.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2023-12-12 17:25:49 CST; 6s ago
Docs: https://www.postgresql.org/docs/15/static/
Process: 2420 ExecStop=/pg/software/bin/pg_ctl stop -D ${PGDATA} -m fast -s (code=exited, status=0/SUCCESS)
Process: 2519 ExecStart=/pg/software/bin/pg_ctl start -D ${PGDATA} -w -t 300 -s (code=exited, status=0/SUCCESS)
Main PID: 2521 (postgres)
CGroup: /system.slice/postgresql-15.service
├─2521 /pg/software/bin/postgres -D /pg/data
├─2522 postgres: logger
├─2523 postgres: checkpointer
├─2524 postgres: background writer
├─2526 postgres: walwriter
├─2527 postgres: autovacuum launcher
└─2528 postgres: logical replication launcher
Dec 12 17:25:49 postgresql systemd[1]: Starting PostgreSQL 15 database server...
Dec 12 17:25:49 postgresql pg_ctl[2519]: 2023-12-12 17:25:49.588 CST [2521] LOG: redirecting log output to logging collector process
Dec 12 17:25:49 postgresql pg_ctl[2519]: 2023-12-12 17:25:49.588 CST [2521] HINT: Future log output will appear in directory "log".
Dec 12 17:25:49 postgresql systemd[1]: Started PostgreSQL 15 database server.