pg14安装_rpm方式

postgresql14-server-14.8-1PGDG.rhel7.x86_64.rpm
postgresql14-libs-14.8-1PGDG.rhel7.x86_64.rpm
postgresql14-14.8-1PGDG.rhel7.x86_64.rpm
postgresql14-contrib-14.8-1PGDG.rhel7.x86_64.rpm
1
2
3
4

--上传操作系统镜像到/opt目录
[root@localhost ~]# ls -l /opt | grep Cen*
-rw-r--r--.  1 root root 4712300544 Aug  8 05:27 CentOS-7-x86_64-DVD-2009.iso
--挂载镜像
[root@localhost ~]# mount /opt/CentOS-7-x86_64-DVD-2009.iso /mnt/
[root@localhost ~]# df -h | grep mnt
/dev/loop0      4.4G  4.4G     0 100% /mnt
--设置开机挂载
cat << EOF >> /etc/fstab
/dev/loop0    /mnt        iso9660 loop            0 0
EOF
--配置本地yum源文件
mkdir -p /etc/yum.repos.d/bak
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
cat >> /etc/yum.repos.d/os.repo <<"EOF"
[OS1]
name=OS
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

yum install libxslt libicu python36-libs python2-libs libperl.so
1

--禁用防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service 
--启用防火墙
firewall-cmd --zone=public --add-port=15400/tcp --permanent
firewall-cmd --zone=public --add-port=22/tcp --permanent
firewall-cmd --reload
--禁用防火墙区域偏移
sed -i 's/^AllowZoneDrifting=yes/AllowZoneDrifting=no/' /etc/firewalld/firewalld.conf 
1
2
3
4
5
6
7
8
9

sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
setenforce 0 
1
2

cat >> /etc/security/limits.conf << "EOF"
#add by postgres
postgres    soft    nproc    65536
postgres    hard    nproc    65536
postgres    soft    nofile   65536
postgres    hard    nofile   65536
postgres    soft    stack    1024000
postgres    hard    stack    1024000
EOF
1
2
3
4
5
6
7
8
9

echo deadline > /sys/block/sda/queue/scheduler
1

cp /etc/sysctl.conf /etc/sysctl.confbak
cat >> /etc/sysctl.conf <

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• 51
• 52
• 53
• 54
• 55
• 56
• 57
• 58
• 59
• 60
• 61
• 62

3.10、关闭进程间通信

#CentOS openEuler操作系统默认为关闭，可以跳过该步骤
sed -i 's/#RemoveIPC=no/RemoveIPC=no/g' /etc/systemd/logind.conf
systemctl daemon-reload
systemctl restart systemd-logind
1
2
3
4

四、安装数据库软件

cd /opt
rpm -ivh *.rpm
1
2

*会自动处理rpm安装顺序
rpm安装方式会自动创建postgres用户
二进制文件位置：/usr/pgsql-14/bin

五、创建数据目录(可选)

如果采用默认数据目录，该步骤忽略

mkdir -p /pgdata
chown -R postgres:postgres /pgdata
chmod -R 755 /pgdata
1
2
3

六、初始化实例

• 默认初始化实例数据目录位置：/var/lib/pgsql/14/data/

cd /usr/pgsql-14/bin
./initdb 
1
2

• 如果自定义数据目录位置：

--username=postgres  \
-D /pgdata \
--encoding=UTF8 \
--lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8

参数说明：
--lc-collate： 字符串排序的顺序
--lc-ctype：字符分类
1
2
3
4
5
6
7
8
9

七、启停

7.1.方式1：系统服务

开机自启系统服务文件位置：/usr/lib/systemd/system/postgresql-14.service

• 默认初始化实例数据目录位置

systemctl start postgresql-14.service
systemctl status postgresql-14.service
1
2

• 自定义数据目录初始化实例

--1.更改开机自启系统服务文件中数据目录位置
vi /usr/lib/systemd/system/postgresql-14.service
Environment=PGDATA=/var/lib/pgsql/14/data/  替换为自定义数据目录

