Infrastructure at your Service

Daniel Westermann

EDB BART 2.0 – How to upgrade and block level incremental backups

We already have some posts on how you can use EDB BART to backup and restore your PostgreSQL instances from one central server (EnterpriseDB Backup and Recovery Tool (BART), getting started with postgres plus advanced server (2) – setting up a backup and recovery server). The current version you can download from the EnterpriseDB website is 1.1 but version 2.0 is in beta and can be tested already. The main new feature is that BART 2.0 allows you to perform block level incremental backups of your PostgreSQL instances starting with PostgreSQL 9.5. In this post we’ll be looking at that feature and we’ll upgrade from BART 1.1. Lets go …

In my test environment I have BART 1.1 configured and working against an EDB Postgres Plus 9.6 instance:

postgres@edbbart:/home/postgres/ [pg950] bart -v
bart (EnterpriseDB) 1.1.1
postgres@edbbart:/home/postgres/ [pg950] bart SHOW-BACKUPS -s pg3
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS  
                                                                                                         
 pg3           1489078376562   2017-03-09 17:53:01 CET   61.93 MB      16.00 MB      1           active  

What do I need to do to upgrade to BART 2.0? Quite easy: Either you have access to the EDB yum repositories (this requires a username and a password) or just install the rpm you which can download here. I will install BART 2.0 from the EDB repositories:

postgres@edbbart:/home/postgres/ [pg950] sudo yum install edb-bart20

That’s it. The first thing to highlight is that the location where BART gets installed changed. Starting with version 2.0 BART will be installed under “/usr/edb/” instead of “/usr/edb-bart-***”.

postgres@edbbart:/home/postgres/ [pg950] ls -la /usr/edb/bart2.0/
total 16
drwxr-xr-x. 5 root root    59 Mar  9 17:56 .
drwxr-xr-x. 4 root root    32 Mar  9 17:56 ..
-rw-r--r--. 1 root root 15272 Feb 21 10:00 bart_license.txt
drwxr-xr-x. 2 root root    36 Mar  9 17:56 bin
drwxr-xr-x. 2 root root    21 Mar  9 17:56 etc
drwxr-xr-x. 2 root root    56 Mar  9 17:56 lib

We always recommend to work with links to point to you current BART installation to make life more easy. If you followed that rule all you have to do is:

postgres@edbbart:/home/postgres/ [pg950] sudo rm /usr/edb-bart
postgres@edbbart:/home/postgres/ [pg950] sudo ln -s /usr/edb/bart2.0/ /usr/edb-bart
postgres@edbbart:/home/postgres/ [pg950] bart -v
bart (EnterpriseDB) 2.0.0

… and you point to the new binaries. For sure you want to copy over your BART 1.1 configuration to the new location:

postgres@edbbart:/home/postgres/ [pg950] sudo cp /usr/edb-bart-1.1/etc/bart.cfg /usr/edb/bart2.0/etc/

From now on you are working with BART 2.0:

postgres@edbbart:/home/postgres/ [pg950] bart show-servers
SERVER NAME         : pg3
BACKUP FRIENDLY NAME: PG3_%year-%month-%dayT%hour:%minute
HOST NAME           : 192.168.22.37
USER NAME           : backupuser
PORT                : 4445
REMOTE HOST         : postgres@192.168.22.37
RETENTION POLICY    : 2016-12-09 18:02:23 CET
DISK UTILIZATION    : 189.93 MB
NUMBER OF ARCHIVES  : 8
ARCHIVE PATH        : /u90/pgdata/backup/pg3/archived_wals
ARCHIVE COMMAND     : scp %p postgres@edbbart:/u90/pgdata/backup/pg3/archived_wals/%f
XLOG METHOD         : fetch
WAL COMPRESSION     : enabled
TABLESPACE PATH(s)  : 
INCREMENTAL BACKUP  : DISABLED      <============================ NEW
DESCRIPTION         : "postgres PG3"

You’ll immediately notice that there is a new line in the output: “INCREMENTAL BACKUP”. Lets keep that for later. What I am most interested in right now is: Can I still backup my PostgreSQL instances with the new version of BART:

postgres@edbbart:/home/postgres/ [pg950] bart backup -s pg3
INFO:  creating backup for server 'pg3'
INFO:  backup identifier: '1489078978132'
63413/63413 kB (100%), 1/1 tablespace

