Set up a database server: Difference between revisions
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'