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.
