In this tutorial, we'll walk you through the process of setting up PostgreSQL 16 using Docker Compose. PostgreSQL is a powerful, open-source relational database system, widely used for its robustness and flexibility. We'll show you how to define a docker-compose.yml file to set up the PostgreSQL service, automatically create a database, and insert predefined data upon initialization.
By the end of this guide, you'll have a running PostgreSQL instance with predefined data ready for your applications. Whether you're a developer looking to quickly set up a local database for testing or you're deploying a service with a database that requires initial data, this tutorial will provide you with the tools to get started efficiently.
Key Takeaways:
- Understanding the basics of Docker Compose for PostgreSQL.
- Creating and configuring a docker-compose.yml file.
- Automating database initialization with custom SQL scripts.
- Ensuring data persistence with Docker volumes.
This step-by-step guide is perfect for developers, database administrators, and DevOps professionals who want to leverage Docker for their database needs.
Prerequisites
- A Ubuntu 24.04 installed dedicated server or KVM VPS.
- A root user access or normal user with sudo rights.
- Basic knowledge of Docker.
Setting Up PostgreSQL 16 Using Docker Compose
Step 1: Update System
Keep the server updated.
sudo apt update
Step 2: Install Docker
We need to install Docker. If you have already installed Docker in your system, you can skip this step.
Following commands are copied from official website:
# Add Docker's official GPG key:
sudo apt-get install ca-certificates curl -y
sudo install -m 0755 -d /etc/apt/keyrings
sudo curl -fsSL https://download.docker.com/linux/ubuntu/gpg -o /etc/apt/keyrings/docker.asc
sudo chmod a+r /etc/apt/keyrings/docker.asc
echo \
"deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.asc] https://download.docker.com/linux/ubuntu \
$(. /etc/os-release && echo "$VERSION_CODENAME") stable" | \
sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt-get update
To install the latest version, run:
sudo apt-get install docker-ce -y
Step 3: Create the docker-compose.yml File
Create a docker-compose.yml file. This file will define the services for PostgreSQL database.
nano docker-compose.yml
Add following content:
version: '3.8'
services:
postgres:
image: postgres:16
container_name: postgres
environment:
POSTGRES_USER: myuser
POSTGRES_PASSWORD: mypassword
POSTGRES_DB: mydatabase
volumes:
- postgres_data:/var/lib/postgresql@16/data
- ./init:/docker-entrypoint-initdb.d/
ports:
- "5432:5432"
volumes:
postgres_data:
Step 4: Create the Initialization Script
In the same directory as your docker-compose.yml file, create a folder named initdb. Inside initdb, create an SQL script file named init.sql:
mkdir init
Next, create init file:
nano init/init.sql
Add following content:
-- Create a table
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
-- Insert predefined data
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com');
Save the docker-compose.yml and init.sql files in the same directory.
Step 5: Execute Docker Compose
Now, you can deploy the Docker containers using Docker Compose.
docker compose up -d
This will set up PostgreSQL with your specified configuration and insert the predefined data into the database.
Step 6: Verify Database
To verify that the database and table is created and inserted the values, execute following set of commands:
Check the container id:
docker ps
Copy the containter id use it in the following command:
docker exec -it [CONTAINER ID] psql -U myuser mydatabase
List the databases:
\l
Next, connect the the database:
\c mydatabase
List the tables
\dt
Check the data
SELECT * FROM users;
That's it we've have successfully seen the process of setting up PostgreSQL 16 using Docker Compose.