PostgreSQL 9.1 Streaming Replication Example

=======================================
2015.12.08Step by step streaming replication instructions
100.100.100.22 - master(p03)
100.100.100.13 - slave(p03rep)
user replicator password somepassword
CREATE ROLE replicator LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;
PostgreSQL version = 9.1
=======================================

ssh postgres@100.100.100.22

# Check replication connection settings
grep "100.100.100.13" /var/lib/pgsql/data/pg_hba.conf
host replication replicator 100.100.100.13/32 md5
# GOOD!!!

# Check database replication settings
grep -E "(max_wal_senders|wal_level|wal_keep_segments|archive_mode|archive_command)" postgresql.conf
max_wal_senders = 5
wal_level = hot_standby
wal_keep_segments = 100
archive_mode = on
archive_command = 'test ! -f /u04/pgarch/%f && cp %p /u04/pgarch/%f'
# GOOD!!!

ssh root@100.100.100.13

service postgresql stop

ssh postgres@100.100.100.13

cd /u02/pgwork/pgbb
rm -rfv p03.pgbb
pg_basebackup -h 100.100.100.22 -U replicator -W -D p03.pgbb -v -Ft -x -z -P
rm -rfv /var/lib/pgsql/data/*
rm -rfv /u02/pgdata/oblbase/*
rm -rfv /u03/pgdata/oblbase_ix/*                                                                                                
tar -C /var/lib/pgsql/data -xvzf p03.pgbb/base.tar.gz                                                                           
rm -rfv /var/lib/pgsql/data/pg_log/*                                                                                            
tar -C /var/lib/pgsql/data/pg_tblspc/16475 -xvzf p03.pgbb/16475.tar.gz                                                          
tar -C /var/lib/pgsql/data/pg_tblspc/16476 -xvzf p03.pgbb/16476.tar.gz                                                          
chmod 0700 /var/lib/pgsql/data                                                                                                  
mv -v /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf.workonproduction                                  
cat << EOF > /var/lib/pgsql/data/postgresql.conf                                                                                
listen_addresses = '*'                                                                                                          
max_connections = 3000                                                                                                          
shared_buffers = 500MB #40000MB                                                                                                 
max_prepared_transactions = 2000
work_mem = 10MB #360MB
maintenance_work_mem = 102MB #1024MB
synchronous_commit = off
checkpoint_segments = 8 #20
join_collapse_limit = 100
seq_page_cost = 0.0000001
random_page_cost = 2.0
cpu_operator_cost = 0.0000001
constraint_exclusion = on
log_destination = 'stderr'
logging_collector = on
log_line_prefix = '%d %u %h %a %s %t %p '
log_min_duration_statement = 15000
log_statement = ddl
log_hostname = on
log_temp_files = 0
log_lock_waits = on
autovacuum = off
datestyle = 'iso, dmy'
lc_messages = 'ru_RU.UTF-8'
lc_monetary = 'ru_RU.UTF-8'
lc_numeric = 'ru_RU.UTF-8'
lc_time = 'ru_RU.UTF-8'
default_text_search_config = 'pg_catalog.russian'
max_locks_per_transaction = 250
max_pred_locks_per_transaction = 250
max_wal_senders = 5
wal_level = hot_standby
hot_standby = on
wal_keep_segments = 100
archive_mode = on
archive_command = 'test ! -f /u04/pgarch/%f && cp %p /u04/pgarch/%f'
max_standby_archive_delay = -1
max_standby_streaming_delay = -1
EOF
chmod 600 /var/lib/pgsql/data/postgresql.conf
cat << EOF > /var/lib/pgsql/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=100.100.100.22 port=5432 user=replicator password=somepassword'
restore_command = 'cp /u04/pgarch/%f %p'
archive_cleanup_command = 'pg_archivecleanup /u04/pgarch %r'
EOF
chmod 600 /var/lib/pgsql/data/recovery.conf

ssh root@100.100.100.13

service postgresql start

=======================================

Monitoring of replication state on master:
select * from pg_stat_replication;
Читать далее