--2.启动
systemctl start postgresql-14.service
systemctl status postgresql-14.service
1
2
3
4
5
6
7

7.2、方式2：命令

--启动命令
pg_ctl start -D /var/lib/pgsql/14/data/
--重启命令
pg_ctl restart  -D /var/lib/pgsql/14/data/
--查看数据库运行状态
pg_ctl status  -D /var/lib/pgsql/14/data/
--停止数据库
pg_ctl stop  -D /var/lib/pgsql/14/data/
1
2
3
4
5
6
7
8

八、配置环境变量

echo "##postgres user env configuration" >> /var/lib/pgsql/.bash_profile
cp /var/lib/pgsql/.bash_profile  /var/lib/pgsql/.bash_profile
sed -i 's/^export PATH/#export PATH/'  /var/lib/pgsql/.bash_profile
echo "#add by postgres" >>  /var/lib/pgsql/.bash_profile
echo 'export PGHOME=/usr/pgsql-14' >>  /var/lib/pgsql/.bash_profile
echo 'export PGPORT=5432' >>  /var/lib/pgsql/.bash_profile
echo 'export PATH=$PGHOME/bin:$PATH' >>  /var/lib/pgsql/.bash_profile
echo 'export MANPATH=$PGHOME/share/man:$MANPATH' >>  /var/lib/pgsql/.bash_profile
echo 'export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH' >>  /var/lib/pgsql/.bash_profile
echo 'export LANG="en_US.UTF-8"' >>  /var/lib/pgsql/.bash_profile
echo 'export DATE=`date +"%Y%m%d%H%M"`' >>  /var/lib/pgsql/.bash_profile
source /var/lib/pgsql/.bash_profile	
1
2
3
4
5
6
7
8
9
10
11
12

九、参数配置

配置文件：
位置：数据目录PGDATA目录
postgresql.conf --数据库参数
pg_hba.conf --用户访问权限文件

9.1、数据库参数配置(含归档配置)

--创建归档目录
mkdir -p /var/lib/pgsql/14/pg_archive
--更改配置文件
cd $PGDATA
cp postgresql.conf postgresql.confbak
sed -i "/^#listen_addresses = 'localhost'/s/#listen_addresses = 'localhost'/listen_addresses = '*'/" postgresql.conf
sed -i "s/^#port = 5432/port = 5432/" postgresql.conf
sed -i 's/max_connections = 100/max_connections = 500/' postgresql.conf
sed -i "/^#wal_level/s/^#//" postgresql.conf #去掉注释
sed -i 's/#archive_mode = off/archive_mode = on/' postgresql.conf
sed -i "/^#archive_command = ''/s/#archive_command = ''/archive_command ='\/usr\/bin\/lz4 -q -z %p \/var\/lib\/pgsql\/14\/pg_archive/%f.lz4'/" postgresql.conf  #-q取消警告-z强制压缩
sed -i "/^#log_destination = 'stderr'/s/#log_destination = 'stderr'/log_destination = 'csvlog'/" postgresql.conf
sed -i "/^#logging_collector = off/s/#logging_collector = off/logging_collector = on/" postgresql.conf
sed -i "/^#log_directory = 'log'/s/^#//" postgresql.conf #去掉注释
sed -i "/^#log_filename/s/^#//" postgresql.conf #去掉注释
sed -i "/^#log_file_mode/s/^#//" postgresql.conf #去掉注释
sed -i "/^#log_rotation_age/s/^#//" postgresql.conf #去掉注释
sed -i "/^#log_rotation_size/s/^#//" postgresql.conf #去掉注释
sed -i "/^shared_buffers = 128MB/s/shared_buffers = 128MB/shared_buffers = 1024MB/" postgresql.conf #物理内存25~40%
sed -i "/^#work_mem = 4MB/s/#work_mem = 4MB/work_mem = 30MB/" postgresql.conf
sed -i "/^#maintenance_work_mem = 64MB/s/#maintenance_work_mem = 64MB/maintenance_work_mem = 256MB/" postgresql.conf
sed -i "/^#temp_buffers = 8MB/s/#temp_buffers = 8MB/temp_buffers = 256MB/" postgresql.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

