PostgreSQL database (Linux)¶
Creating the database¶
Install PostgreSQL and the python connector¶
sudo apt-get install postgresql libpq-dev
If you are using a virtualenv, make sure you are in it and it is active (source bin/activate
)
pip install psycopg2
Change the security configuration¶
The default security settings are too restrictive to allow access to the database. Assumes version 9.5
, change as
appropriate.
sudo nano /etc/postgresql/9.5/main/pg_hba.conf
Scroll down to the bottom of the file and edit the following line from peer
to md5
:
local all all md5
Don’t worry about any lines that start with a #
as they are ignored. If you can’t access the database when
everything else is configured, you might need to revisit this file and see if there are other lines with a method of
peer
that need to be md5
Note
If you need to have different settings for different databases on your server, you can use the database name instead
of the first all
, and/or the the database user name instead of the second all
.
Restart PostgreSQL so the new settings take effect:
sudo service postgresql restart
Optional: Specify the location for the database files¶
You might like to do this if you want to put the database on an encrypted location instead of /var/lib/postgresql
.
For this example, I’m going to assume all the OpenREM programs and data are in the folder /var/openrem/
and
PostgreSQL is at version 9.5
(change both as appropriate)
sudo service postgresql stop
mkdir /var/openrem/database
sudo cp -aRv /var/lib/postgresql/9.5/main /var/openrem/database/
sudo nano /etc/postgresql/9.5/main/postgresql.conf
Change the line
data_directory = '/var/lib/postgresql/9.5/main'
to
data_directory = '/var/openrem/database/main'
then restart PostgreSQL:
sudo service postgresql start
Create a user for the OpenREM database¶
sudo -u postgres createuser -P openremuser
Enter a new password for the openremuser
, twice
Create the OpenREM database¶
sudo -u postgres createdb -T template1 -O openremuser -E 'UTF8' openremdb
If this is your initial install, you are now ready to install OpenREM, so go to the Installing OpenREM docs.
If you are replacing a SQLite test install with PostgreSQL, continue here.
Configure OpenREM to use the database¶
Move to the OpenREM install directory:
- Ubuntu linux:
/usr/local/lib/python2.7/dist-packages/openrem/
- Other linux:
/usr/lib/python2.7/site-packages/openrem/
- Linux virtualenv:
vitualenvfolder/lib/python2.7/site-packages/openrem/
- Windows:
C:\Python27\Lib\site-packages\openrem\
- Windows virtualenv:
virtualenvfolder\Lib\site-packages\openrem\
Edit the settings file, eg
nano openremproject/local_settings.py
Set the following (changing database name, user and password as appropriate)
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'openremdb',
'USER': 'openremuser',
'PASSWORD': 'openrem_pw',
Backup the database¶
Ad-hoc backup from the command line¶
sudo -u postgres pg_dump openremdb > /path/to/backup.bak
If you are moving a backup file between systems, or keeping a few backups, you may like to compress the backup; for example a 345 MB OpenREM database compresses to 40 MB:
tar -czf backup.bak.tar.gz backup.bak
Automated backup with a bash script¶
#! /bin/bash
rm -rf /path/to/db/backups/*
PGPASSWORD="openrem_pw" /usr/bin/pg_dump -Uopenremuser openremdb > /path/to/db/backups/openrem.bak
This script could be called by a cron task, or by a backup system such as backuppc prior to running the system backup.
Restore the database¶
If the restore is taking place on a different system,
- ensure that PostgreSQL is installed and the same user has been added as was used to create the initial database (see Creating the database).
- Ensure that the new system has the same version of OpenREM installed as the system the database was backed up from.
- Ensure the
openrem/remapp/migrations/
folder has no files in except __init__.py
Create a fresh database and restore from the backup:
sudo -u postgres createdb -T template0 new_openremdb_name
sudo -u postgres psql new_openremdb_name < /path/to/db/backups/openrem.bak
Reconfigure local_settings.py
with the new database details and introduce OpenREM to the restored database:
python manage.py migrate --fake-initial
python manage.py makemigrations remapp
python manage.py migrate remapp --fake
If you are creating a second system in order to test upgrading, you can do this now followed by the usual python
manage.py makemigrations remapp
then python manage.py migrate remapp
as per the upgrade instructions.
Useful PostgreSQL commands¶
-- Start the PostgreSQL console
sudo -u postgres psql
-- List users
\du
-- List databases
\l
-- Exit the console
\q