System/PostgreSQL - Revision history

Eadam: /* Built-in Replication */

2012-12-21T09:43:49Z

Built-in Replication

Eadam: /* Statement based replication */

2012-12-21T09:41:50Z

Statement based replication

Eadam: /* Statement based replication */

2012-12-21T09:41:09Z

Statement based replication

Eadam at 09:40, 21 December 2012

2012-12-21T09:40:57Z

Eadam: /* Replication */

2012-12-15T22:25:18Z

Replication

Eadam: /* Replication */

2012-12-15T22:24:56Z

Replication

Eadam: /* Replication */

2012-12-15T22:21:51Z

Replication

Eadam: /* Replication */

2012-12-15T22:20:25Z

Replication

Eadam: /* Replication */

2012-12-15T22:19:32Z

Replication

Show changes

Eadam: /* Replication */

2012-12-15T22:16:52Z

Replication

@@ Line 15: / Line 15: @@
 . Set up connections and authentication so that the standby server can successfully connect to the replication pseudo-database on the primary.
-  $ $EDITOR postgresql.conf
+  $ vim postgresql.conf
   listen_addresses = '192.168.0.10'
-  $ $EDITOR pg_hba.conf
+  $ vim pg_hba.conf
   # The standby server must have superuser access privileges.
   host  replication  postgres  192.168.0.20/22  trust

@@ Line 7: / Line 7: @@
 == Statement based replication ==
-[System/PostgreSQL/Londiste Londiste]
+[[System/PostgreSQL/Londiste]]
 == Built-in Replication ==

@@ Line 7: / Line 7: @@
 == Statement based replication ==
-[[System/PostgreSQL/Londiste Londiste]]
+[System/PostgreSQL/Londiste Londiste]
 == Built-in Replication ==

