Install PostgreSQL with Docker: Persistent Data, psql and Backups

What is PostgreSQL #

PostgreSQL is an open source relational database with transactions, JSON support and a large ecosystem of extensions. Running it in Docker is the fastest way to have one for development and tests, with the exact version you use in production, for example on Amazon RDS.

Run PostgreSQL #

docker run -d --name pg \
  -e POSTGRES_PASSWORD=change-me \
  -e POSTGRES_USER=app -e POSTGRES_DB=appdb \
  -p 127.0.0.1:5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

• POSTGRES_PASSWORD is required. POSTGRES_USER and POSTGRES_DB create a user and a database on the first start.
• pgdata is a named volume. Without a volume the data disappears with the container. See Docker volumes and bind mounts.
• Pin the major version (16), because a data directory from one major version cannot be opened by another.

Connect with psql #

docker exec -it pg psql -U app -d appdb

CREATE TABLE tasks (id serial PRIMARY KEY, title text NOT NULL, done boolean DEFAULT false);
INSERT INTO tasks (title) VALUES ('install PostgreSQL'), ('write a backup');
SELECT * FROM tasks;

Useful meta commands: \l lists databases, \dt lists tables, \d tasks describes a table and \q quits.

Create a user with limited rights #

Applications should not use the superuser:

CREATE ROLE reader LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE appdb TO reader;
GRANT USAGE ON SCHEMA public TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;

Initialize with SQL scripts #

Every .sql or .sh file in /docker-entrypoint-initdb.d/ runs in alphabetical order the first time the container starts with an empty data directory.

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: change-me
      POSTGRES_DB: appdb
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d appdb"]
      interval: 10s
      timeout: 3s
      retries: 5
volumes:
  pgdata:

Other services in the same Compose file connect to db:5432. Use the healthcheck with depends_on: condition: service_healthy so the application waits for the database (Docker Compose tutorial).

Backup and restore #

# logical backup of one database
docker exec pg pg_dump -U app -d appdb -Fc > appdb.dump

# restore into an empty database
docker exec -i pg pg_restore -U app -d appdb --clean --if-exists < appdb.dump

pg_dumpall backs up every database and role. Copy the dumps out of the machine: a backup next to the data does not protect you from losing the disk.

PostgreSQL in Kubernetes #

For a database inside a cluster with persistent storage see PostgreSQL on Kubernetes with an NFS volume. For production on AWS prefer RDS or Aurora.