Ubantu编译安装Postgresql主从

环境准备

主机地址	操作系统	数据库版本	数据库角色
10.124.50.10	Ubuntu 20.04.3 LTS	Postgresql-13.12	master
10.124.50.11	Ubuntu 20.04.3 LTS	Postgresql-13.12	replica

依赖安装

sudo sed -i 's/archive.ubuntu.com/mirrors.aliyun.com/g' /etc/apt/sources.list

sudo apt-get update

sudo apt-get install -y gcc make libreadline-dev zlib1g-dev

编译安装postgres

sudo tar -zxvf postgresql-13.12.tar.gz

sudo cd /data/postgresql-13.12/

sudo ./configure --prefix=/data/pgsql-13.12

sudo make -j `nproc` && make install

sudo make world && make install -world

sudo mkdir -p /data/pgsql-13.12/{logs,data}

sudo adduser postgres sudo

sudo chown -R postgres:postgres /data/pgsql-13.12

数据库配置

配置环境变量

su - postgres

cat >> .profile << EOF
##postgres
PGPORT=5432
PGDATA=/data/pgsql-13.12/data
export PGPORT PGDATA
export LANG=zh_CN.utf8
export PGHOME=/data/pgsql-13.12
LD_LIBRARY_PATH=\$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:\$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=\$PGHOME/bin:\$PATH
export MANPATH=\$PGHOME/share/man:\$MANPATH
export PGUSER=postgres
export PGHOST=127.0.0.1
export PGDATABASE=postgres
PGLOG="\$PGHOME/logs"
EOF

配置主库

数据初始化

sudo /data/pgsql-13.12/bin/initdb --encoding=utf8 -D /data/pgsql-13.12/data

修改客户端认证配置/data/pgsql-13.12/data/pg_hba.conf

host    replication      replica         10.124.50.0/24          md5

host    all             all             10.124.50.0/24          trust

修改主配置/data/pgsql-13.12/data/postgresql.conf

logging_collector = on# Enable capturing of stderr and csvlog      # 这个参数为on时，pg数据库就开始记录日志了，但是默认为off

log_directory = 'pg_log'# directory where log files are written, # 该参数是配置日志的目录，可以是绝对目录，也可以是相对目录

log_filename = 'postgresql-%u.log'# log file name pattern, # 该参数是配置log的名字，%u表示星期几（取值1-7）

log_truncate_on_rotation = on

log_rotation_age = 1d

log_rotation_size = 20GB

log_min_messages = warning

log_min_duration_statement = 3000   # -1 is disabled, 0 logs all statements  # 这个参数非常重要，是排查慢查询的好工具，-1是关闭记录>这类日志

log_line_prefix = '%m %p %u %d %r %e'# special values:            # 日志输出格式（%m,%p实际意义配置文件中有解释）,可根据自己需要设置

listen_addresses = '*'

wal_level = replica

max_wal_senders= 4 #可以设置最多几个流复制链接，差不多有几个从，就设置多少

wal_keep_size = 1024

max_connections = 2048 #从库的 max_connections要大于主库

archive_mode = on #允许归档

archive_command = 'cp %p /data/pgsql-13.12/data/archive/%f'   #根据实际情况设置

wal_sender_timeout = 120s

hot_standby = on

hot_standby_feedback = on

checkpoint_timeout = 30min

max_wal_size = 64GB

min_wal_size = 4GB

wal_compression = on #开启wal日志压缩，会有额外的cpu开销

设置systemd托管服务

cat EOF > /etc/systemd/system/pgsql.service < EOF
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/data/pgsql-13.12/data/
OOMScoreAdjust=-1000
ExecStart=/data/pgsql-13.12/bin/pg_ctl start -D \${PGDATA} -s -o "-p \${PGPORT}" -w -t 300
ExecStop=/data/pgsql-13.12/bin/pg_ctl stop -D \${PGDATA} -s -m fast
ExecReload=/data/pgsql-13.12/bin/pg_ctl reload -D \${PGDATA} -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload

systemctl start pgsql.service

systemctl enable pgsql.service

配置主从同步

创建主从账号

su - postgres

psql

postgres=# CREATE ROLE replica login replication encrypted password 'replica';
postgres=# \q;

从库操作

pg_basebackup -U replica -h 10.124.50.10 -p 5432 -F p -X s -v -P -R -D /data/pgsql-13.12/data/

主库查看主从状态

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

各节点查看数据同步情况

pg_controldata