r2.sh

application/x-shellscript

Download

#!/bin/sh -eux

# Goto script's directory
cd "$(dirname "$0")"
# Stop master if exists
test -f master/postmaster.pid && pg_ctl -w -D master stop -m fast || echo $?
# Remove master directory
rm -rf master
# Create master directory
mkdir -p master
# Initialize master, data checksums are mandatory
pg_ctl initdb -D master -o "--data-checksums -N -A trust --wal-segsize 1"
# Debug to see recycling WAL, keep only 5 WAL files (because segsize 1MB)
cat >>master/postgresql.conf <<EOF
hot_standby = on
logging_collector = on
log_min_messages = debug
#synchronous_standby_names = postgres
wal_keep_size = 5MB
EOF
# Accept replication connections on master
cat >> master/pg_hba.conf <<EOF
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
EOF
# Start master
pg_ctl -w -D master start -o "-p 5000"
# Stop standby1 if exists
test -f standby1/postmaster.pid && pg_ctl -w -D standby1 stop -m fast || echo $?
# Remove standby1 directory
rm -rf standby1
# Base backup is taken with xlog files included
pg_basebackup -D standby1 -p 5000 -X fetch --verbose --write-recovery-conf
# Start standby1
pg_ctl -w -D standby1 start -o "-p 5001"
# Stop standby2 if exists
test -f standby2/postmaster.pid && pg_ctl -w -D standby2 stop -m fast || echo $?
# Remove standby2 directory
rm -rf standby2
# Base backup is taken with xlog files included
pg_basebackup -D standby2 -p 5000 -X fetch --verbose --write-recovery-conf
# Start standby2
pg_ctl -w -D standby2 start -o "-p 5002"
# Create table and insert lot of rows
psql -d postgres -a --no-psqlrc -p 5000 <<EOF
create table a (a int, b int);
insert into a select i, i from generate_series(1, 100000) i;
EOF
# Stop master to recycle WAL files
pg_ctl -w -D master stop -m fast
# Stop standby1 to recycle WAL files
pg_ctl -w -D standby1 stop -m fast
# Stop standby2 to recycle WAL files
pg_ctl -w -D standby2 stop -m fast
# Start master
pg_ctl -w -D master start -o "-p 5000"
# Start standby1
pg_ctl -w -D standby1 start -o "-p 5001"
# Start standby2
pg_ctl -w -D standby2 start -o "-p 5002"
# Find latest master WAL file
LAST_MASTER_WAL_FILE="$(pg_controldata master | grep "Latest checkpoint's REDO WAL file:" | cut -d : -f 2 | grep -oP "\w+")"
# Now promote standby1
pg_ctl -w -D standby1 promote
# Wait until standby1 is promoted
while ! pg_isready -p 5001; do sleep 1; done
# Find latest standby1 WAL file
LAST_STANDBY_WAL_FILE="$(pg_controldata standby1 | grep "Latest checkpoint's REDO WAL file:" | cut -d : -f 2 | grep -oP "\w+")"
# Print last timeline history
cat "$(find standby1/pg_wal -name "*.history" -type f -print0 | xargs -r -0 ls -1 -t | head -1)"
# Print control information of master
pg_controldata master
# Print control information of standby1
pg_controldata standby1
# Print control information of standby2
pg_controldata standby2
# Print last 10 lines from dumped master WAL file
pg_waldump -p master "$LAST_MASTER_WAL_FILE" | tail -n 10
# Print last 10 lines from dumped standby1 WAL files
pg_waldump -p standby1 "$LAST_MASTER_WAL_FILE" | tail -n 10
pg_waldump -p standby1 "$LAST_STANDBY_WAL_FILE" | tail -n 10
# Print last 10 lines from dumped standby2 WAL file
pg_waldump -p standby2 "$LAST_MASTER_WAL_FILE" | tail -n 10
# Insert some rows
psql -d postgres -a --no-psqlrc -p 5000 <<EOF
--set synchronous_standby_names = 'postgres';
--select pg_reload_conf();
insert into a select i, i from generate_series(1, 340) i;
--commit;
EOF
# Standby2 node need to be stopped as well.
pg_ctl -w -D standby2 stop -m fast
# Stop the master after standby promotion
pg_ctl -w -D master stop -m fast
# Standby1 node need to be stopped as well.
pg_ctl -w -D standby1 stop -m fast
# Print control information of master
pg_controldata master
# Print control information of standby1
pg_controldata standby1
# Print control information of standby2
pg_controldata standby2
# Print last 10 lines from dumped master WAL file
pg_waldump -p master "$LAST_MASTER_WAL_FILE" | tail -n 10
# Print last 10 lines from dumped standby1 WAL files
pg_waldump -p standby1 "$LAST_MASTER_WAL_FILE" | tail -n 10
pg_waldump -p standby1 "$LAST_STANDBY_WAL_FILE" | tail -n 10
# Print last 10 lines from dumped standby2 WAL file
pg_waldump -p standby2 "$LAST_MASTER_WAL_FILE" | tail -n 10
# Do rewind using standby1 pgdata as source
pg_rewind --progress --debug --source-pgdata=standby1 --target-pgdata=standby2
# Parameters for standby2 postgresql.conf
cat >> standby2/postgresql.conf <<EOF
primary_conninfo = 'port=5001'
EOF
# Place standby2 signal file
touch standby2/standby.signal
# Start standby1
pg_ctl -w -D standby1 start -o "-p 5001"
# Start standby2
pg_ctl -w -D standby2 start -o "-p 5002"
# Same query
psql -d postgres -a --no-psqlrc -p 5001 <<EOF
select count(*) from a;
EOF
# Different results
psql -d postgres -a --no-psqlrc -p 5002 <<EOF
select count(*) from a;
EOF
# Stop standby1
pg_ctl -w -D standby1 stop -m fast
# Stop standby2
pg_ctl -w -D standby2 stop -m fast