PostgreSQL 16数据库的各种安装方式汇总（yum、编译、docker等）

简介

本文通过讲解PG 16的各种安装方法。

docker快速使用

详细使用请参考：https://www.xmmup.com/dbbao69zaidockerzhongkuaisushiyonggegebanbendepostgresqlshujuku.html

Docker Hub的官网地址：https://hub.docker.com/_/postgres

GitHub的地址：https://github.com/docker-library/postgres

代码语言：javascript复制

nohup docker pull postgres:16.0 &



docker rm -f lhrpg16
docker run --name lhrpg16 -h lhrpg16 -d -p 54329:5432 -e POSTGRES_PASSWORD=lhr -e TZ=Asia/Shanghai postgres:16.0


docker exec -it lhrpg16 bash

docker exec -it lhrpg16 psql -U postgres -d postgres

select * from pg_tables;
select version();

示例：

代码语言：javascript复制

[root@lhrdb soft]# docker exec -it lhrpg16 psql -U postgres -d postgres
psql (16.0 (Debian 16.0-1.pgdg120 1))
Type "help" for help.

postgres=# select version();
                                                       version                                                       
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.0 (Debian 16.0-1.pgdg120 1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=# l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
----------- ---------- ---------- ----------------- ------------ ------------ ------------ ----------- -----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres           
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres           
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
(3 rows)

postgres=# create database lhrdb;
CREATE DATABASE
postgres=# l
                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
----------- ---------- ---------- ----------------- ------------ ------------ ------------ ----------- -----------------------
 lhrdb     | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres           
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres           
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
(4 rows)

postgres=# 

yum安装

详细请参考：https://www.xmmup.com/dbbao67shiyongyumlaianzhuangpostgresql13-3shujuku.html

注意：RHEL7和CentOS 8已不提供PG 16的yum源安装

代码语言：javascript复制

docker rm -f pg16
docker run -itd --name pg16 -h pg16 
  -p 5436:5432 -p 34389:3389 
  -v /sys/fs/cgroup:/sys/fs/cgroup 
  --privileged=true lhrbest/centosstream8:4.0 
  /usr/sbin/init
 docker exec -it pg16 bash 



-- 一些依赖包
dnf install -y cmake make gcc zlib gcc-c   perl readline readline-devel zlib zlib-devel 
perl python36 tcl openssl ncurses-devel openldap pam  perl-IPC-Run libicu-devel


dnf install epel-release -y
dnf --enablerepo=powertools install perl-IPC-Run -y


-- 自2023年8月起，PostgreSQL RPM repo停止向PostgreSQL RPM repo添加新包，**包括PostgreSQL 16**.我们将维护旧的主要版本，直到每个主要版本被PostgreSQL项目终止。请访问这里每个主要版本的最新发布日期。

-- 地址：https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/

wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgresql16-16.0-1PGDG.rhel8.x86_64.rpm
wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgresql16-server-16.0-1PGDG.rhel8.x86_64.rpm
wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgresql16-contrib-16.0-1PGDG.rhel8.x86_64.rpm
wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgis34_16-devel-3.4.0-1PGDG.rhel8.x86_64.rpm
wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgresql16-libs-16.0-1PGDG.rhel8.x86_64.rpm



-- 安装pg
dnf localinstall postgresql16-*


-- （建议安装）安装开发包，若后期需要编译一些插件，例如pg_recovery、pg_dirtyread等都需要该包
dnf localinstall postgresql16-devel-16.0-1PGDG.rhel8.x86_64.rpm


-- 验证
[root@pg16 yum.repos.d]# rpm -aq| grep postgres
postgresql16-libs-16.0-1PGDG.rhel8.x86_64
postgresql16-devel-16.0-1PGDG.rhel8.x86_64
postgresql16-16.0-1PGDG.rhel8.x86_64
postgresql16-contrib-16.0-1PGDG.rhel8.x86_64
postgresql16-server-16.0-1PGDG.rhel8.x86_64


-- 环境变量
echo "export PATH=/usr/pgsql-16/bin:$PATH" >> /etc/profile


-- 初始化
/usr/pgsql-16/bin/postgresql-16-setup initdb
systemctl enable postgresql-16
systemctl start postgresql-16
systemctl status postgresql-16


-- 本地登陆
su - postgres
psql

-- 安装插件
create extension pageinspect;
create extension pg_stat_statements;

select * from pg_extension ;
select * from pg_available_extensions order by name;

-- 修改postgres密码
alter user postgres with  password 'lhr'; 或 password
select * from pg_tables;
select version();



-- 配置允许PG远程登录，注意版本：
cat >> /var/lib/pgsql/16/data/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
unix_socket_directories='/var/lib/pgsql/16/data'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF

cat  << EOF > /var/lib/pgsql/16/data/pg_hba.conf
# TYPE  DATABASE    USER    ADDRESS       METHOD
local     all       all                    trust
host      all       all    ::1/128         trust
host      all       all   127.0.0.1/32     trust
host      all       all    0.0.0.0/0        md5
host   replication  all    0.0.0.0/0        md5
EOF

systemctl restart postgresql-16
systemctl status postgresql-16


-- 远程登陆
psql -U postgres -h 192.168.66.35 -d postgres -p54327

-- 从Postgresql 9.2开始，还可以使用URI格式进行远程连接：psql postgresql://myuser:mypasswd@myhost:5432/mydb
psql postgresql://postgres:lhr@192.168.66.35:54327/postgres



-- 环境变量
mkdir -p /home/postgres
chown postgres.postgres /home/postgres -R
sed -i 's|/var/lib/pgsql|/home/postgres|' /etc/passwd
echo "lhr" |passwd --stdin postgres


cat > /home/postgres/.bash_profile <<"EOF"
export PGPORT=5432
export PGHOME=/usr/pgsql-16
export PGDATA=/var/lib/pgsql/16/data
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.UTF-8
export DATE='date  "%Y%m%d%H%M"'
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGPASSWORD=lhr
export PGDATABASE=postgres
export PS1="[u@h W]$ "

alias ll='ls -l'
EOF


chown postgres.postgres /home/postgres/.bash_profile

编译安装

代码语言：javascript复制

wget https://ftp.postgresql.org/pub/source/v16.0/postgresql-16.0.tar.gz

-- 创建用户
groupadd -g 60000 pgsql
useradd -u 60000 -g pgsql pgsql
echo "lhr" | passwd --stdin pgsql


-- 创建目录
mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg16,soft}
chown -R pgsql:pgsql /postgresql
chmod -R 775 /postgresql