INFO:  backup completed successfully
WARNING: log_timezone is not set in the server, using the local timezone information
INFO:  backup checksum: d318744e42819f76b137edf197a0b59b of base.tar
INFO:  
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1489078978132
BACKUP NAME: PG3_2017-03-09T18:02
BACKUP PARENT: none
BACKUP LOCATION: /u90/pgdata/backup/pg3/1489078978132
BACKUP SIZE: 61.93 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
 ChkSum                             File      
 d318744e42819f76b137edf197a0b59b   base.tar  
TABLESPACE(s): 0
START WAL LOCATION: 00000001000000000000000D
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2017-03-09 18:03:00 CET
STOP TIME: 2017-03-09 18:02:59 CET
TOTAL DURATION: 0 sec(s)

Looks fine. I did not need to change anything in the configuration file and can just start using BART 2.0. So, now I have two backups:

postgres@edbbart:/home/postgres/ [pg950] bart show-backups -s PG3
 SERVER NAME   BACKUP ID       BACKUP NAME            PARENT ID   BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS  
                                                                                                                                            
 pg3           1489078978132   PG3_2017-03-09T18:02   none        2017-03-09 18:02:59 CET   61.93 MB      16.00 MB      1           active  
 pg3           1489078376562   PG3_2017-03-09T17:52   none        2017-03-09 17:53:01 CET   61.93 MB      32.00 MB      2           active  

Time to do my first incremental backup. What do I need to do? First of all we need to disable wal compression as this is not supported with incremental backups:

postgres@edbbart:/home/postgres/ [pg950] cat /usr/edb-bart/etc/bart.cfg | grep -i COMPRE
wal_compression = disabled

The next thing to do is obviously to enable incremental backups:

postgres@edbbart:/home/postgres/ [pg950] cat /usr/edb-bart/etc/bart.cfg | grep increme
allow_incremental_backups = enabled

My complete BART configuration then looks like this:

[BART]
bart-host = postgres@edbbart
backup_path = /u90/pgdata/backup
pg_basebackup_path = /usr/edb/as9.6/bin/pg_basebackup
xlog-method = fetch
retention_policy = 3 MONTHS
logfile = /var/log/bart.logs

[PG3]
host = 192.168.22.37
port = 4445
user = backupuser
backup-name = PG3_%year-%month-%dayT%hour:%minute
remote-host = postgres@192.168.22.37
description = "postgres PG3"
wal_compression = disabled
allow_incremental_backups = enabled

Make sure that the show-servers command displays what you expect:

postgres@edbbart:/home/postgres/ [pg950] bart show-servers
SERVER NAME         : pg3
BACKUP FRIENDLY NAME: PG3_%year-%month-%dayT%hour:%minute
HOST NAME           : 192.168.22.37
USER NAME           : backupuser
PORT                : 4445
REMOTE HOST         : postgres@192.168.22.37
RETENTION POLICY    : 2016-12-10 16:53:05 CET
DISK UTILIZATION    : 283.86 MB
NUMBER OF ARCHIVES  : 10
ARCHIVE PATH        : /u90/pgdata/backup/pg3/archived_wals
ARCHIVE COMMAND     : scp %p postgres@edbbart:/u90/pgdata/backup/pg3/archived_wals/%f
XLOG METHOD         : fetch
WAL COMPRESSION     : disabled
TABLESPACE PATH(s)  : 
INCREMENTAL BACKUP  : ENABLED
DESCRIPTION         : "postgres PG3"

With BART 2.0 there is a new component called the “WAL scanner”. The binary is at the same location as BART itself:

postgres@edbbart:/home/postgres/ [pg950] ls -la /usr/edb/bart2.0/bin/bart-scanner 
-rwxr-xr-x. 1 root root 603832 Feb 21 10:01 /usr/edb/bart2.0/bin/bart-scanner

What does it do? The WAL scanner (as the name implies) scans the WALs that are archived from your PostgreSQL instances to the BART host for changes and then writes a so called “modified block map (MBM)” (this is the reason why wal compression is not supported). As this should happen as soon as the WALs arrive on the BART host the WAL scanner should run all the time. To start it in daemon mode:

