PostgreSQL - Revision history

Merenber: /* Installation */

2024-03-30T14:47:16Z

Installation

2024-03-16T09:54:48Z

Cron

← Older revision		Revision as of 04:54, 16 March 2024
Line 180:		Line 180:
	Type=oneshot		Type=oneshot
	User=postgres		User=postgres
	ExecStart=/var/lib/postgresql/bin/pgbackrest-wrapper.sh --stanza=main backup --type=~~full~~		ExecStart=/var/lib/postgresql/bin/pgbackrest-wrapper.sh --stanza=main backup --type=%i

	[Unit]		[Unit]

2024-03-16T09:24:18Z

Cron

@@ Line 169: / Line 169: @@
 ==== Cron ====
 <pre>
 # Full back up at 00:15 every Sunday and Wednesday
 # Differential backup at 00:30 every day
 # Incremental backup at the 45th minute of every hour
 </pre>

2023-12-02T23:11:43Z

Upgrades

← Older revision		Revision as of 18:11, 2 December 2023
Line 131:		Line 131:

	set -ex		set -ex
	if [ $~~USER~~ != postgres ]; then		if [ "$(id -un)" != postgres ]; then
	echo "This script should run as the postgres user" >&2		echo "This script should run as the postgres user" >&2
	exit 1		exit 1

2023-11-25T16:22:46Z

Backups

← Older revision		Revision as of 11:22, 25 November 2023
Line 56:		Line 56:

	=== Backups ===		=== Backups ===
	We use [https://pgbackrest.org pgBackRest] for Postgres backups.		We use [https://pgbackrest.org pgBackRest] for Postgres backups. It has already been installed on coffee and caffeine.

	==== Installation ====		==== Installation ====

2023-11-25T16:13:26Z

Upgrades

← Older revision		Revision as of 11:13, 25 November 2023
Line 144:		Line 144:
	mapfile -t pgbr_versions < <(grep -oP '/var/lib/postgresql/\K(\d+)' /etc/pgbackrest.conf)		mapfile -t pgbr_versions < <(grep -oP '/var/lib/postgresql/\K(\d+)' /etc/pgbackrest.conf)
	if [ ${#pgbr_versions[@]} -ne 1 ]; then		if [ ${#pgbr_versions[@]} -ne 1 ]; then
	echo "Expected to find 1 pgBackRest folder, found ${pgbr_versions[@]} instead: ${pgbr_versions[*]}" >&2		echo "Expected to find 1 pgBackRest folder, found ${#pgbr_versions[@]} instead: ${pgbr_versions[*]}" >&2
	exit 1		exit 1
	fi		fi

2023-11-25T16:09:07Z

Installation

← Older revision		Revision as of 11:09, 25 November 2023
Line 81:		Line 81:
	mkdir ~/backups/coffee/pgbackrest		mkdir ~/backups/coffee/pgbackrest
	</pre>		</pre>
	Next, on coffee, paste the following into /etc/pgbackrest.conf (adjust repo1-path and pg1-path ~~as appropriate):~~		Next, on coffee, paste something like the following into /etc/pgbackrest.conf. <strong>Make sure to adjust repo1-path and pg1-path.</strong>
	<pre>		<pre>
	[global]		[global]

2023-11-25T16:08:05Z

Installation

← Older revision		Revision as of 11:08, 25 November 2023
Line 81:		Line 81:
	mkdir ~/backups/coffee/pgbackrest		mkdir ~/backups/coffee/pgbackrest
	</pre>		</pre>
	Next, on coffee, paste the following into /etc/pgbackrest.conf (adjust ~~the~~ pg1-path as appropriate):		Next, on coffee, paste the following into /etc/pgbackrest.conf (adjust repo1-path and pg1-path as appropriate):
	<pre>		<pre>
	[global]		[global]

2023-11-25T16:02:37Z

Backups

← Older revision		Revision as of 11:02, 25 November 2023
Line 180:		Line 180:
	45 * * * * postgres chronic ~/bin/pgbackrest-wrapper.sh --stanza=main backup --type=incr		45 * * * * postgres chronic ~/bin/pgbackrest-wrapper.sh --stanza=main backup --type=incr
	</pre>		</pre>

			==== Restore ====
			Suppose we want to restore the latest backup, and the installed Postgres is 15. First, make sure that you actually have at least one backup present for this version:
			<pre>
			su -c postgres -c 'pgbackrest --stanza=main info'
			</pre>
			Next, stop the database and delete all of the files:
			<pre>
			systemctl stop postgresql@15-main
			rm -rf /var/lib/postgresql/15/main/*
			</pre>
			Now switch to the postgres user and run the "restore" command:
			<pre>
			su - postgres
			pgbackrest --stanza=main restore
			</pre>
			If you start Postgres, everything should be in a working state:
			<pre>
			systemctl start postgresql@15-main
			</pre>

			If you want to restore a backup which is not the latest version, pass the <code>--set</code> argument to pgbackrest. See https://pgbackrest.org/user-guide.html#restore for more details.

2023-11-25T15:56:42Z

Cron

← Older revision		Revision as of 10:56, 25 November 2023
Line 169:		Line 169:

	==== Cron ====		==== Cron ====
	We want backups to be taken periodically. Paste the following into e.g. /etc/cron.d/postgres_backup (~~adjust~~ ~~the~~ ~~frequencies~~ as ~~desired~~):		We want backups to be taken periodically. Paste the following into e.g. /etc/cron.d/postgres_backup (this file must be owned by root):
	<pre>		<pre>
	MAILTO=root@csclub.uwaterloo.ca		MAILTO=root@csclub.uwaterloo.ca