Postgresql Warm Standby Testing

use pgFouine analyse postgresql slow queries

oracle 10g em and isqlplus unrecognizable chinese characters on debian lenny

Large | Medium | Small

[

2009/12/03 11:24 | by askwan ]

OS: Debian5.0 lenny x86_64
Postgresql:8.3.8
Master :192.168.0.121
Warm Standby:192.168.0.122

1. install postgresql on both master and warm standby server.

#cd /usr/local/src/postgresql-8.3.8/
#./configure –prefix=/usr/local/pgsql
#make
#make install
#mkdir /usr/local/pgsql/data
#chown postgres /usr/local/pgsql/data
#su - postgres
$/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

2. Config passwordless ssh authentication use user postgres
Please refer to http://www.petefreitag.com/item/532.cfm

3. Install pg_standby on both both master and warm standby server.

#cd /usr/local/src/postgresql-8.3.8/contrib/pg_standby
#make
#make install

4. Config postgresql

In master server :

Quotation

archive_mode = on
archive_command = 'rsync -arv %p postgres@192.168.0.122:/wal_archives/%f'
archive_timeout = 1200

in warm standby server :

#mkdir /wal_archives
#chown postgres /wal_archives/

start master server :

pg_ctl -D /usr/local/pgsql/data/ start -l /usr/local/pgsql/data/logfile

5.  Dump all production data to master server  .

You can check the warm standby server ‘s /wal_archives directory , whether there is archive logs  from master server located on .

6.  Login to master  server as postgres user.
Create a script to do a base backup:

Quotation

#!/bin/bash
PGDATA=/usr/local/pgsql/data
psql -c "CHECKPOINT;"
psql -c "SELECT pg_start_backup('BASEBACKUP');"
tar -C /usr/local/pgsql/ -zcf - data |ssh 192.168.0.122 "tar -C /usr/local/pgsql/ -zxf -"
psql -c "select pg_stop_backup();"
echo "BASEBACKUP compete!"

7. Login to warm standby server as postgresql user.
Create a script to do some clean and configuration

Quotation

#!/bin/bash
PG_HOME=/usr/local/pgsql
PGDATA=/usr/local/pgsql/data
trigger_file=/tmp/pgsql.trigger
pg_standby=$PG_HOME/bin/pg_standby
PG_ARCHIVES=/wal_archives
  rm -f $PGDATA/recovery.*
  rm -f $PGDATA/logfile
  rm -f $PGDATA/postmaster.pid
  rm -f $PGDATA/pg_xlog/0*
  rm -f $PGDATA/pg_xlog/archive_status/0*
  sed -i '/^archive_/s/^/#/g' $PGDATA/postgresql.conf
  echo "restore_command = '$pg_standby -l -d -s 2 -t $trigger_file $PG_ARCHIVES %f %p %r 2>>/tmp/standby.log'" > $PGDATA/recovery.co
nf
  chown postgres.postgres $PGDATA/recovery.conf
  echo "Init completed! now start warm standby server."

Note:PostgreSQL 8.4 provides the recovery_end_command option.so,in recovery.conf add recovery_end_command=’rm –f /tmp/pgsql.trigger ’

pg_standby supports creation of a "warm standby" database server. It is designed to be a production-ready program, as well as a customizable template should you require specific modifications.
More about pg_standby,refer to http://www.postgresql.org/docs/current/static/pgstandby.html
http://www.enterprisedb.com/docs/en/8.4/pg/pgstandby.html

8. Start warm standby server

pg_ctl -D /usr/local/pgsql/data start -l /usr/local/pgsql/data/logfile

9. check warm standby postgresql logfile:

Quotation

LOG:  database system was interrupted; last known up at 2009-12-03 11:44:37 CST
LOG:  starting archive recovery
LOG:  restore_command = '/usr/local/pgsql/bin/pg_standby -l -d -s 2 -t /tmp/pgsql.trigger /wal_archives %f %p %r 2>>/tmp/standby.log
'
LOG:  restored log file "000000010000000000000014.00000020.backup" from archive
LOG:  restored log file "000000010000000000000014" from archive
LOG:  automatic recovery in progress
LOG:  redo starts at 0/14000068
FATAL:  the database system is starting up
LOG:  restored log file "000000010000000000000015" from archive
LOG:  restored log file "000000010000000000000016" from archive
LOG:  restored log file "000000010000000000000017" from archive
LOG:  restored log file "000000010000000000000018" from archive
LOG:  restored log file "000000010000000000000019" from archive
LOG:  restored log file "00000001000000000000001A" from archive
LOG:  restored log file "00000001000000000000001B" from archive
LOG:  restored log file "00000001000000000000001C" from archive
LOG:  restored log file "00000001000000000000001D" from archive
LOG:  restored log file "00000001000000000000001E" from archive
LOG:  restored log file "00000001000000000000001F" from archive
LOG:  restored log file "000000010000000000000020" from archive
LOG:  restored log file "000000010000000000000021" from archive
……

Check standby logfile:

Quotation

Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 00000001.history
WAL file path           : /wal_archives/00000001.history
Restoring to...         : pg_xlog/RECOVERYHISTORY
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/00000001.history" "pg_xlog/RECOVERYHISTORY"
Keep archive history    : 000000000000000000000000 and later
running restore         : OK
Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000014.00000020.backup
WAL file path           : /wal_archives/000000010000000000000014.00000020.backup
Restoring to...         : pg_xlog/RECOVERYHISTORY
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000014.00000020.backup" "pg_xlog/RECOVERYHISTORY"
Keep archive history    : 000000000000000000000000 and later
running restore         : OK
Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000014
WAL file path           : /wal_archives/000000010000000000000014
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000014" "pg_xlog/RECOVERYXLOG"
Keep archive history    : 000000000000000000000000 and later
running restore         : OK

Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000015
WAL file path           : /wal_archives/000000010000000000000015
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000015" "pg_xlog/RECOVERYXLOG"
Keep archive history    : 000000010000000000000014 and later
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
Trigger file            : /tmp/pgsql.trigger
Waiting for WAL file    : 000000010000000000000016
WAL file path           : /wal_archives/000000010000000000000016
Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds

Restoring to...         : pg_xlog/RECOVERYXLOG
Sleep interval          : 2 seconds
Max wait interval       : 0 forever
Command for restore     : ln -s -f "/wal_archives/000000010000000000000016" "pg_xlog/RECOVERYXLOG"
Keep archive history    : 000000010000000000000014 and later
running restore         : OK
......
WAL file not present yet. Checking for trigger file...
WAL file not present yet. Checking for trigger file...
......

10. failover
In warm standby server
touch /tmp/ pgsql.trigger

作者：askwan@『AskWan』
地址：http://www.askwan.com/Postgresql_Warm_Standby_point_in_time_recovery/
可以转载,转载时请以链接形式注明作者和原始出处及本声明！