echo $PATH / include bart bin
postgres@edbbart:/home/postgres/ [pg950] /usr/edb-bart/bin/bart-scanner --daemon
postgres@edbbart:/home/postgres/ [pg950] ps -ef | grep scanner
postgres  2473     1  0 16:58 ?        00:00:00 /usr/edb-bart/bin/bart-scanner --daemon
postgres  2474  2473  0 16:58 ?        00:00:00 /usr/edb-bart/bin/bart-scanner --daemon
postgres  2476  2185  0 16:58 pts/0    00:00:00 grep --color=auto scanner

This will fork one wal scanner process for each PostgreSQL instance configured for incremental backups.

Now we need a new full backup (the scanner did not run when we did the previous backups and the previous WAL files were compressed, so they can not be used):

postgres@edbbart:/home/postgres/ [pg950] bart backup -s PG3 --backup-name full0
INFO:  creating backup for server 'pg3'
INFO:  backup identifier: '1489161554590'
63416/63416 kB (100%), 1/1 tablespace

INFO:  backup completed successfully
WARNING: log_timezone is not set in the server, using the local timezone information
INFO:  backup checksum: f1c917edd0734c155ddace77bfbc3a17 of base.tar
INFO:  
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1489161554590
BACKUP NAME: full0
BACKUP PARENT: none
BACKUP LOCATION: /u90/pgdata/backup/pg3/1489161554590
BACKUP SIZE: 61.93 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
 ChkSum                             File      
 f1c917edd0734c155ddace77bfbc3a17   base.tar  

TABLESPACE(s): 0
START WAL LOCATION: 00000001000000000000000F
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2017-03-10 16:59:14 CET
STOP TIME: 2017-03-10 16:59:18 CET
TOTAL DURATION: 4 sec(s)

postgres@edbbart:/home/postgres/ [pg950] bart show-backups -s pg3
 SERVER NAME   BACKUP ID       BACKUP NAME            PARENT ID   BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS  
                                                                                                                                            
 pg3           1489161554590   full0                  none        2017-03-10 16:59:18 CET   61.93 MB      16.00 MB      1           active  
 pg3           1489078978132   PG3_2017-03-09T18:02   none        2017-03-09 18:02:59 CET   61.93 MB      32.00 MB      2           active  
 pg3           1489078376562   PG3_2017-03-09T17:52   none        2017-03-09 17:53:01 CET   61.93 MB      32.00 MB      2           active  

Lets do some work on the PostgreSQL which we are backing up to generate WAL:

(postgres@[local]:4445) [postgres] > create table t1 ( a int );
CREATE TABLE
Time: 2.972 ms
(postgres@[local]:4445) [postgres] > insert into t1 (a) values (generate_series(1,1000000));
INSERT 0 1000000
Time: 512.806 ms
(postgres@[local]:4445) [postgres] > select * from pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/10C19D98
(1 row)

Do the first incremental backup based on the full backup from above:

postgres@edbbart:/home/postgres/ [pg950] bart backup -s pg3 -F p --parent 1489161554590 --backup-name incr1
INFO:  creating incremental backup for server 'pg3'
INFO:  checking mbm files /u90/pgdata/backup/pg3/archived_wals
INFO:  new backup id generated 1489161760522
INFO:  reading directory /u90/pgdata/backup/pg3/archived_wals
INFO:  all files processed
WARNING: log_timezone is not set in the server, using the local timezone information
INFO:  server has been successfully backed up

Now you can do another incremental backup but from the incremental backup taken above instead of the full backup:

postgres@edbbart:/home/postgres/ [pg950] bart BACKUP -s pg3 -F p --parent 1489161760522 --backup-name incr2
INFO:  creating incremental backup for server 'pg3'
INFO:  checking mbm files /u90/pgdata/backup/pg3/archived_wals
INFO:  new backup id generated 1489162048588
INFO:  reading directory /u90/pgdata/backup/pg3/archived_wals
INFO:  all files processed
WARNING: log_timezone is not set in the server, using the local timezone information
INFO:  server has been successfully backed up

So, what do we have now:

