PostgreSQL Streaming replication config files example

Server 1 - master (111.111.111.22)

postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

listen_addresses = '*'
max_connections = 3000

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

shared_buffers = 40000MB
max_prepared_transactions = 2000
work_mem = 360MB
maintenance_work_mem = 1024MB

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

synchronous_commit = off
checkpoint_segments = 20

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------                                               

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'

#------------------------------------------------------------------------------                                               
# QUERY TUNING                                                                                                                
#------------------------------------------------------------------------------                                               

join_collapse_limit = 100                                                                                                     
seq_page_cost = 0.0000001                                                                                                     
random_page_cost = 2.0
cpu_operator_cost = 0.0000001
constraint_exclusion = on

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

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 PARAMETERS
#------------------------------------------------------------------------------

autovacuum = off

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

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'

#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------

max_locks_per_transaction = 250
max_pred_locks_per_transaction = 250

pg_hba.conf

local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         0.0.0.0/0             password
host    replication replicator  111.111.111.29/32        md5

Server 2 - slave (111.111.111.29)

postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

listen_addresses = '*'
max_connections = 3000

#------------------------------------------------------------------------------                                               
# RESOURCE USAGE (except WAL)                                                                                                 
#------------------------------------------------------------------------------                                               

shared_buffers = 10000MB                                                                                                      
max_prepared_transactions = 2000                                                                                              
work_mem = 100MB                                                                                                              
maintenance_work_mem = 1024MB                                                                                                 

#------------------------------------------------------------------------------                                               
# WRITE AHEAD LOG                                                                                                             
#------------------------------------------------------------------------------                                               

synchronous_commit = off                                                                                                      
checkpoint_segments = 20                                                                                                      

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------

hot_standby = on
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'

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

join_collapse_limit = 100
seq_page_cost = 0.0000001
random_page_cost = 2.0
cpu_operator_cost = 0.0000001
constraint_exclusion = on

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

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
log_checkpoints = on

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = off

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

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'

#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------

max_locks_per_transaction = 250
max_pred_locks_per_transaction = 250