How can you backup your PostgreSQL to a Hetzner S3 object storage ?

Here is how it’s done.

Creating S3 Object Storage

First, let’s create our S3 Object Storage:

s3-storage-hetzner

Next, we should create our S3 access key and secret:

s3-storage-credentials

Backup and restore

Let’s create a database with sample data:

-- Create the database
CREATE DATABASE music_streaming_platform;
\c music_streaming_platform;

-- Create users table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    date_joined DATE DEFAULT CURRENT_DATE,
    country VARCHAR(50)
);

-- Create artists table
CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    artist_name VARCHAR(100) UNIQUE NOT NULL,
    genre VARCHAR(50),
    country VARCHAR(50),
    active_since DATE
);

-- Create albums table
CREATE TABLE albums (
    album_id SERIAL PRIMARY KEY,
    album_name VARCHAR(100) NOT NULL,
    release_date DATE,
    artist_id INT REFERENCES artists(artist_id) ON DELETE CASCADE,
    genre VARCHAR(50)
);

-- Create songs table
CREATE TABLE songs (
    song_id SERIAL PRIMARY KEY,
    song_name VARCHAR(100) NOT NULL,
    duration TIME NOT NULL,
    album_id INT REFERENCES albums(album_id) ON DELETE CASCADE,
    plays INT DEFAULT 0,
    release_date DATE
);

