Published: May 22, 2023 by Isaac Johnson

I’ve had a few primary node crashes lately, some of my own doing. One was due to me just knocking the power cord out and the battery draining. Regardless of cause, it made me realize the danger of having a production database without any backups.

Today we’ll use pg_basebackup to create a full backup of a PosgreSQL cluster and restore it elsewhere (to illustrate the process).

Postgres Backups

First, let’s review the cluster in question

isaac@isaac-MacBookAir:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

We have a standard PostgreSQL 12 DB hosted on the default port of 5432.

Next, we’ll login and create a super user I can use later if needed

isaac@isaac-MacBookAir:~$ sudo su - postgres
[sudo] password for isaac:
postgres@isaac-MacBookAir:~$ psql
psql (12.14 (Ubuntu 12.14-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# CREATE USER builder WITH SUPERUSER;
CREATE ROLE
postgres=# ALTER USER builder WITH PASSWORD 'NotTheRealPassword';
ALTER ROLE
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO builder;
ALTER DEFAULT PRIVILEGES
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO builder;
ALTER DEFAULT PRIVILEGES
postgres=# \q

NAS mounts

At this moment, I have a NAS mount for the NFS provisioner on the host

postgres@isaac-MacBookAir:~$ cat /etc/fstab
# /etc/fstab: static file system information.
#
# Use 'blkid' to print the universally unique identifier for a
# device; this may be used with UUID= as a more robust way to name devices
# that works even if disks are added and removed. See fstab(5).
#
# <file system> <mount point>   <type>  <options>       <dump>  <pass>
# / was on /dev/sda2 during installation
UUID=71e567ec-f9d6-48df-813e-ebb14d2ae929 /               ext4    errors=remount-ro 0       1
# /boot/efi was on /dev/sda1 during installation
UUID=9171-667C  /boot/efi       vfat    umask=0077      0       1
/swapfile                                 none            swap    sw              0       0
192.168.1.129:/volume1/k3snfs   /mnt/nfs/k3snfs nfs     auto,nofail,noatime,nolock,intr,tcp,actimeo=1800        0       0

But to be thorough, let us create a new backup mount just for databases.

I’ll create a new folder in Synology

If this volume had PHI and/or PII, as is sometimes the case in my profession, I would encrypt it. As it stands, this has only metrics data and some container images

I’ll change user perms as necessary

Next, I’ll right-click and edit

Then on “NFS Permissions”, I’ll whitelist this primary node IP. In fact, just to cover IP movement, I’ll whitelist the CIDR of the node pools and map all users to admin.

Once saved, I can see the NFS rule

Mounting backup dir with NFS

Now to use it, back on the primary node, I’ll create a mount point

$ sudo mkdir /mnt/psqlbackups
[sudo] password for isaac:

Then edit fstab and fire up the mount

isaac@isaac-MacBookAir:~$ sudo vi /etc/fstab
isaac@isaac-MacBookAir:~$ cat /etc/fstab | tail -n2
192.168.1.129:/volume1/k3snfs   /mnt/nfs/k3snfs nfs     auto,nofail,noatime,nolock,intr,tcp,actimeo=1800        0       0
192.168.1.129:/volume1/postgres-prod-dbbackups  /mnt/psqlbackups nfs    auto,nofail,noatime,nolock,intr,tcp,actimeo=1800        0       0

isaac@isaac-MacBookAir:~$ sudo mount -a

I can test by creating a file and seeing that it lands in the NAS

Now I can launch the pg_basebackup command using -Ft for tar format and -z for gzip compression.

isaac@isaac-MacBookAir:~$ pg_basebackup -h localhost -p 5432 -U builder -D /mnt/psqlbackups/2023-05-21 -Ft -z -Xs -P -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 3/5D000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_174464"
502741/502741 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 3/5D0D3178
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

Lastly, I can see the full database there on external storage

isaac@isaac-MacBookAir:~$ ls -ltrah /mnt/psqlbackups/2023-05-21
total 107M
drwxrwxrwx 5 root root  4.0K May 21 06:49 ..
drwxrwxrwx 2 1024 users 4.0K May 21 06:52 .
-rwxrwxrwx 1 1024 users 107M May 21 06:53 base.tar.gz
-rwxrwxrwx 1 1024 users 346K May 21 06:53 pg_wal.tar.gz

Restoring

I’ll setup a new server

builder@builder-T100:~$ sudo apt update && sudo apt -y install vim bash-completion wget
[sudo] password for builder:
Sorry, try again.
[sudo] password for builder:
Hit:1 http://us.archive.ubuntu.com/ubuntu focal InRelease
Hit:2 https://download.docker.com/linux/ubuntu focal InRelease
Get:3 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
Get:4 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease [108 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 DEP-11 Metadata [275 kB]
Get:7 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 DEP-11 Metadata [409 kB]
Get:8 http://us.archive.ubuntu.com/ubuntu focal-updates/multiverse amd64 DEP-11 Metadata [940 B]
Get:9 http://us.archive.ubuntu.com/ubuntu focal-backports/main amd64 DEP-11 Metadata [7,976 B]
Get:10 http://us.archive.ubuntu.com/ubuntu focal-backports/universe amd64 DEP-11 Metadata [30.5 kB]
Get:11 http://security.ubuntu.com/ubuntu focal-security/main amd64 DEP-11 Metadata [59.8 kB]
Get:12 http://security.ubuntu.com/ubuntu focal-security/universe amd64 DEP-11 Metadata [95.6 kB]
Get:13 http://security.ubuntu.com/ubuntu focal-security/multiverse amd64 DEP-11 Metadata [940 B]
Fetched 1,216 kB in 1s (1,026 kB/s)
Error: Timeout was reached
Reading package lists... Done
Building dependency tree
Reading state information... Done
8 packages can be upgraded. Run 'apt list --upgradable' to see them.
Reading package lists... Done
Building dependency tree
Reading state information... Done
bash-completion is already the newest version (1:2.10-1ubuntu1).
bash-completion set to manually installed.
vim is already the newest version (2:8.1.2269-1ubuntu5.14).
wget is already the newest version (1.20.3-1ubuntu2).
wget set to manually installed.
The following packages were automatically installed and are no longer required:
  gir1.2-goa-1.0 libfprint-2-tod1 libfwupdplugin1 libllvm10 libxmlb1
Use 'sudo apt autoremove' to remove them.
0 upgraded, 0 newly installed, 0 to remove and 8 not upgraded.

Get Postgres apt repo and keys

builder@builder-T100:~$ curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
builder@builder-T100:~$ echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main

Now we can install PostgreSQL 12

builder@builder-T100:~$ sudo apt update && sudo apt -y install vim bash-completion wget
[sudo] password for builder:
Sorry, try again.
[sudo] password for builder:
Hit:1 http://us.archive.ubuntu.com/ubuntu focal InRelease
Hit:2 https://download.docker.com/linux/ubuntu focal InRelease
Get:3 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
Get:4 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease [108 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 DEP-11 Metadata [275 kB]
Get:7 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 DEP-11 Metadata [409 kB]
Get:8 http://us.archive.ubuntu.com/ubuntu focal-updates/multiverse amd64 DEP-11 Metadata [940 B]
Get:9 http://us.archive.ubuntu.com/ubuntu focal-backports/main amd64 DEP-11 Metadata [7,976 B]
Get:10 http://us.archive.ubuntu.com/ubuntu focal-backports/universe amd64 DEP-11 Metadata [30.5 kB]
Get:11 http://security.ubuntu.com/ubuntu focal-security/main amd64 DEP-11 Metadata [59.8 kB]
Get:12 http://security.ubuntu.com/ubuntu focal-security/universe amd64 DEP-11 Metadata [95.6 kB]
Get:13 http://security.ubuntu.com/ubuntu focal-security/multiverse amd64 DEP-11 Metadata [940 B]
Fetched 1,216 kB in 1s (1,026 kB/s)
Error: Timeout was reached
Reading package lists... Done
Building dependency tree
....

We can now install PG 12

builder@builder-T100:~$ sudo apt update && sudo apt -y install postgresql-12 postgresql-client-12
[sudo] password for builder:
Hit:1 http://us.archive.ubuntu.com/ubuntu focal InRelease
Get:2 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
Hit:3 https://download.docker.com/linux/ubuntu focal InRelease
Get:4 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease [108 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu focal-updates/main i386 Packages [822 kB]
Get:7 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [2,567 kB]
Get:8 http://security.ubuntu.com/ubuntu focal-security/main amd64 DEP-11 Metadata [59.8 kB]
Hit:9 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease
Get:10 http://us.archive.ubuntu.com/ubuntu focal-updates/main Translation-en [433 kB]
Get:11 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 DEP-11 Metadata [274 kB]
Get:12 http://us.archive.ubuntu.com/ubuntu focal-updates/restricted amd64 Packages [1,879 kB]
Get:13 http://us.archive.ubuntu.com/ubuntu focal-updates/restricted Translation-en [264 kB]
Get:14 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 DEP-11 Metadata [410 kB]
Get:15 http://us.archive.ubuntu.com/ubuntu focal-updates/multiverse amd64 DEP-11 Metadata [940 B]
Get:16 http://security.ubuntu.com/ubuntu focal-security/universe amd64 DEP-11 Metadata [95.5 kB]
Get:17 http://us.archive.ubuntu.com/ubuntu focal-backports/main amd64 DEP-11 Metadata [8,000 B]
Get:18 http://security.ubuntu.com/ubuntu focal-security/multiverse amd64 DEP-11 Metadata [940 B]
Get:19 http://us.archive.ubuntu.com/ubuntu focal-backports/universe amd64 DEP-11 Metadata [30.5 kB]

We can double check it is enabled to come up after reboot

builder@builder-T100:~$ systemctl is-enabled postgresql
enabled

And that it’s running (or at least loaded)

builder@builder-T100:~$ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sun 2023-05-21 19:11:02 CDT; 10h ago
   Main PID: 2654149 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 9099)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

May 21 19:11:02 builder-T100 systemd[1]: Starting PostgreSQL RDBMS...
May 21 19:11:02 builder-T100 systemd[1]: Finished PostgreSQL RDBMS.

This new host never had NFS mounts, so we’ll need to add nfs-common

builder@builder-T100:~$ sudo apt-get install nfs-common
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  gir1.2-goa-1.0 libfprint-2-tod1 libfwupdplugin1 libxmlb1
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  keyutils libnfsidmap2 libtirpc-common libtirpc3 rpcbind
Suggested packages:
  open-iscsi watchdog
The following NEW packages will be installed:
  keyutils libnfsidmap2 libtirpc-common libtirpc3 nfs-common rpcbind
0 upgraded, 6 newly installed, 0 to remove and 8 not upgraded.
Need to get 405 kB of archives.
After this operation, 1,519 kB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 libtirpc-common all 1.2.5-1ubuntu0.1 [7,712 B]
Get:2 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 libtirpc3 amd64 1.2.5-1ubuntu0.1 [77.9 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu focal/main amd64 rpcbind amd64 1.2.5-8 [42.8 kB]
Get:4 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 keyutils amd64 1.6-6ubuntu1.1 [44.8 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libnfsidmap2 amd64 0.25-5.1ubuntu1 [27.9 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 nfs-common amd64 1:1.3.4-2.5ubuntu3.4 [204 kB]
Fetched 405 kB in 0s (1,078 kB/s)
Selecting previously unselected package libtirpc-common.
(Reading database ... 188138 files and directories currently installed.)
Preparing to unpack .../0-libtirpc-common_1.2.5-1ubuntu0.1_all.deb ...
Unpacking libtirpc-common (1.2.5-1ubuntu0.1) ...
Selecting previously unselected package libtirpc3:amd64.
Preparing to unpack .../1-libtirpc3_1.2.5-1ubuntu0.1_amd64.deb ...
Unpacking libtirpc3:amd64 (1.2.5-1ubuntu0.1) ...
Selecting previously unselected package rpcbind.
Preparing to unpack .../2-rpcbind_1.2.5-8_amd64.deb ...
Unpacking rpcbind (1.2.5-8) ...
Selecting previously unselected package keyutils.
Preparing to unpack .../3-keyutils_1.6-6ubuntu1.1_amd64.deb ...
Unpacking keyutils (1.6-6ubuntu1.1) ...
Selecting previously unselected package libnfsidmap2:amd64.
Preparing to unpack .../4-libnfsidmap2_0.25-5.1ubuntu1_amd64.deb ...
Unpacking libnfsidmap2:amd64 (0.25-5.1ubuntu1) ...
Selecting previously unselected package nfs-common.
Preparing to unpack .../5-nfs-common_1%3a1.3.4-2.5ubuntu3.4_amd64.deb ...
Unpacking nfs-common (1:1.3.4-2.5ubuntu3.4) ...
Setting up libtirpc-common (1.2.5-1ubuntu0.1) ...
Setting up keyutils (1.6-6ubuntu1.1) ...
Setting up libnfsidmap2:amd64 (0.25-5.1ubuntu1) ...
Setting up libtirpc3:amd64 (1.2.5-1ubuntu0.1) ...
Setting up rpcbind (1.2.5-8) ...
Created symlink /etc/systemd/system/multi-user.target.wants/rpcbind.service → /lib/systemd/system/rpcbind.service.
Created symlink /etc/systemd/system/sockets.target.wants/rpcbind.socket → /lib/systemd/system/rpcbind.socket.
Setting up nfs-common (1:1.3.4-2.5ubuntu3.4) ...

Creating config file /etc/idmapd.conf with new version
Adding system user `statd' (UID 130) ...
Adding new user `statd' (UID 130) with group `nogroup' ...
Not creating home directory `/var/lib/nfs'.
Created symlink /etc/systemd/system/multi-user.target.wants/nfs-client.target → /lib/systemd/system/nfs-client.target.
Created symlink /etc/systemd/system/remote-fs.target.wants/nfs-client.target → /lib/systemd/system/nfs-client.target.
nfs-utils.service is a disabled or a static unit, not starting it.
Processing triggers for systemd (245.4-4ubuntu3.21) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.9) ...
Error: Timeout was reached

I’ll create a mount and add a line to /etc/fstab

builder@builder-T100:~$ sudo mkdir /mnt/psqlbackups

builder@builder-T100:~$ cat /etc/fstab | tail -n2
/swapfile                                 none            swap    sw              0       0
192.168.1.129:/volume1/postgres-prod-dbbackups  /mnt/psqlbackups nfs    auto,nofail,noatime,nolock,intr,tcp,actimeo=1800        0       0

At this point, between nfs-common, psql and other updates, I decided just to reboot

builder@builder-T100:~$ sudo reboot
Connection to 192.168.1.99 closed by remote host.
Connection to 192.168.1.99 closed.

Once back in, I’ll stop postgres and verify the data directory

builder@builder-T100:~$ sudo systemctl stop postgresql.service
[sudo] password for builder:
builder@builder-T100:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

builder@builder-T100:~$ sudo su -
root@builder-T100:~# ls /var/lib/postgresql/12/main/
base    pg_commit_ts  pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf
global  pg_dynshmem   pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postmaster.opts

Next I’ll check I can see the contents of the backup

root@builder-T100:~# tar tfv /mnt/psqlbackups/2023-05-21/base.tar.gz
-rw------- postgres/postgres 226 2023-05-21 06:52 backup_label
-rw------- postgres/postgres   0 2023-05-21 06:52 tablespace_map
drwx------ postgres/postgres   0 2022-10-31 06:27 pg_replslot/
drwx------ postgres/postgres   0 2022-10-31 06:27 pg_twophase/
drwx------ postgres/postgres   0 2022-10-31 06:27 pg_dynshmem/
drwx------ postgres/postgres   0 2023-05-13 09:45 pg_xact/
-rw------- postgres/postgres 262144 2023-04-20 16:20 pg_xact/0000
-rw------- postgres/postgres 229376 2023-05-21 06:50 pg_xact/0004
-rw------- postgres/postgres 262144 2023-05-05 23:32 pg_xact/0002
-rw------- postgres/postgres 262144 2023-04-28 02:18 pg_xact/0001
-rw------- postgres/postgres 262144 2023-05-13 09:45 pg_xact/0003
drwx------ postgres/postgres      0 2023-05-21 06:52 pg_wal/
drwx------ postgres/postgres      0 2023-05-21 06:52 ./pg_wal/archive_status/
drwx------ postgres/postgres      0 2022-10-31 06:27 pg_serial/
drwx------ postgres/postgres      0 2022-10-31 06:27 pg_snapshots/
drwx------ postgres/postgres      0 2022-10-31 06:27 pg_commit_ts/
-rw------- postgres/postgres      3 2022-10-31 06:27 PG_VERSION
drwx------ postgres/postgres      0 2023-05-20 06:59 pg_subtrans/
drwx------ postgres/postgres      0 2023-05-20 22:05 pg_stat/
drwx------ postgres/postgres      0 2023-04-14 06:42 base/
drwx------ postgres/postgres      0 2023-05-20 22:05 base/1/
-rw------- postgres/postgres  24576 2022-10-31 06:27 base/1/3394_fsm
-rw------- postgres/postgres   8192 2022-10-31 06:27 base/1/13321
....

Next, I’ll restore the base dir

postgres@builder-T100:~$ cd  /var/lib/postgresql/12/
postgres@builder-T100:~/12$ ls
main
postgres@builder-T100:~/12$ mv main main.old

postgres@builder-T100:~/12$ mkdir /var/lib/postgresql/12/main

postgres@builder-T100:~/12$ tar xvf /mnt/psqlbackups/2023-05-21/base.tar.gz -C /var/lib/postgresql/12/main
backup_label
tablespace_map
pg_replslot/
...

And the wal files

postgres@builder-T100:~/12$ tar xvf /mnt/psqlbackups/2023-05-21/pg_wal.tar.gz -C /var/lib/postgresql/12/main/pg_wal
00000001000000030000005D
archive_status/00000001000000030000005D.done
00000001000000030000005E

Now start PSQL

builder@builder-T100:~$ sudo systemctl start postgresql.service
builder@builder-T100:~$ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2023-05-22 06:32:41 CDT; 5s ago
    Process: 260601 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 260601 (code=exited, status=0/SUCCESS)

May 22 06:32:41 builder-T100 systemd[1]: Starting PostgreSQL RDBMS...
May 22 06:32:41 builder-T100 systemd[1]: Finished PostgreSQL RDBMS.
builder@builder-T100:~$ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2023-05-22 06:32:41 CDT; 9s ago
    Process: 260601 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 260601 (code=exited, status=0/SUCCESS)

May 22 06:32:41 builder-T100 systemd[1]: Starting PostgreSQL RDBMS...
May 22 06:32:41 builder-T100 systemd[1]: Finished PostgreSQL RDBMS.

I saw it crashed, seems permissions related

builder@builder-T100:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
builder@builder-T100:~$ tail -f /var/log/postgresql/postgresql-12-main.log
2023-05-22 06:00:15.605 CDT [721] LOG:  database system is ready to accept connections
2023-05-22 06:25:52.130 CDT [721] LOG:  received fast shutdown request
2023-05-22 06:25:52.131 CDT [721] LOG:  aborting any active transactions
2023-05-22 06:25:52.132 CDT [721] LOG:  background worker "logical replication launcher" (PID 729) exited with exit code 1
2023-05-22 06:25:52.133 CDT [724] LOG:  shutting down
2023-05-22 06:25:52.143 CDT [721] LOG:  database system is shut down
2023-05-22 06:32:41.346 CDT [260574] FATAL:  data directory "/var/lib/postgresql/12/main" has invalid permissions
2023-05-22 06:32:41.346 CDT [260574] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
pg_ctl: could not start server
Examine the log output.

I fixed and checked the ownership, looks good now

builder@builder-T100:~$ sudo chmod 750 /var/lib/postgresql/12/main
builder@builder-T100:~$ ls -ltra /var/lib/postgresql/12/main
ls: cannot open directory '/var/lib/postgresql/12/main': Permission denied
builder@builder-T100:~$ sudo ls -ltra /var/lib/postgresql/12/main
total 88
-rw-------  1 postgres postgres   88 Oct 31  2022 postgresql.auto.conf
-rw-------  1 postgres postgres    3 Oct 31  2022 PG_VERSION
drwx------  2 postgres postgres 4096 Oct 31  2022 pg_twophase
drwx------  2 postgres postgres 4096 Oct 31  2022 pg_tblspc
drwx------  2 postgres postgres 4096 Oct 31  2022 pg_snapshots
drwx------  2 postgres postgres 4096 Oct 31  2022 pg_serial
drwx------  2 postgres postgres 4096 Oct 31  2022 pg_replslot
drwx------  4 postgres postgres 4096 Oct 31  2022 pg_multixact
drwx------  2 postgres postgres 4096 Oct 31  2022 pg_dynshmem
drwx------  2 postgres postgres 4096 Oct 31  2022 pg_commit_ts
drwx------  9 postgres postgres 4096 Apr 14 06:42 base
drwx------  2 postgres postgres 4096 May 13 09:45 pg_xact
drwx------  2 postgres postgres 4096 May 20 06:59 pg_subtrans
drwx------  2 postgres postgres 4096 May 20 22:05 pg_stat_tmp
drwx------  2 postgres postgres 4096 May 20 22:05 pg_stat
drwx------  2 postgres postgres 4096 May 20 22:05 pg_notify
drwx------  2 postgres postgres 4096 May 21 06:27 global
-rw-------  1 postgres postgres    0 May 21 06:52 tablespace_map
drwx------  4 postgres postgres 4096 May 21 06:52 pg_logical
-rw-------  1 postgres postgres  226 May 21 06:52 backup_label
drwxr-xr-x  4 postgres postgres 4096 May 22 06:30 ..
drwxr-x--- 19 postgres postgres 4096 May 22 06:30 .
drwx------  3 postgres postgres 4096 May 22 06:32 pg_wal
builder@builder-T100:~$

I actually had to change that to 700 (750 did not work)

builder@builder-T100:~$ ls -ltra //var/lib/postgresql/12
total 16
drwx------ 19 postgres postgres 4096 May 22 06:25 main.old
drwxr-xr-x  4 postgres postgres 4096 May 22 06:30 .
drwxr-x--- 19 postgres postgres 4096 May 22 06:30 main
drwxr-xr-x  3 postgres postgres 4096 May 22 06:32 ..
builder@builder-T100:~$ sudo chmod 700 /var/lib/postgresql/12/main
builder@builder-T100:~$ sudo systemctl stop postgresql.service
builder@builder-T100:~$ sudo systemctl start postgresql.service


builder@builder-T100:~$ tail -f /var/log/postgresql/postgresql-12-main.log
Examine the log output.
2023-05-22 06:35:25.565 CDT [283508] LOG:  starting PostgreSQL 12.15 (Ubuntu 12.15-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
2023-05-22 06:35:25.565 CDT [283508] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-05-22 06:35:25.566 CDT [283508] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-05-22 06:35:25.578 CDT [283513] LOG:  database system was interrupted; last known up at 2023-05-21 06:52:36 CDT
2023-05-22 06:35:25.695 CDT [283513] LOG:  redo starts at 3/5D000028
2023-05-22 06:35:25.698 CDT [283513] LOG:  consistent recovery state reached at 3/5D0D3178
2023-05-22 06:35:25.698 CDT [283513] LOG:  invalid record length at 3/5E0000E8: wanted 24, got 0
2023-05-22 06:35:25.698 CDT [283513] LOG:  redo done at 3/5E0000C0
2023-05-22 06:35:25.721 CDT [283508] LOG:  database system is ready to accept connections

And we can see we are online

builder@builder-T100:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log

I can of course login and see the backup is solid

builder@builder-T100:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
12  main    5432 online postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
builder@builder-T100:~$ sudo su - postgres
postgres@builder-T100:~$ psql
psql (12.15 (Ubuntu 12.15-1.pgdg20.04+1))
Type "help" for help.

postgres=# \l
                                    List of databases
     Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
---------------+----------+----------+-------------+-------------+-----------------------
 notary_server | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
               |          |          |             |             | postgres=CTc/postgres+
               |          |          |             |             | harbor=CTc/postgres  +
               |          |          |             |             | instana=c/postgres
 notary_signer | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
               |          |          |             |             | postgres=CTc/postgres+
               |          |          |             |             | harbor=CTc/postgres  +
               |          |          |             |             | instana=c/postgres
 postgres      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
               |          |          |             |             | postgres=CTc/postgres+
               |          |          |             |             | instana=CTc/postgres
 registry      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
               |          |          |             |             | postgres=CTc/postgres+
               |          |          |             |             | harbor=CTc/postgres  +
               |          |          |             |             | instana=c/postgres
 template0     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres+
               |          |          |             |             | instana=c/postgres
 template1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres+
               |          |          |             |             | instana=c/postgres
 zabbix        | zabbix   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)

Next Steps

If I were to need to use this host, I would have to ensure my hba.conf is set properly, the host itself has firewalls disabled or at least allows 4321 through for Postgres and change the calling services.

The more likely scenario is that the host server crashed and I rebuilt, and I want a point in time snapshot.

My other step would (will) be to setup a crontab to schedule backups, something similar to that which is covered in this SO post. For now, I’m happy with a single point in time.

Summary

Today we covered backing up PostgreSQL 12 using pg_basebackup, sending it to an NFS share, then restoring to a different Linux host. This is a pretty basic DR flow we can build upon later.