Database Replication¶

We plan two run two CDB machines:

• one on MICENET, this will be the primary.
• one on the PPD network (the current machine), this will be the standby.

The primary will give read write access to the CDB. Access to this machine will only be allowed from other machines on MICENET.

The standby will be set up as a hot standby using streaming replication, see http://developer.postgresql.org/pgdocs/postgres/hot-standby.html and http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial. It will only provide read only access, which will be via a web service running on micewww.

http://wiki.postgresql.org/wiki/Streaming_Replication

Primary Server¶

• set up connections and authentication so that the standby server can successfully connect to the replication pseudo-database on the primary
  

  emacs /var/lib/pgsql/data/postgresql.conf -nw
      listen_addresses = 'localhost, ip.of.standby'
  
  emacs /var/lib/pgsql/data/pg_hba.conf -nw
  # The standby server must have superuser access privileges.
      host  replication  postgres  ip.of.standby/22  trust
  

• set up the streaming replication related parameters on the primary server
  

  emacs /var/lib/pgsql/data/postgresql.conf -nw
      max_wal_senders = 5
      wal_keep_segments = 32
  

• start postgres
  

  service pgsql-cdb start
  

• make a base backup by copying the primary server's data directory to the standby server
  

  psql -c "SELECT pg_start_backup('label', true)" 
  rsync -a ${PGDATA}/ standby:/srv/pgsql/standby/ --exclude postmaster.pid
  psql -c "SELECT pg_stop_backup()"

• check if the wal sender is streaming:
  

  [root@configdb ~]# ps aux | grep "sender process postgres" 
  postgres  4521  0.0  0.0 158676  2572 ?        Ss   11:25   0:00 postgres: wal sender process postgres 130.246.47.69(34451) streaming 0/8B000078
  postgres  6816  0.0  0.0 158692  2712 ?        Ss   17:01   0:00 postgres: wal sender process postgres 130.246.47.72(54022) streaming 0/8B000078
  

Standby Server¶

• Install postgreSQL

• set up connections and authentication so that the standby server can successfully connect to the replication pseudo-database on the primary
  

  emacs /var/lib/pgsql/data/postgresql.conf -nw
      listen_addresses = 'localhost, ip.of.primary, ip.of.public'
  
  emacs /var/lib/pgsql/data/pg_hba.conf -nw
  # The standby server must have superuser access privileges.
      host  replication  postgres  ip.of.primary/22  trust
  

• set up the streaming replication related parameters on the standby server
  

  emacs /var/lib/pgsql/data/postgresql.conf -nw
      max_wal_senders = 5
      wal_keep_segments = 32
      hot_standby = on
  

• create a recovery command file in the standby server; the following parameters are required for streaming replication
  

  cat > /var/lib/pgsql/data/recovery.conf
  # Specifies whether to start the server as a standby. In streaming replication,
  # this parameter must to be set to on.
  standby_mode          = 'on'
  
  # Specifies a connection string which is used for the standby server to connect
  # with the primary.
  primary_conninfo      = 'host=ip.of.primary port=5432 user=postgres'
  
  # Specifies a trigger file whose presence should cause streaming replication to
  # end (i.e., failover).
  trigger_file = '~/data/failover'
  
  # Specifies a command to load archive segments from the WAL archive. If
  # wal_keep_segments is a high enough number to retain the WAL segments
  # required for the standby server, this may not be necessary. But
  # a large workload can cause segments to be recycled before the standby
  # is fully synchronized, requiring you to start again from a new base backup.
  restore_command = 'cp ~/data/pg_xlog/archive/%f "%p"'
  EOF
  

• check the permissions of /data:
  

  chmod 700 /var/lib/pgsql/data
  

• hot standby mode:
  

  > emacs -nw /var/lib/pgsql/data/postgresql.conf
  hot_standby = on   
  

• start postgres
  

  service pgsql-cdb start
  

• test the slave:
  

  psql -U mice -W cdb
  
  cdb=> select * from alarm;
  cdb==> \q