-- Create playlists table
CREATE TABLE playlists (
    playlist_id SERIAL PRIMARY KEY,
    playlist_name VARCHAR(100) NOT NULL,
    user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create playlist_songs table (many-to-many relationship between playlists and songs)
CREATE TABLE playlist_songs (
    playlist_id INT REFERENCES playlists(playlist_id) ON DELETE CASCADE,
    song_id INT REFERENCES songs(song_id) ON DELETE CASCADE,
    PRIMARY KEY (playlist_id, song_id)
);

-- Insert data into users table
INSERT INTO users (username, email, country)
VALUES 
    ('musiclover_101', 'lover101@example.com', 'USA'),
    ('rockfanatic', 'rockfan@example.com', 'UK'),
    ('classicbuff', 'classicbuff@example.com', 'Germany');

-- Insert data into artists table
INSERT INTO artists (artist_name, genre, country, active_since)
VALUES 
    ('The Electric Beats', 'Rock', 'USA', '2005-06-15'),
    ('Synthwave Dream', 'Electronic', 'UK', '2012-04-20'),
    ('Classical Vibes', 'Classical', 'Germany', '1995-09-25');

-- Insert data into albums table
INSERT INTO albums (album_name, release_date, artist_id, genre)
VALUES 
    ('Rock Revolution', '2018-07-22', 1, 'Rock'),
    ('Synthwave Sunrise', '2020-03-15', 2, 'Electronic'),
    ('Timeless Classics', '2000-11-12', 3, 'Classical');

-- Insert data into songs table
INSERT INTO songs (song_name, duration, album_id, plays, release_date)
VALUES 
    ('Thunderstrike', '00:03:45', 1, 52340, '2018-07-22'),
    ('Neon Dreams', '00:04:12', 2, 78423, '2020-03-15'),
    ('Symphony No. 5', '00:07:30', 3, 152340, '2000-11-12'),
    ('Rock On', '00:03:55', 1, 62340, '2018-07-22'),
    ('Night Drive', '00:04:55', 2, 46450, '2020-03-15');

-- Insert data into playlists table
INSERT INTO playlists (playlist_name, user_id)
VALUES 
    ('Morning Rock Hits', 1),
    ('Relaxing Classical', 3),
    ('Electronic Vibes', 2);

-- Insert data into playlist_songs table
INSERT INTO playlist_songs (playlist_id, song_id)
VALUES 
    (1, 1),  -- Morning Rock Hits -> Thunderstrike
    (1, 4),  -- Morning Rock Hits -> Rock On
    (2, 3),  -- Relaxing Classical -> Symphony No. 5
    (3, 2),  -- Electronic Vibes -> Neon Dreams
    (3, 5);  -- Electronic Vibes -> Night Drive

-- Query to check all data
SELECT * FROM users;
SELECT * FROM artists;
SELECT * FROM albums;
SELECT * FROM songs;
SELECT * FROM playlists;
SELECT * FROM playlist_songs;

The database contains:

  • users: Contains information about platform users (ID, username, email, date joined, country)
  • artists: Stores information about artists (ID, name, genre, country, and when they started)
  • albums: Holds album data (ID, name, release date, associated artist)
  • songs: Stores song data (ID, name, duration, album, plays count, release date)
  • playlists: Contains user-generated playlists (ID, name, user who created it, creation timestamp)
  • playlist_songs: A many-to-many relationship between playlists and songs

Now let’s performs backups of a PostgreSQL database to an S3-compatible storage using eeshugerman/postgres-backup-s3 docker image:

docker run \
  -e S3_REGION=US \
  -e S3_ACCESS_KEY_ID=key \
  -e S3_SECRET_ACCESS_KEY=secret \
  -e S3_BUCKET=pg-bkp \
  -e S3_PREFIX=backup \
  -e POSTGRES_HOST=localhost \
  -e POSTGRES_DATABASE=music_streaming_platform \
  -e POSTGRES_USER=dummy \
  -e POSTGRES_PASSWORD=123456 \
  -e S3_ENDPOINT=https://fsn1.your-objectstorage.com \
  --net=host \
  eeshugerman/postgres-backup-s3:16

S3 Configuration needed :

  • S3_REGION=US: Specifies the region where the S3 bucket is located (in this case, US).
  • S3_ACCESS_KEY_ID=key: The access key ID used to authenticate with the S3-compatible storage service.
  • S3_SECRET_ACCESS_KEY=secret: The secret access key used for authentication, paired with the access key ID.
  • S3_BUCKET=pg-bkp: The name of the S3 bucket where backups will be stored.
  • S3_PREFIX=backup: A folder-like prefix within the S3 bucket where backups will be saved. Think of it as a directory path.
  • S3_ENDPOINT=https://fsn1.your-objectstorage.com: The endpoint URL for the S3-compatible object storage service. It’s used for non AWS S3 compatible storage

If everything it’s ok, then you’ll see:

Creating backup of music_streaming_platform database...
Uploading backup to pg-bkp-xxxx...
upload: ./db.dump to s3://pg-bkp-xxxx/backup/music_streaming_platform_2024-10-12T09:42:21.dump
Backup complete.

Now let’s drop the database and create an empty one:

DROP DATABASE music_streaming_platform;
CREATE DATABASE music_streaming_platform;

docker run \
  -e S3_REGION=US \
  -e S3_ACCESS_KEY_ID=key \
  -e S3_SECRET_ACCESS_KEY=secret \
  -e S3_BUCKET=pg-bkp \
  -e S3_PREFIX=backup \
  -e POSTGRES_HOST=localhost \
  -e POSTGRES_DATABASE=music_streaming_platform \
  -e POSTGRES_USER=dummy \
  -e POSTGRES_PASSWORD=123456 \
  -e S3_ENDPOINT=https://fsn1.your-objectstorage.com \
  --net=host \
  eeshugerman/postgres-backup-s3:16 \
  sh restore.sh

If everything it’s ok, then you’ll see:

Finding latest backup...
Fetching backup from S3...
download: s3://pg-bkp-xxxx/backup/music_streaming_platform_2024-10-12T09:42:21.dump to ./db.dump
Restoring from backup...
Restore complete.

And in S3 object storage:

backup

Now we can connect to Postgres and see our data:

sudo -u postgres psql

 \c music_streaming_platform
You are now connected to database "music_streaming_platform" as user "postgres".
music_streaming_platform=# \dt
             List of relations
 Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
 public | albums         | table | postgres
 public | artists        | table | postgres
 public | playlist_songs | table | postgres
 public | playlists      | table | postgres
 public | songs          | table | postgres
 public | users          | table | postgres
(6 rows)

music_streaming_platform=# select * from users;
 user_id |    username    |          email          | date_joined | country
---------+----------------+-------------------------+-------------+---------
       1 | musiclover_101 | lover101@example.com    | 2024-10-12  | USA
       2 | rockfanatic    | rockfan@example.com     | 2024-10-12  | UK
       3 | classicbuff    | classicbuff@example.com | 2024-10-12  | Germany
(3 rows)