Patroni Cluster - Revision history

Ian.readman: /* PGBackRest */

2025-08-03T15:39:07Z

PGBackRest

← Older revision		Revision as of 15:39, 3 August 2025
Line 326:		Line 326:
	=== PGBackRest ===		=== PGBackRest ===

	Setup the repository on Debian3		Setup the repository on Debian3. </br>
			Install pgbackrest on all the servers … </br>
	Install pgbackrest on all the servers …		<pre>
	sudo apt install pgbackrest		sudo apt install pgbackrest
	sudo mkdir -p -m 770 /var/log/pgbackrest		sudo mkdir -p -m 770 /var/log/pgbackrest
Line 336:		Line 336:
	sudo chmod 640 /etc/pgbackrest/pgbackrest.conf		sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
	sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf		sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
			</pre>

	On debian3, create the directory that will hold the backups …		On debian3, create the directory that will hold the backups … </br>
			<pre>
	sudo mkdir -p /etc/pgbackrest/conf.d		sudo mkdir -p /etc/pgbackrest/conf.d
	sudo mkdir -p /var/lib/pgbackrest		sudo mkdir -p /var/lib/pgbackrest
	sudo chmod 750 /var/lib/pgbackrest		sudo chmod 750 /var/lib/pgbackrest
	sudo chown postgres:postgres /var/lib/pgbackrest		sudo chown postgres:postgres /var/lib/pgbackrest
	~~Setup SSH~~		</pre>

	We will need a passwordless SSH connection between the repository and the database servers.		==== Setup SSH ====
	For this setup I was using postgres as the user that controls pgbackrest.		We will need a passwordless SSH connection between the repository and the database servers. </br>
			For this setup I was using postgres as the user that controls pgbackrest. </br>
			<pre>
	# debian1		# debian1
	ssh-keygen		ssh-keygen
Line 354:		Line 358:
	scp ./.ssh/id_ras.pub postgres@debian1		scp ./.ssh/id_ras.pub postgres@debian1
	ssh postgres@debian1		ssh postgres@debian1
	~~Configuration~~		</pre>

	As the postgres user create the configuration file on the repository		==== Configuration ====

			As the postgres user create the configuration file on the repository. </br>
			<pre>
	vi /etc/pgbackrest/pgbackrest.conf		vi /etc/pgbackrest/pgbackrest.conf

	[cybertec]		[cybertec]
	pg1-host=debian1		pg1-host=debian1
Line 368:		Line 376:
	repo1-retention-full=2		repo1-retention-full=2
	start-fast=y		start-fast=y
			</pre>

	As the postgres user create the configuration file on the database server		As the postgres user create the configuration file on the database server. </br>
			<pre>
	[cybertec]		[cybertec]
	pg1-path=/var/lib/postgresql/17		pg1-path=/var/lib/postgresql/17
Line 377:		Line 387:
	repo1-host=debian3		repo1-host=debian3
	repo1-host-user=postgres		repo1-host-user=postgres
			</pre>

	Alter the archive_command parameter so that it uses the pgbackrest executable		Alter the archive_command parameter so that it uses the pgbackrest executable. </br>
	patronictl edit-config --pg archive_command="pgbackrest \		<pre>
	--stanza=cybertec archive-push %p"		patronictl edit-config --pg archive_command="pgbackrest --stanza=cybertec archive-push %p"
			</pre>

	Create a Stanza on the repository server and confirm it is working		Create a Stanza on the repository server and confirm it is working. </br>
			<pre>
	pgbackrest --stanza=cybertec stanza-create		pgbackrest --stanza=cybertec stanza-create
	pgbackrest --stanza=cybertec check		pgbackrest --stanza=cybertec check
			</pre>

	Also check if the Stanza is correct on the database ~~server~~.		Also check if the Stanza is correct on the database servers. </br>
			<pre>
	pgbackrest --stanza=cybertec check		pgbackrest --stanza=cybertec check
			</pre>

Ian.readman: /* VIP Manager */

2025-08-03T15:34:37Z

VIP Manager

