Re: [RFC] Incremental backup v3: incremental PoC

Hi Marco,

On 16/01/15 16:55, Marco Nenciarini wrote:

On 14/01/15 17:22, Gabriele Bartolini wrote:

My opinion, Marco, is that for version 5 of this patch, you:

1) update the information on the wiki (it is outdated - I know you have
been busy with LSN map optimisation)

Done.

2) modify pg_basebackup in order to accept a directory (or tar file) and
automatically detect the LSN from the backup profile

New version of patch attached. The -I parameter now requires a backup
profile from a previous backup. I've added a sanity check that forbid
incremental file level backups if the base timeline is different from
the current one.

3) add the documentation regarding the backup profile and pg_basebackup

Next on my TODO list.

Once we have all of this, we can continue trying the patch. Some
unexplored paths are:

* tablespace usage

I've improved my pg_restorebackup python PoC. It now supports tablespaces.

About tablespaces, I noticed that any pointing to tablespace locations is
lost during the recovery of an incremental backup changing the tablespace
mapping (-T option). Here the steps I followed:

- creating and filling a test database obtained through pgbench

psql -c "CREATE DATABASE pgbench"
pgbench -U postgres -i -s 5 -F 80 pgbench

- a first base backup with pg_basebackup:

mkdir -p backups/$(date '+%d%m%y%H%M')/data && pg_basebackup -v -F
p -D backups/$(date '+%d%m%y%H%M')/data -x

- creation of a new tablespace, alter the table "pgbench_accounts" to
set the new tablespace:

mkdir -p /home/gbroccolo/pgsql/tbls
psql -c "CREATE TABLESPACE tbls LOCATION '/home/gbroccolo/pgsql/tbls'"
psql -c "ALTER TABLE pgbench_accounts SET TABLESPACE tbls" pgbench

- Doing some work on the database:

pgbench -U postgres -T 120 pgbench

- a second incremental backup with pg_basebackup specifying the new
location for the tablespace through the tablespace mapping:

mkdir -p backups/$(date '+%d%m%y%H%M')/data backups/$(date
'+%d%m%y%H%M')/tbls && pg_basebackup -v -F p -D backups/$(date
'+%d%m%y%H%M')/data -x -I backups/2601151641/data/backup_profile -T
/home/gbroccolo/pgsql/tbls=/home/gbroccolo/pgsql/backups/$(date
'+%d%m%y%H%M')/tbls

- a recovery based on the tool pg_restorebackup.py attached in
/messages/by-id/54B9428E.9020001@2ndquadrant.it

./pg_restorebackup.py backups/2601151641/data
backups/2601151707/data /tmp/data -T
/home/gbroccolo/pgsql/backups/2601151707/tbls=/tmp/tbls

In the last step, I obtained the following stack trace:

Traceback (most recent call last):
File "./pg_restorebackup.py", line 74, in <module>
shutil.copy2(base_file, dest_file)
File "/home/gbroccolo/.pyenv/versions/2.7.5/lib/python2.7/shutil.py",
line 130, in copy2
copyfile(src, dst)
File "/home/gbroccolo/.pyenv/versions/2.7.5/lib/python2.7/shutil.py",
line 82, in copyfile
with open(src, 'rb') as fsrc:
IOError: [Errno 2] No such file or directory:
'backups/2601151641/data/base/16384/16406_fsm'

Any idea on what's going wrong?

Thanks,
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it