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

Restore Database¶

Hint

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

Copy Database¶

Hint

copy_db script must be available, see Set Up Dotfiles.
For the special host "localhost" to work properly, Postgres must be set up as described in Setup Postgres.

Examples:

Copy DB nice_abc on db1.prod to db1.stage as nice_abctest:
```
copy_db db1.prod nice_abc db1.stage nice_abctest
```
target_db must exist (e.g. be prevously created by Ansible).
Copy DB nice_abc on db1.prod to db1.stage as nice_abctest_new:
```
copy_db --create --role nice_abctest db1.prod nice_abc db1.stage nice_abctest_new
```
Specifying role explicitly as target_db (nice_abctest_new) and role (nice_abctest) differ.
Copy DB nice_master on db1.stage to localhost as nice_master:
```
copy_db --create db1.prod nice_master
```
Note

When target_db is "localhost" (the default) and --create is used, all created objects are owned by the restoring user.

Set a password for this, connecting user:
```
$ psql
; get user name
$ SELECT current_user;
user
; set a password
$ \password
Enter new password:
Enter it again:
```
Then, have Nice2 connect via this user (application-development.properties):
```
hibernate.main.serverName=localhost
hibernate.main.user=<user name>
hibernate.main.password=<your password>
```

Synopsis:

copy_db [--create] [--role ROLE] source_host source_db [target_host] [target_db]

Use copy_db --help to see all options.

Option	Description
`source_host`	Host from which DB is copied. May be `"localhost"`.
`source_db`	Name of the source database.
`target_host`	Host to which DB is copied. Default: `"localhost"`
`target_db`	Name of the target database. Default: value of `source_db`
`--create`	Create the target DB. DB must not exist yet.
`--role ROLE`	Owner of created DB and objects in it. Defaults: With `--create`, name of target DB. Except for localhost where the connecting user is used. Without `--create`, owner of target DB.

Dump Database¶

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

Tip

In order to create a dumps without binaries, email archive and history, use these arguments additionally:

--table "nice*" --table "database*" --table "_nice*" --exclude-table-data "nice_email_archive" \
--exclude-table-data "nice_email_*_to_*" --exclude-table-data "nice_email_attachment" \
--exclude-table-data "nice_history*"

Restore Database¶

Restore dump:

Create an empty database:

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

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}

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¶

switch to the right project
oc project nice-${INSTALLATION}

check how many instance are running

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

stop instance (if required)
oc scale --replicas=0 dc/nice
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}

restart instances (if previously stopped)
```
bash

oc scale --replicas=${N} dc/nice
```
Start ${N} instances.

Footnotes