Database Backup and Restore

Tip

You might need to stop/restart the following services before restoring the database:

systemctl --user stop ss-pgbouncer
sudo systemctl restart postgresql

After the database has been manually restored, start all services:

systemctl --user start ss-surfly.target

Backup databases

pg_dump -U surfly_app surfly > ~/surfly_$(date +"%Y-%m-%d").sql
pg_dump -U surfly_app console > ~/console_$(date +"%Y-%m-%d").sql

Restore databases

dropdb -U postgres surfly && createdb -U postgres -O surfly_app -E UTF8 surfly && psql -U surfly_app surfly < ~/surfly_$(date +"%Y-%m-%d").sql

dropdb -U postgres console && createdb -U postgres -O surfly_app -E UTF8 console && psql -U surfly_app console < ~/console_$(date +"%Y-%m-%d").sql

If the database file is too big, you can use pigz to create a compressed file:

Backup and compress databases

pg_dump -U surfly_app surfly | pigz > ~/surfly_$(date +"%Y-%m-%d").gz
pg_dump -U surfly_app console | pigz > ~/console_$(date +"%Y-%m-%d").gz

Restore databases from a compressed file

dropdb -U postgres surfly && createdb -U postgres -O surfly_app -E UTF8 surfly && gunzip -c ~/surfly_$(date +"%Y-%m-%d").gz | psql -U surfly_app surfly
dropdb -U postgres console && createdb -U postgres -O surfly_app -E UTF8 console && gunzip -c ~/console_$(date +"%Y-%m-%d").gz | psql -U surfly_app console