[PostgreSQL] PostgreSQL standby recovery script

// postgreSQL 이중화 도중 다운된 노드 recovery script (docker에서 사용 시)

 

// get_primary_ip.sh (현재 동작하고 있는 primary의 ip를 가져오는 스크립트)

# get_primary_ip.sh sudo -u postgres psql -p 54321 -t -A -F ' ' -n -c 'show pool_nodes;' | grep primary | awk '{print $2}'

 

// create_recovery.sh (recovery.conf 를 찍어낼 스크립트)

# create_recovery.sh HOST_ADDR=`sh /usr/pgsql-10/share/get_primary_ip.sh` echo -e "standby_mode=on primary_conninfo='host=$HOST_ADDR port=5432 user=replication password=replication' primary_slot_name='repl_slot_01' trigger_file='/var/lib/postgresql/data/failover_trigger'"

 

// recover_standby.sh (recover를 해줄 스크립트)

// expect 설치 되어있어야함.

# recover_standby.sh NODE_NUM=`sudo -u postgres psql -p 54321 -t -A -F ' ' -n -c 'show pool_nodes;' | grep standby | awk '{print $1+1}'` PGDATA=/var/lib/docker/volumes/pgdata$NODE_NUM/_data PRIMARY_IP=`sh /usr/pgsql-10/share/get_primary_ip.sh` # replication 비밀번호 입력 REPL_PASSWD=replication # postgres계정 (pcp) 비밀번호 입력  PCP_PASSWD=postgres # commands before recover docker stop pg_node$NODE_NUM rm -rf $PGDATA/* # recover standby expect <<EOF spawn pg_basebackup -D $PGDATA -h $PRIMARY_IP -U replication -P -v -X stream expect "Password: "         send "$REPL_PASSWD\r" expect eof EOF wait rm -rf $PGDATA/recovery.done sh /usr/pgsql-10/share/create_recovery.sh >> $PGDATA/recovery.conf chown -R polkitd:input $PGDATA # standby up docker start pg_node$NODE_NUM # standby ssh start docker exec pg_node$NODE_NUM sh -c "service ssh start" # pgnode attach to pgpool expect <<EOF spawn sudo -u postgres pcp_attach_node -n $((NODE_NUM-1)) expect "Password: "         send "$PCP_PASSWD\r" expect eof EOF # finished recovery wait echo "Finished Recovery";