Database administration¶

Docker installations¶

Database backup¶

Open a shell (command prompt) in the Docker folder

$ docker-compose exec db pg_dump -U openremuser -d openrem_prod -F c -f /db_backup/openremdump.bak

To automate a regular backup (recommended) adapt the following command in a bash script:

#!/bin/bash
TODAY=$(date "+%Y-%m-%d")
docker-compose -f /path/to/docker-compose.yml exec db pg_dump -U openremuser -d openrem_prod -F c -f "/db_backup/openremdump-"$TODAY".bak"

or powershell script:

$dateString = "{0:yyyy-MM-dd}" -f (get-date)
docker-compose -f C:\Path\To\docker-compose.yml exec db pg_dump -U openremuser -d openrem_prod -F c -f /db_backup/openremdump-$dateString.bak

You will need to ensure the backups are either regularly deleted/moved, or overwritten so that the backups don’t fill the disk.

Database restore¶

To restore a database backup to a new Docker container, install using the Installation instructions and bring the containers up, but don’t run the database commands. These instructions can also be used to create a duplicate server on a different system for testing or other purposes.

Requires exactly the same version of OpenREM to be installed as the database was exported from
Copy the database backup to the db_backup/ folder of the new install (the name is assumed to be openremdump.bak, change as necessary)
Open a shell (command prompt) in the new install folder (where docker-compose.yml is)

$ docker-compose exec db pg_restore --no-privileges --no-owner -U openremuser -d openrem_prod /db_backup/openremdump.bak

You may get an error about the public schema, this is normal.

Get the database ready and set up Django:

$ docker-compose exec openrem python manage.py migrate --fake-initial

$ docker-compose exec openrem python manage.py makemigrations remapp

$ docker-compose exec openrem python manage.py migrate --fake

$ docker-compose exec openrem python manage.py collectstatic --noinput --clear

$ docker-compose exec openrem python django-admin compilemessages

The OpenREM server should now be ready to use again.

Advanced¶

These methods should not be required in normal use; only do this if you know what you are doing!

psql¶

Start the PostgreSQL console:

$ docker-compose exec db psql -U openremuser openrem_prod

-- List users
\du

-- List databases
\l

-- Exit the console
\q

pgAdmin or other PostgreSQL connections¶

To access the database directly by pgAdmin or other software, the ports must be exposed.

Edit docker-compose.yml to add the ports:

db:
  ports:
    - 5432:5432

If you have a database already running on the host machine, this port will prevent the container starting. In this case, change the first number in the pair to an alternative port.
The service will be accessible on the host machine after the containers are taken down and up again:

$ docker-compose down
$ docker-compose up -d

Linux installations¶

Database backup¶

Check the database username and change in the command below as necessary (openremuser)
Check the database name and change in the command below as necessary (openremdb)
You will need the password for openremuser
Ad hoc:

$ sudo -u postgres pg_dump -U openremuser -d openremdb -F c -f openremdump.bak

To automate a regular backup (recommended) adapt the following command in a bash script:

#! /bin/bash
rm -rf /path/to/db/backups/*
PGPASSWORD="mysecretpassword" /usr/bin/pg_dump -U openremuser -d openremdb -F c -f /path/to/db/backups/openremdump.bak

Database restore¶

Requires the same version of OpenREM to be installed as the database was exported from, unless you are Upgrading a native Linux install or Upgrading to a new Linux server.
Username can be changed on restore by specifying the new user in the restore command. The user must exist in PostgreSQL though - sudo -u postgres createuser -P openremuser if required
openrem/remapp/migrations/ should be empty except __init__.py

$ sudo -u postgres createdb -T template0 new_openremdb_name
$ sudo -u postgres pg_restore --no-privileges --no-owner -U openremuser -d new_openremdb_name path-to/openremdump.bak

Update the local_settings.py file with the new database details, as per Configure OpenREM
Set up the new database with Django/OpenREM after activating the virtualenv and moving to the site-packages/openrem folder:

$ python manage.py migrate --fake-initial
$ python manage.py migrate remapp --fake
$ python manage.py makemigrations remapp
$ python manage.py migrate

Windows installations¶

Database backup¶

Check the database username and change in the command below as necessary (openremuser)
Check the database name and change in the command below as necessary (openremdb)
You will need the password for openremuser
You will need to edit the command for the path to pg_dump.exe - the 14 is likely to be a lower number
Ad hoc:

C:\Users\openrem>"c:\Program Files\PostgreSQL\14\bin\pg_dump.exe" -U openremuser -d openremdb -F c -f windump.bak

To automate a regular backup (recommended) adapt the following command in a bat script:

Warning

Content to be added!

Database restore¶

Requires the same version of OpenREM to be installed as the database was exported from, unless you are Upgrading a native Windows install or Upgrading to a new Windows server.
Username can be changed on restore by specifying the new user in the restore command. The user must exist in PostgreSQL though - create the user in pgAdmin if required
openrem\remapp\migrations\ should be empty except __init__.py

C:\Users\openrem>"c:\Program Files\PostgreSQL\14\bin\pg_restore.exe" --no-privileges --no-owner -U openremuser -d openremdb -W windump.bak

Update the local_settings.py file with the new database details, as per Configure OpenREM
Set up the new database with Django/OpenREM after activating the virtualenv and moving to the site-packages\openrem folder:

(venv) E:\venv\Lib\site-packages\openrem>python manage.py migrate --fake-initial
(venv) E:\venv\Lib\site-packages\openrem>python manage.py migrate remapp --fake
(venv) E:\venv\Lib\site-packages\openrem>python manage.py makemigrations remapp
(venv) E:\venv\Lib\site-packages\openrem>python manage.py migrate