This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.

Copy/Dump/Restore Database

Hint

If you want restore a backup have a look at Database Backups.

Copy Database

Hint

  • For the special host "localhost" to work properly, Postgres must be set up as described in Setup Postgres.

Examples:

  1. Copy DB of installations abc to server where abctest resides naming the new db nice_abctest_new:

    tco cp abc abctest/nice_abctest_new

  2. Copy prod DB of bbg to localhost:

    tco cp bbg

    localhost is default if no target is specified.

See tco --help for more examples and additional information.

Copy Database Using WITH TEMPLATE

This is the fastest way to copy a database. Alternatively, you can dump and then restore the database.

CREATE DATABASE ${TARGET_DB} WITH TEMPLATE ${SOURCE_DB};

Warning

This requires that no one is connected to the database. Consequently, it isn’t possible to copy a database of a running system.

Example

  1. switch to the right project

    oc project nice-${INSTALLATION}

  2. check how many instance are running

    oc get dc/nice -o go-template='{{.spec.replicas}}{{"\n"}}'

  3. stop instance (if required)

    oc scale --replicas=0 dc/nice

  4. copy database

    sql

CREATE DATABASE ${NAME_OF_DB_COPY} WITH TEMPLATE ${SOURCE_DB_NAME};

    Hint

    If you get “source database ‘…’ is being accessed by other users”, try killing the connections to the database first.

    Note

    By convention, databases not used by a test or production systems should follow this naming pattern: nice_${CUSTOMER}_${YOUR_SHORT_NAME}_${YEAR}${MONTH}${DAY}

  1. restart instances (if previously stopped)

    oc scale --replicas=${N} dc/nice

    Start ${N} instances.

Dump Database

tco db-backup dump ${INSTALLATION}

or

tco db-backup dump ${DB_SERVER}/${DB_NAME}

Hint

The restore command is printed by tco db-backup dump. Use it to restore the dump later.

Restore command can be used for several days before the backup is removed after archiving it via Borg and Burp. Talk to Operations you need a backup restored from there.

Restore Database

Restore Backup

For restoring backups, including those made via tco db-backup, see Database Backups

Restore Dump File:

  1. Create an empty database:

    CREATE DATABASE ${DB_NAME} WITH OWNER ${DB_USER};

  2. Restore database

    Regular *.psql/*.dump files or directories:

    pg_restore -j 4 --role ${DB_USER} --no-owner --no-acl -d ${DB_NAME} ${DUMP_FILE_OR_DIRECTORY}

    *.zstd files:

    zstd -qcd ${DUMP_FILE} | pg_restore --role ${DB_USER} --no-owner --no-acl -d ${DB_NAME}

  3. run ANALYZE [1]:

    ANALYZE

Note

If needed, adjust owner of DB and content:

REASSIGN OWNED BY ${OLD_DB_USER} TO ${NEW_DB_USER};

Warning

REASSIGN OWNED will change the owner of all objects in the DB connected to matching BY ${ROLE_NAME} and the owner of all DBs matching. Be careful when there are other DBs that have ${ROLE_NAME} as owner!

Footnotes