01 - PostgreSQL 17 安装和基础概念

更新时间：2024 年 10 月

版本：PostgreSQL 17.

全系统安装官网文档：PostgreSQL: Downloads

简介

官网：PostgreSQL: The world's most advanced open source database

官方文档索引：PostgreSQL: Documentation

一谈到开源单体关系型数据库，基本上都会想起 MySQL，如果说 MySQL 是企业派的代表，那 PostgreSQL 绝对是学术派的代表

PostgreSQL 是一个基于 POSTGRES 4.2 版本的对象关系数据库管理系统（ORDBMS）。POSTGRES 由加州大学伯克利分校计算机科学系开发，开创了许多概念，这些概念直到很久以后才在一些商业数据库系统中可用。PostgreSQL 是这个 POSTGRES 的开源后代，它支持大部分SQL标准，并提供许多现代功能

准备

主机准备

主机名	操作系统	架构	IP	安装软件
db-01.postgresql.local	AlmaLinux 9.4	x86_64	192.168.111.197	PostgreSQL 17

防火墙及 SELinux

# 关闭 SELinux
$ setenforce 0 && sed -i 's/=enforcing/=disabled/g' /etc/sysconfig/selinux && sed -i 's/=enforcing/=disabled/g' /etc/selinux/config

# 设置 firewalld
$ firewall-cmd --permanent --zone=public --add-service=postgresql

# 加载 firewalld 配置
$ firewall-cmd --reload

系统参数

资源限制

PAM 模块

