Docker PostgreSQL Tutorial: Install, Connect, Back Up, and Upgrade

Important change in PostgreSQL 18: Starting with postgres:18, the official image stores data in /var/lib/postgresql/18/docker. Mount the Volume at /var/lib/postgresql. Most online tutorials use the pre-18 path /var/lib/postgresql/data, which causes version 18 to fail at startup. See the test below.

Start with one command

$ docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 127.0.0.1:5432:5432 -v pgdata:/var/lib/postgresql --restart unless-stopped postgres:18
1b34d05a195f347c86e431441162b760167a5c04016a528e10342d0199be016d

$ docker logs pg 2>&1 | tail -n 3
2026-10-13 00:53:32.071 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2026-10-13 00:53:32.075 UTC [72] LOG:  database system was shut down at 2026-10-13 00:53:31 UTC
2026-10-13 00:53:32.080 UTC [1] LOG:  database system is ready to accept connections

$ docker exec pg psql -U postgres -c "SHOW data_directory;"
        data_directory
-------------------------------
 /var/lib/postgresql/18/docker
(1 row)

SHOW data_directory confirms that the actual data directory is /var/lib/postgresql/18/docker, under the mounted /var/lib/postgresql. The directory name includes the major version, so future versions can keep their data alongside the old version in the same Volume.

Other commonly used environment variables:

As with MySQL, these variables only take effect when the data directory is empty. Changing POSTGRES_PASSWORD later does not change the existing password. Use ALTER USER ... PASSWORD '...' to change it.

Test: mounting the old path fails in version 18

Mount the Volume at /var/lib/postgresql/data, as older tutorials instruct:

$ docker run -d --name pgold -e POSTGRES_PASSWORD=secret -v pgold:/var/lib/postgresql/data postgres:18
b480eb9c72b29fa99f1ed8df17972e2bfc2d6da4b86c058a36cf21fd63579bfa

$ docker ps -a --filter name=pgold --format "{{.Status}}"
Exited (1) 5 seconds ago

