PostgreSQL

Im Folgenden finden sich Befehle, um eine Datenbank als komprimierte Binär-Datei zu sichern, zu leeren und wiederherzustellen.

Die Befehle sind darauf ausgelegt kopiert, eingefügt und ausgeführt zu werden. Um das zu erreichen werden Variablen verwendet. Diese müssen vor dem Ausführen der Befehle jeweils einmalig pro Shell wie folgt gesetzt werden:

VARIABLE=wert

Interaktive Kommandozeile

Als interaktive Kommandozeile kommt psql zum Einsatz.

Generell

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; optional

psql ${DATABASE:+--dbname="${DATABASE}"}

Docker

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

CONTAINER: Name des Docker-Containers; notwendig, wenn nicht eindeutig identifizierbar
DATABASE: Name der Datenbank; optional

docker container exec --interactive --tty "${CONTAINER:-$(
    docker container ls --all --format='{{.Names}}' --no-trunc | awk '
        /^(wcp-)?postgres(ql)?[-.0-9]*$/ { database = $0; databases++; }
        END { if (databases == 1) print database; }
    '
)}" sh -c '
    export PGUSER="${POSTGRES_USER:-postgres}"
    export PGPASSWORD="${POSTGRES_PASSWORD:-postgres}"
    exec "$@"
' sh \
    psql ${DATABASE:+--dbname="${DATABASE}"}

Project Task Runner

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; optional
SERVICE: Name der Docker Compose Services; optional; default: postgresql

docker-compose exec "${SERVICE:-postgresql}" sh -c '
    export PGUSER="${POSTGRES_USER:-postgres}"
    export PGPASSWORD="${POSTGRES_PASSWORD:-postgres}"
    exec "$@"
' sh \
    psql ${DATABASE:+--dbname="${DATABASE}"}

Sichern

Beim Abspeichern kommt pg_dump zum Einsatz.

Generell

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; notwendig
FILE: Pfad des Datenbankdumps; optional; default: ${DATABASE}.dump

pg_dump --blobs --compress=9 --dbname="${DATABASE}" --format=custom \
    > "${FILE:-${DATABASE}.dump}"

Docker

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; notwendig
CONTAINER: Name des Docker-Containers; notwendig, wenn nicht eindeutig identifizierbar
FILE: Pfad des Datenbankdumps; optional; default: ${DATABASE}.dump

docker container exec --interactive "${CONTAINER:-$(
    docker container ls --all --format='{{.Names}}' --no-trunc | awk '
        /^(wcp-)?postgres(ql)?[-.0-9]*$/ { database = $0; databases++; }
        END { if (databases == 1) print database; }
    '
)}" sh -c '
    export PGUSER="${POSTGRES_USER:-postgres}"
    export PGPASSWORD="${POSTGRES_PASSWORD:-postgres}"
    exec "$@"
' sh \
    pg_dump --blobs --compress=9 --dbname="${DATABASE}" --format=custom \
    > "${FILE:-${DATABASE}.dump}"

Project Task Runner

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; notwendig
FILE: Pfad des Datenbankdumps; optional; default: ${DATABASE}.dump
SERVICE: Name der Docker Compose Services; optional; default: postgresql

docker-compose exec -T "${SERVICE:-postgresql}" sh -c '
    export PGUSER="${POSTGRES_USER:-postgres}"
    export PGPASSWORD="${POSTGRES_PASSWORD:-postgres}"
    exec "$@"
' sh \
    pg_dump --blobs --compress=9 --dbname="${DATABASE}" --format=custom \
    > "${FILE:-${DATABASE}.dump}"

Leeren

Eine Datenbank kann nicht gelöscht werden, solange aktive Verbindungen zu ihr bestehen. Daher müssen diese unterbrochen werden.

Beim Leeren kommt psql zum Einsatz.

Generell

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; notwendig
OWNER: Besitzer der Datenbank (s.u.); optional; default: ${DATABASE}

echo "
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = '${DATABASE}' AND pid <> pg_backend_pid();
    DROP DATABASE IF EXISTS \"${DATABASE}\";
    CREATE DATABASE \"${DATABASE}\" WITH OWNER \"${OWNER:-${DATABASE}}\";
" | psql --dbname=postgres

Docker

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; notwendig
CONTAINER: Name des Docker-Containers; notwendig, wenn nicht eindeutig identifizierbar
OWNER: Besitzer der Datenbank (s.u.); optional; default: ${DATABASE}

