postgres主备库搭建

postgres主备库搭建

安装

yum install postgresql11-server
yum install postgresql11-contrib
yum install psmisc

创建用户和用户组

userdel postgres
groupadd -g 701 postgres
useradd -g 701 -m -s /bin/bash -u 701 postgres

initdb

su - postgres
initdb
pg_ctl start

基本配置postgresql.conf

wal_log_hints = on   
unix_socket_directories = '/tmp'
max_wal_size = 1GB
min_wal_size = 800M  
listen_addresses = '*' 
logging_collector = 改为on


max_connections  连接数
port  端口
shared_buffers = 128MB 内存

#下面几个是默认的，不改也行，但是比较重要
#wal_level = replica
#hot_standby = on
#max_wal_senders = 10

配置pg_hba.conf

```bash
local   all             all                                     peer
host    all             all             0/0                     md5
local   replication     all                                     peer
host    replication     all             0/0                     md5

修改密码

ALTER USER postgres WITH PASSWORD 'postgres';

搭备库

pg_basebackup -h10.*.*.*  -U postgres -P -R -D $PGDATA

# 因为要写一个自动搭建备库的程序，这个地方需要密码所以可以直接写密码
pg_basebackup -d"host=10.*.*.* port=5432 dbname=template1 password=postgres" -U postgres-P  -R -D $PGDATA

修改recovery.conf

standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'application_name=stb27 user=postgres host=10.*.*.* port=5432 password=postgres sslmode=disable sslcompression=1'

启动数据库

pg_ctl start