Site Tools

You are here: Start » PostgreSQL/Postgres » PostgreSQL replication 9.3+

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		Previous revision
postgresql:replication [2015-10-22] – created dcaipostgresql:replication [2020-04-19] (current) – external edit 127.0.0.1
Line 1: Line 1:
 +====== PostgreSQL replication 9.3+ ======
 +
 +=== On master node ===
 +Install postgresql-9.3-pgpool2
 +<file bash create_role.bash>
 +sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'replicator';"
 +</file>
 +
 +<code>
 +create extension pgpool_regclass;
 +CREATE EXTENSION pgpool_recovery;
 +\df 
 + </code>
 +
 +<file - postgresql.conf>
 +listen_address = "*"
 +wal_level = hot_standby
 +max_wal_senders = 3
 +checkpoint_segments = 8    
 +wal_keep_segments = 8 
 +</file>
 +
 +
 +<file - pg_hba.conf>
 +hostssl replication     replicator      192.168.3.1/8           md5
 +# line below is for pgpool2
 +host all all 192.168.3.1/8 trust
 +</file>
 +=== On slave node ===
 +
 +
 +<file>
 +wal_level = hot_standby
 +max_wal_senders = 3
 +checkpoint_segments = 8    
 +wal_keep_segments = 8 
 +hot_standby = on
 +</file>
 +
 +
 +<file bash>
 +sudo service postgresql stop
 +sudo -u postgres rm -rf /var/lib/postgresql/9.3/main
 +sudo -u postgres pg_basebackup -h 192.168.3.11 -D /var/lib/postgresql/9.3/main -U replicator -v -P
 +## Writing recovery.conf file
 +sudo -u postgres bash -c "cat > /var/lib/postgresql/9.3/main/recovery.conf <<- _EOF1_
 +  standby_mode = 'on'
 +  primary_conninfo = 'host=192.168.3.11 port=5432 user=replicator password=replicator sslmode=require'
 +  trigger_file = '/tmp/postgresql.trigger'
 +_EOF1_
 +"
 +sudo service postgresql start
 +</file>
 +
 +
 +=== pgpool II ===
 +install pgpool2 package
  

Page Tools

User Tools