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.

Dump Database

pg_dump -Fc -f ~/_to_delete/nice2_${CUSTOMER}_$(date +"%Y_%m_%d").psql ${DATABASE};

Restore Database

Restore dump:

  1. Create an empty database:

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

  2. Restore database

    Regular *.psql/*.dump files and 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!

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.

Footnotes