1、安装前准备
关闭防火墙和selinux
systemctl stop firewalld
systemctl disable firewalld
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
2、创建用户和目录
groupadd pg14
useradd -g pg14 pg14
echo 123| passwd --stdin pg14
软件安装路径 /home/pg14/soft
数据目录 /home/pg14/data
su - pg14
mkdir -p /home/pg14/soft
mkdir -p /home/pg14/data
3、解压安装包
tar -zxf postgresql-14.6.tar.gz
4、安装依赖包
yum install -y gcc
yum install -y make
yum install -y readline*
yum install -y zlib-devel
yum install -y libxml2-devel
yum install -y libxslt-devel
yum install -y openssl-devel
yum install -y perl-ExtUtils-Embed
yum install -y perl-ExtUtils-MakeMaker
yum install -y pam-devel
yum install -y openldap-devel
yum install -y libicu-devel
yum install -y tcl-devel
yum install -y llvm-devel \ --*
llvm-toolset \ --*
libicu-devel \
perl-ExtUtils-Embed perl-ExtUtils-MakeMaker \
python39-devel \ --*
readline-devel \
openssl-devel \
pam-devel \
openldap-devel \
libxml2-devel \
libxslt-devel \
tcl-devel
5、编译
./configure --prefix=/home/pg14/soft --with-pgport=5666 --with-openssl
6、安装
make world -j16
make install-world -j16
7、配置环境变量
vi .bashrc 添加
export PGHOME=/home/pg14/soft
export PGDATA=/home/pg14/data
export LD_LIBRARY_PATH=${PGHOME}/lib:${LD_LIBRARY_PATH}
export PATH=${PGHOME}/bin:${PATH}
source .bashrc
8、初始化集簇
启用checksum
超级用户是postgres
initdb -D /home/pg14/data -k -U postgres -W
9、启动数据库
pg_ctl start -D /home/pg14/data -l /tmp/logfile
10、配置pgpass免密登录
cd
vi .pgpass
#format
#host:port:username:dbname:password
localhost:5666:postgres:postgres:123
psql -U postgres
11、为数据库配置ssl支持,建立sslinfo扩展
1)生成证书文件
pg14用户下
openssl req -new -x509 -days 365 -nodes -text -out server.crt \
-keyout server.key -subj "/CN=node1"
chmod og-rwx server.key
openssl req -new -nodes -text -out root.csr \
-keyout root.key -subj "/CN=node1"
chmod og-rwx root.key
openssl x509 -req -in root.csr -text -days 3650 \
-extfile /etc/pki/tls/openssl.cnf -extensions v3_ca \
-signkey root.key -out root.crt
openssl req -new -nodes -text -out server.csr \
-keyout server.key -subj "/CN=node1"
chmod og-rwx server.key
openssl x509 -req -in server.csr -text -days 365 \
-CA root.crt -CAkey root.key -CAcreateserial \
-out server.crt
2) 复制这两个证书文件到$PGDATA
cp /home/pg14/ssl/server.crt /home/pg14/data
cp /home/pg14/ssl/server.key /home/pg14/data
3)postgresql.auto.conf参数文件添加下面三行
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
4)重启数据库
pg_ctl restart -D $PGDATA -l /tmp/logfile
5)创建sslinfo扩展
postgres=# create extension sslinfo;
CREATE EXTENSION
postgres=# select ssl_is_used();
ssl_is_used
-------------
f
(1 row)
退出重新以远程连接方式连接
psql -d postgres -U postgres -h localhost
psql (14.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# select ssl_is_used();
ssl_is_used
-------------
t
(1 row)
错误:
ERROR: could not open extension control file “/home/pg14/soft/share/postgresql/extension/sslinfo.control”: No such file or directory
这是因为在指定目录下没有找到sslinfo.control,可以find一下该文件,发现他在/home/pg14/postgresql-14.6/contrib/sslinfo目录下,这是因为编译安装时没有使用world编译,将这个目录下的所有文件都复制到/home/pg14/soft/share/postgresql/extension/下,创建扩展还是报错:
ERROR: could not access file "
l
i
b
d
i
r
/
s
s
l
i
n
f
o
"
:
N
o
s
u
c
h
f
i
l
e
o
r
d
i
r
e
c
t
o
r
y
,要先弄清
libdir/sslinfo": No such file or directory,要先弄清
libdir/sslinfo":Nosuchfileordirectory,要先弄清libdir是哪个目录,执行pg_config --pkglibdir发现$libdir
是/home/pg14/soft/lib/postgresql目录,里面确实没有sslinfo.so文件,于是去/home/pg14/postgresql-14.6/contrib/sslinfo目录下,把sslinfo.c重新编译,make&&make install 把生成的sslinfo.so文件复制到/home/pg14/soft/lib/postgresql目录下,再次创建扩展成功。