1. master의 데이터 복제/백업
[slave] mv /var/lib/pgsql/13/data /var/lib/pgsql/13/data.org
[slave] mkdir -p /var/lib/pgsql/13/data/
[slave] chown -R postgres:postgres /var/lib/pgsql/13/data
[slave] chmod -R 0700 /var/lib/pgsql/13/data[slave] su- postgres
-bash-4.2$ export LANG=C
-bash-4.2$ /usr/pgsql-13/bin/pg_basebackup -h {master ip} -D /var/lib/pgsql/13/data -U repl -v -P -R -X stream -C -S pgstandby1- /usr/pgsql-13/bin/pg_basebackup -h {master ip} -D /var/lib/pgsql/13/data -U repl -v -P -R -X stream -C -S pgstandby1 위의 명령어는 pg_basebackup 도구를 사용하여 데이터베이스의 베이스 백업을 생성하는 명령어이다.
[all_server] systemctl restart postgresql-13
[all_server] psql -U postgres -p 5432
[all_server] postgres=# select pg_is_in_recovery();를 수행해보면 master는 f 로 slave는 t 로 나온다.
listen_addresses = '*'
# Host name or IP address to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
port = 9999
# Port number
# (change requires restart)
socket_dir = '/var/run/postgresql'
backend_hostname0 = {master ip}
# Host name or IP address to connect to for backend 0
backend_port0 = 5432
# Port number for backend 0
backend_weight0 = 1
# Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/var/lib/pgsql/13/data'
# Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
# Controls various backend behavior
# ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
# or ALWAYS_PRIMARY
backend_application_name0 = 'master'
# walsender's application_name, used for "show pool_nodes" command
backend_hostname1 = {slave01 ip}
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/13/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'slave01'
backend_hostname2 = {slave02 ip}
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/13/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'slave02'
health_check_period = 5
# Health check period
# Disabled (0) by default
health_check_timeout = 30
# Health check timeout
# 0 means no timeout
health_check_user = 'pgpool'
# Health check user
health_check_password = ''
# Password for health check user
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
health_check_database = ''
# Database name for health check. If '', tries 'postgres' frist,
health_check_max_retries = 3
master_slave_mode = on
master_slave_sub_mode = 'stream'
failover_command = '/data/pgpool/script/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/data/pgpool/script/follow_primary.sh %d %h %p %D %m %H %P %r %R'
ecovery_user = 'postgres'
# Online recovery user
recovery_password = ''
# Online recovery password
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
recovery_1st_stage_command = 'recovery_1st_stage'
# Executes a command in first stage
recovery_2nd_stage_command = 'recovery_1st_stage'
use_watchdog = on
enable_pool_hba = on
delegate_IP = {VIP}
# delegate IP address
# If this is empty, virtual IP never bring up.
# (change requires restart)
if_cmd_path = '/sbin'
# path to the directory where if_up/down_cmd exists
# If if_up/down_cmd starts with "/", if_cmd_path will be ignored.
# (change requires restart)
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens33 label ens33:0'
# startup delegate IP command
# (change requires restart)
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens33'
# shutdown delegate IP command
# (change requires restart)
arping_path = '/usr/sbin'
# arping command path
# If arping_cmd starts with "/", if_cmd_path will be ignored.
# (change requires restart)
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens33'
# arping command
# (change requires restart)
# - Watchdog communication Settings -
hostname0 = {master ip}
# Host name or IP address of pgpool node
# for watchdog connection
# (change requires restart)
wd_port0 = 9000
# Port number for watchdog service
# (change requires restart)
pgpool_port0 = 9999
# Port number for pgpool
# (change requires restart)
hostname1 = {slave01 ip}
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = {slave02 ip}
wd_port2 = 9000
pgpool_port2 = 9999
wd_lifecheck_method = 'heartbeat'
# Method of watchdog lifecheck ('heartbeat' or 'query' or 'external')
# (change requires restart)
wd_interval = 10
# lifecheck interval (sec) > 0
# (change requires restart)
wd_heartbeat_keepalive = 2
# Interval time of sending heartbeat signal (sec)
# (change requires restart)
wd_heartbeat_deadtime = 30
# Deadtime interval for heartbeat signal (sec)
# (change requires restart)
wd_escalation_command = '/data/pgpool/script/escalation.sh'
# Executes this command at escalation on new active pgpool.
# (change requires restart)
# -- heartbeat mode --
heartbeat_hostname0 = {master ip}
# Host name or IP address used
# for sending heartbeat signal.
# (change requires restart)
heartbeat_port0 = 9694
# Port number used for receiving/sending heartbeat signal
# Usually this is the same as heartbeat_portX.
# (change requires restart)
heartbeat_device0 = ''
# Name of NIC device (such like 'eth0')
# used for sending/receiving heartbeat
# signal to/from destination 0.
# This works only when this is not empty
# and pgpool has root privilege.
# (change requires restart)
heartbeat_hostname1 = {slave01 ip}
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = {slave02 ip}
heartbeat_port2 = 9694
heartbeat_device2 = ''# - Where to log -
log_destination = 'stderr'
# Where to log
# Valid values are combinations of stderr,
# and syslog. Default to stderr.
# This is used when logging to stderr:
logging_collector = on
# Enable capturing of stderr
# into log files.
# (change requires restart)
# -- Only used if logging_collector is on ---
log_directory = '/var/log/pgpool_log'
# directory where log files are written,
# can be absolute
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
# log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600
# creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on
# If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d
# Automatic rotation of logfiles will
# happen after that (minutes)time.
# 0 disables time based rotation.
log_rotation_size = 10MB
# Automatic rotation of logfiles will
# happen after that much (KB) log output.
# 0 disables size based rotation.