postgres@edbbart:/home/postgres/ [pg950] bart show-backups
 SERVER NAME   BACKUP ID       BACKUP NAME            PARENT ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS  
                                                                                                                                                
 pg3           1489162048588   incr2                  1489161760522   2017-03-10 17:09:51 CET   45.98 MB                                active  
 pg3           1489161760522   incr1                  1489161554590   2017-03-10 17:06:10 CET   67.35 MB                                active  
 pg3           1489161554590   full0                  none            2017-03-10 16:59:18 CET   61.93 MB      112.00 MB     7           active  
 pg3           1489078978132   PG3_2017-03-09T18:02   none            2017-03-09 18:02:59 CET   61.93 MB      32.00 MB      2           active  
 pg3           1489078376562   PG3_2017-03-09T17:52   none            2017-03-09 17:53:01 CET   61.93 MB      32.00 MB      2           active  

Hm this does not really look an improvement. The first incremental backup is even larger than the full backup it is based on. The second one is a bit smaller but as I did not change anything on the source database between the two incremental backups my expectation was that at least the second incremental backup should use far less space. Lets check it on disk:

postgres@edbbart:/u90/pgdata/backup/pg3/ [pg950] pwd
/u90/pgdata/backup/pg3
postgres@edbbart:/u90/pgdata/backup/pg3/ [pg950] du -sh 1489161554590
62M	1489161554590
postgres@edbbart:/u90/pgdata/backup/pg3/ [pg950] du -sh 1489161760522
68M	1489161760522
postgres@edbbart:/u90/pgdata/backup/pg3/ [pg950] du -sh 1489162048588
47M	1489162048588

At least this seems to be consistent. Lets do another one:

postgres@edbbart:/u90/pgdata/backup/pg3/ [pg950] bart backup -s pg3 -F p --parent 1489162048588 --backup-name incr3
INFO:  creating incremental backup for server 'pg3'
INFO:  checking mbm files /u90/pgdata/backup/pg3/archived_wals
INFO:  new backup id generated 1489224698357
INFO:  reading directory /u90/pgdata/backup/pg3/archived_wals
INFO:  all files processed
WARNING: log_timezone is not set in the server, using the local timezone information

postgres@edbbart:/u90/pgdata/backup/pg3/ [pg950] bart show-backups
 SERVER NAME   BACKUP ID       BACKUP NAME            PARENT ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS  
                                                                                                                                                
 pg3           1489224698357   incr3                  1489162048588   2017-03-11 10:31:41 CET   16.58 MB                                active  
 pg3           1489162048588   incr2                  1489161760522   2017-03-10 17:09:51 CET   45.98 MB                                active  
 pg3           1489161760522   incr1                  1489161554590   2017-03-10 17:06:10 CET   67.35 MB                                active  
 pg3           1489161554590   full0                  none            2017-03-10 16:59:18 CET   61.93 MB      160.00 MB     10          active  
 pg3           1489078978132   PG3_2017-03-09T18:02   none            2017-03-09 18:02:59 CET   61.93 MB      32.00 MB      2           active  
 pg3           1489078376562   PG3_2017-03-09T17:52   none            2017-03-09 17:53:01 CET   61.93 MB      32.00 MB      2           active  

Ok, now we can see a real improvement (not sure why there is no improvement for the first ones, need to do more testing). Restores should work as well (I’ll restore the last incremental backup):

postgres@edbbart:/home/postgres/ [pg950] /usr/edb/bart2.0/bin/bart restore -s pg3 -i incr3 -p /var/tmp/restore_test/  -r postgres@localhost
INFO:  restoring incremental backup 'incr3' of server 'pg3'
INFO:  base backup restored
ERROR: failed to overlay modified blocks
command failed with exit code 127
bash: bart: command not found

Hm, what’s that? Why does bart not find bart? Can I restore the full backup?

postgres@edbbart:/home/postgres/ [pg950] /usr/edb/bart2.0/bin/bart restore -s pg3 -i full0 -p /var/tmp/restore_test/  -r postgres@localhost
INFO:  restoring backup 'full0' of server 'pg3'
INFO:  base backup restored
INFO:  archiving is disabled
postgres@edbbart:/home/postgres/ [pg950] ls /var/tmp/restore_test/
backup_label  global        pg_dynshmem    pg_log        pg_notify    pg_snapshots  pg_subtrans  PG_VERSION            postgresql.conf
base          pg_clog       pg_hba.conf    pg_logical    pg_replslot  pg_stat       pg_tblspc    pg_xlog               tablespace_map
dbms_pipe     pg_commit_ts  pg_ident.conf  pg_multixact  pg_serial    pg_stat_tmp   pg_twophase  postgresql.auto.conf

