# Install PostgreSQL with Docker: Persistent Data, psql and Backups

> Run PostgreSQL in a container with a persistent volume, create a database and a user with psql, initialize it with SQL scripts and back up and restore it.

- Source: https://www.itwonderlab.com/install-postgresql-docker/
- Published: 2026-09-28
- Updated: 2026-09-28
- Author: Javier Ruiz Jiménez (https://www.javierruizjimenez.com/)
- Site: IT Wonder Lab (https://www.itwonderlab.com/)

---

## What is PostgreSQL

[PostgreSQL](https://www.itwonderlab.com/postgresql/) is an open source relational database with transactions, JSON support and a large ecosystem of extensions. Running it in [Docker](https://www.itwonderlab.com/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](https://www.itwonderlab.com/aws-rds/).

## Run PostgreSQL

```bash
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](https://www.itwonderlab.com/docker-volumes-bind-mounts/).
- Pin the major version (`16`), because a data directory from one major version cannot be opened by another.

## Connect with psql

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

```sql
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:

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

```yaml title="compose.yaml"
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](https://www.itwonderlab.com/docker-compose-tutorial/)).

## Backup and restore

```bash
# 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](https://www.itwonderlab.com/postgres-kubernetes-nfs-volume/). For production on AWS prefer [RDS](https://www.itwonderlab.com/aws-rds/) or [Aurora](https://www.itwonderlab.com/aws-aurora/).