$ docker logs pgold 2>&1 | head -n 12
Error: in 18+, these Docker images are configured to store database data in a
       format which is compatible with "pg_ctlcluster" (specifically, using
       major-version-specific directory names).  This better reflects how
       PostgreSQL itself works, and how upgrades are to be performed.

       See also https://github.com/docker-library/postgres/pull/1259

       Counter to that, there appears to be PostgreSQL data in:
         /var/lib/postgresql/data (unused mount/volume)

       This is usually the result of upgrading the Docker image without
       upgrading the underlying database using "pg_upgrade" (which requires both

The container exits with Exited (1) after a few seconds. The error explains that version 18 uses major-version-specific directories and found an “unused mount” at /var/lib/postgresql/data. This check prevents you from thinking your data is persisted when it is actually written inside the container and will disappear when you delete it. Change the mount path to /var/lib/postgresql.

Manage with Compose

For a real project, use Compose. Create the pg-demo directory:

pg-demo/
├── compose.yaml
├── .env
└── initdb/
    └── 01-init.sql

.env:

DB_PASSWORD=change-this-password

initdb/01-init.sql (runs during the first initialization to create an extension, a table, and sample data; the final pg_sleep(8) deliberately simulates a slow initialization, such as importing a large amount of data, so you can observe the health check):

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE products (
  id serial PRIMARY KEY,
  name text NOT NULL,
  price integer NOT NULL
);

INSERT INTO products (name, price) VALUES ('珍珠奶茶', 65), ('鳳梨酥', 45), ('牛肉麵', 180);

-- Simulate a slow initialization (for example, importing a large amount of data)
SELECT pg_sleep(8);

compose.yaml:

services:
  db:
    image: postgres:18
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_DB: app
      TZ: Asia/Taipei
    command: ["postgres", "-c", "max_connections=200", "-c", "shared_buffers=256MB"]
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql
      - ./initdb:/docker-entrypoint-initdb.d:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -h 127.0.0.1 -U app -d app"]
      interval: 2s
      timeout: 3s
      retries: 30
    shm_size: 256mb
    restart: unless-stopped

volumes:
  pgdata:

$ docker compose up -d --wait
[+] up 3/3
 ✔ Network pg-demo_default Created                                                                                                                0.0s
 ✔ Volume pg-demo_pgdata   Created                                                                                                                0.0s
 ✔ Container pg-demo-db-1  Healthy                                                                                                               11.3s

$ docker compose exec db psql -U app -h 127.0.0.1 -c "SELECT * FROM products;"
 id |   name   | price
----+----------+-------
  1 | 珍珠奶茶 |    65
  2 | 鳳梨酥   |    45
  3 | 牛肉麵   |   180
(3 rows)


$ docker compose logs db 2>&1 | grep -E "running /docker-entrypoint-initdb.d|init process complete|listening on IPv4"
db-1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/01-init.sql
db-1  | PostgreSQL init process complete; ready for start up.
db-1  | 2026-10-13 08:52:58.663 CST [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432

$ docker compose exec db psql -U app -c "SHOW max_connections;" -c "SHOW shared_buffers;" -c "SELECT now();"
 max_connections
-----------------
 200
(1 row)

 shared_buffers
----------------
 256MB
(1 row)

              now
-------------------------------
 2026-10-13 08:53:01.293902+08
(1 row)


$ docker compose exec db df -h /dev/shm
Filesystem      Size  Used Avail Use% Mounted on
shm             256M  1.1M  255M   1% /dev/shm

$ docker compose exec db psql -U app -c "\dx"
                                             List of installed extensions
  Name   | Version | Default version |   Schema   |                            Description
---------+---------+-----------------+------------+-------------------------------------------------------------------
 pg_trgm | 1.6     | 1.6             | public     | text similarity measurement and index searching based on trigrams
 plpgsql | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
(2 rows)

• Healthy took 11.3 seconds, which is the initialization time plus pg_sleep(8). As soon as it became healthy, the TCP connection worked, the data was present, and the Chinese text displayed correctly.
• The logs show that the initialization script ran, and the server listened on 0.0.0.0:5432 only after initialization completed.
• max_connections is 200 and shared_buffers is 256MB, so the command options took effect. now() shows the +08 time zone.
• /dev/shm is 256M, so shm_size took effect.
• \dx lists the installed extensions; the initialization script created pg_trgm.

Health check trap: without -h, pg_isready reports healthy too soon

A common online example is pg_isready -U app -d app (without -h). Here is a test using that form:

$ docker compose up -d --wait
[+] up 3/3
 ✔ Network pg-demo_default Created                                                                                                                0.0s
 ✔ Volume pg-demo_pgdata   Created                                                                                                                0.0s
 ✔ Container pg-demo-db-1  Healthy                                                                                                                3.2s

$ docker compose exec db psql -U app -h 127.0.0.1 -c "SELECT count(*) FROM products;"
psql: error: connection to server at "127.0.0.1", port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

$ docker compose logs db 2>&1 | grep -E "pg_sleep|init process complete|listening on IPv4"

It reported Healthy after only 3.2 seconds, but a TCP connection returned Connection refused.

The reason is the same as with MySQL: during initialization, the startup script first launches a temporary server that accepts Unix socket connections only so it can run the scripts in initdb. Without -h, pg_isready uses the socket and reports that the server is ready before initialization is done. Applications that depend on it (depends_on with condition: service_healthy) start at that point, and their TCP connections fail. Add -h 127.0.0.1 to force TCP and wait for the final server to start.

Connect and use psql

Use psql

The container includes the psql client. For an interactive session, run docker compose exec db psql -U app (for a standalone container, use docker exec -it pg psql -U postgres). At the app=# prompt, enter SQL or one of the commands below. You can also run a command directly with -c:

$ docker compose exec db psql -U app -c "SELECT version();"
                                                      version
--------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)


$ docker compose exec db psql -U app -c "\l"
                                                 List of databases
   Name    | Owner | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules | Access privileges
-----------+-------+----------+-----------------+------------+------------+--------+-----------+-------------------
 app       | app   | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |
 postgres  | app   | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |
 template0 | app   | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/app           +
           |       |          |                 |            |            |        |           | app=CTc/app
 template1 | app   | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/app           +
           |       |          |                 |            |            |        |           | app=CTc/app
(4 rows)


$ docker compose exec db psql -U app -c "\dt"
          List of tables
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | products | table | app
(1 row)


$ docker compose exec db psql -U app -c "\d products"
                            Table "public.products"
 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+--------------------------------------
 id     | integer |           | not null | nextval('products_id_seq'::regclass)
 name   | text    |           | not null |
 price  | integer |           | not null |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)


$ docker compose exec db psql -U app -c "\du"
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 app       | Superuser, Create role, Create DB, Replication, Bypass RLS

\du shows that app is a Superuser, and app owns every database in \l: setting POSTGRES_USER replaces the default postgres user.

Why you don't need to enter a password inside the container

