Postgres in Docker - A Practical Guide

If you’re building an application that uses PostgreSQL in production for data storage, then at some point you are probably going to need to run PostgreSQL for development on your local machine.

Sometimes it’s possible to use something like SQLite in development, and Postgres in production, but once you get serious about your app, you’ll want your environments to match.

Local Development Postgres Docker Example

I prefer using docker-compose rather than the docker cli, but that choice is up to you. I present both here.

I recommend changing the image used here to match the same version you use in production, for example postgres:9.4 instead of postgres:latest.

Docker CLI PostgreSQL

docker run -d \
  --name psql \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=example \
  -v psql:/var/lib/postgresql/data \
  -p 5555:5432 \
  postgres:latest

This will start your PostgreSQL server and have it be available on localhost:5555. Put it in a script like dev/postgres and start it whenever you start your app.

Docker Compose PostgreSQL

I prefer using Docker Compose, because it makes it easy to change the database configuration, and add more services (eg Redis) whenever I need them.

version: '3'
volumes:
  psql:
services:
  psql:
    image: postgres:latest
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: example # only do this in development
    volumes:
      - psql:/var/lib/postgresql/data # save data between restarts
    ports:
      - 5555:5432 # binds port 5555 on your computer to the postgres port!

Again, your development database will be available on localhost:5555

Recommendations for Local Postgres Development

We recommend managing development postgres in the following ways:

1. Be sure you are using the official Postgres image.
2. Store your postgres config in a Docker Compose file (see below for an example).
3. Check the docker compose file into source control (like Git) along side your project source code.
4. Have everyone on your team use the same settings for postgres in development.
5. Bootstrap your postgres image (if you need to), using some of the more advanced features of the docker image.
6. Make sure to specify a volume for the container to use to store data, that way it persists across runs.
7. Use the built in features of the image to run some bootstrap SQL, set credentials, and create databases.

Docker Vs System Installation Of Postgres

It’s possible to just install Postgres on your computer. There are builds for all three major operating systems. That seems like the easiest way, but there are a few problems with this approach.

Problems with A System Level Install of Postgres

• Postgres will always be running as a system service
• You can easily have one version of Postgres installed and running at a time
• Every app that needs it’s own Postgres database will need to share the same instance
• Requires root/admin access to your machine
• If you work on a team, each setup might be slightly different, which makes sharing dev configs hard.

Docker Solves (Most) Of These Problems

With Docker each project under development can have it’s own Postgres locked to the correct version, with a standard setup that is shared across team mates and can be checked-in to source control.

The biggest problem with running Postgres in a Docker container is, well, learning Docker if you’ve never used it before.

Using Docker for Postgres in Production

If you’re using straight Docker for production, having a Postgres database is pretty similar to our development configuration

version: '3.8'
volumes:
  psql_data:
    driver: local
services:
  postgres:
    image: postgres:14
    container_name: postgres
    restart: always
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mysecurepassword
      POSTGRES_DB: mydatabase
    volumes:
      - psql_data:/var/lib/postgresql/data
      - ./backups:/backups
    ports:
      - 5432:5432
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myuser"]
      interval: 30s
      timeout: 10s
      retries: 5

Backup and Restore Postgres Databases In Docker

The below is not a thorough walkthrough of how to manage production Postgres, but should serve as a simple starting point for a simple backup strategy.

In a production environment, PostgreSQL backups can be automated by periodically exporting the database to a backup file. This can be achieved by scheduling a cron job or a dedicated backup container that periodically runs a pg_dump command to export the database data into a directory (e.g., /backups) mounted on the host system. The backups folder in the above configuration is mounted to a directory on the host machine, where backup files can be safely stored.

Creating Backups In Docker Postgres

For a basic backup strategy:

1. Run pg_dump to export the database:

   docker exec postgres pg_dump -U myuser mydatabase > ./backups/backup_$(date +%F).sql
   

   This command creates a backup of the database inside the backups directory.

2. You can set up a cron job or use another scheduling mechanism to run this command at regular intervals (e.g., daily or weekly).

     0 2 * * * docker exec postgres pg_dump -U myuser mydatabase > /path/to/backups/backup_$(date +\%F).sql
   

3. Upload the backup to S3, or a similar storage location

     aws s3 cp ./backups/backup_$(date +%F).sql s3://your-bucket-name/path/to/backup/
   

   Additionally, you could set up WAL (Write-Ahead Logging) archiving to create continuous backups of changes, allowing for point-in-time recovery, but that is out of scope for this article, check out the official docs for more information.

Restoring a Backup

1. Download the backup from S3

     aws s3 cp s3://your-bucket-name/path/to/backup/backup_YYYY-MM-DD.sql ./backups/
   

   This command will download the backup file from S3 into your local backups directory.

2. Restore it to your database

     docker exec -i postgres psql -U myuser -d mydatabase < ./backups/backup_YYYY-MM-DD.sql
   

   This command will use psql to restore the backup file into your running PostgreSQL container.

Wrap Up

By now you should see the basics of how to start and manage a Postgres Docker container. Please note there are lots of other things you will need to run a production Postgres cluster (eg monitoring, alerts), but hopefully the above will get you started.