[RFC] Incremental backup v3: incremental PoC
Hi Hackers,
following the advices gathered on the list I've prepared a third partial
patch on the way of implementing incremental pg_basebackup as described
here https://wiki.postgresql.org/wiki/Incremental_backup
== Changes
Compared to the previous version I've made the following changes:
* The backup_profile is not optional anymore. Generating it is cheap
enough not to bother the user with such a choice.
* I've isolated the code which detects the maxLSN of a segment in a
separate getMaxLSN function. At the moment it works scanning the whole
file, but I'm looking to replace it in the next versions.
* I've made possible to request an incremental backup passing a "-I
<LSN>" option to pg_basebackup. It is probably too "raw" to remain as
is, but it's is useful at this stage to test the code.
* I've modified the backup label to report the fact that the backup was
taken with the incremental option. The result will be something like:
START WAL LOCATION: 0/52000028 (file 000000010000000000000052)
CHECKPOINT LOCATION: 0/52000060
INCREMENTAL FROM LOCATION: 0/51000028
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2014-10-14 16:05:04 CEST
LABEL: pg_basebackup base backup
== Testing it
At this stage you can make an incremental file-level backup using this
procedure:
pg_basebackup -v -F p -D /tmp/x -x
LSN=$(awk '/^START WAL/{print $4}' /tmp/x/backup_profile)
pg_basebackup -v -F p -D /tmp/y -I $LSN -x
the result will be an incremental backup in /tmp/y based on the full
backup on /tmp/x.
You can "reintegrate" the incremental backup in the /tmp/z directory
with the following little python script, calling it as
./recover.py /tmp/x /tmp/y /tmp/z
----
#!/usr/bin/env python
# recover.py
import os
import shutil
import sys
if len(sys.argv) != 4:
print >> sys.stderr, "usage: %s base incremental destination"
sys.exit(1)
base=sys.argv[1]
incr=sys.argv[2]
dest=sys.argv[3]
if os.path.exists(dest):
print >> sys.stderr, "error: destination must not exist (%s)" % dest
sys.exit(1)
profile=open(os.path.join(incr, 'backup_profile'), 'r')
for line in profile:
if line.strip() == 'FILE LIST':
break
shutil.copytree(incr, dest)
for line in profile:
tblspc, lsn, sent, date, size, path = line.strip().split('\t')
if sent == 't' or lsn=='\\N':
continue
base_file = os.path.join(base, path)
dest_file = os.path.join(dest, path)
shutil.copy2(base_file, dest_file)
----
It has obviously to be replaced by a full-fledged user tool, but it is
enough to test the concept.
== What next
I would to replace the getMaxLSN function with a more-or-less persistent
structure which contains the maxLSN for each data segment.
To make it work I would hook into the ForwardFsyncRequest() function in
src/backend/postmaster/checkpointer.c and update an in memory hash every
time a block is going to be fsynced. The structure could be persisted on
disk at some time (probably on checkpoint).
I think a good key for the hash would be a BufferTag with blocknum
"rounded" to the start of the segment.
I'm here asking for comments and advices on how to implement it in an
acceptable way.
== Disclaimer
The code here is an intermediate step, it does not contain any
documentation beside the code comments and will be subject to deep and
radical changes. However I believe it can be a base to allow PostgreSQL
to have its file-based incremental backup, and a block-based incremental
backup after it.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
Attachments:
file-based-incremental-backup.patchtext/plain; charset=UTF-8; name=file-based-incremental-backup.patch; x-mac-creator=0; x-mac-type=0Download+422-135
I've noticed that I missed to add this to the commitfest.
I've just added it.
It is not meant to end up in a committable state, but at this point I'm
searching for some code review and more discusison.
I'm also about to send an additional patch to implement an LSN map as an
additional fork for heap files.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
I've noticed that I missed to add this to the commitfest.
I've just added it.
It is not meant to end up in a committable state, but at this point I'm
searching for some code review and more discusison.
I'm also about to send an additional patch to implement an LSN map as an
additional fork for heap files.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
On Mon, Jan 5, 2015 at 7:56 PM, Marco Nenciarini
<marco.nenciarini@2ndquadrant.it> wrote:
I've noticed that I missed to add this to the commitfest.
I've just added it.
It is not meant to end up in a committable state, but at this point I'm
searching for some code review and more discusison.I'm also about to send an additional patch to implement an LSN map as an
additional fork for heap files.
Moved to CF 2015-02.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Oct 14, 2014 at 1:17 PM, Marco Nenciarini
<marco.nenciarini@2ndquadrant.it> wrote:
I would to replace the getMaxLSN function with a more-or-less persistent
structure which contains the maxLSN for each data segment.To make it work I would hook into the ForwardFsyncRequest() function in
src/backend/postmaster/checkpointer.c and update an in memory hash every
time a block is going to be fsynced. The structure could be persisted on
disk at some time (probably on checkpoint).I think a good key for the hash would be a BufferTag with blocknum
"rounded" to the start of the segment.I'm here asking for comments and advices on how to implement it in an
acceptable way.
I'm afraid this is going to be quite tricky to implement. There's no
way to make the in-memory hash table large enough that it can
definitely contain all of the entries for the entire database. Even
if it's big enough at a certain point in time, somebody can create
100,000 new tables and now it's not big enough any more. This is not
unlike the problem we had with the visibility map and free space map
before 8.4 (and you probably remember how much fun that was).
I suggest leaving this out altogether for the first version. I can
think of three possible ways that we can determine which blocks need
to be backed up. One, just read every block in the database and look
at the LSN of each one. Two, maintain a cache of LSN information on a
per-segment (or smaller) basis, as you suggest here. Three, scan the
WAL generated since the incremental backup and summarize it into a
list of blocks that need to be backed up. This last idea could either
be done when the backup is requested, or it could be done as the WAL
is generated and used to populate the LSN cache. In the long run, I
think some variant of approach #3 is likely best, but in the short
run, approach #1 (scan everything) is certainly easiest. While it
doesn't optimize I/O, it still gives you the benefit of reducing the
amount of data that needs to be transferred and stored, and that's not
nothing. If we get that much working, we can improve things more
later.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, 6 Jan 2015 08:26:22 -0500
Robert Haas <robertmhaas@gmail.com> wrote:
Three, scan the WAL generated since the incremental backup and summarize it
into a list of blocks that need to be backed up.
This can be done from the archive side. I was talking about some months ago
now:
/messages/by-id/51C4DD20.3000103@free.fr
One of the traps I could think of it that it requires "full_page_write=on" so
we can forge each block correctly. So collar is that we need to start a diff
backup right after a checkpoints then.
And even without "full_page_write=on", maybe we could add a function, say
"pg_start_backupdiff()", which would force to log full pages right after it
only, the same way "full_page_write" does after a checkpoint. Diff backups would
be possible from each LSN where we pg_start_backupdiff'ed till whenever.
Building this backup by merging versions of blocks from WAL is on big step.
But then, there is a file format to define, how to restore it and to decide what
tools/functions/GUCs to expose to admins.
After discussing with Magnus, he adviced me to wait for a diff backup file
format to emerge from online tools, like discussed here (by the time, that was
Michael's proposal based on pg_basebackup that was discussed). But I wonder how
easier it would be to do this the opposite way? If this idea of building diff
backup offline from archives is possible, wouldn't it remove a lot of trouble
you are discussing here?
Regards,
--
Jehan-Guillaume de Rorthais
Dalibo
http://www.dalibo.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Il 06/01/15 14:26, Robert Haas ha scritto:
I suggest leaving this out altogether for the first version. I can
think of three possible ways that we can determine which blocks need
to be backed up. One, just read every block in the database and look
at the LSN of each one. Two, maintain a cache of LSN information on a
per-segment (or smaller) basis, as you suggest here. Three, scan the
WAL generated since the incremental backup and summarize it into a
list of blocks that need to be backed up. This last idea could either
be done when the backup is requested, or it could be done as the WAL
is generated and used to populate the LSN cache. In the long run, I
think some variant of approach #3 is likely best, but in the short
run, approach #1 (scan everything) is certainly easiest. While it
doesn't optimize I/O, it still gives you the benefit of reducing the
amount of data that needs to be transferred and stored, and that's not
nothing. If we get that much working, we can improve things more
later.
Hi,
The patch now uses the approach #1, but I've just sent a patch that uses
the #2 approach.
54AD016E.9020406@2ndquadrant.it
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
Hi Marco,
could you please send an updated version the patch against the current
HEAD in order to facilitate reviewers?
Thanks,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
2015-01-07 11:00 GMT+01:00 Marco Nenciarini <marco.nenciarini@2ndquadrant.it
Show quoted text
:
Il 06/01/15 14:26, Robert Haas ha scritto:
I suggest leaving this out altogether for the first version. I can
think of three possible ways that we can determine which blocks need
to be backed up. One, just read every block in the database and look
at the LSN of each one. Two, maintain a cache of LSN information on a
per-segment (or smaller) basis, as you suggest here. Three, scan the
WAL generated since the incremental backup and summarize it into a
list of blocks that need to be backed up. This last idea could either
be done when the backup is requested, or it could be done as the WAL
is generated and used to populate the LSN cache. In the long run, I
think some variant of approach #3 is likely best, but in the short
run, approach #1 (scan everything) is certainly easiest. While it
doesn't optimize I/O, it still gives you the benefit of reducing the
amount of data that needs to be transferred and stored, and that's not
nothing. If we get that much working, we can improve things more
later.Hi,
The patch now uses the approach #1, but I've just sent a patch that uses
the #2 approach.54AD016E.9020406@2ndquadrant.it
Regards,
Marco--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
Il 13/01/15 12:53, Gabriele Bartolini ha scritto:
Hi Marco,
could you please send an updated version the patch against the current
HEAD in order to facilitate reviewers?
Here is the updated patch for incremental file based backup.
It is based on the current HEAD.
I'm now working to the client tool to rebuild a full backup starting
from a file based incremental backup.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
Attachments:
file-based-incremental-backup-v4.patchtext/plain; charset=UTF-8; name=file-based-incremental-backup-v4.patch; x-mac-creator=0; x-mac-type=0Download+441-139
Hi Marco,
thank you for sending an updated patch. I am writing down a report of
this initial (and partial) review.
IMPORTANT: This patch is not complete, as stated by Marco. See the
"Conclusions" section for my proposed TODO list.
== Patch application
I have been able to successfully apply your patch and compile it.
Regression tests passed.
== Initial run
I have created a fresh new instance of PostgreSQL and activated streaming
replication to be used by pg_basebackup. I have done a pgbench run with
scale 100.
I have taken a full consistent backup with pg_basebackup (in plain format):
pg_basebackup -v -F p -D $BACKUPDIR/backup-$(date '+%s') -x
I have been able to verify that the backup_profile is correctly placed in
the destination PGDATA directory. Here is an excerpt:
POSTGRESQL BACKUP PROFILE 1
START WAL LOCATION: 0/3000058 (file 000000010000000000000003)
CHECKPOINT LOCATION: 0/300008C
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2015-01-14 10:07:07 CET
LABEL: pg_basebackup base backup
FILE LIST
\N \N t 1421226427 206 backup_label
\N \N t 1421225508 88 postgresql.auto.conf
...
As suggested by Marco, I have manually taken the LSN from this file (next
version must do this automatically).
I have then executed pg_basebackup and activated the incremental feature by
using the LSN from the previous backup, as follows:
LSN=$(awk '/^START WAL/{print $4}' backup_profile)
pg_basebackup -v -F p -D $BACKUPDIR/backup-$(date '+%s') -I $LSN -x
The time taken by this operation has been much lower than the previous one
and the size is much lower (I have not done any operation in the meantime):
du -hs backup-1421226*
1,5G backup-1421226427
17M backup-1421226427
I have done some checks on the file system and then used the prototype of
recovery script in Python written by Marco.
./recover.py backup-1421226427 backup-1421226427 new-data
The cluster started successfully. I have then run a pg_dump of the pgbench
database and were able to reload it on the initial cluster.
== Conclusions
The first run of this patch seems promising.
While the discussion on the LSN map continues (which is mainly an
optimisation of this patch), I would really like to see this patch progress
as it would be a killer feature in several contexts (not in every context).
Just in this period we are releasing file based incremental backup for
Barman and customers using the alpha version are experiencing on average a
deduplication ratio between 50% to 70%. This is for example an excerpt of
"barman show-backup" from one of our customers (a daily saving of 550GB is
not bad):
Base backup information:
Disk usage : 1.1 TiB (1.1 TiB with WALs)
Incremental size : 564.6 GiB (-50.60%)
...
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)
2) modify pg_basebackup in order to accept a directory (or tar file) and
automatically detect the LSN from the backup profile
3) add the documentation regarding the backup profile and pg_basebackup
Once we have all of this, we can continue trying the patch. Some unexplored
paths are:
* tablespace usage
* tar format
* performance impact (in both "read-only" and heavily updated contexts)
* consistency checks
I would then leave for version 6 the pg_restorebackup utility (unless you
want to do everything at once).
One limitation of the current recovery script is that it cannot accept
multiple incremental backups (it just accepts three parameters: base
backup, incremental backup and merge destination). Maybe you can change the
syntax as follows:
./recover.py DESTINATION BACKUP_1 BACKUP_2 [BACKUP_3, ...]
Thanks a lot for working on this.
I am looking forward to continuing the review.
Ciao,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
PostgreSQL Training, Services and Support
gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it
2015-01-13 17:21 GMT+01:00 Marco Nenciarini <marco.nenciarini@2ndquadrant.it
Show quoted text
:
Il 13/01/15 12:53, Gabriele Bartolini ha scritto:
Hi Marco,
could you please send an updated version the patch against the current
HEAD in order to facilitate reviewers?Here is the updated patch for incremental file based backup.
It is based on the current HEAD.
I'm now working to the client tool to rebuild a full backup starting
from a file based incremental backup.Regards,
Marco--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
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.
* tar format
* performance impact (in both "read-only" and heavily updated contexts)
From the server point of view, the current code generates a load similar
to normal backup. It only adds an initial scan of any data file to
decide whether it has to send it. One it found a single newer page it
immediately stop scanning and start sending the file. The IO impact
should not be that big due to the filesystem cache, but I agree with you
that it has to be measured.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
Hi,
here it is another version of the file based incremental backup patch.
Changelog from the previous one:
* pg_basebackup --incremental option take the directory containing the
base backup instead of the backup profile file
* rename the backup_profile file at the same time of backup_label file
when starting the first time from a backup.
* handle "pg_basebackup -D -" appending the backup profile to the
resulting tar stream
* added documentation for -I/--incremental option to pg_basebackup doc
* updated replication protocol documentation
The reationale of moving the backup_profile out of the way during
recovery is to avoid using a data directory which has been already
started as a base of a backup.
I've also lightly improved the pg_restorebackup PoC implementing the
syntax advised by Gabriele:
./pg_restorebackup.py DESTINATION BACKUP_1 BACKUP_2 [BACKUP_3, ...]
It also supports relocation of tablespace with -T option.
The -T option is mandatory if there was any tablespace defined in the
PostgreSQL instance when the incremental_backup was taken.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
Il 27/01/15 10:25, Giuseppe Broccolo ha scritto:> 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?
I've done some test and it looks like that FSM nodes always have
InvalidXLogRecPtr as LSN.
Ive updated the patch to always include files if all their pages have
LSN == InvalidXLogRecPtr
Updated patch v7 attached.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
Attachments:
file-based-incremental-backup-v7.patchtext/plain; charset=UTF-8; name=file-based-incremental-backup-v7.patch; x-mac-creator=0; x-mac-type=0Download+686-159
Hi Marco,
2015-01-27 19:04 GMT+01:00 Marco Nenciarini <marco.nenciarini@2ndquadrant.it
:
I've done some test and it looks like that FSM nodes always have
InvalidXLogRecPtr as LSN.Ive updated the patch to always include files if all their pages have
LSN == InvalidXLogRecPtrUpdated patch v7 attached.
Regards,
Marco--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
I've tried again to replay a new test of the incremental backup introducing
a new tablespace after a base backup, considering the version 7 of the
patch and the new version of the restore script attached in
/messages/by-id/54C7CDAD.6060900@2ndquadrant.it:
# define here your work dir
WORK_DIR='/home/gbroccolo/pgsql'
# preliminary steps
rm -rf /tmp/data /tmp/tbls tbls/ backups/
# create a test db and a backup repository
psql -c "DROP DATABASE IF EXISTS pgbench"
psql -c "CREATE DATABASE pgbench"
pgbench -U postgres -i -s 5 -F 80 pgbench
mkdir -p backups
# a first base backup with pg_basebackup
BASE=$(mkdir -vp backups/$(date '+%d%m%y%H%M') | awk -F'[’‘]' '{print $2}')
echo "start a base backup: $BASE"
mkdir -vp $BASE/data
pg_basebackup -v -F p -D $BASE/data -x -c fast
# creation of a new tablespace, alter the table "pgbench_accounts" to
set the new tablespace
mkdir -p $WORK_DIR/tbls
CREATE_CMD="CREATE TABLESPACE tbls LOCATION '$WORK_DIR/tbls'"
psql -c "$CREATE_CMD"
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
INCREMENTAL=$(mkdir -vp backups/$(date '+%d%m%y%H%M') | awk -F'[’‘]'
'{print $2}')
echo "start an incremental backup: $INCREMENTAL"
mkdir -vp $INCREMENTAL/data $INCREMENTAL/tbls
pg_basebackup -v -F p -D $INCREMENTAL/data -x -I $BASE/data -T
$WORK_DIR/tbls=$WORK_DIR/$INCREMENTAL/tbls -c fast
# restore the database
./pg_restorebackup.py -T $WORK_DIR/$INCREMENTAL/tbls=/tmp/tbls
/tmp/data $BASE/data $INCREMENTAL/data
chmod 0700 /tmp/data/
echo "port=5555" >> /tmp/data/postgresql.conf
pg_ctl -D /tmp/data start
now the restore works fine and pointing to tablespaces are preserved also
in the restored instance:
gbroccolo@arnold:~/pgsql (master %)$ psql -c "\db+"
List of tablespaces
Name | Owner | Location | Access
privileges | Options | Size | Description
------------+----------+----------------------------+-------------------+---------+--------+-------------
pg_default | postgres | |
| | 37 MB |
pg_global | postgres | |
| | 437 kB |
tbls | postgres | /home/gbroccolo/pgsql/tbls |
| | 80 MB |
(3 rows)
gbroccolo@arnold:~/pgsql (master %)$ psql -p 5555 -c "\db+"
List of tablespaces
Name | Owner | Location | Access privileges | Options |
Size | Description
------------+----------+-----------+-------------------+---------+--------+-------------
pg_default | postgres | | | | 37 MB |
pg_global | postgres | | | | 437 kB |
tbls | postgres | /tmp/tbls | | | 80 MB |
(3 rows)
Thanks Marco for your reply.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
The current implementation of copydir function is incompatible with LSN
based incremental backups. The problem is that new files are created,
but their blocks are still with the old LSN, so they will not be backed
up because they are looking old enough.
copydir function is used in:
CREATE DATABASE
ALTER DATABASE SET TABLESPACE
I can imagine two possible solutions:
a) wal log the whole copydir operations, setting the lsn accordingly
b) pass to copydir the LSN of the operation which triggered it, and
update the LSN of all the copied blocks
The latter solution is IMO easier to be implemented and does not deviate
much from the current implementation.
I've implemented it and it's attached to this message.
I've also moved the parse_filename_for_notntemp_relation function out of
reinit.c to make it available both to copydir.c and basebackup.c.
I've also limited the LSN comparison to the only MAIN fork, because:
* LSN fork doesn't uses LSN
* VM fork update LSN only when the visibility bit is set
* INIT forks doesn't use LSN. It's only one page anyway.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
Attachments:
0001-public-parse_filename_for_nontemp_relation.patchtext/plain; charset=UTF-8; name=0001-public-parse_filename_for_nontemp_relation.patch; x-mac-creator=0; x-mac-type=0Download+58-59
0002-copydir-LSN.patchtext/plain; charset=UTF-8; name=0002-copydir-LSN.patch; x-mac-creator=0; x-mac-type=0Download+90-52
0003-File-based-incremental-backup-v8.patchtext/plain; charset=UTF-8; name=0003-File-based-incremental-backup-v8.patch; x-mac-creator=0; x-mac-type=0Download+661-168
On Thu, Jan 29, 2015 at 9:47 AM, Marco Nenciarini
<marco.nenciarini@2ndquadrant.it> wrote:
The current implementation of copydir function is incompatible with LSN
based incremental backups. The problem is that new files are created,
but their blocks are still with the old LSN, so they will not be backed
up because they are looking old enough.
I think this is trying to pollute what's supposed to be a pure
fs-level operation ("copy a directory") into something that is aware
of specific details like the PostgreSQL page format. I really think
that nothing in storage/file should know about the page format. If we
need a function that copies a file while replacing the LSNs, I think
it should be a new function living somewhere else.
A bigger problem is that you are proposing to stamp those files with
LSNs that are, for lack of a better word, fake. I would expect that
this would completely break if checksums are enabled. Also, unlogged
relations typically have an LSN of 0; this would change that in some
cases, and I don't know whether that's OK.
The issues here are similar to those in
/messages/by-id/20150120152819.GC24381@alap3.anarazel.de
- basically, I think we need to make CREATE DATABASE and ALTER
DATABASE .. SET TABLESPACE fully WAL-logged operations, or this is
never going to work right. If we're not going to allow that, we need
to disallow hot backups while those operations are in progress.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2015-01-29 12:57:22 -0500, Robert Haas wrote:
The issues here are similar to those in
/messages/by-id/20150120152819.GC24381@alap3.anarazel.de
- basically, I think we need to make CREATE DATABASE and ALTER
DATABASE .. SET TABLESPACE fully WAL-logged operations, or this is
never going to work right. If we're not going to allow that, we need
to disallow hot backups while those operations are in progress.
Yea, the current way is just a hack from the dark ages. Which has some
advantages, true, but I don't think they outweight the disadvantages. I
hope to find time to develop a patch to make those properly WAL logged
(for master) sometime not too far away.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Il 29/01/15 18:57, Robert Haas ha scritto:
On Thu, Jan 29, 2015 at 9:47 AM, Marco Nenciarini
<marco.nenciarini@2ndquadrant.it> wrote:The current implementation of copydir function is incompatible with LSN
based incremental backups. The problem is that new files are created,
but their blocks are still with the old LSN, so they will not be backed
up because they are looking old enough.I think this is trying to pollute what's supposed to be a pure
fs-level operation ("copy a directory") into something that is aware
of specific details like the PostgreSQL page format. I really think
that nothing in storage/file should know about the page format. If we
need a function that copies a file while replacing the LSNs, I think
it should be a new function living somewhere else.
Given that the copydir function is used only during CREATE DATABASE and
ALTER DATABASE SET TABLESPACE, we could move it/renaming it to a better
place that clearly mark it as "knowing about page format". I'm open to
suggestions on where to place it an on what should be the correct name.
However the whole copydir patch here should be treated as a "temporary"
thing. It is necessary until a proper WAL logging of CREATE DATABASE and
ALTER DATABASE SET TABLESPACE will be implemented to support any form of
LSN based incremental backup.
A bigger problem is that you are proposing to stamp those files with
LSNs that are, for lack of a better word, fake. I would expect that
this would completely break if checksums are enabled.
I'm sorry I completely ignored checksums in previous patch. The attached
one works with checksums enabled.
Also, unlogged relations typically have an LSN of 0; this would
change that in some cases, and I don't know whether that's OK.
It shouldn't be a problem because all the code that uses unlogged
relations normally skip all the WAL related operations. From the point
of view of an incremental backup it is also not a problem, because
restoring the backup the unlogged tables will get reinitialized because
of crash recovery procedure. However if you think it is worth the
effort, I can rewrite the copydir as a two pass operation detecting the
unlogged tables on the first pass and avoiding the LSN update on
unlogged tables. I personally think that it doesn't wort the effort
unless someone identify a real path where settins LSNs in unlogged
relations leads to an issue.
The issues here are similar to those in
/messages/by-id/20150120152819.GC24381@alap3.anarazel.de
- basically, I think we need to make CREATE DATABASE and ALTER
DATABASE .. SET TABLESPACE fully WAL-logged operations, or this is
never going to work right. If we're not going to allow that, we need
to disallow hot backups while those operations are in progress.
This is right, but the problem Andres reported is orthogonal with the
one I'm addressing here. Without this copydir patch (or without a proper
WAL logging of copydir operations), you cannot take an incremental
backup after a CREATE DATABASE or ALTER DATABASE SET TABLESPACE until
you get a full backup and use it as base.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
Attachments:
0001-public-parse_filename_for_nontemp_relation.patchtext/plain; charset=UTF-8; name=0001-public-parse_filename_for_nontemp_relation.patch; x-mac-creator=0; x-mac-type=0Download+58-59
0002-copydir-LSN-v2.patchtext/plain; charset=UTF-8; name=0002-copydir-LSN-v2.patch; x-mac-creator=0; x-mac-type=0Download+98-52
0003-File-based-incremental-backup-v8.patchtext/plain; charset=UTF-8; name=0003-File-based-incremental-backup-v8.patch; x-mac-creator=0; x-mac-type=0Download+661-168
On Sat, January 31, 2015 15:14, Marco Nenciarini wrote:
0001-public-parse_filename_for_nontemp_relation.patch
0002-copydir-LSN-v2.patch
0003-File-based-incremental-backup-v8.patch
Hi,
It looks like it only compiles with assert enabled.
This is perhaps not yet really a problem at this stage but I thought I'd mention it:
make --quiet -j 8
In file included from gram.y:14403:0:
scan.c: In function �yy_try_NUL_trans�:
scan.c:10174:23: warning: unused variable �yyg� [-Wunused-variable]
struct yyguts_t * yyg = (struct yyguts_t*)yyscanner; /* This var may be unused depending upon options. */
^
basebackup.c: In function �writeBackupProfileLine�:
basebackup.c:1545:8: warning: format �%lld� expects argument of type �long long int�, but argument 8 has type �__off_t�
[-Wformat=]
filename);
^
basebackup.c:1545:8: warning: format �%lld� expects argument of type �long long int�, but argument 8 has type �__off_t�
[-Wformat=]
pg_basebackup.c: In function �ReceiveTarFile�:
pg_basebackup.c:858:2: warning: implicit declaration of function �assert� [-Wimplicit-function-declaration]
assert(res || (strcmp(basedir, "-") == 0));
^
pg_basebackup.c:865:2: warning: ISO C90 forbids mixed declarations and code [-Wdeclaration-after-statement]
gzFile ztarfile = NULL;
^
pg_basebackup.o: In function `ReceiveAndUnpackTarFile':
pg_basebackup.c:(.text+0x690): undefined reference to `assert'
pg_basebackup.o: In function `ReceiveTarFile':
pg_basebackup.c:(.text+0xeb0): undefined reference to `assert'
pg_basebackup.c:(.text+0x10ad): undefined reference to `assert'
collect2: error: ld returned 1 exit status
make[3]: *** [pg_basebackup] Error 1
make[3]: *** Waiting for unfinished jobs....
make[2]: *** [all-pg_basebackup-recurse] Error 2
make[2]: *** Waiting for unfinished jobs....
make[1]: *** [all-bin-recurse] Error 2
make: *** [all-src-recurse] Error 2
The configure used was:
./configure \
--prefix=/home/aardvark/pg_stuff/pg_installations/pgsql.incremental_backup \
--bindir=/home/aardvark/pg_stuff/pg_installations/pgsql.incremental_backup/bin.fast \
--libdir=/home/aardvark/pg_stuff/pg_installations/pgsql.incremental_backup/lib.fast \
--with-pgport=6973 --quiet --enable-depend \
--with-extra-version=_incremental_backup_20150131_1521_08bd0c581158 \
--with-openssl --with-perl --with-libxml --with-libxslt --with-zlib
A build with --enable-cassert and --enable-debug builds fine:
./configure \
--prefix=/home/aardvark/pg_stuff/pg_installations/pgsql.incremental_backup \
--bindir=/home/aardvark/pg_stuff/pg_installations/pgsql.incremental_backup/bin \
--libdir=/home/aardvark/pg_stuff/pg_installations/pgsql.incremental_backup/lib \
--with-pgport=6973 --quiet --enable-depend \
--with-extra-version=_incremental_backup_20150131_1628_08bd0c581158 \
--enable-cassert --enable-debug \
--with-openssl --with-perl --with-libxml --with-libxslt --with-zlib
I will further test with that.
thanks,
Erik Rijkers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Il 31/01/15 17:22, Erik Rijkers ha scritto:
On Sat, January 31, 2015 15:14, Marco Nenciarini wrote:
0001-public-parse_filename_for_nontemp_relation.patch
0002-copydir-LSN-v2.patch
0003-File-based-incremental-backup-v8.patchHi,
It looks like it only compiles with assert enabled.
It is due to a typo (assert instead of Assert). You can find the updated
patch attached to this message.
Regards,
Marco
--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco.nenciarini@2ndQuadrant.it | www.2ndQuadrant.it