Looks fine, the first incremental:

postgres@edbbart:/home/postgres/ [pg950] /usr/edb/bart2.0/bin/bart restore -s pg3 -i incr1 -p /var/tmp/restore_test/  -r postgres@localhost
INFO:  restoring incremental backup 'incr1' of server 'pg3'
INFO:  base backup restored
ERROR: failed to overlay modified blocks
command failed with exit code 127
bash: bart: command not found

No. Time for the “–debug” mode:

postgres@edbbart:/home/postgres/ [pg950] rm -rf /var/tmp/restore_test/*
postgres@edbbart:/home/postgres/ [pg950] /usr/edb/bart2.0/bin/bart --debug restore -s pg3 -i incr1 -p /var/tmp/restore_test/  -r postgres@localhost
DEBUG: Server: Global, Now: 2017-03-13 12:12:24 CET, RetentionWindow: 7776000 (secs) ==> 2160 hour(s)
DEBUG: Exec Command: ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost exit
DEBUG: Exec Command: ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost test -d /var/tmp/restore_test && echo "exists"
DEBUG: Exec Command: ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost touch /var/tmp/restore_test/tmp-incr1 && echo "exists"
DEBUG: Exec Command: ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost rm -f /var/tmp/restore_test/tmp-incr1
DEBUG: Exec Command: ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost ls -A /var/tmp/restore_test
INFO:  restoring incremental backup 'incr1' of server 'pg3'
DEBUG: restoring backup: 1489161554590
DEBUG: restoring backup to /var/tmp/restore_test
DEBUG: restore command: cat /u90/pgdata/backup/pg3/1489161554590/base.tar | ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost " tar -C /var/tmp/restore_test -xf - "
DEBUG: Exec Command: cat /u90/pgdata/backup/pg3/1489161554590/base.tar | ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost " tar -C /var/tmp/restore_test -xf - "
INFO:  base backup restored
DEBUG: backup '1489161554590' restored to '/var/tmp/restore_test'
DEBUG: restoring backup: 1489161760522
DEBUG: Exec Command: cd /u90/pgdata/backup/pg3/1489161760522/base && tar -cf - * | ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost " tar -C /var/tmp/restore_test -xf - "
DEBUG: Exec Command: cd . && scp -o BatchMode=yes -o PasswordAuthentication=no  -r /u90/pgdata/backup/pg3/1489161760522/base/../1489161760522.cbm postgres@localhost:/var/tmp/restore_test
DEBUG: Exec Command: ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost "bart --debug apply-incremental --source /var/tmp/restore_test --tsmap '' --backup 1489161760522"
ERROR: failed to overlay modified blocks
command failed with exit code 127
bash: bart: command not found

The command that does not seem to work is this one:

DEBUG: Exec Command: ssh -o BatchMode=yes -o PasswordAuthentication=no postgres@localhost "bart --debug apply-incremental --source /var/tmp/restore_test --tsmap '' --backup 1489161760522"

Lets do that manually:

ostgres@edbbart:/home/postgres/ [pg950] bart --debug apply-incremental --source /var/tmp/restore_test --tsmap '' --backup 1489161760522
DEBUG: loading '/var/tmp/restore_test/1489161760522.cbm' MBM/CBM file 
DEBUG: mbm chksum: old f60a435d4d3709302e5b7acc3f8d8ecb, new f60a435d4d3709302e5b7acc3f8d8ecb
DEBUG: applying incremental 1489161760522 (pid 3686)
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/1247.blk to /var/tmp/restore_test/base/15184/1247
src size: 16384, dst size: 139264
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/1247.blk, to /var/tmp/restore_test/base/15184/1247
src size: 16384, dst size: 139264
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/1249.blk to /var/tmp/restore_test/base/15184/1249
src size: 8192, dst size: 753664
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/1249.blk, to /var/tmp/restore_test/base/15184/1249
src size: 8192, dst size: 753664
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/1259.blk to /var/tmp/restore_test/base/15184/1259
src size: 8192, dst size: 139264
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/1259.blk, to /var/tmp/restore_test/base/15184/1259
src size: 8192, dst size: 139264
DEBUG: copying relation node files from (src): /var/tmp/restore_test/base/15184/16386.all, to (dst): /var/tmp/restore_test/base/15184/16386
src size: 36249600, dst size: 0
DEBUG: Exec Command: cp --preserve /var/tmp/restore_test/base/15184/16386.all /var/tmp/restore_test/base/15184/16386
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/2608.blk to /var/tmp/restore_test/base/15184/2608
src size: 16384, dst size: 663552
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/2608.blk, to /var/tmp/restore_test/base/15184/2608
src size: 16384, dst size: 663552
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/2658.blk to /var/tmp/restore_test/base/15184/2658
src size: 8192, dst size: 204800
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/2658.blk, to /var/tmp/restore_test/base/15184/2658
src size: 8192, dst size: 204800
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/2659.blk to /var/tmp/restore_test/base/15184/2659
src size: 8192, dst size: 139264
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/2659.blk, to /var/tmp/restore_test/base/15184/2659
src size: 8192, dst size: 139264
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/2662.blk to /var/tmp/restore_test/base/15184/2662
src size: 8192, dst size: 32768
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/2662.blk, to /var/tmp/restore_test/base/15184/2662
src size: 8192, dst size: 32768
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/2663.blk to /var/tmp/restore_test/base/15184/2663
src size: 8192, dst size: 57344
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/2663.blk, to /var/tmp/restore_test/base/15184/2663
src size: 8192, dst size: 57344
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/2673.blk to /var/tmp/restore_test/base/15184/2673
src size: 16384, dst size: 540672
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/2673.blk, to /var/tmp/restore_test/base/15184/2673
src size: 16384, dst size: 540672
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/2674.blk to /var/tmp/restore_test/base/15184/2674
src size: 24576, dst size: 557056
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/2674.blk, to /var/tmp/restore_test/base/15184/2674
src size: 24576, dst size: 557056
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/2703.blk to /var/tmp/restore_test/base/15184/2703
src size: 8192, dst size: 40960
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/2703.blk, to /var/tmp/restore_test/base/15184/2703
src size: 8192, dst size: 40960
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/2704.blk to /var/tmp/restore_test/base/15184/2704
src size: 16384, dst size: 57344
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/2704.blk, to /var/tmp/restore_test/base/15184/2704
src size: 16384, dst size: 57344
DEBUG: copying blocks for relation from: /var/tmp/restore_test/base/15184/3455.blk to /var/tmp/restore_test/base/15184/3455
src size: 8192, dst size: 49152
DEBUG: block copy complete: from /var/tmp/restore_test/base/15184/3455.blk, to /var/tmp/restore_test/base/15184/3455
src size: 8192, dst size: 49152

.. and that works. So, next test:

postgres@edbbart:/home/postgres/ [pg950] ssh postgres@localhost "bart -version"
bash: bart: command not found
postgres@edbbart:/home/postgres/ [pg950] ssh postgres@localhost "which bart"
which: no bart in (/usr/local/bin:/usr/bin)

Here we have the issue. As we do not get a login shell for these types of ssh commands:

postgres@edbbart:/home/postgres/ [pg950] echo "PATH=\$PATH:/usr/edb-bart/bin/" >> ~/.bashrc
postgres@edbbart:/home/postgres/ [pg950] echo "export PATH" >> ~/.bashrc
postgres@edbbart:/home/postgres/ [pg950] ssh postgres@localhost "which bart"
/usr/edb-bart/bin/bart

Try again:

postgres@edbbart:/home/postgres/ [pg950] rm -rf /var/tmp/restore_test/*
postgres@edbbart:/home/postgres/ [pg950] /usr/edb/bart2.0/bin/bart restore -s pg3 -i incr3 -p /var/tmp/restore_test/  -r postgres@localhostINFO:  restoring incremental backup 'incr3' of server 'pg3'
INFO:  base backup restored
INFO:  archiving is disabled

… and it works. But: This does mean that you have to install BART on all the hosts where you have a PostgreSQL instance if you want to restore to the same host where the instance is running. Not sure if I really like that (or I completely missed something) …

 

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure