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:
Next, we should create our S3 access key and secret:
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:
...