数据库能够接受的最大请求连接并发数

max_connections = 100

数据库服务器将使用的共享内存缓冲区量。建议值：数据库独立服务器的1/4内存。

shared_buffers =1GB

9.2、用户访问权限配置

cd $PGDATA
cp pg_hba.conf pg_hba.confbak
cat   > pg_hba.conf << EOF
# TYPE  DATABASE    USER    ADDRESS       METHOD
local      all       all                        trust
host      all       all    0.0.0.0/0        md5
EOF
1
2
3
4
5
6
7

第一行的内容表示local本机，all所有用户可以访问postgre的所有数据库，并且密码发送方式不需要加密(trust)。
第二行的内容用于远程访问，指定了可以访问postgreSql数据库的远程用户的ip范围， 0.0.0.0/0 表示所有ip都可以。如果你希望指定ip段，可以像这样去配置192.168.3.1/24(表示的ip范围是：192.168.3.1到192.168.3.255)。md5表示数据库访问密码使用md5的加密方式发送。

十、初始化数据环境

视情况执行如下脚本

--初始化postgres用户密码
alter user postgres with password '你设置的密码';
--创建新用户
create user top_sjjs with encrypted password 'top_sjjs';
alter user top_sjjs with superuser;
--创建数据库
create database mix_db owner top_sjjs;
grant all privileges on database mix_db to top_sjjs;
grant all privileges on all tables in schema public to top_sjjs;
--创建程序用户，用于操作数据库表里面的数据，完成增删改查
create user top_sjjs with encrypted password 'top_sjjs';
GRANT  update,delete,insert,select  ON  ALL TABLES IN SCHEMA public TO top_sjjs;
1
2
3
4
5
6
7
8
9
10
11
12

八、启停数据库

九、开机自启动

为了让PostgreSQL在以后每次服务器重启之后，都能够自动启动，写一个自启动配置文件。下面的命令需要使用root用户执行

十、参数优化（可选）

包含配置归档参数，默认开启归档。

10.1.参数配置

--更改配置文件
cp /pgdb/data/postgresql.conf /pgdb/data/postgresql.confbak
sed -i "/^#listen_addresses = 'localhost'/s/#listen_addresses = 'localhost'/listen_addresses = '*'/" /pgdb/data/postgresql.conf
sed -i "s/^#port = 5432/port = 5432/" /pgdb/data/postgresql.conf
sed -i 's/max_connections = 100/max_connections = 500/' /pgdb/data/postgresql.conf
sed -i "/^#wal_level/s/^#//" /pgdb/data/postgresql.conf #去掉注释
sed -i 's/#archive_mode = off/archive_mode = on/' /pgdb/data/postgresql.conf
sed -i "/^#archive_command = ''/s/#archive_command = ''/archive_command ='\/usr\/bin\/lz4 -q -z %p \/pgdb\/pg_archive\/%f.lz4'/" /pgdb/data/postgresql.conf  #-q取消警告-z强制压缩
sed -i "/^#log_destination = 'stderr'/s/#log_destination = 'stderr'/log_destination = 'csvlog'/" /pgdb/data/postgresql.conf
sed -i "/^#logging_collector = off/s/#logging_collector = off/logging_collector = on/" /pgdb/data/postgresql.conf
sed -i "/^#log_directory = 'log'/s/^#//" /pgdb/data/postgresql.conf #去掉注释
sed -i "/^#log_filename/s/^#//" /pgdb/data/postgresql.conf #去掉注释
sed -i "/^#log_file_mode/s/^#//" /pgdb/data/postgresql.conf #去掉注释
sed -i "/^#log_rotation_age/s/^#//" /pgdb/data/postgresql.conf #去掉注释
sed -i "/^#log_rotation_size/s/^#//" /pgdb/data/postgresql.conf #去掉注释
sed -i "/^shared_buffers = 128MB/s/shared_buffers = 128MB/shared_buffers = 1024MB/" /pgdb/data/postgresql.conf #物理内存25~40%
sed -i "/^#work_mem = 4MB/s/#work_mem = 4MB/work_mem = 30MB/" /pgdb/data/postgresql.conf
sed -i "/^#maintenance_work_mem = 64MB/s/#maintenance_work_mem = 64MB/maintenance_work_mem = 256MB/" /pgdb/data/postgresql.conf
sed -i "/^#temp_buffers = 8MB/s/#temp_buffers = 8MB/temp_buffers = 256MB/" /pgdb/data/postgresql.conf
--重启数据库
systemctl restart postgres.service
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