← Older revision		Revision as of 15:34, 3 August 2025
Line 287:		Line 287:

	=== VIP Manager ===		=== VIP Manager ===
	Copy the latest release to your server. ~~</br>~~		Copy the latest release to your server.
	<pre>		<pre>
	sudo dpkg -i vip-manager_2.8.0_Linux_x86_64.deb		sudo dpkg -i vip-manager_2.8.0_Linux_x86_64.deb

Ian.readman: /* VIP Manager */

2025-08-03T15:34:29Z

VIP Manager

← Older revision		Revision as of 15:34, 3 August 2025
Line 287:		Line 287:

	=== VIP Manager ===		=== VIP Manager ===
	Copy the latest release to your server : </br>		Copy the latest release to your server. </br>
	<pre>		<pre>
	sudo dpkg -i vip-manager_2.8.0_Linux_x86_64.deb		sudo dpkg -i vip-manager_2.8.0_Linux_x86_64.deb

Ian.readman: /* VIP Manager */

2025-08-03T15:34:16Z

VIP Manager

← Older revision		Revision as of 15:34, 3 August 2025
Line 287:		Line 287:

	=== VIP Manager ===		=== VIP Manager ===

	Copy the latest release to your server : </br>		Copy the latest release to your server : </br>
	<pre>		<pre>

Ian.readman at 15:33, 3 August 2025

2025-08-03T15:33:55Z

← Older revision		Revision as of 15:33, 3 August 2025
Line 288:		Line 288:
	=== VIP Manager ===		=== VIP Manager ===

	Copy the latest release to your server :		Copy the latest release to your server : </br>
			<pre>

	sudo dpkg -i vip-manager_2.8.0_Linux_x86_64.deb		sudo dpkg -i vip-manager_2.8.0_Linux_x86_64.deb
	sudo systemctl stop vip-manager		sudo systemctl stop vip-manager
	sudo vi /etc/patroni/vip-manager.yml		sudo vi /etc/patroni/vip-manager.yml
			</pre>

	The trigger key below contains the namespace and scope of the Patroni service.		The trigger key below contains the namespace and scope of the Patroni service. </br>
			<pre>
	ip: 192.168.59.100		ip: 192.168.59.100
	netmask: 24		netmask: 24
Line 302:		Line 303:
	dcs-type: etcd		dcs-type: etcd
	dcs-endpoints: http://192.168.59.101:2379		dcs-endpoints: http://192.168.59.101:2379
			</pre>

			<pre>
	sudo vi /etc/systemd/system/multi-user.target.wants/vip-manager.service		sudo vi /etc/systemd/system/multi-user.target.wants/vip-manager.service

	[Unit]		[Unit]
	Description=Manages Virtual IP for Patroni		Description=Manages Virtual IP for Patroni
Line 318:		Line 323:
	sudo systemctl start vip-manager		sudo systemctl start vip-manager
	sudo journalctl -u vip-manager.service -n 100 -f		sudo journalctl -u vip-manager.service -n 100 -f
	PGBackRest		</pre>

			=== PGBackRest ===

	Setup the repository on Debian3		Setup the repository on Debian3

Ian.readman at 15:21, 3 August 2025

2025-08-03T15:21:35Z

← Older revision		Revision as of 15:21, 3 August 2025
Line 205:		Line 205:
	</pre>		</pre>

	Validate the configuration file		Validate the configuration file </br>
			<pre>
	patroni --validate-config /etc/patroni/patroni.yml		patroni --validate-config /etc/patroni/patroni.yml
	Service		</pre>
			==== Service ====
			<pre>
	sudo vi /etc/systemd/system/multi-user.target.wants/patroni.service		sudo vi /etc/systemd/system/multi-user.target.wants/patroni.service

	[Unit]		[Unit]
	Description=Runners to orchestrate a high-availability PostgreSQL		Description=Runners to orchestrate a high-availability PostgreSQL