None of the psql commands above prompted for a password. Check pg_hba.conf (PostgreSQL's connection authentication rules) in the data directory:

$ docker compose exec db grep -v "^#" /var/lib/postgresql/18/docker/pg_hba.conf | grep -v "^$"
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host all all all scram-sha-256

$ docker run --rm --network pg-demo_default -e PGPASSWORD=wrong postgres:18 psql -h db -U app -c "SELECT 1;"
psql: error: connection to server at "db" (172.19.0.2), port 5432 failed: FATAL:  password authentication failed for user "app"

$ docker run --rm --network pg-demo_default -e PGPASSWORD=change-this-password postgres:18 psql -h db -U app -c "SELECT current_user, inet_server_addr();"
 current_user | inet_server_addr
--------------+------------------
 app          | 172.19.0.2
(1 row)

• The official image configuration uses trust for connections inside the container (Unix socket, 127.0.0.1, and ::1); it does not check the password.
• Connections from other sources (the last host all all all line) use scram-sha-256 password authentication.
• When another container on the same Compose network connects to db, a wrong password produces password authentication failed; the correct password allows the connection.

Anyone who can access the container can operate the database directly, which is why you should protect Docker access (see Docker security). When you connect from the host through the port published with -p, the source IP is the Docker network gateway, so a password is still required.

Connect from the host or another container

postgres://app:password@127.0.0.1:5432/app   # From the host (GUI tool, application)
postgres://app:password@db:5432/app          # From another service in the same Compose project

Common connection errors

$ docker compose exec db psql -c "SELECT 1;"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

$ docker compose exec db psql -U postgres -c "SELECT 1;"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

$ docker compose exec db psql -U app -d shop -c "SELECT 1;"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "shop" does not exist

$ docker compose exec -e PGPASSWORD=wrong db psql -h 127.0.0.1 -U app -c "SELECT 1;"
 ?column?
----------
        1
(1 row)


$ docker compose exec db psql -U app -h 127.0.0.1 -p 5433 -c "SELECT 1;"
psql: error: connection to server at "127.0.0.1", port 5433 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?

Notice the fourth command: inside the container, connecting to 127.0.0.1 with a wrong password still succeeds. That is the trust rule described above.

Back up and restore

$ docker compose exec -T db pg_dump -U app app > app.sql

$ ls -lh app.sql && grep -E "^CREATE (TABLE|EXTENSION)" app.sql
-rw-r--r--    1 root     root        2.3K Oct 13 00:53 app.sql
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
CREATE TABLE public.products (

$ docker compose exec -T db pg_dump -U app -Fc app > app.dump

$ ls -lh app.dump
-rw-r--r--    1 root     root        3.1K Oct 13 00:53 app.dump

$ docker compose exec db psql -U app -c "DROP TABLE products;"
DROP TABLE

$ docker compose exec -T db pg_restore -U app -d app --clean --if-exists < app.dump

$ docker compose exec db psql -U app -c "SELECT count(*) FROM products;"
 count
-------
     3
(1 row)


$ rm app.sql app.dump

The backup includes CREATE EXTENSION, so the extension is created when you restore to a new database. This example has very little data, so the custom format (3.1K) is actually larger than plain text (2.3K); with more data, the custom format is much smaller.

Other useful commands:

# Back up all databases and roles (users and passwords)
docker compose exec -T db pg_dumpall -U app > all.sql

# Restore plain-text SQL
docker compose exec -T db psql -U app -d app < app.sql

You can schedule regular backups with cron using the commands above, then copy the files to another machine or cloud storage.

Back up and restore on Windows

PowerShell does not support <, and > changes file encoding in Windows PowerShell 5.1. It also corrupts binary -Fc backups. Instead, use pg_dump -f to create a file in the container, then copy it with docker compose cp. This works on every operating system:

# Back up
docker compose exec db pg_dump -U app -Fc -f /tmp/app.dump app
docker compose cp db:/tmp/app.dump app.dump

# Restore
docker compose cp app.dump db:/tmp/restore.dump
docker compose exec db pg_restore -U app -d app --clean --if-exists /tmp/restore.dump

These commands do not use $(pwd), <, or >, so you can run them directly in bash, PowerShell, or CMD. Tested by creating a table, backing it up, dropping it, and restoring it:

$ docker compose exec db psql -U app -c 'CREATE TABLE t (id int); INSERT INTO t VALUES (1),(2);'
CREATE TABLE
INSERT 0 2

$ docker compose exec db pg_dump -U app -Fc -f /tmp/app.dump app

$ docker compose cp db:/tmp/app.dump app.dump
 pgwin-db-1 Copying pgwin-db-1:/tmp/app.dump to app.dump
 pgwin-db-1 Copied pgwin-db-1:/tmp/app.dump to app.dump

$ ls -l app.dump
-rw-rw-rw-    1 root     root          1251 Oct 13 05:30 app.dump

$ docker compose exec db psql -U app -c 'DROP TABLE t;'
DROP TABLE

$ docker compose cp app.dump db:/tmp/restore.dump
 pgwin-db-1 Copying app.dump to pgwin-db-1:/tmp/restore.dump
 pgwin-db-1 Copied app.dump to pgwin-db-1:/tmp/restore.dump

$ docker compose exec db pg_restore -U app -d app /tmp/restore.dump

$ docker compose exec db psql -U app -c 'SELECT count(*) FROM t;'
 count
-------
     2
(1 row)

Major-version upgrade (for example, 16 → 18)

For a minor update (for example, 18.5 → 18.6), just pull the new image and recreate the container. Major versions are different: PostgreSQL data files are incompatible across major versions.

First, create some data with version 16:

$ docker run -d --name pg16 -e POSTGRES_PASSWORD=secret -v pg16data:/var/lib/postgresql/data postgres:16
20691764a98828d24c958eaaa71e83c08ebff29b984350ff7f6782ba8d74b4ca

$ docker exec pg16 psql -U postgres -c "CREATE TABLE notes (id serial PRIMARY KEY, body text); INSERT INTO notes (body) VALUES ('written by 16');"
CREATE TABLE
INSERT 0 1

$ docker stop pg16
pg16

Switching the image directly fails

Open version 16's data with postgres:18 (here, PGDATA points to the old path to simulate changing the tag directly from 16 to 18):

$ docker run --name pg18 -e POSTGRES_PASSWORD=secret -e PGDATA=/var/lib/postgresql/data -v pg16data:/var/lib/postgresql/data postgres:18 2>&1 | tail -n 3

2026-10-13 00:54:09.740 UTC [1] FATAL:  database files are incompatible with server
2026-10-13 00:54:09.740 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 16, which is not compatible with this version 18.6 (Debian 18.6-1.pgdg13+2).

$ docker rm pg18
pg18

database files are incompatible with server: the data directory was initialized with version 16, and 18.6 cannot use it.

Export and import

The simplest safe approach is to export all data with the old version's pg_dumpall, then import it into the new version's new Volume.

$ docker start pg16
pg16

$ docker exec pg16 pg_dumpall -U postgres > upgrade.sql && ls -lh upgrade.sql
-rw-r--r--    1 root     root        3.4K Oct 13 00:54 upgrade.sql

$ docker run -d --name pg18 -e POSTGRES_PASSWORD=secret -v pg18data:/var/lib/postgresql postgres:18
ec651837b76a0c9cc8b2468ec5a2ff4002334dcf015514ef94490a6c38cfe73d

$ docker exec -i pg18 psql -U postgres -q < upgrade.sql
ERROR:  role "postgres" already exists
 set_config
------------

(1 row)

 set_config
------------

(1 row)

 setval
--------
      1
(1 row)


$ docker exec pg18 psql -U postgres -c "SHOW server_version;" -c "SELECT * FROM notes;"
        server_version
-------------------------------
 18.6 (Debian 18.6-1.pgdg13+2)
(1 row)

 id |     body
----+---------------
  1 | written by 16
(1 row)


$ rm upgrade.sql

• You can ignore ERROR: role "postgres" already exists during import: the new version 18 container already created the postgres user, and pg_dumpall tries to create it again.
• The final check confirms the version is 18.6 and the data written by version 16 (written by 16) is present.
• Note that the new container's Volume is mounted at /var/lib/postgresql (the new path for version 18).

After confirming that the data and application work correctly, delete the old container and Volume. If you have a large amount of data and cannot accept extended downtime, look into pg_upgrade or logical replication, but the process is much more complicated. Practice in a test environment first.

Recap

• Mount PostgreSQL 18's Volume at /var/lib/postgresql.
• Use pg_isready -h 127.0.0.1 for the health check.
• Connections inside the container use trust; external connections require a password.
• For a major-version upgrade, export with the old version's pg_dumpall and import into the new version's new Volume.

Exercises

Exercise 1: Use psql to create a notes table in the app database, then verify it with \dt.

docker compose exec db psql -U app -c "CREATE TABLE notes (id serial PRIMARY KEY, body text);"
docker compose exec db psql -U app -c "\dt"

Exercise 2: What is the difference between a custom-format (-Fc) backup and plain-text SQL?

Updated October 14, 2026