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.
I’m going to use PostgreSQL APT repository to download the latest package of barman.
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
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:
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
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
; 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
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 backup openerp
I had a problem with the backup the first time the INCOMING_WALS_DIRECTORY was not created. Check your Database server logs.
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