-- 安装一些依赖包
yum install -y cmake make gcc zlib gcc-c   perl readline readline-devel zlib zlib-devel 
perl python36 tcl openssl ncurses-devel openldap pam perl-IPC-Run libicu-devel


-- 编译
su - pgsql
cd /postgresql/soft
tar zxvf postgresql-16.0.tar.gz
cd postgresql-16.0
./configure --prefix=/postgresql/pg16
make -j 8 && make install
make world -j 8 && make install-world



-- 配置环境变量
cat >>  ~/.bash_profile <<"EOF"
export LANG=en_US.UTF-8
export PS1="[u@h W]$ "
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg16
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export DATE=`date  "%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
EOF

source  ~/.bash_profile



-- 初始化
su - pgsql
/postgresql/pg16/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres --data-checksums



-- 修改参数
cat >> /postgresql/pgdata/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
unix_socket_directories='/postgresql/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF

cat   >> /postgresql/pgdata/pg_hba.conf << EOF
# TYPE  DATABASE    USER    ADDRESS       METHOD
local     all       all                    trust
host      all       all   127.0.0.1/32     trust
host      all       all    0.0.0.0/0        md5
host   replication  all    0.0.0.0/0        md5
EOF

-- 启动
su - pgsql
pg_ctl start
pg_ctl status
pg_ctl stop

-- 修改密码
pg_ctl start 
psql
alter user postgres with  password 'lhr';
exit


-- 或：
nohup /postgresql/pg13/bin/postgres -D /postgresql/pgdata > /postgresql/pg13/pglog.out 2>&1 &




-- 配置系统服务
cat > /etc/systemd/system/PG16.service <<"EOF"
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network.target

[Service]
Type=forking
User=pgsql
Group=pgsql
Environment=PGPORT=5432
Environment=PGDATA=/postgresql/pgdata
OOMScoreAdjust=-1000
ExecStart=/postgresql/pg16/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/postgresql/pg16/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/postgresql/pg16/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target
EOF



systemctl daemon-reload
systemctl enable PG16
systemctl start PG16
systemctl status PG16

示例：

代码语言：javascript复制

[root@pg16 soft]# systemctl start PG16
[root@pg16 soft]# systemctl status PG16
● PG16.service - PostgreSQL database server
   Loaded: loaded (/etc/systemd/system/PG16.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2023-09-18 11:14:58 CST; 2s ago
     Docs: man:postgres(1)
  Process: 12228 ExecStart=/postgresql/pg16/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
 Main PID: 12230 (postgres)
   CGroup: /docker/a856bf75c0ffafd02328e2a7fcfb5e26b8efcef5e013efe3ee81717f5f78ca15/system.slice/PG16.service
           ├─12230 /postgresql/pg16/bin/postgres -D /postgresql/pgdata -p 5432
           ├─12231 postgres: logger 
           ├─12232 postgres: checkpointer 
           ├─12233 postgres: background writer 
           ├─12235 postgres: walwriter 
           ├─12236 postgres: autovacuum launcher 
           └─12237 postgres: logical replication launcher 

Sep 18 11:14:58 pg16 systemd[1]: Starting PostgreSQL database server...
Sep 18 11:14:58 pg16 pg_ctl[12228]: 2023-09-18 11:14:58.262 CST [12230] LOG:  redirecting log output to logging collector process
Sep 18 11:14:58 pg16 pg_ctl[12228]: 2023-09-18 11:14:58.262 CST [12230] HINT:  Future log output will appear in directory "pg_log".
Sep 18 11:14:58 pg16 systemd[1]: Started PostgreSQL database server.
[root@pg16 soft]# su - pgsql
Last login: Mon Sep 18 11:14:43 CST 2023 on pts/1
[pgsql@pg16 ~]$ psql
psql (16.0)
Type "help" for help.

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.0 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=# 

参考

https://www.xmmup.com/dbbao69zaidockerzhongkuaisushiyonggegebanbendepostgresqlshujuku.html

https://www.xmmup.com/dbbao67shiyongyumlaianzhuangpostgresql13-3shujuku.html

数据库 postgresql 容器镜像服务 yum 编译

0 人点赞