echo "
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = '${DATABASE}' AND pid <> pg_backend_pid();
    DROP DATABASE IF EXISTS \"${DATABASE}\";
    CREATE DATABASE \"${DATABASE}\" WITH OWNER \"${OWNER:-${DATABASE}}\";
" | docker container exec --interactive "${CONTAINER:-$(
    docker container ls --all --format='{{.Names}}' --no-trunc | awk '
        /^(wcp-)?postgres(ql)?[-.0-9]*$/ { database = $0; databases++; }
        END { if (databases == 1) print database; }
    '
)}" sh -c '
    export PGUSER="${POSTGRES_USER:-postgres}"
    export PGPASSWORD="${POSTGRES_PASSWORD:-postgres}"
    exec "$@"
' sh \
    psql --dbname=postgres

Project Task Runner

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; notwendig
OWNER: Besitzer der Datenbank (s.u.); optional; default: ${DATABASE}
SERVICE: Name der Docker Compose Services; optional; default: postgresql

echo "
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = '${DATABASE}' AND pid <> pg_backend_pid();
    DROP DATABASE IF EXISTS \"${DATABASE}\";
    CREATE DATABASE \"${DATABASE}\" WITH OWNER \"${OWNER:-${DATABASE}}\";
" | docker-compose exec -T "${SERVICE:-postgresql}" sh -c '
    export PGUSER="${POSTGRES_USER:-postgres}"
    export PGPASSWORD="${POSTGRES_PASSWORD:-postgres}"
    exec "$@"
' sh \
    psql --dbname=postgres

Wiederherstellen

Beim Wiederherstellen kommt pg_restore zum Einsatz. Vor dem Wiederherstellen sollte darauf geachtet werden, dass die Datenbank leer ist.

Zusätzlich zu den Besitzern pro Datenbank hat PostgreSQL auch Besitzer pro TABLE, SEQUENCE und VIEW. Auf diese muss beim Er- und Wiederherstellen der Datenbank geachtet werden. Üblicher weise hat der Besitzer den gleichen Namen wie die Datenbank. Falls nicht lässt er mit folgender SQL-Abfrage auf der gewünschten Datenbank ermitteln:

SELECT pg_catalog.pg_get_userbyid(datdba)
FROM pg_catalog.pg_database
WHERE datname = current_database()

Generell

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; notwendig
FILE: Pfad des Datenbankdumps; optional; default: ${DATABASE}.dump
OWNER: Besitzer der Datenbank; optional; default: ${DATABASE}

pg_restore --dbname="${DATABASE}" --no-owner --no-privileges --role="${OWNER:-${DATABASE}}" --schema=public \
    < "${FILE:-${DATABASE}.dump}"

Docker

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; notwendig
CONTAINER: Name des Docker-Containers; notwendig, wenn nicht eindeutig identifizierbar
FILE: Pfad des Datenbankdumps; optional; default: ${DATABASE}.dump
OWNER: Besitzer der Datenbank; optional; default: ${DATABASE}

docker container exec --interactive "${CONTAINER:-$(
    docker container ls --all --format='{{.Names}}' --no-trunc | awk '
        /^(wcp-)?postgres(ql)?[-.0-9]*$/ { database = $0; databases++; }
        END { if (databases == 1) print database; }
    '
)}" sh -c '
    export PGUSER="${POSTGRES_USER:-postgres}"
    export PGPASSWORD="${POSTGRES_PASSWORD:-postgres}"
    exec "$@"
' sh \
    pg_restore --dbname="${DATABASE}" --no-owner --no-privileges --role="${OWNER:-${DATABASE}}" --schema=public \
    < "${FILE:-${DATABASE}.dump}"

Project Task Runner

Folgende Variablen haben Einfluss auf den Unten stehenden Befehl:

DATABASE: Name der Datenbank; notwendig
FILE: Pfad des Datenbankdumps; optional; default: ${DATABASE}.dump
OWNER: Besitzer der Datenbank; optional; default: ${DATABASE}
SERVICE: Name der Docker Compose Services; optional; default: postgresql

docker-compose exec -T "${SERVICE:-postgresql}" sh -c '
    export PGUSER="${POSTGRES_USER:-postgres}"
    export PGPASSWORD="${POSTGRES_PASSWORD:-postgres}"
    exec "$@"
' sh \
    pg_restore --dbname="${DATABASE}" --no-owner --no-privileges --role="${OWNER:-${DATABASE}}" --schema=public \
    < "${FILE:-${DATABASE}.dump}"