PostgreSQL编译安装-CentOS-7.9 Arm

环境信息

操作系统: CentOS 7.9
架构：Arm
PG版本：13.21

编译

• 安装必要的依赖库和编译工具

sudo yum groupinstall "Development Tools" -y
sudo yum install readline-devel zlib-devel gcc make wget libuuid-devel systemd-devel openssl-devel  -y

• 下载 PGSQL 源码

从 PG 官方网站或 GitHub 仓库下载 MySQL 的源码包。

wget https://ftp.postgresql.org/pub/source/v13.21/postgresql-13.21.tar.gz
tar -xzvf postgresql-13.21.tar.gz
cd postgresql-13.21

• 编译PG

./configure --prefix=/usr/local/pgsql13 \
            --with-includes=/usr/include \
            --with-libraries=/usr/lib64 \
            --with-openssl \
            --with-systemd

make -j$(nproc)      # 并行编译，按 CPU 核心数自动处理
sudo make install

• 编译并安装 contrib 插件

cd contrib
make -j$(nproc)
make install

这会编译并安装所有 contrib 插件（包括 pg_stat_statements.so, uuid-ossp.so, pgcrypto.so 等）到 $PREFIX/lib/postgresql/ 下。

安装

• 创建postgres用户

sudo useradd -m -s /bin/bash postgres
sudo passwd postgres

• 上传至服务器并安装

注意：服务器需要自己挂盘，建议统一挂载到/data目录

#解压安装包提取文件内容到/data目录
tar -xvf pg-13.21-arm.tgz -C /usr/local/pgsql13
#创建数据目录
sudo mkdir -p /data/pgdata /data/pgdata_archives
#给目录授权
sudo chown -R postgres:postgres /data/pgdata /data/pgdata_archives

• 初始化数据库（使用 postgres 用户）

su - postgres
initdb -D /data/pgdata

• 配置pg

创建 PGSQL 配置文件，并根据需要进行定制。

# primary_postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 5000
superuser_reserved_connections = 10
password_encryption = scram-sha-256
shared_buffers = 128MB
dynamic_shared_memory_type = posix
wal_level = replica
max_wal_size = 4096MB
min_wal_size = 80MB
max_wal_senders = 10
wal_keep_size= 4096MB
#synchronous_standby_names = '*'
hot_standby = on
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_line_prefix = '%m [%p] '
log_timezone = 'PRC'
log_min_duration_sample = 30000ms
log_statement_sample_rate = 0.2
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
archive_mode = on
archive_command = 'gzip < %p > /data/pgdata_archives/%f.gz && echo %f.gz >> /data/pgdata_archives/archive.list'
#archive_timeout = 600
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

#primary_pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
# add connection host and replication
host    all             all             0.0.0.0/0            	scram-sha-256
host    replication     repl            {{ standby IP }}/32        trust

• 添加环境变量

sudo tee /etc/profile.d/pgsql13.sh <<EOF
export PATH=/usr/local/pgsql13/bin:\$PATH
export PGDATA=/data/pg_data
export LD_LIBRARY_PATH=/usr/local/pgsql13/lib:\$LD_LIBRARY_PATH
EOF

source /etc/profile.d/pgsql13.sh

• 设置为 systemd 服务

创建 service 文件 /etc/systemd/system/postgresql-13.service：

# /etc/systemd/system/postgresql-13.service
[Unit]
Description=PostgreSQL 13 database server
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres

ExecStart=/usr/local/pgsql13/bin/pg_ctl start -D /data/pgdata -s -l /data/pgdata/server.log -o "-c config_file=/data/pgdata/postgresql.conf"
ExecStop=/usr/local/pgsql13/bin/pg_ctl stop -D /data/pgdata -s -m fast
ExecReload=/usr/local/pgsql13/bin/pg_ctl reload -D /data/pgdata -s
Restart=on-failure

# 防止失败过快被 systemd 拉黑
StartLimitIntervalSec=30
StartLimitBurst=5

[Install]
WantedBy=multi-user.target

启用并启动服务：

sudo systemctl daemon-reload
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13

• 修改密码，并允许远程连接

#免密登录,修改密码 并新增超管用户
$ su - postgres 
$ psql
psql (13.21)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'yyWr4$8c&a3!b!'
postgres=# CREATE ROLE pgtest superuser PASSWORD 'E^NWgE@KFDkFM%' login;
CREATE ROLE
#退出
postgres=# exit

• 验证安装

通过登录 MySQL 来验证安装是否成功。

$ pgsql -Upgtest -d postgres --password
Password:
psql (13.21)
Type "help" for help.

postgres=#

输入设置的密码后，进入命令行界面。如果成功进入，说明编译安装完成。