Set up a database server: Difference between revisions

From DISI
Jump to navigation Jump to search
(Created page with "This page describes how to set up a database server. We will set up a psql server for ZINC and Drupal and a MySQL server for DOCK Blaster / SEA. Category:Sysadmin [[Ca...")
 
(Adding replication instructions)
 
Line 2: Line 2:


We will set up a psql server for ZINC and Drupal and a MySQL server for DOCK Blaster / SEA.
We will set up a psql server for ZINC and Drupal and a MySQL server for DOCK Blaster / SEA.
= Replication =
Note: Trigger files and restore logs could (should) be on a shared file system
    VERSION=9.2
    MASTER=10.1.2.3
    SLAVE=10.1.2.4
    USER=replication
    PASS=
    BASE=/var/lib/pgsql/$VERSION
    ARCHIVE=/srv/archive/pgsql/$VERSION
    REPLOG=$ARCHIVE/wal/
== Command Order ==
=== 1) On Master ===
    pgsql "SELECT pg_start_backup('copy-to-$SLAVE', true);"
    rsync -av --exclude postmaster.pid --exclude postgresql.conf $BASE root@$SLAVE:$BASE
    rsync -av $REPLOG $SLAVE:$REPLOG
=== 2) On Slave ===
Make sure $BASE/recovery.config exists (below)
    service postgresql-$VERSION start
=== 3) On Master ===
    pgsql -c "SELECT pg_stop_backup();"
    pgsql -c "SELECT pg_current_xlog_location();"
=== 4) On Slave ===
    pgsql -c "SELECT pg_last_xlog_receive_location();"
    pgsql -c "SELECT pg_last_xlog_replay_location();"
== Configuration File Requirements ==
=== $MASTER:$BASE/postgresql.conf ===
    max_wal_senders = 5
    wal_keep_segments = 32
    archive_mode = on
    archive_command = 'cp %p $REPLOG/%f'
    wal_level = hot_standby
=== $SLAVE:$BASE/postgresql.conf ===
    wal_level = hot_standby
    max_wal_senders = 5
    wal_keep_segments = 32
    archive_mode = off
    hot_standby = on
=== $SLAVE:$BASE/recover.conf ===
    standby_mode          = 'on'
    primary_conninfo      = 'host=$MASTER port=5432 user=$USER password=$PASS'
    trigger_file          = '$BASE.trigger'
    restore_command      = 'cp $REPLOG/%f %p'




[[Category:Sysadmin]]
[[Category:Sysadmin]]
[[Category:Databases]]
[[Category:Databases]]

Latest revision as of 22:03, 9 March 2016

This page describes how to set up a database server.

We will set up a psql server for ZINC and Drupal and a MySQL server for DOCK Blaster / SEA.


Replication

Note: Trigger files and restore logs could (should) be on a shared file system

   VERSION=9.2
   MASTER=10.1.2.3
   SLAVE=10.1.2.4
   USER=replication
   PASS=
   BASE=/var/lib/pgsql/$VERSION
   ARCHIVE=/srv/archive/pgsql/$VERSION
   REPLOG=$ARCHIVE/wal/

Command Order

1) On Master

   pgsql "SELECT pg_start_backup('copy-to-$SLAVE', true);"
   rsync -av --exclude postmaster.pid --exclude postgresql.conf $BASE root@$SLAVE:$BASE
   rsync -av $REPLOG $SLAVE:$REPLOG

2) On Slave

Make sure $BASE/recovery.config exists (below)

   service postgresql-$VERSION start

3) On Master

   pgsql -c "SELECT pg_stop_backup();"
   pgsql -c "SELECT pg_current_xlog_location();"

4) On Slave

   pgsql -c "SELECT pg_last_xlog_receive_location();"
   pgsql -c "SELECT pg_last_xlog_replay_location();"

Configuration File Requirements

$MASTER:$BASE/postgresql.conf

   max_wal_senders = 5
   wal_keep_segments = 32
   archive_mode = on
   archive_command = 'cp %p $REPLOG/%f'
   wal_level = hot_standby

$SLAVE:$BASE/postgresql.conf

   wal_level = hot_standby
   max_wal_senders = 5
   wal_keep_segments = 32
   archive_mode = off
   hot_standby = on

$SLAVE:$BASE/recover.conf

   standby_mode          = 'on'
   primary_conninfo      = 'host=$MASTER port=5432 user=$USER password=$PASS'
   trigger_file          = '$BASE.trigger'
   restore_command       = 'cp $REPLOG/%f %p'