Line 236:		Line 240:
	[Install]		[Install]
	WantedBy=multi-user.target		WantedBy=multi-user.target
			</pre>

	Set permissions of the data directory to 700		Set permissions of the data directory to 700 </br>
			<pre>
	sudo -iu postgres		sudo -iu postgres
	chmod 700 /var/lib/postgresql/17		chmod 700 /var/lib/postgresql/17
			</pre>

	Start the patroni service		Start the patroni service </br>
			<pre>
	sudo systemctl daemon-reload		sudo systemctl daemon-reload
	sudo systemctl start patroni		sudo systemctl start patroni
			</pre>

	Check status		Check status
			<pre>
	export PATRONICTL_CONFIG_FILE=/etc/patroni/patroni.yml		export PATRONICTL_CONFIG_FILE=/etc/patroni/patroni.yml
	patronictl -c /etc/patroni/patroni.yml list		patronictl -c /etc/patroni/patroni.yml list
Line 256:		Line 266:
	\| debian3 \| 192.168.59.103 \| Leader \| running \| 1 \| \|		\| debian3 \| 192.168.59.103 \| Leader \| running \| 1 \| \|
	+---------+----------------+---------+-----------+----+-----------+		+---------+----------------+---------+-----------+----+-----------+
			</pre>
	Change configuration		Change configuration </br>
			<pre>
	PATRONICTL_CONFIG_FILE=/etc/patroni/patroni.yml		PATRONICTL_CONFIG_FILE=/etc/patroni/patroni.yml
	patronictl edit-config --pg archive_command="pgbackrest --stanza=ian archive-push %p"		patronictl edit-config --pg archive_command="pgbackrest --stanza=ian archive-push %p"
	Issues		</pre>

			==== Issues ====

	Replication showed the following error		Replication showed the following error
			<pre>
	ERROR: Requested starting point 0/4000000 is ahead of the WAL flush		ERROR: Requested starting point 0/4000000 is ahead of the WAL flush
	position of this server 0/3000060		position of this server 0/3000060
			</pre>

	The replica instance needed to be re-initialised		The replica instance needed to be re-initialised </br>
			<pre>
	patronictl -c /etc/patroni/patroni.yml reinit <scope> <name>		patronictl -c /etc/patroni/patroni.yml reinit <scope> <name>
	VIP Manager		</pre>

			=== VIP Manager ===

	Copy the latest release to your server :		Copy the latest release to your server :

Ian.readman at 15:18, 3 August 2025

2025-08-03T15:18:54Z

← Older revision		Revision as of 15:18, 3 August 2025
Line 95:		Line 95:
	==== Issues ====		==== Issues ====

	One of the members would not connect to the cluster.		One of the members would not connect to the cluster. </br>
	The error message on the leader was “Prober detected unhealthy status …”		The error message on the leader was “Prober detected unhealthy status …” </br>
	On the problem member the message was “Failed to publish local member to cluster through raft”		On the problem member the message was “Failed to publish local member to cluster through raft” </br>
	No network issues, solved by removing and adding the member		No network issues, solved by removing and adding the member. </br>
	<pre>		<pre>
	etcdctl member list -w table		etcdctl member list -w table

Ian.readman at 15:17, 3 August 2025

2025-08-03T15:17:53Z

← Older revision		Revision as of 15:17, 3 August 2025
Line 93:		Line 93:
	</pre>		</pre>

	=== Issues ===		==== Issues ====

	One of the members would not connect to the cluster.		One of the members would not connect to the cluster.
Line 112:		Line 112:

	=== Patroni ===		=== Patroni ===
			<pre>
	sudo mkdir -p /etc/patroni/		sudo mkdir -p /etc/patroni/
	sudo chown -R postgres:postgres /etc/patroni/		sudo chown -R postgres:postgres /etc/patroni/
			</pre>
	As the postgres user, create a configuration file.		As the postgres user, create a configuration file. </br>
			<pre>
	sudo -iu postgres		sudo -iu postgres
	vi /etc/patroni/patroni.yml		vi /etc/patroni/patroni.yml

	namespace: /db/		namespace: /db/
	scope: cluster_1		scope: cluster_1
Line 200:		Line 203:
	clonefrom: false		clonefrom: false
	nosync: false		nosync: false
			</pre>

	Validate the configuration file		Validate the configuration file

Ian.readman at 15:16, 3 August 2025

2025-08-03T15:16:36Z

