Deploying PostgreSQL with Docker on VPS: A Beginner’s Guide

Docker simplifies the deployment and management of PostgreSQL on a VPS by isolating the database within a container. This method offers a clean and efficient way to run PostgreSQL without worrying about system dependencies or configurations, as everything is encapsulated within the container environment.

Using Docker for PostgreSQL on a VPS brings several benefits. It allows you to quickly deploy PostgreSQL, manage it with ease, and scale as needed. Containers ensure that the database runs consistently across different environments, whether it’s on a development machine or a production VPS. Additionally, Docker containers can be easily backed up, restored, and migrated, providing flexibility in managing your PostgreSQL database.

In this guide, we’ll walk through setting up PostgreSQL inside a Docker container on your VPS, ensuring that your database is secure, optimized, and ready for use.

Setting Up Docker on VPS for PostgreSQL Deployment

To deploy PostgreSQL in Docker on your VPS, the first step is to ensure that Docker is properly installed and configured. Below is a step-by-step guide to help you set up Docker and prepare it for PostgreSQL deployment.

Update the VPS

Start by updating your VPS to ensure that all packages are up to date. This helps avoid issues during the Docker installation.

sudo apt update && sudo apt upgrade -y

Install Docker

Once your system is updated, install Docker by following these steps. Docker provides an easy-to-follow installation script that simplifies the process.

Run the following commands to install Docker:

sudo apt install apt-transport-https ca-certificates curl software-properties-common -y
curl -fsSL https://get.docker.com -o get-docker.sh
sudo sh get-docker.sh

This script will install Docker from the official repository.

Verify Docker Installation

After installation, verify that Docker is running correctly:

sudo systemctl status docker

You should see Docker’s status as active and running. To confirm Docker’s version:

docker --version

Install Docker Compose (Optional)

If you plan to use Docker Compose for managing multiple containers, you should install it as well. Docker Compose allows you to define multi-container applications using a simple YAML file.

To install Docker Compose:

sudo curl -L "https://github.com/docker/compose/releases/download/1.29.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
sudo chmod +x /usr/local/bin/docker-compose

Check the installation:

docker-compose --version

Allow Non-root User to Run Docker (Optional)

If you don’t want to use sudo every time you run Docker commands, you can add your user to the Docker group:

sudo usermod -aG docker $USER

After this, log out and back in for the changes to take effect.

With Docker successfully installed and configured, you’re now ready to deploy PostgreSQL inside a Docker container on your VPS.

Pulling and Running the PostgreSQL Docker Image

Once Docker is set up on your VPS, the next step is to pull the official PostgreSQL Docker image and run it in a container. This will give you a fully functional PostgreSQL instance without the need for manual installation.

Pull the PostgreSQL Docker Image

Docker Hub contains the official PostgreSQL image. To pull the latest version, run the following command:

docker pull postgres

This command downloads the official PostgreSQL Docker image from Docker Hub. If you need a specific version, you can specify the version tag like this:

docker pull postgres:13

Replace 13 with the version you need. This will download PostgreSQL version 13.

Run the PostgreSQL Container

After pulling the image, you can now run it in a container. To start a new PostgreSQL container with the default settings, use the following command:

docker run --name postgres-container -e POSTGRES_PASSWORD=mysecretpassword -d postgres

This command does the following:

• --name postgres-container: Names the container “postgres-container.”
• -e POSTGRES_PASSWORD=mysecretpassword: Sets the password for the default PostgreSQL user postgres.
• -d: Runs the container in detached mode (in the background).
• postgres: Specifies the PostgreSQL image to use.

Verify the Container is Running

You can check if your PostgreSQL container is running by using the following command:

docker ps

This will show a list of all running containers. You should see your postgres-container listed.

Connect to PostgreSQL Inside the Container

To access PostgreSQL inside the container, run the following command to start a psql session:

docker exec -it postgres-container psql -U postgres

This opens an interactive PostgreSQL session where you can run SQL queries.

Exposing PostgreSQL to External Connections (Optional)

If you want to access PostgreSQL from outside the container, such as from your local machine, you need to map the container’s port to a port on your VPS. You can do this when running the container:

docker run --name postgres-container -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

Here, the -p 5432:5432 option maps the container’s port 5432 (default PostgreSQL port) to port 5432 on the VPS, allowing external connections.

Configuring Persistent Storage for PostgreSQL Containers

