Home » Database » OpenERP Backup and Point-in-time Recovery

OpenERP Backup and Point-in-time Recovery

OpenERP backup is one of the main things to do when installing OpenERP on a production server. But for many of us, simple daily backup won’t do the job. We need to utilize PostgreSQL Point-in-time Recovery to reduce the downtime when disaster happens.

PostgreSQL server records all transaction like insert, update or delete and write it into a file called write-ahead (WAL) log file. This mechanism use the history records stored in WAL file to do roll-forward changes made since last full database backup.

This backups are important to critical systems that can not afford even a minute of downtime. With Point-in-time Recovery, database backup downtime can be totally eliminated because this mechanism can make database backup and system access happened at the same time.

For this purpose I’ll be using barman which is (backup and recovery manager) administration tool for disaster recovery of PostgreSQL servers written in Python.

I’m going to use dedicated backup server where the barman will be installed and will pull the backups from the main PostgreSQL server.

Installing barman

I’m going to use PostgreSQL APT repository to download the latest package of barman.

vi /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get install barman

Configuring the servers

Now we need to create bidirectional SSH connection between the barman user on the backup server and the postgres user on the main database server.

On the Backup Server login as barman

ssh-keygen -t rsa
cat ~/.ssh/id_rsa.pub | ssh postgres@POSTGRESQL_SERVER_IP 'cat >> ~/.ssh/authorized_keys'

On the main Database Server login as postgres

ssh-keygen -t rsa
cat ~/.ssh/id_rsa.pub | ssh barman@BACKUP_SERVER_IP 'cat >> ~/.ssh/authorized_keys'

You should know that you should not be giving passphrase while generating the ssh keys in both the machines. Also on both servers uncomment the AuthorizedKeysFile from /etc/ssh/sshd_config

vi /etc/ssh/sshd_config
AuthorizedKeysFile      %h/.ssh/authorized_keys
/etc/init.d/ssh restart

Now we make sure that we can connect to the main Database server from the backup server. In pg_hba.conf add the following line:

vi /etc/postgresql/<db_version>/main/pg_hba.conf
host  all  all  <BACKUP_SERVER_IP>/32 trust

With that setup through, we can test if we can connect to database server from the backup server with the following command.

By default PostgreSQL listens only on localhost check for listen_addresses in /etc/postgresql/<db_version>/main/postgresql.conf

psql -c 'SELECT version()' -U postgres -h POSTGRESQL_SERVER_IP

Configuring barman

Since I’ll be using this Backup server to backup multiple PostgreSQL servers I’m going to use separate config files. In /etc/barman.conf uncomment configuration_files_directory path

vi /etc/barman.conf
; Directory of configuration files. Place your sections in separate files with .conf extension
; For example place the 'main' server section in /etc/barman.d/main.conf
configuration_files_directory = /etc/barman.d
mkdir /etc/barman.d
vi /etc/barman.d/openerp.conf
[openerp]
description = PosgreSQL Server for OpenERP
ssh_command = ssh postgres@POSTGRESQL_SERVER_IP
conninfo = host=POSTGRESQL_SERVER_IP user=postgres

Now we need to find the INCOMING_WALS_DIRECTORY so we can enable WAL archiving on the Database server

barman show-server openerp

default is: /var/lib/barman/openerp/incoming keep this to put it in your Database server when enabling WAL archiving

Now on the main Database server edit postgresql.conf and enable WAL archiving

vi /etc/postgresql/<db_version>/main/postgresql.conf
wal_level = 'archive' # Needed if using PostgreSQL >= 9.0
archive_mode = on
archive_command = 'rsync -a %p barman@BACKUP_SERVER_IP:INCOMING_WALS_DIRECTORY/%f'

Barman commands

Execure backup

barman backup openerp

I had a problem with the backup the first time the INCOMING_WALS_DIRECTORY was not created. Check your Database server logs.

List backups

barman list-backup openerp

Recover the server

barman recover openerp 20130422T213445 /path/to/recover/directory

Now start your server

pg_ctl -D /path/to/recover/directory start

Recover to Point-in-time using timestamp or transaction ID

  • –target-time TARGET_TIME: to specify a timestamp
  • –target-xid TARGET_XID: to specify a transaction ID

For advanced configuration see http://docs.pgbarman.org/
Barman homepage: http://www.pgbarman.org/

About Nikola Stojanoski

System Administrator and Developer. Giving back to the community by blogging about my problems, solutions and practical howto's.