Yet another hot back up tool for PostgreSQL.
pg_bman
is similar to pg_rman, but can take a backup from a remote server.
pg_bman
is similar to pgbarman, but requires neither ssh nor rsync for take a backup.
Notice: To restore a database, ftp or scp is required.
リモートホストにバックアップできるpg_rmanみたいなツール。 標準の通信プロトコル(libpq)だけでオンラインのフルバックアップとインクリメンタルバックアップができます。sshもrsyncもftpも不要です。
PostgreSQL BackupServer
192.168.1.100 192.168.1.200
+---+ libpq +---+
| |============>| |
+---+ +---+
If you can make an archiving log directory under $PGDATA, make there.
$ mkdir /usr/local/pgsql/data/archives
If you cannot make it under $PGDATA, make a directory where you can, and link to under $PGDATA.
$ mkdir /home/postgres/archives
$ ln -s /home/postgres/archives /usr/local/pgsql/data/archives
If you cannot link to a directory under $PGDATA, make a directory where you can, and install the pg_bman extension.
$ mkdir /home/postgres/archives
$ cd ~/contrib/
$ unzip pg_bman.zip
$ cd pg_bman
$ make && make install
$ psql sampledb
psql (9.3.0)
Type "help" for help.
sampledb=# CREATE EXTENSION pg_bman;
max_wal_senders = 5
wal_level = archive
archive_mode = on
archive_command = 'cp %p /usr/local/pgsql/data/archives/%f'
host all all 192.168.1.200/32 trust
host replication all 192.168.1.200/32 trust
Assume that the PostgreSQL binary and the source code installed (here, installation directories are "/usr/local/pgsql/" and "/usr/local/src/postgresql/").
Unzip pg_bman
to the contrib directory, make.
$ cd /usr/local/src/postgresql/contrib/
$ unzip pg_bman.zip
$ cd pg_bman
$ make
Copy pg_bman
and pg_arcivebackup
to a directory where you like (here, installation directory is "/usr/local/bin").
$ cp pg_archivebackup /usr/local/bin
$ cp pg_bman /usr/local/bin
$ chmod +x /usr/local/bin/pg_bman
Here, "home/postgres/BACKUP" is a repository directory.
$ mkdir /home/postgres/BACKUP
Absolute paths only. Do not forget to check "EXTENSION_MODE".
##-------------------------
## Global Options
##-------------------------
# If you installed the pg_bman extension on the PostgreSQL, set "ON"
EXTENSION_MODE="OFF"
GZIP_MODE="ON"
##-------------------------
## Backup Server
##-------------------------
REPOSITORY="/home/postgres/BACKUP"
PG_ARCHIVEBACKUP="/usr/local/bin/pg_archivebackup"
PGHOME="/usr/local/pgsql"
PG_BASEBACKUP=$PGHOME/bin/pg_basebackup
RECOVERY_CONF_SAMPLE=$PGHOME/share/recovery.conf.sample
##-------------------------
## PostgreSQL Server
##-------------------------
PGDATA="/usr/local/pgsql/data"
ARCHIVINGLOG_DIR=$PGDATA/archives
HOST="127.0.0.1"
DB="sampledb"
USER="postgres"
PORT="5432"
PASSWORD=""
# This directory is written to the restore_command on a recovery.conf.
ARCHIVAL_STORAGE_DIR="/home/postgres/restore_archives"
$ pg_bman BACKUP FULL
$ pg_bman BACKUP INCREMENTAL
$ pg_bman SHOW
$ pg_bman SHOW
1:Basebackup20140710-200012 (TimeLineID=00000001)
0:Fullbackup
Incremental:
1:20140710-201046
2:20140710-202041
2:Basebackup20140710-210018 (TimeLineID=00000001)
0:Fullbackup
Incremental:
1:20140710-212040
3:Basebackup20140710-220009 (TimeLineID=00000001)
0:Fullbackup
Incremental:
1:20140711-221013
2:20140711-222023 <-- Our choice.
3:20140711-223011
4:20140711-224041
Here, we choose basebackup_no = 3
, and incrementalbackup_no = 2
(timestamp=20140711-222023).
RESTORE command sets up a base backup, archive logs you need, and a recovery.conf file under the "$REPOSITORY/Restore" directory.
$ pg_bman RESTORE 3 2
How to restore your PostgreSQL:
(1) Make a database cluster.
mkdir $PGDATA && chmod 700 $PGDATA
cd $PGDATA
scp BackupServer:$REPOSITORY/Restore/basebackup/base.tar.gz .
tar xvfz base.tar.gz
(2) Copy the recovery.conf file.
scp BackupServer:$REPOSITORY/Restore/recovery.conf $PGDATA
(3) Copy archive logs.
mkdir $ARCHIVAL_STORAGE_DIR
cd $ARCHIVAL_STORAGE_DIR
scp BackupServer:$REPOSITORY/Restore/incrementalbackup/* .
(4) Start!