MAINTENANCE

Operating system updates

sudo apt-get update
sudo apt-get dist-upgrade

Application backup

Database

sudo -u postgres pg_dump -Fc geotrekdb > `date +%Y%m%d%H%M`-database.backup

Media files

tar -zcvf `date +%Y%m%d%H%M`-media.tar.gz /opt/geotrek-admin/var/media/

Configuration

tar -zcvf `date +%Y%m%d%H%M`-conf.tar.gz /opt/geotrek-admin/var/conf/

Application restore

Create empty database :

sudo -u postgres psql -c "CREATE DATABASE geotrekdb ENCODING 'UTF8' TEMPLATE template0;"
sudo -u psql -d geotrekdb -c "CREATE EXTENSION postgis;"

Restore backup :

sudo -u postgres pg_restore -d geotrekdb 20140610-geotrekdb.backup

Extract media and configuration files :

tar -zxvf 20140610-media.tar.gz
tar -zxvf 20140610-conf.tar.gz

Follow fresh installation method. Choose to manage database by yourself.

PostgreSQL optimization

• Increase shared_buffers and work_mem according to your RAM
• Log long queries
• Use pg activity for monitoring

Access your database securely on your local machine (QGis)

Instead of opening your database to the world (by opening the port 5432 for example), you can use SSH tunnels.