简介
PostgreSQL 16已于2023年9月14日发布，这是世界上最先进的开源数据库的最新版本。相关介绍可以参考：国际新闻：PostgreSQL 16 发布！
本文通过讲解PG 16的各种安装方法。
docker快速使用
详细使用请参考：https://www.xmmup.com/dbbao69zaidockerzhongkuaisushiyonggegebanbendepostgresqlshujuku.html
Docker Hub的官网地址：https://hub.docker.com/_/postgres
GitHub的地址：https://github.com/docker-library/postgres
1nohup docker pull postgres:16.0 &
2
3
4
5docker rm -f lhrpg16
6docker run --name lhrpg16 -h lhrpg16 -d -p 54329:5432 -e POSTGRES_PASSWORD=lhr -e TZ=Asia/Shanghai postgres:16.0
7
8
9docker exec -it lhrpg16 bash
10
11docker exec -it lhrpg16 psql -U postgres -d postgres
12
13select * from pg_tables;
14select version();
示例：
1[root@lhrdb soft]# docker exec -it lhrpg16 psql -U postgres -d postgres
2psql (16.0 (Debian 16.0-1.pgdg120+1))
3Type "help" for help.
4
5postgres=# select version();
6 version
7---------------------------------------------------------------------------------------------------------------------
8 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
9(1 row)
10postgres=# \l
11 List of databases
12 Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
13-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
14 postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
15 template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
16 | | | | | | | | postgres=CTc/postgres
17 template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
18 | | | | | | | | postgres=CTc/postgres
19(3 rows)
20
21postgres=# create database lhrdb;
22CREATE DATABASE
23postgres=# \l
24 List of databases
25 Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
26-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
27 lhrdb | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
28 postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
29 template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
30 | | | | | | | | postgres=CTc/postgres
31 template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
32 | | | | | | | | postgres=CTc/postgres
33(4 rows)
34
35postgres=#
36
yum安装
详细请参考：https://www.xmmup.com/dbbao67shiyongyumlaianzhuangpostgresql13-3shujuku.html
注意：RHEL7和CentOS 8已不提供PG 16的yum源安装
1docker rm -f pg16
2docker run -itd --name pg16 -h pg16 \
3 -p 5436:5432 -p 34389:3389 \
4 -v /sys/fs/cgroup:/sys/fs/cgroup \
5 --privileged=true lhrbest/centosstream8:4.0 \
6 /usr/sbin/init
7 docker exec -it pg16 bash
8
9
10
11-- 一些依赖包
12dnf install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel \
13perl python36 tcl openssl ncurses-devel openldap pam perl-IPC-Run libicu-devel
14
15
16dnf install epel-release -y
17dnf --enablerepo=powertools install perl-IPC-Run -y
18
19
20-- 自2023年8月起，PostgreSQL RPM repo停止向PostgreSQL RPM repo添加新包，**包括PostgreSQL 16**.我们将维护旧的主要版本，直到每个主要版本被PostgreSQL项目终止。请访问这里每个主要版本的最新发布日期。
21
22-- 地址：https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/
23
24wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgresql16-16.0-1PGDG.rhel8.x86_64.rpm
25wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgresql16-server-16.0-1PGDG.rhel8.x86_64.rpm
26wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgresql16-contrib-16.0-1PGDG.rhel8.x86_64.rpm
27wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgis34_16-devel-3.4.0-1PGDG.rhel8.x86_64.rpm
28wget https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8-x86_64/postgresql16-libs-16.0-1PGDG.rhel8.x86_64.rpm
29
30
31
32-- 安装pg
33dnf localinstall postgresql16-*
34
35
36-- （建议安装）安装开发包，若后期需要编译一些插件，例如pg_recovery、pg_dirtyread等都需要该包
37dnf localinstall postgresql16-devel-16.0-1PGDG.rhel8.x86_64.rpm
38
39
40-- 验证
41[root@pg16 yum.repos.d]# rpm -aq| grep postgres
42postgresql16-libs-16.0-1PGDG.rhel8.x86_64
43postgresql16-devel-16.0-1PGDG.rhel8.x86_64
44postgresql16-16.0-1PGDG.rhel8.x86_64
45postgresql16-contrib-16.0-1PGDG.rhel8.x86_64
46postgresql16-server-16.0-1PGDG.rhel8.x86_64
47
48
49-- 环境变量
50echo "export PATH=/usr/pgsql-16/bin:$PATH" >> /etc/profile
51
52
53-- 初始化
54/usr/pgsql-16/bin/postgresql-16-setup initdb
55systemctl enable postgresql-16
56systemctl start postgresql-16
57systemctl status postgresql-16
58
59
60-- 本地登陆
61su - postgres
62psql
63
64-- 安装插件
65create extension pageinspect;
66create extension pg_stat_statements;
67
68select * from pg_extension ;
69select * from pg_available_extensions order by name;
70
71-- 修改postgres密码
72alter user postgres with password 'lhr'; 或 \password
73select * from pg_tables;
74select version();
75
76
77
78-- 配置允许PG远程登录，注意版本：
79cat >> /var/lib/pgsql/16/data/postgresql.conf <<"EOF"
80listen_addresses = '*'
81port=5432
82unix_socket_directories='/var/lib/pgsql/16/data'
83logging_collector = on
84log_directory = 'pg_log'
85log_filename = 'postgresql-%a.log'
86log_truncate_on_rotation = on
87EOF
88
89cat << EOF > /var/lib/pgsql/16/data/pg_hba.conf
90# TYPE DATABASE USER ADDRESS METHOD
91local all all trust
92host all all ::1/128 trust
93host all all 127.0.0.1/32 trust
94host all all 0.0.0.0/0 md5
95host replication all 0.0.0.0/0 md5
96EOF
97
98systemctl restart postgresql-16
99systemctl status postgresql-16
100
101
102-- 远程登陆
103psql -U postgres -h 192.168.66.35 -d postgres -p54327
104
105-- 从Postgresql 9.2开始，还可以使用URI格式进行远程连接：psql postgresql://myuser:mypasswd@myhost:5432/mydb
106psql postgresql://postgres:lhr@192.168.66.35:54327/postgres
107
108
109
110-- 环境变量
111mkdir -p /home/postgres
112chown postgres.postgres /home/postgres -R
113sed -i 's|/var/lib/pgsql|/home/postgres|' /etc/passwd
114echo "lhr" |passwd --stdin postgres
115
116
117cat > /home/postgres/.bash_profile <<"EOF"
118export PGPORT=5432
119export PGHOME=/usr/pgsql-16
120export PGDATA=/var/lib/pgsql/16/data
121export PATH=$PGHOME/bin:$PATH
122export MANPATH=$PGHOME/share/man:$MANPATH
123export LANG=en_US.UTF-8
124export DATE='date +"%Y%m%d%H%M"'
125export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
126export PGHOST=$PGDATA
127export PGUSER=postgres
128export PGPASSWORD=lhr
129export PGDATABASE=postgres
130export PS1="[\u@\h \W]\$ "
131
132alias ll='ls -l'
133EOF
134
135
136chown postgres.postgres /home/postgres/.bash_profile
编译安装
1wget https://ftp.postgresql.org/pub/source/v16.0/postgresql-16.0.tar.gz
2
3-- 创建用户
4groupadd -g 60000 pgsql
5useradd -u 60000 -g pgsql pgsql
6echo "lhr" | passwd --stdin pgsql
7
8
9-- 创建目录
10mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg16,soft}
11chown -R pgsql:pgsql /postgresql
12chmod -R 775 /postgresql
13
14
15
16-- 安装一些依赖包
17yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel \
18perl python36 tcl openssl ncurses-devel openldap pam perl-IPC-Run libicu-devel
19
20
21-- 编译
22su - pgsql
23cd /postgresql/soft
24tar zxvf postgresql-16.0.tar.gz
25cd postgresql-16.0
26./configure --prefix=/postgresql/pg16
27make -j 8 && make install
28make world -j 8 && make install-world
29
30
31
32-- 配置环境变量
33cat >> ~/.bash_profile <<"EOF"
34export LANG=en_US.UTF-8
35export PS1="[\u@\h \W]\$ "
36export PGPORT=5432
37export PGDATA=/postgresql/pgdata
38export PGHOME=/postgresql/pg16
39export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
40export PATH=$PGHOME/bin:$PATH:.
41export DATE=`date +"%Y%m%d%H%M"`
42export MANPATH=$PGHOME/share/man:$MANPATH
43export PGHOST=$PGDATA
44export PGUSER=postgres
45export PGDATABASE=postgres
46EOF
47
48source ~/.bash_profile
49
50
51
52-- 初始化
53su - pgsql
54/postgresql/pg16/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres --data-checksums
55
56
57
58-- 修改参数
59cat >> /postgresql/pgdata/postgresql.conf <<"EOF"
60listen_addresses = '*'
61port=5432
62unix_socket_directories='/postgresql/pgdata'
63logging_collector = on
64log_directory = 'pg_log'
65log_filename = 'postgresql-%a.log'
66log_truncate_on_rotation = on
67EOF
68
69cat >> /postgresql/pgdata/pg_hba.conf << EOF
70# TYPE DATABASE USER ADDRESS METHOD
71local all all trust
72host all all 127.0.0.1/32 trust
73host all all 0.0.0.0/0 md5
74host replication all 0.0.0.0/0 md5
75EOF
76
77-- 启动
78su - pgsql
79pg_ctl start
80pg_ctl status
81pg_ctl stop
82
83-- 修改密码
84pg_ctl start
85psql
86alter user postgres with password 'lhr';
87exit
88
89
90-- 或：
91nohup /postgresql/pg13/bin/postgres -D /postgresql/pgdata > /postgresql/pg13/pglog.out 2>&1 &
92
93
94
95
96-- 配置系统服务
97cat > /etc/systemd/system/PG16.service <<"EOF"
98[Unit]
99Description=PostgreSQL database server
100Documentation=man:postgres(1)
101After=network.target
102
103[Service]
104Type=forking
105User=pgsql
106Group=pgsql
107Environment=PGPORT=5432
108Environment=PGDATA=/postgresql/pgdata
109OOMScoreAdjust=-1000
110ExecStart=/postgresql/pg16/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
111ExecStop=/postgresql/pg16/bin/pg_ctl stop -D ${PGDATA} -s -m fast
112ExecReload=/postgresql/pg16/bin/pg_ctl reload -D ${PGDATA} -s
113KillMode=mixed
114KillSignal=SIGINT
115TimeoutSec=0
116
117[Install]
118WantedBy=multi-user.target
119EOF
120
121
122
123systemctl daemon-reload
124systemctl enable PG16
125systemctl start PG16
126systemctl status PG16
示例：
1[root@pg16 soft]# systemctl start PG16
2[root@pg16 soft]# systemctl status PG16
3● PG16.service - PostgreSQL database server
4 Loaded: loaded (/etc/systemd/system/PG16.service; enabled; vendor preset: disabled)
5 Active: active (running) since Mon 2023-09-18 11:14:58 CST; 2s ago
6 Docs: man:postgres(1)
7 Process: 12228 ExecStart=/postgresql/pg16/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
8 Main PID: 12230 (postgres)
9 CGroup: /docker/a856bf75c0ffafd02328e2a7fcfb5e26b8efcef5e013efe3ee81717f5f78ca15/system.slice/PG16.service
10 ├─12230 /postgresql/pg16/bin/postgres -D /postgresql/pgdata -p 5432
11 ├─12231 postgres: logger
12 ├─12232 postgres: checkpointer
13 ├─12233 postgres: background writer
14 ├─12235 postgres: walwriter
15 ├─12236 postgres: autovacuum launcher
16 └─12237 postgres: logical replication launcher
17
18Sep 18 11:14:58 pg16 systemd[1]: Starting PostgreSQL database server...
19Sep 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
20Sep 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".
21Sep 18 11:14:58 pg16 systemd[1]: Started PostgreSQL database server.
22[root@pg16 soft]# su - pgsql
23Last login: Mon Sep 18 11:14:43 CST 2023 on pts/1
24[pgsql@pg16 ~]$ psql
25psql (16.0)
26Type "help" for help.
27
28postgres=# select version();
29 version
30---------------------------------------------------------------------------------------------------------
31 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
32(1 row)
33
34postgres=#
参考
https://www.xmmup.com/dbbao69zaidockerzhongkuaisushiyonggegebanbendepostgresqlshujuku.html
https://www.xmmup.com/dbbao67shiyongyumlaianzhuangpostgresql13-3shujuku.html
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
