Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Upgrading the major version of the local PostgreSQL server

When running the PostgreSQL database server as a Docker container, the most straightforward approach to upgrade the major version of PostgreSQL is to:

  1. Start the server
  2. Dump the database to a local file using pg_dump
  3. Stop the server and destroy the Docker volume containing the PostgreSQL server data
  4. Bump the PostgreSQL version
  5. Start the server
  6. Restore the database from the local dump file using pg_restore

Start the PostgreSQL database server

$ docker compose up -d postgresql

[+] Running 3/3
 ✔ Network sparklemuffin_default         Created
 ✔ Volume "sparklemuffin_postgres-data"  Created
 ✔ Container sparklemuffin-postgres-1    Started

Dump the database

$ make pgdump

# mkdir -p dump
# docker compose exec postgres pg_dump -U sparklemuffin sparklemuffin --format custom --compress zst > dump/sparklemuffin.sql.zst

Stop the PostgreSQL server and delete its Docker volume

$ docker compose down -v

[+] Running 3/3
 ✔ Container sparklemuffin-postgres-1  Removed
 ✔ Volume sparklemuffin_postgres-data  Removed
 ✔ Network sparklemuffin_default       Removed

Update the PostgreSQL server version

Edit docker-compose.yml and docker-compose.dev.yml to set the desired version of the PostgreSQL server:

services:
  postgres:
    image: postgres:17
    # [...]

Start the PostgreSQL database server

$ docker compose up -d postgresql

[+] Running 3/3
 ✔ Network sparklemuffin_default         Created
 ✔ Volume "sparklemuffin_postgres-data"  Created
 ✔ Container sparklemuffin-postgres-1    Started

Restore the PostgreSQL database

$ make pgrestore

# docker compose exec -T postgres pg_restore -U sparklemuffin --dbname sparklemuffin < dump/sparklemuffin.sql.zst

$ make pgreindex

# docker compose exec postgres psql -U sparklemuffin -d sparklemuffin -c "REINDEX DATABASE sparklemuffin;"
REINDEX

# docker compose exec postgres psql -U sparklemuffin -d sparklemuffin -c "ALTER DATABASE sparklemuffin REFRESH COLLATION VERSION;"
ALTER DATABASE

Verification

$ make psql

# docker compose exec postgres psql -U sparklemuffin

psql (17.5 (Debian 17.5-1.pgdg120+1))
Type "help" for help.

sparklemuffin=# SELECT COUNT(*) FROM bookmarks;

 count
-------
  5126
(1 row)

Reference

PostgreSQL documentation

  • pg_dump - Extract a PostgreSQL database into a script file or other archive file
  • pg_restore - Restore a PostgreSQL database from an archive file created by pg_dump
  • psql - PostgreSQL interactive terminal
  • PostgreSQL 16 Release Notes - PostgreSQL 16 adds LZ4 and Zstandard compression to pg_dump

Sparklemuffin database

Articles