Postgres

Let’s install and configure PgHero and PgAdmin4 in docker to connect to the databases deloyed in the previous post. If you don’t have docker installed, now is the time to do it. PgHero: Create the config for PgHero pghero.yml : databases: database1: url: postgres://dummy:123456@10.0.0.3:5432/postgres database2: url: postgres://dummy:123456@10.0.0.4:5432/postgres And run the docker command to start it: docker run -it -d -v $(pwd)/pghero.yml:/app/config/pghero.yml -p 8080:8080 ankane/pghero If everything is ok, you should see:...

Create 3 servers on the cloud of choice. This is a diagram of the servers: Node 1 On Node 1 we install postgres, start the postgresql service and add the port to firewall: sudo apt install -y postgresql sudo systemctl enable postgresql sudo systemctl start postgresql sudo systemctl status postgresql sudo ufw allow from 10.0.0.0/16 to any port 5432 Next, we open psql to create the replication user: sudo -u postgres psql CREATE USER ruser REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'rpassword'; CREATE USER dummy WITH SUPERUSER PASSWORD '123456'; We should open /etc/postgresql/14/main/pg_hba....

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....

Do you ever want to run a CLI before your main app in Docker? Here is a complicated way to do just that. We need a folder named success_flag that will contain a flag for the main app to know when the cli has finished running. First, we delete any existing success flag that remains: base: build: . container_name: base volumes: - ./success_flag:/success_flag command: bash -c "rm -f /success_flag/cli_success" restart: "no" Second, let’s run the database layer, in this case Postgres (but it can be any database engine)....