统信V20 1070e X86系统编译安装PostgreSQL-13.11版本以及主从构建

设备信息

资源包

该包包含postgresql-13.11源码包、统信编译postgresql-13.11安装包

通过网盘分享的文件：统信postgresql-13.11-x86.zip
链接: https://pan.baidu.com/s/1GiaHrvDZbPbbmJ1smoJPAA?pwd=4rki 提取码: 4rki

编译过程

[root@uos1 ~]# yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake

[root@uos1 ~]# wget https://ftp.postgresql.org/pub/source/v13.11/postgresql-13.11.tar.gz

[root@uos1 ~]# tar -xf postgresql-13.11.tar.gz 

[root@uos1 ~]# cd postgresql-13.11

[root@uos1 postgresql-13.11]# ./configure --prefix=/usr/local/postgresql-13.11

[root@uos1 postgresql-13.11]# make && make install 

[root@uos1 postgresql-13.11]# cd contrib 

[root@uos1 contrib]# make && make install 

[root@uos1 postgresql-13.11]# cp -r contrib/start-scripts/ /usr/local/postgresql-13.11/

[root@uos1 postgresql-13.11]# cd /usr/local

[root@uos1 local]# tar -czvf postgresql-13.11-x86.tgz postgresql-13.11/

安装过程

#安装过程
[root@uos1 ~]# tar -xf postgresql-13.11.tgz -C /usr/local/

[root@uos1 ~]# groupadd postgres && useradd -g postgres postgres

[root@uos1 ~]# mkdir /data/postgresql_data /data/pgdata_archives

[root@uos1 ~]# chown postgres:postgres /data/postgresql_data /data/pgdata_archives

[root@uos1 ~]# vim /etc/profile 或vim /home/postgres/.bash_profile
export PGHOME=/usr/local/postgresql-13.11/
export PGDATA=/data/postgresql_data
export PATH=$PATH:$HOME/bin:$PGHOME/bin

[root@uos1 ~]# source /etc/profile 或source /home/postgres/.bash_profile

[root@uos1 ~]# su - postgres 

[postgres@uos1 ~]$ initdb

[postgres@uos1 ~]$ vim /data/postgresql_data/postgresql.conf 
listen_addresses = '*'
port = 5432
max_connections = 2000
superuser_reserved_connections = 10
password_encryption = scram-sha-256
shared_buffers = 128MB
dynamic_shared_memory_type = posix
wal_level = replica
max_wal_size = 4096MB    #wal日志分段大小
min_wal_size = 80MB   
max_wal_senders = 10
wal_keep_size= 4096MB   #文件大小保留策略
#synchronous_standby_names = '*'
hot_standby = on
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_line_prefix = '%m [%p] '
log_timezone = 'PRC'
log_statement = 'all'    #记录所有sql语句日志
log_duration = on     #记录每条sql语句日志执行时间
log_min_duration_sample = 30000ms
log_statement_sample_rate = 0.2
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
archive_mode = on   #开启归档
archive_command = 'gzip < %p > /data/pgdata_archives/%f.gz'  #归档gzip格式，使wal_keep_size生效           
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

[postgres@uos1 ~]$ vim /data/postgresql_data/pg_hba.conf
# IPv4 local connections:
host    all             all             0.0.0.0/0               trust
host    all             all             127.0.0.1/32            trust
# replication privilege.
local   replication     all                                     trust
host    replication     repl            0.0.0.0/0               md5   #配置此用户的复制权限

[postgres@uos1 ~]$ exit
[root@uos1 ~]# chmod +x /usr/local/postgresql-13.11/start-scripts/linux && cp /usr/local/postgresql-13.11/start-scripts/linux /etc/init.d/postgresql-13.11
prefix=/usr/local/postgresql-13.11/
PGDATA="/data/postgresql_data"

[root@uos1 ~]# systemctl enable postgresql-13.11.service 或者 chkconfig --add postgresql-13.11
[root@uos1 ~]# systemctl start postgresql-13.11.service 或者 service postgresql-13.11 start

[root@uos1 ~]# ps -ef | grep postgres
postgres   12110       1  1 13:12 ?        00:00:00 /usr/lib/systemd/systemd --user
postgres   12111   12110  0 13:12 ?        00:00:00 (sd-pam)
postgres   12117   12110  0 13:12 ?        00:00:00 /usr/bin/deepin-service-manager
postgres   12123   12110  0 13:12 ?        00:00:00 /usr/bin/dbus-daemon --session --address=systemd: --nofork --nopidfile --systemd-activation --syslog-only
postgres   12126   12110  0 13:12 ?        00:00:00 /usr/bin/deepin-service-manager -g app
postgres   12231       1  5 13:12 ?        00:00:00 /usr/local/postgresql-13.11//bin/postmaster -D /data/postgresql_data
postgres   12233   12231  0 13:13 ?        00:00:00 postgres: logger 
postgres   12235   12231  0 13:13 ?        00:00:00 postgres: checkpointer 
postgres   12236   12231  0 13:13 ?        00:00:00 postgres: background writer 
postgres   12237   12231  0 13:13 ?        00:00:00 postgres: walwriter 
postgres   12238   12231  0 13:13 ?        00:00:00 postgres: autovacuum launcher 
postgres   12240   12231  0 13:13 ?        00:00:00 postgres: archiver 
postgres   12241   12231  0 13:13 ?        00:00:00 postgres: stats collector 
postgres   12242   12231  0 13:13 ?        00:00:00 postgres: logical replication launcher 
root       12244   10969  0 13:13 pts/0    00:00:00 grep --color=auto postgres
[root@uos1 ~]# ss -lntup | grep 5432
tcp   LISTEN 0      2048         0.0.0.0:5432       0.0.0.0:*    users:(("postmaster",pid=12231,fd=6)) 
tcp   LISTEN 0      2048            [::]:5432          [::]:*    users:(("postmaster",pid=12231,fd=7))    

#profile配置的全局的可以root身份下执行，否则需要su至postgres用户执行
[root@uos1 ~]# psql -Upostgres
ALTER USER postgres WITH PASSWORD 'admin@2020';
create user repl replication login encrypted password 'repl@2020';
create extension if not exists pg_stat_statements;  #创建pg_stat_statements扩展，若此前未编译安装contrib，则此处创建失败

主从复制构建

#从pg安装时在initdb后执行
[root@uos2 data]# rm -rf /data/postgresql_data/*

[root@uos2 data]# pg_basebackup -Xs -v -Fp -P -R -d "hostaddr=192.168.2.156 port=5432 user=repl password=repl@2020" -D /data/postgresql_data
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_13416"
24354/24354 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/2000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

[root@uos2 data]# cat postgresql_data/postgresql.auto.conf   #生成了auto.conf文件
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl password=''repl@2020'' channel_binding=disable hostaddr=192.168.2.156 port=5432 sslmode=disable sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

[root@uos2 ~]# chmod +x /usr/local/postgresql-13.11/start-scripts/linux && cp /usr/local/postgresql-13.11/start-scripts/linux /etc/init.d/postgresql-13.11

[root@uos2 ~]# systemctl enable postgresql-13.11
[root@uos2 ~]# systemctl start postgresql-13.11

[root@uos2 ~]# psql -Upostgres
postgres=# select pg_is_in_recovery();   #为true则表示为从
 pg_is_in_recovery 
-------------------
 t
(1 row)