Backup PostgreSQL DB

Contents

  1. 1 Overview
  2. 2 Access to PostgreSQL Server
    1. 2.1 Password-Less Access
    2. 2.2 Listening Port
  3. 3 Backup All DB
    1. 3.1 Find User Databases
    2. 3.2 Backup User Databases
    3. 3.3 Contents of TOC.DAT
  4. 4 Backup Global Settings
  5. 5 Backup Configuration Files
  6. 6 Outline of Server Restoration
  7. 7 References
  8. 8 File Downloads

Overview

This procedure does a full backup of all user databases in a PostgreSQL 14 server.

Access to PostgreSQL Server

Password-Less Access

The user, postgres , can access the PostgreSQL 14 server without needing to provide a password because of the following settings in /etc/postgresql/14/main/pg_hba.conf : 

local   all             postgres                                peer

This is set up by default.

Listening Port

The PostgreSQL 14 server is listening on port 5434.

I had originally installed PostgreSQL 12 and upgraded from there. The next iteration of the PostgreSQL software chose the next available port after the default of 5433.

Backup All DB

Find User Databases

I used the following command as the postgres user to find all database in the PostgreSQL 14 server (listening on port 5434): 

/usr/lib/postgresql/14/bin/psql \
    --port=5434 \
    --command '\l'

The output is: 

                                     List of databases
      Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------------+----------+----------+-------------+-------------+-----------------------
 books           | douglas  | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 douglas         | postgres | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 postgres        | postgres | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 postgresql_book | douglas  | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | 
 template0       | postgres | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | =c/postgres          +
                 |          |          |             |             | postgres=CTc/postgres
 template1       | postgres | UTF8     | en_AU.UTF-8 | en_AU.UTF-8 | =c/postgres          +
                 |          |          |             |             | postgres=CTc/postgres
(6 rows)

The following databases are installed when the initdb is run, and are not considered user databases:

Thus, the following databases need to be backed up:

Backup User Databases

I used the following commands as the postgres user to backup all user databases in the PostgreSQL 14 server. The directories used be empty : 

mkdir ~/backups
cd ~/backups 
for db_name in books douglas postgresql_book
do /usr/lib/postgresql/14/bin/pg_dump \
    --port=5434 \
    --blobs \
    --create \
    --file=pg_14_${db_name}_backup \
    --format=custom \
    --format=directory \
    --jobs=6 \
    --verbose \
    ${db_name}
done

The output has been uploaded as full_backup_all_14_db.log . The list of files can be found in full_backup_files.lst .

Contents of TOC.DAT

As can be seen from the listings in full_backup_files.lst , there is a toc.dat in every backup directory. The printable contents of one of these ( backup/pg_14_douglas_backup/toc.dat ) is: 

PGDMP
douglas
14.11 (Ubuntu 14.11-1.pgdg22.04+1)
14.11 (Ubuntu 14.11-1.pgdg22.04+1)
ENCODING
ENCODING
SET client_encoding = 'UTF8';
false
STDSTRINGS
STDSTRINGS
SET standard_conforming_strings = 'on';
false
SEARCHPATH
SEARCHPATH
SELECT pg_catalog.set_config('search_path', '', false);
false
1262
16480
douglas
DATABASE
CREATE DATABASE douglas WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'en_AU.UTF-8';
DROP DATABASE douglas;
postgres
false
3079
16481
adminpack
EXTENSION
CREATE EXTENSION IF NOT EXISTS adminpack WITH SCHEMA pg_catalog;
DROP EXTENSION adminpack;
false
EXTENSION adminpack
COMMENT
COMMENT ON EXTENSION adminpack IS 'administrative functions for PostgreSQL';
false

Backup Global Settings

I used the following commands as the postgres user to backup the global settings for the PostgreSQL 14 server running on port 5434: 

cd ~/backups
pg_dumpall --globals-only --file=globals.backup

The editted contents of globals.backup are: 

--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE books;
ALTER ROLE books WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:****************************************************************************************************=';
CREATE ROLE douglas;
ALTER ROLE douglas WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:****************************************************************************************************=';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:***************************************************************************************************=';




--
-- Tablespaces
--

CREATE TABLESPACE books OWNER douglas LOCATION '/var/lib/postgresql/14/main';


--
-- PostgreSQL database cluster dump complete
--

Backup Configuration Files

I used the following commands to backup the configuration files for the PostgreSQL 14 server: 

cd /etc/postgresql/14/main
postgres@coogee:/etc/postgresql/14/main$ tar czvf ~/backup/settings.tgz .

The following files and directories are backed up: 

./
./start.conf
./postgresql.conf
./conf.d/
./pg_hba.conf
./pg_ctl.conf
./pg_ident.conf
./environment

Outline of Server Restoration

The general steps to restore the PostgreSQL 14 server are:

  1. Install PostgreSQL 14 software, if required. This will create a default server with three (3) databases.
  2. Restore configuration files, if required. The installation of the PostgreSQL 14 software will create default configuration files.
  3. Start PostgreSQL 14 server, if required. The installation of the PostgreSQL 14 software will start the server automatically.
  4. Restore global settings.
  5. Restore all user databases.

References