← Older revision		Revision as of 15:16, 3 August 2025
Line 90:		Line 90:
	Change the leader if necessary </br>		Change the leader if necessary </br>
	<pre>		<pre>
	etcdctl move-leader 939ed44c2e6d5e5b --endpoints=$~~ENDPOINTS~~		etcdctl move-leader 939ed44c2e6d5e5b --endpoints=$ETCDCTL_ENDPOINTS
	</pre>		</pre>

Line 98:		Line 98:
	The error message on the leader was “Prober detected unhealthy status …”		The error message on the leader was “Prober detected unhealthy status …”
	On the problem member the message was “Failed to publish local member to cluster through raft”		On the problem member the message was “Failed to publish local member to cluster through raft”

	No network issues, solved by removing and adding the member		No network issues, solved by removing and adding the member
			<pre>
	etcdctl member list -w table		etcdctl member list -w table
	sudo systemctl stop etcd (On server being removed)		sudo systemctl stop etcd (On server being removed)
	etcdctl member remove <ID>		etcdctl member remove <ID>
			etcdctl member add debian2 --peer-urls=http://192.168.59.102:2380
			</pre>
			Edit the etcd.yml files copying all information displayed, including the new order for ETCD_INITIAL_CLUSTER </br>
			On the server being added ..
			<pre>
			sudo systemctl start etcd
			</pre>

	~~etcdctl member add debian2 --peer-urls~~=~~http://192.168.59.102:2380~~		=== Patroni ===
	~~# Edit the etcd.yml files copying all information displayed,~~
	~~# including the new order for ETCD_INITIAL_CLUSTER~~
	~~sudo systemctl start etcd (On server being added)~~
	Patroni
	sudo mkdir -p /etc/patroni/		sudo mkdir -p /etc/patroni/
	sudo chown -R postgres:postgres /etc/patroni/		sudo chown -R postgres:postgres /etc/patroni/

Ian.readman at 15:14, 3 August 2025

2025-08-03T15:14:05Z

← Older revision		Revision as of 15:14, 3 August 2025
Line 79:		Line 79:
	</pre>		</pre>
	<pre>		<pre>
	+----------------------------+------------------+---------+---------+~~-----------+-~~-----------+-----------+------------+--------+		+----------------------------+------------------+---------+---------+-----------+-----------+------------+--------+
	\| ENDPOINT \| ID \| VERSION \| DB SIZE \| IS LEADER ~~\| IS LEARNER~~ \| RAFT TERM \| RAFT INDEX \| ERRORS \|		\| ENDPOINT \| ID \| VERSION \| DB SIZE \| IS LEADER \| RAFT TERM \| RAFT INDEX \| ERRORS \|
	+----------------------------+------------------+---------+---------+~~-----------+-~~-----------+-----------+------------+--------+		+----------------------------+------------------+---------+---------+-----------+-----------+------------+--------+
	\| http://192.168.59.102:2379 \| 3cdde56ec82a0ca0 \| 3.5.16 \| 53 kB \| true ~~\| false~~ \| 13 \| 145 \| \|		\| http://192.168.59.102:2379 \| 3cdde56ec82a0ca0 \| 3.5.16 \| 53 kB \| true \| 13 \| 145 \| \|
	\| http://192.168.59.101:2379 \| 406189a3bb67b8bc \| 3.5.16 \| 53 kB \| ~~false \|~~ false \| 13 \| 145 \| \|		\| http://192.168.59.101:2379 \| 406189a3bb67b8bc \| 3.5.16 \| 53 kB \| false \| 13 \| 145 \| \|
	\| http://192.168.59.103:2379 \| 939ed44c2e6d5e5b \| 3.5.16 \| 53 kB \| ~~false \|~~ false \| 13 \| 145 \| \|		\| http://192.168.59.103:2379 \| 939ed44c2e6d5e5b \| 3.5.16 \| 53 kB \| false \| 13 \| 145 \| \|
	+----------------------------+------------------+---------+---------+~~-----------+-~~-----------+-----------+------------+--------+		+----------------------------+------------------+---------+---------+-----------+-----------+------------+--------+
	</pre>		</pre>