PgSQL Replication - Revision history

Alnaucler at 19:27, 20 February 2020

2020-02-20T19:27:35Z

← Older revision		Revision as of 21:27, 20 February 2020
Line 71:		Line 71:
	{{NavBox Hub}}		{{NavBox Hub}}
	[[Category:NoPublic]]		[[Category:NoPublic]]
	[[Category:~~VIE~~]]		[[Category:NAA]]

Alnaucler at 14:41, 8 July 2019

2019-07-08T14:41:23Z

← Older revision		Revision as of 16:41, 8 July 2019
Line 13:		Line 13:
	<li>Uncomment and edit the following lines in the '''postgresql.conf''' file in C:/IMSMAng/pgsql/data:		<li>Uncomment and edit the following lines in the '''postgresql.conf''' file in C:/IMSMAng/pgsql/data:
	<pre>		<pre>
	153 wal_level = hot_standby		row 153 wal_level = hot_standby
	174 checkpoint_segments = 8		row 174 checkpoint_segments = 8
	196 max_wal_senders = 3		row 196 max_wal_senders = 3
	199 wal_keep_segments = 8		row 199 wal_keep_segments = 8
	</pre>		</pre>
	The numbers on the left correspond to the number of the line.		The numbers on the left correspond to the number of the line.
Line 38:		Line 38:
	pg_basebackup –h <master-ip> -D c:/IMSMAng/pgsql/data –U replicator –v –P		pg_basebackup –h <master-ip> -D c:/IMSMAng/pgsql/data –U replicator –v –P
	</pre>		</pre>
	This command makes the initial synchronisation, i.e. copies the entire database from the master to the standby. This can take some time, depending on the size of the database. The progress can be followed on the command window as shown below.		This command makes the initial synchronisation, i.e. copies the entire database from the master to the standby. This can take some time, depending on the size of the database. The progress can be followed on the command window as shown below.<br />
	[[File:Pgsql replication basebackup1.png]]<br />		[[File:Pgsql replication basebackup1.png]]<br />
	The following warning message can be ignored:<br />		The following warning message can be ignored:<br />

Evinek at 11:43, 6 August 2014

2014-08-06T11:43:27Z

← Older revision		Revision as of 13:43, 6 August 2014
Line 66:		Line 66:
	After having set up the replication, it is good practice to perform a few tests. These can typically include the following:		After having set up the replication, it is good practice to perform a few tests. These can typically include the following:
	* Add/edit/delete data on the master server and verify that the changes are replicated to the standby.		* Add/edit/delete data on the master server and verify that the changes are replicated to the standby.
	* Verify that he standby is read-only (this is very important for data consistency) by trying to edit something directly on the standby.		* Verify that he standby is read-only (this is very important for data consistency) by trying to edit something directly on the standby. There should be an error message such as "Error approving fieldreport" when trying to save/approve a new object or edit an existing object in IMSMA.
	* Shut down the standby, make changes on the master, start the standby and verify that all pending changes have been applied.		* Shut down the standby, make changes on the master, start the standby and verify that all pending changes have been applied.

Evinek at 09:00, 6 August 2014

2014-08-06T09:00:29Z

← Older revision		Revision as of 11:00, 6 August 2014
Line 62:		Line 62:
	</li>		</li>
	</ol>		</ol>

			== Testing the replication ==__NOEDITSECTION__
			After having set up the replication, it is good practice to perform a few tests. These can typically include the following:
			* Add/edit/delete data on the master server and verify that the changes are replicated to the standby.
			* Verify that he standby is read-only (this is very important for data consistency) by trying to edit something directly on the standby.
			* Shut down the standby, make changes on the master, start the standby and verify that all pending changes have been applied.

	{{NavBox Hub}}		{{NavBox Hub}}

	[[Category:NoPublic]]		[[Category:NoPublic]]
	[[Category:VIE]]		[[Category:VIE]]

Evinek at 08:55, 6 August 2014

2014-08-06T08:55:53Z

← Older revision		Revision as of 10:55, 6 August 2014
Line 2:		Line 2:

	== Requirements ==__NOEDITSECTION__		== Requirements ==__NOEDITSECTION__
			* Two computers/servers that can communicate with each other over the network. The communication goes over the port number 5432 (in a default setup), thus there might be firewall settings to be adapted, to ensure that the 5432 port on each computer is accessible for the other one.
			* The '''exact same''' IMSMA version on both computers.

	== Steps for stetting up the replication with pgSQL ==__NOEDITSECTION__		== Steps for stetting up the replication with pgSQL ==__NOEDITSECTION__
Line 16:		Line 18:
	199 wal_keep_segments = 8		199 wal_keep_segments = 8
	</pre>		</pre>
			The numbers on the left correspond to the number of the line.
	</li>		</li>
	<li>Add the following line in the '''pg_hba.conf''' file in C:/IMSMAng/pgsql/data:		<li>Add the following line in the '''pg_hba.conf''' file in C:/IMSMAng/pgsql/data:

