PostgreSQL 16 编译安装

说明

以CentOS 8为例。

安装依赖

libxslt

yum install libxslt-devel

readline

yum install readline-devel

zlib

yum install zlib-devel

xml2

yum install libxml2-devel

xslt

yum install libxslt-devel

uuid

yum install uuid-devel

libicu，PostgreSQL 16中默认会开启icu对不同语言的字符串进行排序。

yum install libicu-devel

不安装icu会得到以下错误

checking whether to build with ICU support... yes
checking for icu-uc icu-i18n... no
configure: error: ICU library not found
If you have ICU already installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-icu to disable ICU support.

配置 PGDATA 环境变量

将PGDATA环境变量写入/etc/environment，为后续使用pg_ctl提供便利（可以省略-D参数）

echo 'PGDATA=/home/postgres/16.1/data' >> /etc/environment

使配置生效

source /etc/environment

创建用户

PostgreSQL不能以root用户启动。

添加用户postgres。

添加用户叫postgres，是为了使用psql时默认会指定数据库postgres和默认角色postgres，这样psql就可以不用加-d和-U参数了。

useradd postgres

用户组添加postgres

groupadd postgres

可能会得到以下告警，忽略即可。

groupadd: group 'postgres' already exists

查看用户

cat /etc/passwd

删除用户

如果想要删除指定用户，可以使用userdel命令。

userdel postgres

出现如下告警，需要退出SSH再进

userdel: user postgres is currently used by process 8243

切换用户进行安装

su postgres

进入 postgres 主目录

cd ~

下载源码

从清华大学镜像中下载，选择16.1版本：https://mirrors.tuna.tsinghua.edu.cn/postgresql/source

wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v16.1/postgresql-16.1.tar.gz

解压

tar -zxvf postgresql-16.1.tar.gz

进入目录

cd postgresql-16.1

配置编译参数

./configure --prefix=/home/postgres/16.1 --with-libxml --with-libxslt --with-uuid=ossp

不编译安装任何插件

编译（不编译任何插件）

make

安装（不安装任何插件）

make install

编译安装全部插件

编译（编译全部插件）

make world

安装（安装全部插件）

make install-world

初始化数据库

创建数据目录

mkdir -p /home/postgres/16.1/data

初始化数据库，前面段落中已经配置了PGDATA环境变量，所以可以省略 -D /home/postgres/16.1/data

/home/postgres/16.1/bin/pg_ctl init

初始化成功信息

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 /home/postgresql/16.1/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:

    /home/postgres/16.1/bin/pg_ctl -D /home/postgres/16.1/data -l logfile start

如果是root用户启动，会得到以下报错

initdb: error: cannot be run as root
initdb: hint: Please log in (using, e.g., "su") as the (unprivileged) user that will own the server process.

启动服务

启动服务。首次启动服务使不需要指定-l，后续会修改logging_collector参数重定向到log_directory目录下。

/home/postgres/16.1/bin/pg_ctl start

设置预加载插件

psql -c "alter system set shared_preload_libraries = pg_stat_statements, auto_explain"

开启日志重定向

psql -c "alter system set logging_collector = on"

重启服务

/home/postgres/16.1/bin/pg_ctl restart

修改配置

进入 psql

psql

日志相关配置

alter system set log_min_duration_statement = '250ms';
alter system set log_line_prefix = '%m [%p] %q%u@%d/%a ';
alter system set log_rotation_age = '30d';
alter system set log_rotation_size = '50MB';
alter system set log_connections = on;
alter system set log_disconnections = on;
alter system set log_hostname = on;

自动 explain 配置

alter system set auto_explain.log_min_duration = '250ms';
alter system set auto_explain.log_analyze = on;
alter system set auto_explain.log_verbose = on;

热更新配置

select pg_reload_conf();

停止服务

/home/postgres/16.1/bin/pg_ctl stop

开机启动

拷贝脚本

cp /home/postgres/postgresql-16.1/contrib/start-scripts/linux /etc/init.d/postgresql

编辑脚本

vim /etc/init.d/postgresql

主要参数

prefix=/home/postgres/16.1
PGDATA="/home/postgres/16.1/data"

修改权限

chmod +x /etc/init.d/postgresql

添加配置

chkconfig --add postgresql

查看配置列表

chkconfig --list

查看状态

备注：需重启机器reboot

systemctl status postgresql

也可以使用pg_ctl查看运行状态

/home/postgres/16.1/bin/pg_ctl status

连接数据库

为psql创建软链接

ln -s /home/postgres/16.1/bin/psql /usr/local/bin/psql

直接使用psql即可

psql

root用户连接

psql -d postgres -U postgres

或者

sudo -u postgres psql