By default, data stored in Docker containers is not persistent. This means that if the container is stopped or deleted, all the data inside it will be lost. To ensure your PostgreSQL data remains intact even after container restarts or re-creations, you need to configure persistent storage using Docker volumes.

Step 1: Create a Docker Volume for PostgreSQL Data

First, create a Docker volume to store your PostgreSQL data. Docker volumes are designed for persistent storage, and they are stored outside the container filesystem.

To create a volume, run:

docker volume create postgres-data

This creates a volume named postgres-data which will be used to store the PostgreSQL database files.

Step 2: Run PostgreSQL Container with the Volume

Next, when running the PostgreSQL container, map the created volume to the container’s data directory /var/lib/postgresql/data. This ensures that PostgreSQL will use the volume to store its data.

Use the following command to start the PostgreSQL container with the volume:

docker run --name postgres-container -e POSTGRES_PASSWORD=mysecretpassword -v postgres-data:/var/lib/postgresql/data -p 5432:5432 -d postgres

Here’s what happens in this command:

• -v postgres-data:/var/lib/postgresql/data: Maps the postgres-data volume to the /var/lib/postgresql/data directory inside the container. This ensures that all PostgreSQL data will be stored in the volume.
• -p 5432:5432: Exposes the PostgreSQL port 5432 for external access.

Step 3: Verify Persistent Storage

To verify that the volume is working correctly, you can check the data inside the container by entering the PostgreSQL database:

docker exec -it postgres-container psql -U postgres

Create a test database or table, then stop and restart the container to ensure the data persists.

Stop the container:

docker stop postgres-container

Start the container again:

docker start postgres-container

After restarting, you can log in to PostgreSQL again and check if your data is still there.

Step 4: Backup and Restore with Docker Volumes

Another advantage of using Docker volumes is the ability to easily back up and restore data. You can back up the volume by creating a tarball of the volume data.

To back up the volume:

docker run --rm -v postgres-data:/data -v $(pwd):/backup ubuntu tar czf /backup/backup.tar.gz /data

This command creates a backup file backup.tar.gz in the current directory.

To restore the backup:

docker run --rm -v postgres-data:/data -v $(pwd):/backup ubuntu bash -c "tar xzf /backup/backup.tar.gz -C /data"

This restores the backup into the postgres-data volume.

Connecting to the PostgreSQL Container

Once your PostgreSQL container is up and running, you may need to connect to it in order to manage your databases, run queries, or perform administrative tasks. There are several ways to connect to a PostgreSQL container.

Accessing the Container via docker exec

The simplest method to connect to your running PostgreSQL container is using the docker exec command, which allows you to run commands inside the container.

To connect to the PostgreSQL container and access its command-line interface, use the following command:

docker exec -it postgres-container psql -U postgres

• docker exec -it postgres-container: This part of the command connects to the running postgres-container.
• psql -U postgres: This tells PostgreSQL to log in using the postgres user.

You should now be inside the PostgreSQL prompt, where you can run SQL commands and manage your databases.

Connecting from Outside the Container

In case you want to connect to the PostgreSQL instance from outside the container (for example, from your local machine or a different server), ensure that the PostgreSQL port (5432 by default) is exposed to the host machine when starting the container.

For example, if you used this command to run the container:

docker run --name postgres-container -e POSTGRES_PASSWORD=mysecretpassword -v postgres-data:/var/lib/postgresql/data -p 5432:5432 -d postgres

You can now connect to PostgreSQL using the following command from another machine or locally:

psql -h <host-ip> -U postgres -d postgres

Replace <host-ip> with the IP address of the machine where the PostgreSQL container is running. The default username is postgres, and you will be prompted to enter the password (mysecretpassword if you used the above example).

Connecting via pgAdmin or GUI Tools

You can also connect to the PostgreSQL container using GUI tools like pgAdmin. Just enter the IP address of the host machine and the appropriate credentials (username postgres, password as set during container setup), and you should be able to interact with your PostgreSQL databases.

Using Docker to deploy PostgreSQL on a VPS offers several advantages, making it a powerful solution for managing databases in a containerized environment. By leveraging Docker, you can easily set up, scale, and maintain PostgreSQL without worrying about complex configurations or dependency management. Docker provides a consistent and isolated environment for your PostgreSQL databases, ensuring that they run smoothly across different systems.