Hot Standby

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(+cat)
Line 1: Line 1:
 
Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. Log Shipping replication allows you to create one or more standby nodes that are replicas of the primary node (or master node). Standby nodes can then be used for read-only query access.
 
Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. Log Shipping replication allows you to create one or more standby nodes that are replicas of the primary node (or master node). Standby nodes can then be used for read-only query access.
  
Hot Standby has been developed for inclusion in PostgreSQL 9.0 by Simon Riggs of [http://www.2ndQuadrant.com/ 2ndQuadrant] via public sponsorship.
+
Hot Standby was developed for inclusion in PostgreSQL 9.0 by Simon Riggs of [http://www.2ndQuadrant.com/ 2ndQuadrant] via public sponsorship.
  
The feature is now committed to CVS and is included as of PostgreSQL 8.5 Alpha3, see [http://developer.postgresql.org/pgdocs/postgres/hot-standby.html Hot Standby docs] for full details.
+
The feature was committed in PostgreSQL 8.5 Alpha3 and is available in PostgreSQL 9.0 and above. See [http://www.postgresql.org/docs/current/static/hot-standby.html the Hot Standby docs] for full details.
  
This feature will augment the capabilities of [[Streaming_Replication|streaming replication]] that is also planned for inclusion in r9.0, though it does not rely on it and has minimal interaction with it.
+
This feature augments [[Streaming_Replication|streaming replication]] feature that was introduced in PostgreSQL 9.1, though it does not rely on it and has minimal interaction with it.
  
= Questions, Issues, Bugs =
+
= Questions, issues and bugs =
  
If you have a Question, an Issue or wish to report a Bug then you are welcome to do that via pgsql-bugs@postgresql.org or pgsql-hackers@postgresql.org, though please also copy simon@2ndQuadrant.com on those mails.
+
If you have a question/issue or wish to report a bug then you are welcome to do that via [the pgsql-bugs or pgsql-hackers mailing lists](http://www.postgresql.org/community/lists/). Please also copy simon@2ndQuadrant.com on hot standby related mail.
  
 
== Quick Start ==
 
== Quick Start ==
Line 40: Line 40:
 
3. Create a backup of the master
 
3. Create a backup of the master
  
If you're setting this up for testing purposes on the same machine, a script as follows will probably work for you:  
+
The best option for creating the base backup is to use the standard [http://www.postgresql.org/docs/current/static/app-pgbasebackup.html <tt>pg_basebackup</tt> tool] that comes with PostgreSQL 9.1 and above. If you intend to use 9.0 you will need to use your own scripts using <tt>pg_start_backup</tt>, <tt>pg_stop_backup</tt>, <tt>rsync</tt> or similar, and WAL archiving. <tt>pg_basebackup</tt> is strongly recommended instead.
  
#!/bin/sh
+
A simple example might be, as the <tt>postgres</tt> user who wants to put a trial hot standby in the current directory, to run:
+
psql postgres -c "select pg_start_backup('backup')"
+
cp -pr hotstandby1/ hotstandby2
+
psql postgres -c "select pg_stop_backup()"
+
rm hotstandby2/postmaster.pid
+
rm -r hotstandby2/pg_xlog/*
+
+
echo 'hot_standby = on' >> hotstandby2/postgresql.conf
+
echo 'port = 5433' >> hotstandby2/postgresql.conf
+
+
echo "standby_mode = 'on'" >> hotstandby2/recovery.conf
+
echo "restore_command = 'cp -i /path/to/archive/%f %p'" >> hotstandby2/recovery.conf
+
  
YMMV with that - if you're shipping between two distinct servers or VMs, you'll need to configure your master server so that the WAL is shipped with something like RSYNC.
+
pg_basebackup -D hotstandby2 -w -R --xlog-method=stream --dbname="host=master user=postgres"
 +
 
 +
A more typical location to put the standby would be under <tt>/var/lib/pgsql</tt> or <tt>/var/lib/postgresql</tt>, depending on your install.
 +
 
 +
Windows and Mac users can use pg_basebackup, it isn't limited to unix/linux. It doesn't have to be run as the <tt>postgres</tt> user, that's just convention on unix/linux systems.
  
 
4. Start your standby server
 
4. Start your standby server
  
Start up your standby server with pg_ctl or equivalent startup command for your operating system!
+
Start up your standby server with pg_ctl or equivalent startup command for your operating system, running as the same user you made the <tt>pg_basebackup</tt> with:
  
 
  pg_ctl -D hotstandby2 -l hotstandby2.log start
 
  pg_ctl -D hotstandby2 -l hotstandby2.log start

Revision as of 05:33, 7 May 2013

Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. Log Shipping replication allows you to create one or more standby nodes that are replicas of the primary node (or master node). Standby nodes can then be used for read-only query access.

Hot Standby was developed for inclusion in PostgreSQL 9.0 by Simon Riggs of 2ndQuadrant via public sponsorship.

The feature was committed in PostgreSQL 8.5 Alpha3 and is available in PostgreSQL 9.0 and above. See the Hot Standby docs for full details.

This feature augments streaming replication feature that was introduced in PostgreSQL 9.1, though it does not rely on it and has minimal interaction with it.

Contents

Questions, issues and bugs

If you have a question/issue or wish to report a bug then you are welcome to do that via [the pgsql-bugs or pgsql-hackers mailing lists](http://www.postgresql.org/community/lists/). Please also copy simon@2ndQuadrant.com on hot standby related mail.

Quick Start

There is also a more extensive Binary Replication Tutorial

Setting up Hot Standby is much the same as Warm Standby, with a few extra parameters.

The most basic set of instructions you'll need are as follows:

  1. Create the master database
  2. Configure the master for WAL archiving
  3. Create a backup of the master
  4. Start up the hot standby instance

1. Create the master database

Use initdb to create the master database.

2. Configure the master for WAL archiving

The most limited set of parameters you'll need to change are:

wal_level = hot_standby
archive_mode = on
archive_command = 'cp -i %p /path/to/archive/%f'

Start the master database.

3. Create a backup of the master

The best option for creating the base backup is to use the standard pg_basebackup tool that comes with PostgreSQL 9.1 and above. If you intend to use 9.0 you will need to use your own scripts using pg_start_backup, pg_stop_backup, rsync or similar, and WAL archiving. pg_basebackup is strongly recommended instead.

A simple example might be, as the postgres user who wants to put a trial hot standby in the current directory, to run:

pg_basebackup -D hotstandby2 -w -R --xlog-method=stream --dbname="host=master user=postgres"

A more typical location to put the standby would be under /var/lib/pgsql or /var/lib/postgresql, depending on your install.

Windows and Mac users can use pg_basebackup, it isn't limited to unix/linux. It doesn't have to be run as the postgres user, that's just convention on unix/linux systems.

4. Start your standby server

Start up your standby server with pg_ctl or equivalent startup command for your operating system, running as the same user you made the pg_basebackup with:

pg_ctl -D hotstandby2 -l hotstandby2.log start

Diagnosing problems

There are sometimes reasons why you cannot connect. Check the log for messages.

First, check that you are connected to the correct server and that the server is in recovery. You can check this by looking at "ps" output and looking for a "startup process". If you can't find one, you aren't in recovery.

If you can connect, check you are in recovery by running:

   SELECT pg_is_in_recovery();

If you think you should be able to see an object or some data, check that the standby has processed all outstanding WAL. It may just be the standby is lagging behind.

Look at the current snapshot using:

   SELECT txid_current_snapshot();

Look at pg_locks and pg_stat_activity.

Current TODO

Latest TODO

Personal tools