Evinek at 08:52, 6 August 2014

2014-08-06T08:52:13Z

← Older revision		Revision as of 10:52, 6 August 2014
Line 36:		Line 36:
	</pre>		</pre>
	This command makes the initial synchronisation, i.e. copies the entire database from the master to the standby. This can take some time, depending on the size of the database. The progress can be followed on the command window as shown below.		This command makes the initial synchronisation, i.e. copies the entire database from the master to the standby. This can take some time, depending on the size of the database. The progress can be followed on the command window as shown below.
			[[File:Pgsql replication basebackup1.png]]<br />
			The following warning message can be ignored:<br />
			[[File:Pgsql replication basebackup2.png]]<br />
	{{Note\|The hot standby replication mechanism can only be applied to the '''entire''' database server, i.e. all the databases from the master will be replication to the standby -- not only the database ''imsma'' but any database that is running on the master.}}		{{Note\|The hot standby replication mechanism can only be applied to the '''entire''' database server, i.e. all the databases from the master will be replication to the standby -- not only the database ''imsma'' but any database that is running on the master.}}
	</li>		</li>

Evinek at 08:42, 6 August 2014

2014-08-06T08:42:06Z

← Older revision		Revision as of 10:42, 6 August 2014
Line 1:		Line 1:
	This page describes how to set up direct replication with pgSQL. Generally, replication allows having two database servers (typically one for data entry and one for reporting) that stay synchronised. Concretely, when data is added, deleted or updated on the '''master''' server, the changes get immediately replicated on the '''slave''' or '''standby''' server. The standby database is read-only, i.e. no changes can be made directly there - only changes on the master are being replicated. Currently (August 2014) this mechanism is only in place at MACCA in Kabul.		This page describes how to set up direct replication with pgSQL. Generally, replication allows having two database servers (typically one for data entry and one for reporting) that stay synchronised. Concretely, when data is added, deleted or updated on the '''master''' server, the changes get immediately replicated on the '''slave''' or '''standby''' server. The standby database is read-only, i.e. no changes can be made directly there - only changes on the master are being replicated. Currently (August 2014) this mechanism is only in place at MACCA in Kabul.

	== Requirements ==~~__NOEDITSECTION~~		== Requirements ==__NOEDITSECTION__

	== Steps for stetting up the replication with pgSQL ==__NOEDITSECTION__		== Steps for stetting up the replication with pgSQL ==__NOEDITSECTION__

Evinek at 08:40, 6 August 2014

2014-08-06T08:40:52Z

@@ Line 1: / Line 1: @@
 This page describes how to set up direct replication with pgSQL. Generally, replication allows having two database servers (typically one for data entry and one for reporting) that stay synchronised. Concretely, when data is added, deleted or updated on the '''master''' server, the changes get immediately replicated on the '''slave''' or '''standby''' server. The standby database is read-only, i.e. no changes can be made directly there - only changes on the master are being replicated. Currently (August 2014) this mechanism is only in place at MACCA in Kabul.
 == Steps for stetting up the replication with pgSQL ==__NOEDITSECTION__
@@ Line 35: / Line 37: @@
 This command makes the initial synchronisation, i.e. copies the entire database from the master to the standby. This can take some time, depending on the size of the database. The progress can be followed on the command window as shown below.
 {{Note|The hot standby replication mechanism can only be applied to the '''entire''' database server, i.e. all the databases from the master will be replication to the standby -- not only the database ''imsma'' but any database that is running on the master.}}
 </li>
 </ol>

Evinek at 08:36, 6 August 2014

2014-08-06T08:36:31Z

@@ Line 2: / Line 2: @@
 == Steps for stetting up the replication with pgSQL ==__NOEDITSECTION__
 {{NavBox Hub}}
 [[Category:NoPublic]]
 [[Category:VIE]]

Evinek at 08:03, 6 August 2014

2014-08-06T08:03:30Z

← Older revision		Revision as of 10:03, 6 August 2014
Line 1:		Line 1:
	This page describes how to set up direct replication with pgSQL. Generally, replication allows having two database servers (typically one for data entry and one for reporting) that stay synchronised. Concretely, when data is added, deleted or updated on the '''master''' server, the changes get immediately replicated on the '''slave''' or '''standby''' server. The standby database is read-only, i.e. no changes can be made directly there - only changes on the master are being replicated. Currently (August 2014) this mechanism is only in place at MACCA in Kabul.		This page describes how to set up direct replication with pgSQL. Generally, replication allows having two database servers (typically one for data entry and one for reporting) that stay synchronised. Concretely, when data is added, deleted or updated on the '''master''' server, the changes get immediately replicated on the '''slave''' or '''standby''' server. The standby database is read-only, i.e. no changes can be made directly there - only changes on the master are being replicated. Currently (August 2014) this mechanism is only in place at MACCA in Kabul.

			== Steps for stetting up the replication with pgSQL ==__NOEDITSECTION__

	{{NavBox Hub}}		{{NavBox Hub}}