01-Alma 9 上安装 PostgreSQL 16

更新时间：2023 年 9 月

版本： PostgreSQL 16

简介

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

官方文档索引：PostgreSQL: Documentation

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

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

安装

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

准备

主机准备

主机名	操作系统	架构	IP	安装软件
db-postgresql-01.skynemo.cn	AlmaLinux 9.2	x86_64	192.168.111.194	PostgreSQL 16

关闭防火墙

# 关闭 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

其他系统参数配置

详见 附录-系统设置

包管理器安装（DNF）

选择系统类别

选择安装版本和平台架构

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

$ PG_VERSION=16

# 安装仓库
$ sudo 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_VERSION}-server

源码编译安装（未完成）

下载

$ wget https://ftp.postgresql.org/pub/source/v15.4/postgresql-15.4.tar.gz

安装后设置

指定数据目录

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

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


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

初始化数据库

$ sudo /usr/pgsql-${PG_VERSION}/bin/postgresql-${PG_VERSION}-setup initdb
#### 输出信息
Initializing database ... OK

修改监听设置

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

启动并设置开机启动

$ sudo systemctl enable --now postgresql-${PG_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  /data/pgsql/15/postgresql.conf
password_encryption = scram-sha-256     # scram-sha-256 or md5

修改外部连接权限

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

$ vim /data/pgsql/15/pg_hba.conf
host    all             postgres        0.0.0.0/0               scram-sha-256

重新加载配置

# systemd 方式
$ systemctl reload postgresql-15

# 命令行方式
$ /usr/pgsql-15/bin/pg_ctl reload -D /data/pgsql/15

# 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;

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 类似

附录

系统设置

资源限制

PAM 模块

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

# 设置限制
$ cat > /etc/security/limits.d/pg.conf <<EOF
*            -    core            0
*            -    nproc           unlimited
*            -    nofile          1048576
*            -    memlock         unlimited
*            -    msgqueue        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/pg.conf << EOF
[Manager]
DefaultLimitCORE=0
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=0@' /etc/systemd/system.conf || echo "DefaultLimitCORE=0" >> /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


######  防 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



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

net.netfilter.nf_conntrack_tcp_timeout_established = 43200
net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60
net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120
net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120


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




######  其他设置  #######
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


######  内存相关设置 #######
vm.swappiness = 0
vm.max_map_count = 262144

###### 文件相关 #######
fs.file-max = 1048576
fs.nr_open = 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

###### 进程相关 #######
kernel.pid_max = 132768

EOF



# 应用参数
$ sysctl -p

如果觉得文章对你有用，请随意赞赏

PostgreSQL

01-Alma 9 上安装 PostgreSQL 16

https://www.skynemo.cn//archives/01-postgresql16-install-in-alma9

作者

Nemo

发布于

2023-09-16

更新于

2023-10-23

许可协议

CC BY 4.0

01-Alma 9 上安装 PostgreSQL 16

简介

安装

准备

主机准备

包管理器安装（DNF）

源码编译安装（未完成）

安装后设置

基础

PG 中的层级

数据库（database）

模式（schema）

表（table）

其他常用操作

用户 postgres 初始化

用户管理

PG 架构基础

客户端工具

pgAdmin

Navicat

附录

系统设置

资源限制

内核参数

作者

发布于

更新于

许可协议

评论

数据库（`database`）

模式（`schema`）

表（`table`）

用户 `postgres` 初始化