PAM 模块 pam_limits 对用户会话中可以获得的系统资源设置了限制，可以使用 ulimit 命令进行修改，或者直接将修改的值写入配置文件 /etc/security/limits.conf 和 /etc/security/limits.d/*.conf

# 备份原有的设置
find /etc/security/limits.d -type f -name *.conf  -exec mv {} {}.bak.`date +"%Y%m%d"` \;

# 设置限制
cat > /etc/security/limits.d/sys.conf <<EOF
*            -    core            unlimited
*            -    nproc           unlimited
*            -    nofile          1048576
*            -    memlock         unlimited
*            -    msgqueue        unlimited
*            -    stack           unlimited	
EOF

systemd

systemd 有独立于 PAM 的资源限制（setrlimit），若服务通过 systemd 启动也需要设置

###### 修改用户级默认配置 ######
# 备份原有的文件或创建目录
[ -f /etc/systemd/user.conf.d/ ] && find /etc/systemd/user.conf.d/ -type f -name *.conf  -exec mv {} {}.bak.`date +"%Y%m%d"` \; || mkdir -p /etc/systemd/user.conf.d

# 修改配置
cat > /etc/systemd/user.conf.d/sys.conf << EOF
[Manager]
DefaultLimitCORE=infinity
DefaultLimitNPROC=infinity
DefaultLimitNOFILE=1048576
DefaultLimitMEMLOCK=infinity
DefaultLimitMSGQUEUE=infinity
EOF

###### 修改系统级默认配置 ######
# 备份原有的配置
cp /etc/systemd/system.conf /etc/systemd/system.conf.bak`date +"%Y%m%d"`


# 修改配置
grep -q '^#* *DefaultLimitCORE.*' /etc/systemd/system.conf && sed -ri 's@^#* *(DefaultLimitCORE).*@\1=infinity@' /etc/systemd/system.conf || echo "DefaultLimitCORE=infinity" >> /etc/systemd/system.conf

grep -q '^#* *DefaultLimitNPROC.*' /etc/systemd/system.conf && sed -ri 's@^#* *(DefaultLimitNPROC).*@\1=infinity@' /etc/systemd/system.conf || echo "DefaultLimitNPROC=infinity" >> /etc/systemd/system.conf

grep -q '^#* *DefaultLimitNOFILE.*' /etc/systemd/system.conf && sed -ri 's@^#* *(DefaultLimitNOFILE).*@\1=1048576@' /etc/systemd/system.conf || echo "DefaultLimitNOFILE=1048576" >> /etc/systemd/system.conf

grep -q '^#* *DefaultLimitMEMLOCK.*' /etc/systemd/system.conf && sed -ri 's@^#* *(DefaultLimitMEMLOCK).*@\1=infinity@' /etc/systemd/system.conf || echo "DefaultLimitMEMLOCK=infinity" >> /etc/systemd/system.conf

grep -q '^#* *DefaultLimitMSGQUEUE.*' /etc/systemd/system.conf && sed -ri 's@^#* *(DefaultLimitMSGQUEUE).*@\1=infinity@' /etc/systemd/system.conf || echo "DefaultLimitMSGQUEUE=infinity" >> /etc/systemd/system.conf


# 重启 systemd 生效
systemctl daemon-reexec

内核参数

cat >/etc/sysctl.d/99-sysctl.conf  <<EOF
######  TCP 连接快速释放设置  ######
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 1200
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 3



######  TIME_WAIT 过多时设置   ######
net.ipv4.tcp_tw_reuse = 1
#net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_max_tw_buckets=5000


######  端口相关设置  ######
# 设定允许系统主动打开的端口范围，根据需要设置，默认 32768	60999
net.ipv4.ip_local_port_range = 32768	65530


######  防 SYNC 攻击设置  ######
net.ipv4.tcp_syncookies=1
net.ipv4.tcp_syn_retries=3
net.ipv4.tcp_synack_retries=2
net.ipv4.tcp_max_syn_backlog=8192
# 配置 TCP 重传的最大次数减少到 5 次，超时时间约为 6 秒，方便及时发现节点故障
# net.ipv4.tcp_retries2=5

######  其他 TCP 设置  ######
# 系统当前因后台进程无法处理的新连接而溢出，则允许系统重置新连接
net.ipv4.tcp_abort_on_overflow=1


#######  nf_conntrack 相关设置（k8s、docker 防火墙的 nat）  #######
net.netfilter.nf_conntrack_max = 262144
net.nf_conntrack_max = 262144

net.netfilter.nf_conntrack_tcp_timeout_established = 86400
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 3600
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120


####### socket 相关设置 ######
net.core.somaxconn = 32768
net.core.netdev_max_backlog = 32768




######  其他设置  #######
net.ipv4.conf.default.rp_filter=0
net.ipv4.conf.default.accept_source_route=0
net.ipv4.ip_forward = 1
net.ipv4.ip_nonlocal_bind = 1
#
net.ipv4.conf.all.forwarding=1
net.ipv6.conf.all.forwarding=1


######  内存相关设置 #######
vm.swappiness = 0
vm.max_map_count = 655360
vm.overcommit_memory = 0
# vm.min_free_kbytes = 1048576

###### 文件相关 #######
fs.file-max = 6573688
fs.nr_open = 1048576
fs.aio-max-nr = 1048576


#######  K8S 相关设置 ######
# 必须先加载 br_netfilter 模块
# 二层的网桥在转发包时也会被 arptables/ip6tables/iptables 的 FORWARD 规则所过滤
#net.bridge.bridge-nf-call-arptables = 1
#net.bridge.bridge-nf-call-ip6tables = 1
#net.bridge.bridge-nf-call-iptables = 1

###### 进程相关 #######
# 最大进程 id，默认值为 32768，最大值根据发行版有所不同
kernel.pid_max = 132768
kernel.threads-max = 123342

EOF


sysctl --system

包管理器安装（DNF）

安装

选择系统类别

选择安装版本和平台架构

基础设置

PG_MAJOR_VERSION=17

PGDATA="/data/pgsql/${PG_MAJOR_VERSION}"
# 创建数据目录
mkdir -p /data/pgsql/${PG_MAJOR_VERSION}

按照选择后的文档进行安装

# 安装仓库
$ dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 
$ sudo dnf -qy module disable postgresql

# 安装 postgresql 
$ sudo dnf install -y postgresql${PG_MAJOR_VERSION}-server

安装后设置

指定数据目录

# 授权
$ chown -R postgres:postgres ${PGDATA}

chown -R postgres:postgres `dirname ${PGDATA}`

$ sed -ri 's@(.*Environment=PGDATA).*@\1='"${PGDATA}"'@g' /usr/lib/systemd/system/postgresql-${PG_MAJOR_VERSION}.service


$ sed -ri 's|(PGDATA)=.*|\1'="$PGDATA"'|g' /var/lib/pgsql/.bash_profile

初始化数据库

$ sudo /usr/pgsql-${PG_MAJOR_VERSION}/bin/postgresql-${PG_MAJOR_VERSION}-setup initdb

#### 输出信息
Initializing database ... OK

修改监听设置

$ vim /data/pgsql/${PG_MAJOR_VERSION}/postgresql.conf
# 监听 IP
listen_addresses = '*'
# 监听端口
port = 5432
# 最大连接数
max_connections = 500

启动并设置开机启动

$ sudo systemctl enable --now postgresql-${PG_MAJOR_VERSION}

源码编译安装

参考：PostgreSQL: Installation from Source Code

安装依赖

参考：PostgreSQL: Documentation: 17: 17.1. Requirements

安装相关工具

dnf -y install tar vim


dnf -y install make

安装 PG 所需依赖

# 必须依赖
dnf -y install gcc-c++ \
               libicu libicu-devel \
               flex bison \
               perl \
               readline readline-devel \
               zlib zlib-devel \
               systemd-devel
       
# 构建文档可选依赖       
dnf -y install docbook-dtds docbook-style-xsl libxslt libxslt-devel
               
# 编程相关可选依赖
dnf -y install python3 python3-devel
dnf -y install tcl tcl-devel

# ssl 相关可选依赖
dnf -y install openssl openssl-devel

# 认证相关可选依赖
dnf -y install krb5-workstation krb5-libs krb5-devel
dnf -y install openldap openldap-clients openldap-devel
dnf -y install pam pam-devel


# 压缩相关可选依赖
dnf -y install lz4 lz4-devel
dnf -y install zstd

基础变量设置

PG_MAJOR_VERSION=17
PG_VERSION=17.0

PG_HOME=/opt/pgsql

PG_HOME_SPEC=${PG_HOME}-${PG_VERSION}

PG_USER=postgres
PG_USER_GROUP=postgres

PGDATA="/data/pgsql/${PG_MAJOR_VERSION}"


mkdir -p ${PGDATA}

部署

参考：PostgreSQL: Documentation: Building and Installation with Autoconf and Make

下载

下载地址：PostgreSQL: File Browser


curl -o /usr/local/src/postgresql-${PG_VERSION}.tar.gz \
    -L https://ftp.postgresql.org/pub/source/v${PG_VERSION}/postgresql-${PG_VERSION}.tar.gz

解压

tar -zxvf /usr/local/src/postgresql-${PG_VERSION}.tar.gz \
    -C /usr/local/src/
    
cd /usr/local/src/postgresql-${PG_VERSION}

配置

# 创建配置目录
mkdir -p /usr/local/src/postgresql-${PG_VERSION}-build

cd /usr/local/src/postgresql-${PG_VERSION}-build

# 运行配置
/usr/local/src/postgresql-${PG_VERSION}/configure \
    --prefix=${PG_HOME_SPEC} \
    --with-systemd

构建

构建所有，包括文档和手册

make -j 2 world

安装

安装所有，包括手册

make install-world

创建软连接

ln -sf ${PG_HOME_SPEC} ${PG_HOME}

部署后设置

创建用户和用户组

groupadd -r ${PG_USER_GROUP}

useradd -r \
    -g ${PG_USER_GROUP} \
    -s "/bin/bash" \
    -c "PostgreSQL Server" \
    -m \
    ${PG_USER}

授权

chown -R ${PG_USER}:${PG_USER_GROUP} ${PGDATA}

chown -R postgres:postgres `dirname ${PGDATA}`

设置环境变量

cat  <<  EOF  >  /etc/profile.d/pgsql.sh
export PG_HOME=${PG_HOME}
export PGDATA=${PGDATA}
export PATH=\${PATH}:\${PG_HOME}/bin
export MANPATH=$MANPATH:\${PG_HOME}/share/man/
EOF

source /etc/profile

设置 systemd 管理

创建 systemd 管理文件

cat << EOF > /usr/lib/systemd/system/postgresql-${PG_MAJOR_VERSION}.service

[Unit]
Description=PostgreSQL 17 database server
Documentation=https://www.postgresql.org/docs/17/static/
# After=syslog.target
After=network-online.target

[Service]
Type=notify

User=postgres
Group=postgres

# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.

# Location of database directory
Environment=PGDATA=${PGDATA}

# 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 postgres main process
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

# ExecStartPre=${PG_HOME}/bin/postgresql-17-check-db-dir ${PGDATA}
ExecStart=${PG_HOME}/bin/postgres -D ${PGDATA}
ExecReload=/bin/kill -HUP \$MAINPID
KillMode=mixed
KillSignal=SIGINT
 
# Do not set any timeout value, so that systemd will not kill postgres 
# main process 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


EOF

初始化

sudo -u postgres ${PG_HOME}/bin/initdb \
  --pgdata=$PGDATA

#### 输出信息
Initializing database ... OK

修改监听设置

$ vim ${PGDATA}/postgresql.conf
# 监听 IP
listen_addresses = '*'
# 监听端口
port = 5432
# 最大连接数
max_connections = 500

启动并设置开机启动

systemctl enable --now postgresql-${PG_MAJOR_VERSION}

基础

PG 的架构层级

数据库（`database`）

类似于 MySQL 的 database。每个 PG 实例可以包含多个独立的 database

创建数据库

# 命令行创建数据库
$ createdb mydb

# 登录后创建数据库
postgres=# CREATE DATABASE mydb;

删除数据库

# 命令行创建数据库
$ dropdb mydb

# 登录后删除数据库
postgres=# DROP DATABASE mydb;

查看数据库

-- psql 方式
postgres=# \l

-- 查询方式
postgres=# SELECT * FROM pg_database;

连接到指定数据库

# 切换为 postgres 用户
$ su - postgres

$ psql mydb

mydb=#

模式（`schema`）

模式是 MySQL 中没有的层级，新增一个模式层级有很多好处：

权限管理：通过模式，可以更细致地控制用户对数据的访问权限。例如，可以让指定用户只能访问特定模式下的表
命名管理：不同的模式可以有相同名称的表，这使得多个项目或多个用户可以在同一个数据库内独立工作，而不必担心表名冲突
便于迁移: 如果从其他数据库系统（如 Oracle）迁移，模式可以提供一个更为平滑的迁移路径

在创建一个新的 database 时，PG 会自动为其创建一个名为 public 的 schema。如果未设置 searc_path 变量，那么 PG 会将你创建的所有对象默认放入 public 这个 schema 中

创建模式

mydb=# CREATE SCHEMA IF NOT EXISTS my_schema;

-- 指定所有者
mydb=# CREATE SCHEMA IF NOT EXISTS myschema AUTHORIZATION username;

查看模式

-- 查询方式
mydb=# SELECT schema_name FROM information_schema.schemata;


-- psql 方式
mydb=# \dn
        List of schemas
   Name    |       Owner       
-----------+-------------------
 my_schema | postgres
 public    | pg_database_owner
(2 rows)

删除模式

-- 仅删除模式
mydb=# DROP SCHEMA my_schema;


-- 删除模式以及关联的所有对象（表、视图等）
mydb=# DROP SCHEMA my_schema CASCADE;

更改默认模式

默认模式为 public，可以通过修改 search_path 来修改会话的默认模式

mydb=# SET search_path TO my_schema;

表（`table`）

与 MySQL 中的表类似，不过 PG 的表是在 scheme 层级之下

创建表

mydb=# CREATE TABLE IF NOT EXISTS weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

-- 创建表并指定所属模式
mydb=# CREATE TABLE IF NOT EXISTS my_schema.weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
    prcp            real,          -- precipitation
    date            date
);

查看表

mydb=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | weather | table | postgres
(1 row)

修改表

mydb=# ALTER TABLE table_name SET SCHEMA my_schema;

删除表

mydb=# DROP TABLE weather;

其他常用操作

连接数据库

# 切换为 postgres 用户
$ su - postgres

$ psql 

postgres=#

用户/角色 `postgres` 初始化

修改密码

-- 使用完整命令行
$ ALTER USER postgres WITH PASSWORD 'Qwert1234..';

-- 使用 psql 的 \password
postgres=# \password postgres
Enter new password for user "postgres": 
Enter it again:

修改用户身份验证方式

修改配置文件 postgresql.conf，允许 postgres 用户使用密码进行身份验证

$ vim  ${PGDATA}/postgresql.conf
password_encryption = scram-sha-256     # scram-sha-256 or md5

修改外部连接权限

修改配置文件 pg_hba.conf ，允许 postgres 用户在任意 IP 地址连接并使用 scram-sha-256 密码进行验证

$ vim ${PGDATA}/pg_hba.conf
# IPv4 local connections:
host    all             postgres        0.0.0.0/0               scram-sha-256
# 如果需要其他用户也可以远程密码登录，则添加这一行
#  host    all             all             0.0.0.0/0               scram-sha-256
......
host    all             all             127.0.0.1/32            scram-sha-256
#

重新加载配置

# systemd 方式
$ systemctl reload postgresql-${PG_MAJOR_VERSION}

# 命令行方式
$ pg_ctl reload -D ${PGDATA}

# psql 方式
postgres=# SELECT pg_reload_conf();

用户/角色管理

创建用户

-- 格式
CREATE USER username WITH PASSWORD 'password';

-- username：用户名
-- password：密码

-- 示例
postgres=# CREATE USER nemo WITH PASSWORD 'Nemo.Yang';

删除用户

DROP USER username;

用户授权

格式

GRANT permission_name ON database_name TO username;

示例

-- 授权用户访问数据库
GRANT CONNECT ON DATABASE my_database TO my_user;

-- 授权用户对指定数据库的读取权限
GRANT SELECT ON my_db TO my_user;

-- 授予用户在特定表上的所有权限
GRANT ALL PRIVILEGES ON TABLE my_table TO my_user;

-- 同时授权多个权限
GRANT SELECT, INSERT ON TABLE my_table TO my_user;

-- 授权指定 scheme 的所有表的所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA my_scheme TO my_user;

完整实例

-- 1. 授予数据库级别权限
GRANT ALL PRIVILEGES ON DATABASE halo TO halo;

-- 2. 授予对 public schema 的 CREATE 权限
GRANT CREATE ON SCHEMA public TO halo;

-- 3. 授予对现有表的所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO halo;

-- 4. 授予对现有序列的所有权限
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO halo;

-- 5. 自动授予新建表的所有权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO halo;

-- 6. 自动授予新建序列的所有权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO halo;



-- 1. 查看数据库级别权限
SELECT 'Database Privileges' as privilege_type, 
       datname as object_name, 
       has_database_privilege('halo', datname, 'CONNECT') as can_connect,
       has_database_privilege('halo', datname, 'CREATE') as can_create,
       has_database_privilege('halo', datname, 'TEMP') as can_create_temp
FROM pg_database
WHERE datname = 'halo'

UNION ALL

-- 2. 查看 schema 级别权限
SELECT 'Schema Privileges' as privilege_type, 
       nspname as object_name,
       has_schema_privilege('halo', nspname, 'CREATE') as can_create,
       has_schema_privilege('halo', nspname, 'USAGE') as can_usage,
       NULL as can_create_temp
FROM pg_namespace
WHERE nspname NOT IN ('information_schema', 'pg_catalog')

UNION ALL

-- 3. 查看用户对所有表的权限
SELECT 'Table Privileges' as privilege_type, 
       table_schema || '.' || table_name as object_name, 
       NULL as can_create, 
       NULL as can_usage, 
       privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'halo'

UNION ALL

-- 4. 查看用户对所有序列的权限
SELECT 'Sequence Privileges' as privilege_type, 
       sequence_schema || '.' || sequence_name as object_name, 
       NULL as can_create, 
       NULL as can_usage, 
       privilege_type
FROM information_schema.sequence_privileges
WHERE grantee = 'halo'

UNION ALL

-- 5. 查看默认权限（针对新建表和序列的默认权限）
SELECT
    defaclnamespace::regnamespace AS schema_name,
    defaclobjtype AS object_type,
    (aclexplode(defaclacl)).grantee::regrole AS grantee,
    (aclexplode(defaclacl)).privilege_type AS privilege_type,
    (aclexplode(defaclacl)).is_grantable AS is_grantable
FROM
    pg_default_acl
WHERE
    defaclrole = 'halo'::regrole;

PG 架构基础

PostgreSQL 是一个多进程架构的关系型数据库管理系统，以下列举部分主要进程

服务器进程（postgres server process）：主进程，也是一个守护进程，由 postmaster 运行。可以接受并处理来自客户端的多个并发连接，为每个连接启动（fork）一个新进程
客户端连接进程（backend process）：每个连接对应一个连接进程，进程会记录连接用户、连接的数据库、连接网络和当前状态
后台工作进程（background worker processes）：
- 后台写进程（background writer）：background 负责把共享内存中的脏页写到磁盘上，它会周期性地唤醒，以释放更多内存供新的数据库操作使用
- 预写日志进程（walwriter）：负责管理 Write-Ahead Logging（WAL），将预写日志信息写入磁盘。对于数据恢复和数据复制是非常重要
- 检查点进程（checkpointer）：负责执行检查点的进程，检查点是 WAL 序列中的一个点，在该点上，可以保证堆和索引数据文件已经用在该检查点之前修改的共享内存中的所有信息进行了更新
- 自动清理进程（autovacuum launcher）：当数据被更新或删除时，旧版本的数据不会被立即删除。而是通过自动清理进程在空闲时自动清理
- 逻辑复制进程（logical replication launcher）：负责逻辑复制，允许你将选定的数据库表、视图或自定义复制集同步到远程系统
- 日志进程（logger）：负责日志管理，包括日志消息的生成和传输
- **自定义 Work 进程：**部分插件使用

客户端工具

pgAdmin

官网：pgAdmin - PostgreSQL Tools

pgAdmin 是官方出品的一款免费客户端工具，也是最常用的工具，

Navicat

Navicat 是商业收费的客户端工具，提供的功能与 pgAdmin 类似

菜单

01 - PostgreSQL 17 安装和基础概念

分享

01 - PostgreSQL 17 安装和基础概念

简介

准备

主机准备

防火墙及 SELinux

系统参数

资源限制

内核参数

包管理器安装（DNF）

安装

安装后设置

源码编译安装

安装依赖

安装相关工具

安装 PG 所需依赖

基础变量设置

部署

部署后设置

创建用户和用户组

设置环境变量

设置 systemd 管理

初始化

基础

PG 的架构层级

数据库（`database`）

模式（`schema`）

表（`table`）

其他常用操作

用户/角色 `postgres` 初始化

用户/角色管理

PG 架构基础

客户端工具

pgAdmin

Navicat

评论