← Older revision		Revision as of 09:40, 21 December 2012
Line 4:		Line 4:

	= Replication =		= Replication =
		+
		+	== Statement based replication ==
		+
		+	[[System/PostgreSQL/Londiste Londiste]]
		+
		+	== Built-in Replication ==

	Adapted from [http://wiki.postgresql.org/wiki/Streaming_Replication The official doc here].		Adapted from [http://wiki.postgresql.org/wiki/Streaming_Replication The official doc here].

@@ Line 5: / Line 5: @@
 = Replication =
-Adapted from [http://wiki.postgresql.org/wiki/Streaming_Replication]
+Adapted from [http://wiki.postgresql.org/wiki/Streaming_Replication The official doc here].
 . Set up connections and authentication so that the standby server can successfully connect to the replication pseudo-database on the primary.

@@ Line 17: / Line 17: @@
 . Set up the streaming replication related parameters on the primary server.
-  $ $EDITOR postgresql.conf
+  $ vim postgresql.conf
   wal_level = hot_standby
   max_wal_senders = 5
   wal_keep_segments = 32
   archive_mode    = on
-  archive_command = 'cp %p /path_to/archive/%f'
+  archive_command = 'rsync -a %p standby:/path_to/archive/%f'
 . Start postgres on the primary server.
@@ Line 36: / Line 36: @@
 . Enable read-only queries on the standby server. But if wal_level is archive on the primary, leave hot_standby unchanged (i.e., off).
-  $ $EDITOR postgresql.conf
+  $ vim postgresql.conf
   hot_standby = on
 . Create a recovery command file in the standby server; the following parameters are required for streaming replication.
-  $ $EDITOR recovery.conf
+  $ vim recovery.conf
   # Note that recovery.conf must be in $PGDATA directory.
   standby_mode          = 'on'
@@ Line 83: / Line 83: @@
 Create the trigger file in the standby after the primary fails.
 * How to restart streaming replication after failover
-Repeat the operations from 6th; making a fresh backup, some configurations and starting the original primary as the
+Repeat the operations from 4th; making a fresh backup, some configurations and starting the original primary as the
 standby. The primary server doesn't need to be stopped during these operations.
@@ Line 103: / Line 99: @@
 * How to re-synchronize the stand-alone standby after isolation
-Shut down the standby as usual. And repeat the operations from 6th.
+Shut down the standby as usual. And repeat the operations from 4th.
 If you have more than one slave, promoting one will break the other(s). Update their recovery.conf settings to point to the new master, set recovery_target_timeline to 'latest', scp/rsync the pg_xlog directory, and restart the slave.

@@ Line 80: / Line 80: @@
-   * How to do failover
+* How to do failover
 Create the trigger file in the standby after the primary fails.
- * How to stop the primary or the standby server
+* How to stop the primary or the standby server
 Shut down it as usual (pg_ctl stop).
- * How to restart streaming replication after failover
+* How to restart streaming replication after failover
 Repeat the operations from 6th; making a fresh backup, some configurations and starting the original primary as the
 standby. The primary server doesn't need to be stopped during these operations.
- * How to restart streaming replication after the standby fails
+* How to restart streaming replication after the standby fails
 Restart postgres in the standby server after eliminating the cause of failure.
- * How to disconnect the standby from the primary
+* How to disconnect the standby from the primary
 Create the trigger file in the standby while the primary is running. Then the standby would be brought up.
- * How to re-synchronize the stand-alone standby after isolation
+* How to re-synchronize the stand-alone standby after isolation
 Shut down the standby as usual. And repeat the operations from 6th.
 If you have more than one slave, promoting one will break the other(s). Update their recovery.conf settings to point to the new master, set recovery_target_timeline to 'latest', scp/rsync the pg_xlog directory, and restart the slave.

← Older revision		Revision as of 22:16, 15 December 2012
Line 4:		Line 4:

	= Replication =		= Replication =
		+
		+	From [http://wiki.postgresql.org/wiki/Streaming_Replication]
		+
		+	Install postgres in the primary and standby server as usual. This requires only configure, make and make install.
		+	2. Create the initial database cluster in the primary server as usual, using initdb.
		+	3. Set up connections and authentication so that the standby server can successfully connect to the replication pseudo-database on the primary.
		+	$ $EDITOR postgresql.conf
		+
		+	listen_addresses = '192.168.0.10'
		+
		+	$ $EDITOR pg_hba.conf
		+
		+	# The standby server must have superuser access privileges.
		+	host replication postgres 192.168.0.20/22 trust
		+	4. Set up the streaming replication related parameters on the primary server.
		+	$ $EDITOR postgresql.conf
		+
		+	# To enable read-only queries on a standby server, wal_level must be set to
		+	# "hot_standby". But you can choose "archive" if you never connect to the
		+	# server in standby mode.
		+	wal_level = hot_standby
		+
		+	# Set the maximum number of concurrent connections from the standby servers.
		+	max_wal_senders = 5
		+
		+	# To prevent the primary server from removing the WAL segments required for
		+	# the standby server before shipping them, set the minimum number of segments
		+	# retained in the pg_xlog directory. At least wal_keep_segments should be
		+	# larger than the number of segments generated between the beginning of
		+	# online-backup and the startup of streaming replication. If you enable WAL
		+	# archiving to an archive directory accessible from the standby, this may
		+	# not be necessary.
		+	wal_keep_segments = 32
		+
		+	# Enable WAL archiving on the primary to an archive directory accessible from
		+	# the standby. If wal_keep_segments is a high enough number to retain the WAL
		+	# segments required for the standby server, this is not necessary.
		+	archive_mode = on
		+	archive_command = 'cp %p /path_to/archive/%f'
		+	5. Start postgres on the primary server.
		+	6. 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()"
		+	7. Set up replication-related parameters, connections and authentication in the standby server like the primary, so that the standby might work as a primary after failover.
		+	8. Enable read-only queries on the standby server. But if wal_level is archive on the primary, leave hot_standby unchanged (i.e., off).
		+	$ $EDITOR postgresql.conf
		+
		+	hot_standby = on
		+	9. Create a recovery command file in the standby server; the following parameters are required for streaming replication.
		+	$ $EDITOR recovery.conf
		+	# Note that recovery.conf must be in $PGDATA directory.
		+
		+	# 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=192.168.0.10 port=5432 user=postgres'
		+
		+	# Specifies a trigger file whose presence should cause streaming replication to
		+	# end (i.e., failover).
		+	trigger_file = '/path_to/trigger'
		+
		+	# 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 /path_to/archive/%f "%p"'
		+	10. Start postgres in the standby server. It will start streaming replication.
		+	11. You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using pg_current_xlog_location on the primary and the pg_last_xlog_receive_location/pg_last_xlog_replay_location on the standby, respectively.
		+	$ psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10 (primary host)
		+	pg_current_xlog_location
		+	--------------------------
		+	0/2000000
		+	(1 row)
		+
		+	$ psql -c "select pg_last_xlog_receive_location()" -h192.168.0.20 (standby host)
		+	pg_last_xlog_receive_location
		+	-------------------------------
		+	0/2000000
		+	(1 row)
		+
		+	$ psql -c "select pg_last_xlog_replay_location()" -h192.168.0.20 (standby host)
		+	pg_last_xlog_replay_location
		+	------------------------------
		+	0/2000000
		+	(1 row)
		+	12. You can also check the progress of streaming replication by using ps command.
		+	# The displayed LSNs indicate the byte position that the standby server has
		+	# written up to in the xlogs.
		+	[primary] $ ps -ef \| grep sender
		+	postgres 6879 6831 0 10:31 ? 00:00:00 postgres: wal sender process postgres 127.0.0.1(44663) streaming 0/2000000
		+
		+	[standby] $ ps -ef \| grep receiver
		+	postgres 6878 6872 1 10:31 ? 00:00:01 postgres: wal receiver process streaming 0/2000000
		+	How to do failover
		+	Create the trigger file in the standby after the primary fails.
		+	How to stop the primary or the standby server
		+	Shut down it as usual (pg_ctl stop).
		+	How to restart streaming replication after failover
		+	Repeat the operations from 6th; making a fresh backup, some configurations and starting the original primary as the standby. The primary server doesn't need to be stopped during these operations.
		+	How to restart streaming replication after the standby fails
		+	Restart postgres in the standby server after eliminating the cause of failure.
		+	How to disconnect the standby from the primary
		+	Create the trigger file in the standby while the primary is running. Then the standby would be brought up.
		+	How to re-synchronize the stand-alone standby after isolation
		+	Shut down the standby as usual. And repeat the operations from 6th.
		+	If you have more than one slave, promoting one will break the other(s). Update their recovery.conf settings to point to the new master, set recovery_target_timeline to 'latest', scp/rsync the pg_xlog directory, and restart the slave.