银河v10 sp3 安装pg12.7 开启单向ssl 1主1从复制

银河v10 sp3 安装pg12.7   开启单向ssl  1主1从复制
 

一，安装数据库（俩台服务器都安装）
1.关闭防火墙等
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service
 
echo "SELINUX=disabled"  /etc/selinux/config
cat  /etc/selinux/config
 
2. ssh快速登录
vim /etc/ssh/sshd_config
UseDNS no
GSSAPIAuthentication no

service sshd restart
/bin/systemctl restart sshd.service

3. 修改主机名
hostnamectl set-hostname db02
（另一台 hostnamectl set-hostname db01）

vim /etc/hosts
10.0.18.109    db02
10.0.18.108    db01
 
 
4. 配置yum源
mount -o loop Kylin-Server-V10-SP3-General-Release-2303-X86_64.iso /media

[root@localhost yum.repos.d]# vim  kylin.repo
[ks10-local-iso]
name = Kylin Linux Advanced Server 10 - local
baseurl = file:///media
gpgcheck = 1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-kylin
enabled = 1

--yum补充安装包
yum install -y gcc*  c++* 
yum install -y zlib-devel* lrzsz* 
yum install -y sysbench* openssh* 
yum install -y initscripts*  net-tools* 
yum install -y ncurses*  libtermcap-devel*
yum install -y cmake* make*
yum install -y zlib*  readline*
yum install -y tcl* openssl* 
yum install -y openldap*  gcc-c++*  
yum install -y openssl-devel*  pam*
yum install -y perl     
yum install -y python36     
yum -y install e2fsprogs-devel uuid uuid-devel libuuid-devel
yum -y install readline readline-devel zlib zlib-devel gettext gettext-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel uuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*

tar -zxvf rlwrap-0.37.tar.gz 
./configure
make
make install


5. 创建用户
groupadd -g 60001 pg12
useradd -u 60001 -g pg12 pg12
echo "Bdstar1234" | passwd --stdin pg12


6. 创建目录
mkdir -p  /4T/postgresql/pg12/{pgdata,archive,scripts,backup,pg12,soft,pgdir}
chown -R pg12:pg12 /4T/postgresql/pg12
chmod -R 775 /4T/postgresql/pg12

 
su - pg12
cd /data/soft
tar -zxvf postgresql-12.7.tar.gz
cd postgresql-12.7

7. 编译数据库以及相关编译（重点）
./configure --prefix=/4T/postgresql/pg12/pg12 --without-readline --with-uuid=e2fs   --with-openssl
 make -j 8 && make install

cd   contrib/uuid-ossp/
make && make install

cd   ../../contrib/sslinfo
make && make install

cd   ../../contrib/passwordcheck
make && make install

cd   ../../contrib/file_fdw
make && make install


8. 配置环境变量
cat >>  ~/.bash_profile <<"EOF"
export LANG=en_US.UTF-8
export PS1="[\u@\h \W]\$ "
export PGPORT=15433
export PGDATA=/4T/postgresql/pg12/pgdata
export PGHOME=/4T/postgresql/pg12/pg12
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

alias psql='rlwrap psql' 
EOF

source  ~/.bash_profile

9. 初始化
su - pgsql
initdb -D /4T/postgresql/pg12/pgdata -E UTF8 --locale=en_US.utf8 -U postgres  


10. 修改参数
cat >> /4T/postgresql/pg12/pgdata/postgresql.conf <<"EOF"

timezone = 'Asia/Shanghai'
listen_addresses = '*'
port=15433
unix_socket_directories='/4T/postgresql/pg12/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF

cat   > /4T/postgresql/pg12/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

11.启动检查
su - pg12
pg_ctl start
pg_ctl status
pg_ctl stop



二，主库启用openssl


1. 查看postgresql是否使用openssl选项编译安装，没有则需重新编译：
[pg12@db01 pgdata]$  pg_config|grep CONFIGURE
CONFIGURE = '--prefix=/4T/postgresql/pg12/pg12' '--without-readline' '--with-uuid=e2fs' '--with-openssl'


2. 查看ssl_library的参数值是OpenSSL

postgres=#  show ssl_library ;
 ssl_library 
-------------
 OpenSSL
(1 row)

postgres=# select version();
                                   version                                   
-----------------------------------------------------------------------------
 PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 row)


3.PostgreSQL配置单向SSL认证连接
mkdir /4T/postgresql/pg12/pgdata/openssl
openssl req -new -x509 -days 365 -nodes -text -subj '/CN=postgres' -out /4T/postgresql/pg12/pgdata/openssl/server.crt -keyout /4T/postgresql/pg12/pgdata/openssl/server.key
chmod 600 /4T/postgresql/pg12/pgdata/openssl/server.key

