test.sh

application/octet-stream

Download

#!/bin/bash

port_primary=5432
port_secondary=5433
port_subscriber=5434

echo '=========='
echo '=Clean up='
echo '=========='

pg_ctl stop -D data_primary
pg_ctl stop -D data_secondary
pg_ctl stop -D data_subscriber

rm -rf data_* *log
rm -rf /home/houzj/archivedir/*

echo '======================='
echo '=Set up primary server='
echo '======================='

initdb -D data_primary

cat << EOF >> data_primary/postgresql.conf
wal_level = logical 
port = $port_primary
#standby_slot_names = 'physical'
#log_replication_commands = 'on'
#max_slot_wal_keep_size = 64kB

max_wal_senders=550
max_worker_processes=1000 
max_replication_slots=550 
log_replication_commands = 'on' 
checkpoint_timeout = 1d 
shared_buffers = 6GB 
max_worker_processes = 32 
max_parallel_maintenance_workers = 24 
max_parallel_workers = 32 
synchronous_commit = off 
checkpoint_timeout = 1d 
max_wal_size = 24GB 
min_wal_size = 15GB 
autovacuum = off

#log_min_messages = 'debug2'

#archive_mode = on
#archive_command = 'cp %p /home/houzj/archivedir/%f'
#restore_command = 'cp /home/houzj/archivedir/%f %p'

EOF

cat << EOF >> data_primary/pg_hba.conf
host replication houzj 0.0.0.0/0 trust
EOF


pg_ctl -D data_primary start -w 

pgbench -i -s 100 -p $port_primary postgres

echo '========================='
echo '=Set up secondary server='
echo '========================='

pg_basebackup -D data_secondary -p $port_primary

cat << EOF >> data_secondary/postgresql.conf
port = $port_secondary
primary_conninfo = 'port=$port_primary application_name=secondary dbname=postgres user=houzj'
#primary_conninfo = 'port=$port_primary application_name=secondary dbname=postgreis'
primary_slot_name = 'physical'
#hot_standby = off
hot_standby_feedback = on
#enable_syncslot = on
sync_replication_slots = on
standby_slot_names = ''
EOF

cat << EOF >> data_secondary/standby.signal
EOF


psql -d postgres -p $port_primary -c "SELECT * FROM pg_create_physical_replication_slot('physical', true, false);"

pg_ctl -D data_secondary start -w -l secondary.log 


psql -d postgres -p $port_primary -c "CREATE PUBLICATION pub FOR ALL TABLES"


echo '==================='
echo '=Set up subscirber='
echo '==================='

initdb -D data_subscriber

cat << EOF >> data_subscriber/postgresql.conf
port = $port_subscriber
wal_level = logical


checkpoint_timeout = 1h
shared_buffers = '8GB'
wal_buffers = '1GB'
max_connections = '5000'
max_wal_size = 20GB
min_wal_size = 10GB
max_wal_senders = 100
max_replication_slots = 101
EOF

pg_ctl start -D data_subscriber

#psql -d postgres -p $port_subscriber -c "CREATE TABLE tbl (id int primary key);"

pgbench -i -Idtpf -p $port_subscriber postgres

psql -d postgres -p $port_secondary -c "SELECT * FROM pg_replication_slots;"
psql -d postgres -p $port_subscriber -c "CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres port=$port_primary' PUBLICATION pub WITH (copy_data = on, failover = true)"

exit