10.2.手动切归档

/pgdb/pgsql/bin/psql -Upostgres -W -d postgres -h127.0.0.1 -p5432 -c "select pg_switch_wal();"
1

10.3.归档定期删除策略

cat >> /var/spool/cron/postgres << "EOF"
# PostgresBegin
#设置归档策略:每晚零点10分删除7天前归档文件，视情况更改成1个月
10 00 * * * find /var/lib/pgsql/14/pg_archive -type f -name "0000000*" -mtime +7 -exec rm {} \; > /dev/null 2>&1
#设置日志保留2天，视情况更改成半年或3个月
00 01 * * * find /server/data/pgdb/data/log -type f -name "postgresql*.log" -mtime +7 -exec rm {} \; > /dev/null 2>&1
00 01 * * * find /server/data/pgdb/data/log -type f -name "postgresql*.csv" -mtime +7 -exec rm {} \; > /dev/null 2>&1
EOF
1
2
3
4
5
6
7
8

11.常见问题

1.安装报错

--问题描述
[root@localhost opt]# ls -l
total 8064
-rw-r--r--. 1 root root 1563508 Oct 22 09:09 postgresql14-14.8-1PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root  704024 Oct 22 09:03 postgresql14-contrib-14.8-1PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root  277080 Oct 22 09:03 postgresql14-libs-14.8-1PGDG.rhel7.x86_64.rpm
-rw-r--r--. 1 root root 5707012 Oct 22 09:03 postgresql14-server-14.8-1PGDG.rhel7.x86_64.rpm
[root@localhost opt]# rpm -ivh *.rpm
warning: postgresql14-14.8-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
error: Failed dependencies:
	libicu is needed by postgresql14-14.8-1PGDG.rhel7.x86_64
	libperl.so()(64bit) is needed by postgresql14-contrib-14.8-1PGDG.rhel7.x86_64
	libpython3.6m.so.1.0()(64bit) is needed by postgresql14-contrib-14.8-1PGDG.rhel7.x86_64
	libicui18n.so.50()(64bit) is needed by postgresql14-server-14.8-1PGDG.rhel7.x86_64
	libicuuc.so.50()(64bit) is needed by postgresql14-server-14.8-1PGDG.rhel7.x86_64

--解决办法：
yum install libxslt libicu python36-libs python2-libs libperl.so

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2.编译报错

问题1：
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.
安装：yum install perl-ExtUtils-Embed

问题2：
configure: error: OpenSSL Crypto library not found
安装：yum -y install openssl-devel 

 
问题3：
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is not
installed.
解决方法：
yum install perl-ExtUtils-Embed
 
问题4：
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-readline to disable readline support.
解决方法：
yum install readline readline-devel
问题5：
checking for inflate in -lz... no
configure: error: zlib library not found
If you have zlib already installed, see config.log for details on the
failure. It is possible the compiler isn't looking in the proper directory.
Use --without-zlib to disable zlib support.
解决方法：
yum install zlib zlib-devel
 
 
问题6：
checking for CRYPTO_new_ex_data in -lcrypto... no
configure: error: library 'crypto' is required for OpenSSL
解决方法：
yum install openssl openssl-devel
 
问题7：
checking for pam_start in -lpam... no
configure: error: library 'pam' is required for PAM
解决方法：
yum install pam pam-devel
 
