设备信息
| 操作系统版本 | 架构 | CPU | 内存 | 备注 |
| 统信UOS V20 1070e | X86 | 4C | 8G | 此配置仅做编译安装验证，持续运行或数据量增长大请自行评估资源配置。 |
| 统信UOS V20 1070e | X86 | 4C | 8G |
资源包
该包包含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)
