Daily *Nix

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Replication PGSQL ======

==== Building PGsQL ====
<code bash>
./configure --prefix=/usr/local/pg-9.0.3 --with-openssl --with-ossp-uuid --with-perl
</code>

NOTE : Use **//--with-pgport=5435//** if you want to hardcode port.

==== postgresql.conf ====
 
<code bash>
listen_addresses = '*'
port = 5435 #### Only Define if you running on non default port.
escape_string_warning = off
shared_buffers = 8192MB 
temp_buffers = 16MB
wal_level = hot_standby                
archive_mode = on   
archive_command = 'rsync -a /usr/local/pg-9.0.3/Archive/%p <IP>:Archive/%f'
max_wal_senders = 5    ####( this setting for  1 slave can connect with Master)        
wal_keep_segments = 32          
lc_messages = 'en_US.UTF-8'                    
lc_monetary = 'en_US.UTF-8'                   
lc_numeric = 'en_US.UTF-8'                     
lc_time = 'en_US.UTF-8'                       
default_text_search_config = 'pg_catalog.english'
ssl = on                                # (change requires restart)
ssl_ciphers = 'SSLv3:+HIGH:+MEDIUM:!SSLv2:!ADH:!LOW:!EXP:!aNULL:!eNULL:@STRENGTH'       # allowed SSL ciphers
</code>

Setup access rule.

<code bash | pg_hba.conf>
#Replication Trust
hostssl    replication     all             <IP>/32                 trust
</code>

Postgres restart on Master
<code bash>
bin/pg_ctl -p bin/postmaster -D data -l pgstartup.log start
bin/pg_ctl -p bin/postmaster -D data -l pgstartup.log stop
</code>

Sync current data over Rsync.
<code bash>
bin/psql -c "SELECT pg_start_backup('label', true)"
rsync -av data/ <IP>:standby/ --exclude postmaster.pid
bin/psql -c "SELECT pg_stop_backup()"
</code>

==== Recovery.conf ====
<code>
standby_mode = 'on'      # to enable the standby (read-only) mode.
primary_conninfo = 'host=<IP> port=5432 user=postgres'
# primary_conninfo = 'host=127.0.0.1 port=5436 user=postgres'
                         # to specify a connection info to the master node.
trigger_file = '/var/lib/pgsql/tmp/pg_failover_trigger'
                         # to specify a trigger file to recognize a fail over.
restore_command = 'cp /var/lib/pgsql/Archive/%f "%p"'
                         # to specify a recovery command.
archive_cleanup_command = '/usr/local/pg-9.0.3/bin/pg_archivecleanup -d /usr/local/pg-9.0.3/Archive %r 2>>pg_log/cleanup.log'
</code>


http://ketan.lithiumfox.com/doku.php?id=postgresql_replication_monitor
Daily *Nix

User Tools

Site Tools

Page Tools