问题8：
checking for xmlSaveToBuffer in -lxml2... no
configure: error: library 'xml2' (version >= 2.6.23) is required for XML support
解决方法：
yum install libxml2 libxml2-devel
 
问题9：
checking for xsltCleanupGlobals in -lxslt... no
configure: error: library 'xslt' is required for XSLT support
解决方法：
yum install libxslt libxslt-devel
 
 
问题10：
configure: error: Tcl shell not found
解决方法：
yum install tcl tcl-devel
 
 
问题11：
checking for ldap.h... no
configure: error: header file is required for LDAP
解决方法：
yum install openldap openldap-devel
 
问题12：
checking for Python.h... no
configure: error: header file  is required for Python
解决方法：
yum install python python-devel
 
问题13：
Error when bootstrapping CMake:
Cannot find appropriate C++ compiler on this system.
Please specify one using environment variable CXX.
See cmake_bootstrap.log for compilers attempted.
解决方法：
yum install gcc-c++
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

3.启动报错"max_stack_depth" must not exceed 7680kB

--报错：
[root@top132:/pgdb/data]$ systemctl start postgres
Job for postgres.service failed because the control process exited with error code. See "systemctl status postgres.service" and "journalctl -xe" for details.
[root@top132:/pgdb/data]$ journalctl -xe
Oct 09 04:12:48 top132 systemd[1]: Configuration file /usr/lib/systemd/system/postgres.service is marked executable. Please remove executable permission bits. Proceeding anyway.
Oct 09 04:12:56 top132 oracledb_exporter[29984]: ts=2023-10-09T08:12:56.802Z caller=collector.go:262 level=error Errorpingingoracle:="missing port in address"
Oct 09 04:13:11 top132 oracledb_exporter[29984]: ts=2023-10-09T08:13:11.805Z caller=collector.go:262 level=error Errorpingingoracle:="missing port in address"
Oct 09 04:13:26 top132 oracledb_exporter[29984]: ts=2023-10-09T08:13:26.803Z caller=collector.go:262 level=error Errorpingingoracle:="missing port in address"
Oct 09 04:13:41 top132 oracledb_exporter[29984]: ts=2023-10-09T08:13:41.806Z caller=collector.go:262 level=error Errorpingingoracle:="missing port in address"
Oct 09 04:13:56 top132 oracledb_exporter[29984]: ts=2023-10-09T08:13:56.802Z caller=collector.go:262 level=error Errorpingingoracle:="missing port in address"
Oct 09 04:14:04 top132 polkitd[727]: Registered Authentication Agent for unix-process:9141:161465164 (system bus name :1.6876 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object path /org/freedesktop/PolicyKit1/AuthenticationAgent, loca
Oct 09 04:14:04 top132 systemd[1]: Starting PostgreSQL database server...
-- Subject: Unit postgres.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit postgres.service has begun starting up.
Oct 09 04:14:04 top132 pg_ctl[9147]: waiting for server to start....2023-10-09 08:14:04.952 GMT [9151] LOG:  invalid value for parameter "max_stack_depth": 8192
Oct 09 04:14:04 top132 pg_ctl[9147]: 2023-10-09 08:14:04.952 GMT [9151] DETAIL:  "max_stack_depth" must not exceed 7680kB.
Oct 09 04:14:04 top132 pg_ctl[9147]: 2023-10-09 08:14:04.952 GMT [9151] HINT:  Increase the platform's stack depth limit via "ulimit -s" or local equivalent.
Oct 09 04:14:04 top132 pg_ctl[9147]: 2023-10-09 04:14:04.953 EDT [9151] FATAL:  configuration file "/pgdb/data/postgresql.conf" contains errors
Oct 09 04:14:05 top132 pg_ctl[9147]: stopped waiting
Oct 09 04:14:05 top132 pg_ctl[9147]: pg_ctl: could not start server
Oct 09 04:14:05 top132 pg_ctl[9147]: Examine the log output.
Oct 09 04:14:05 top132 systemd[1]: postgres.service: control process exited, code=exited status=1

--解决办法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28