Restoring and backing up a database¶

On the production server you can run

sudo -u postgres bash backup_db.sh

which will save the latest database version to the /home/ubuntu/sql_backups/rhizome_latest.sql

Copying a database from prod to your local¶

After running the backup db script above...

$ scp prod_server:/home/ubuntu/sql_backups/rhizome_latest.sql.gz ~/
$ gunzip ~/rhizome_latest.sql.gz
$ psql postgres
psql (9.4.5)
Type "help" for help.

postgres=# DROP DATABASE rhizome;
postgres=# CREATE DATABASE rhizome WITH OWNER djangapp;

$ psql rhizome -f ~/rhizome_latest.sql

Now your local server has the same database as production!

backup db script¶

see here https://github.com/unicef/rhizome/blob/master/bin/backup_db.sh

and here http://serverfault.com/questions/59838/whats-the-best-way-to-automate-backing-up-of-postgresql-databases

#!/bin/bash

#
BACKUPDIR=/some/dir/sql_backups/
DATE=`date +%Y-%m-%dT%H:%M:%S`
DB=rhizome

PSQL="/usr/bin/psql"

BACKUPFILE="$BACKUPDIR$DATE.sql"

pg_dump --verbose -f $BACKUPFILE $DB

gzip "$BACKUPFILE"

cp $BACKUPFILE'.gz'  $BACKUPDIR'rhizome_latest.sql.gz'

# delete backup files older than 10 days
OLD=$(find $BACKUPDIR -type d -mtime +10)
if [ -n "$OLD" ] ; then

        echo deleting old backup files: $OLD
        echo $OLD | xargs rm -rfv
fi