4. postgresql.conf配置文件添加
ssl = on
ssl_cert_file = '/4T/postgresql/pg12/pgdata/openssl/server.crt'
ssl_key_file = '/4T/postgresql/pg12/pgdata/openssl/server.key'

 pg_hba.conf配置文件添加
 host     all        all    all              md5
 hostssl  all        all    0.0.0.0/0        cert

5. 重启，连接的时候需要加上-h参数，否则不是以ssl连接的
[pg12@db01 pgdata]$ pg_ctl restart
[pg12@db01 pgdata]$ psql -h localhost -d postgres -U postgres -p 15433

Password for user postgres: 
psql (12.7)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.


postgres=# select ssl_is_used();


6.创建sslinfo
postgres=# create extension sslinfo;
CREATE EXTENSION

postgres=# select ssl_is_used();
 ssl_is_used 
-------------
 t


7.检查ssl 版本
postgres=# select ssl_version();
 ssl_version 
-------------
 TLSv1.3
(1 row)



三，配置主从复制（异步）

1. 主库配置
cat >> /4T/postgresql/pg12/pgdata/postgresql.conf <<"EOF"



wal_level='replica'
archive_mode='on'
archive_command='test ! -f /4T/postgresql/pg12/archive/%f && cp %p /4T/postgresql/pg12/archive/%f'
restore_command='cp /4T/postgresql/pg12/archive/%f %p'

max_wal_senders=10
wal_sender_timeout=60s

wal_keep_segments = 10000 

EOF

cat  << EOF > /4T/postgresql/pg12/pgdata/pg_hba.conf
# 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

2. 主库重启
pg_ctl restart

psql -U postgres -h 192.168.18.109 -p 15433
密码：
select * from pg_settings where name in ('wal_level','archive_mode','archive_command');

-- 切换归档
select pg_switch_wal();
select * from pg_settings where name in ('wal_level','archive_mode','archive_command');
select * from pg_stat_get_archiver();


3. 还原从库
pg_basebackup -h 192.168.18.109  -U postgres -l bk20240729 -F p -P -R -D /4T/postgresql/pg12/pgdir/bak
（说明，开启ssl备份数据库时，时间可能比较长）

4. 关闭从库，删除从库的数据文件，并且将备份文件覆盖从库的数据文件
rm -rf /4T/postgresql/pg12/pgdata/ *
mv  /4T/postgresql/pg12/pgdir/bak/ *   /4T/postgresql/pg12/pgdata/


5. 修改从库primary_conninfo参数
cat >> /4T/postgresql/pg12/pgdata/postgresql.conf <<"EOF"
primary_conninfo = 'host=192.168.18.109 port=15433 user=postgres pasword=Bdstar1234'
EOF


6. 启从库
pg_ctl stop 
pg_ctl start 

7. 查询复制状态
\x
--主库查看wal日志发送状态
select * from pg_stat_replication;
--从库查看wal日志接收状态
select * from pg_stat_wal_receiver;

-- 也可以查看这个，主库是f代表false ；备库是t，代表true
select pg_is_in_recovery();


pg_ctl stop 
pg_ctl start 


检查数据
\dt+
create table test1 as select * from bds_agreement;
select * from test1;



四，主从复制，切换

1.模拟主库出现问题

# 主库停止进程以及服务
pg_ctl stop -m fast

# 查看状态是否停止
pg_ctl status

2.从库提升为主库（自动删除standby.signal文件）
pg_ctl promote
 
# 查看状态
pg_ctl status

3. 修改原主库配置文件（从库）
cat >> /4T/postgresql/pg12/pgdata/postgresql.conf <<"EOF"
primary_conninfo = 'host=192.168.18.108 port=15433 user=postgres pasword=Bdstar1234'
EOF

添加文件
touch /4T/postgresql/pg12/pgdata/standby.signal


4. 修改原从库配置文件（主库）
vim /4T/postgresql/pg12/pgdata/postgresql.conf 
#primary_conninfo = 'host=192.168.18.109 port=15433 user=postgres pasword=Bdstar1234'


5. 查询复制状态
\x
--主库查看wal日志发送状态
select * from pg_stat_replication;
--从库查看wal日志接收状态
select * from pg_stat_wal_receiver;

=========================================================================
可能出现其他问题

同步出现的问题
db01，db02 启动同步数据
cat >> /4T/postgresql/pg12/pgdata/postgresql.conf <<"EOF"
synchronous_commit='on'
synchronous_standby_names='*' 
EOF

--如果不能同步，需要关闭同步参数再启动
--synchronous_commit='off'
--synchronous_standby_names=''