Re: [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(dot)nenciarini(at)2ndQuadrant(dot)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(dot)nenciarini(at)2ndQuadrant(dot)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(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

On Mon, Jan 5, 2015 at 7:56 PM, Marco Nenciarini
<marco(dot)nenciarini(at)2ndquadrant(dot)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

On Tue, Oct 14, 2014 at 1:17 PM, Marco Nenciarini
<marco(dot)nenciarini(at)2ndquadrant(dot)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

On Tue, 6 Jan 2015 08:26:22 -0500
Robert Haas <robertmhaas(at)gmail(dot)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:

http://www.postgresql.org/message-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

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(dot)9020406(at)2ndquadrant(dot)it

Regards,
Marco

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco(dot)nenciarini(at)2ndQuadrant(dot)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(dot)bartolini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

2015-01-07 11:00 GMT+01:00 Marco Nenciarini <marco(dot)nenciarini(at)2ndquadrant(dot)it
>:

> 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(dot)9020406(at)2ndquadrant(dot)it
>
> Regards,
> Marco
>
> --
> Marco Nenciarini - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> marco(dot)nenciarini(at)2ndQuadrant(dot)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(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

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(dot)bartolini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

2015-01-13 17:21 GMT+01:00 Marco Nenciarini <marco(dot)nenciarini(at)2ndquadrant(dot)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(dot)nenciarini(at)2ndQuadrant(dot)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(dot)nenciarini(at)2ndQuadrant(dot)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(dot)nenciarini(at)2ndQuadrant(dot)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
> http://www.postgresql.org/message-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(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

Hi Marco,

2015-01-27 19:04 GMT+01:00 Marco Nenciarini <marco(dot)nenciarini(at)2ndquadrant(dot)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 == InvalidXLogRecPtr
>
> Updated patch v7 attached.
>
> Regards,
> Marco
>
> --
> Marco Nenciarini - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> marco(dot)nenciarini(at)2ndQuadrant(dot)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
http://www.postgresql.org/message-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(at)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(at)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(dot)broccolo(at)2ndQuadrant(dot)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(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

On Thu, Jan 29, 2015 at 9:47 AM, Marco Nenciarini
<marco(dot)nenciarini(at)2ndquadrant(dot)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
http://www.postgresql.org/message-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

On 2015-01-29 12:57:22 -0500, Robert Haas wrote:
> The issues here are similar to those in
> http://www.postgresql.org/message-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

Il 29/01/15 18:57, Robert Haas ha scritto:
> On Thu, Jan 29, 2015 at 9:47 AM, Marco Nenciarini
> <marco(dot)nenciarini(at)2ndquadrant(dot)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
> http://www.postgresql.org/message-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(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

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

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.patch
>
> Hi,
>
> 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(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

On Sat, Jan 31, 2015 at 6:47 PM, Marco Nenciarini
<marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
> 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.patch
>>
>> Hi,
>>
>> 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.

I would sure like it if you would avoid changing the subject line
every time you post a new version of this patch. It breaks the
threading for me.

It seems to have also broken it for the CommitFest app, which thinks
v3 is the last version. I was not able to attach the new version.
When I clicked on "attach thread" without having logged in, it took me
to a bad URL. When I clicked on it after having logged in, it
purported to work, but AFAICS, it didn't actually do anything.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, Feb 2, 2015 at 10:06 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Sat, Jan 31, 2015 at 6:47 PM, Marco Nenciarini
> <marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
> > 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.patch
> >>
> >> Hi,
> >>
> >> 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.
>
> I would sure like it if you would avoid changing the subject line
> every time you post a new version of this patch. It breaks the
> threading for me.
>

+1 - it does break gmail.

It seems to have also broken it for the CommitFest app, which thinks
> v3 is the last version. I was not able to attach the new version.
>

The CF app has detected that it's the same thread, because of the headers
(gmail is the buggy one here - the headers of the email are perfectly
correct).

It does not, however, pick up and show the change of subject there (but you
can see if if you click the link for the latest version into the archives -
the link under "latest" or "latest attachment" both go to the v9 patch).

> When I clicked on "attach thread" without having logged in, it took me
> to a bad URL. When I clicked on it after having logged in, it
>

Clearly a bug.

> purported to work, but AFAICS, it didn't actually do anything.
>

That's because the thread is already there, and you're adding it again. Of
course, it wouldn't hurt if it actually told you that :)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Il 02/02/15 22:28, Magnus Hagander ha scritto:
> On Mon, Feb 2, 2015 at 10:06 PM, Robert Haas <robertmhaas(at)gmail(dot)com
> <mailto:robertmhaas(at)gmail(dot)com>> wrote:
>
> On Sat, Jan 31, 2015 at 6:47 PM, Marco Nenciarini
> <marco(dot)nenciarini(at)2ndquadrant(dot)it
> <mailto:marco(dot)nenciarini(at)2ndquadrant(dot)it>> wrote:
> > 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.patch
> >>
> >> Hi,
> >>
> >> 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.
>
> I would sure like it if you would avoid changing the subject line
> every time you post a new version of this patch. It breaks the
> threading for me.
>
>
> +1 - it does break gmail.

Ok, sorry for that.

>
>
>
> It seems to have also broken it for the CommitFest app, which thinks
> v3 is the last version. I was not able to attach the new version.
>
>
> The CF app has detected that it's the same thread, because of the
> headers (gmail is the buggy one here - the headers of the email are
> perfectly correct).
>
> It does not, however, pick up and show the change of subject there (but
> you can see if if you click the link for the latest version into the
> archives - the link under "latest" or "latest attachment" both go to the
> v9 patch).
>
>
>
> When I clicked on "attach thread" without having logged in, it took me
> to a bad URL. When I clicked on it after having logged in, it
>
>
> Clearly a bug.
>
>
>
> purported to work, but AFAICS, it didn't actually do anything.
>
>
> That's because the thread is already there, and you're adding it again.
> Of course, it wouldn't hurt if it actually told you that :)
>

I'm also confused from the "(Patch: No)" part at the end of every line
if you expand the last attachment line.

Every message shown here contains one or more patch attached.

Regards,
Marco

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

On Mon, Feb 2, 2015 at 10:28 PM, Magnus Hagander <magnus(at)hagander(dot)net>
wrote:

> On Mon, Feb 2, 2015 at 10:06 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
>
>>
>>
>>
> When I clicked on "attach thread" without having logged in, it took me
>> to a bad URL. When I clicked on it after having logged in, it
>>
>
> Clearly a bug.
>

bug has now been fixed.

> purported to work, but AFAICS, it didn't actually do anything.
>>
>
> That's because the thread is already there, and you're adding it again. Of
> course, it wouldn't hurt if it actually told you that :)
>
>
A message telling you what happened has been added.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Hi Marco,

On Sunday 01 February 2015 00:47:24 Marco Nenciarini wrote:
> You can find the updated patch attached to this message.

I've been testing the v9 patch with checksums enabled and I end up with a lot
of warnings like these ones:

WARNING: page verification failed, calculated checksum 47340 but expected
47342
WARNING: page verification failed, calculated checksum 16649 but expected
16647
WARNING: page verification failed, calculated checksum 13567 but expected
13565
WARNING: page verification failed, calculated checksum 14110 but expected
14108
WARNING: page verification failed, calculated checksum 40990 but expected
40988
WARNING: page verification failed, calculated checksum 46242 but expected
46244

I can reproduce the problem with the following script:

WORKDIR=/home/fcanovai/tmp
psql -c "CREATE DATABASE pgbench"
pgbench -i -s 100 --foreign-keys pgbench
mkdir $WORKDIR/tbsp
psql -c "CREATE TABLESPACE tbsp LOCATION '$WORKDIR/tbsp'"
psql -c "ALTER DATABASE pgbench SET TABLESPACE tbsp"

Regards,
Francesco

--
Francesco Canovai - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
francesco(dot)canovai(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

Hi,

I've attached an updated version of the patch. This fixes the issue on
checksum calculation for segments after the first one.

To solve it I've added an optional uint32 *segno argument to
parse_filename_for_nontemp_relation, so I can know the segment number
and calculate the block number correctly.

Il 29/01/15 18:57, Robert Haas ha scritto:
> On Thu, Jan 29, 2015 at 9:47 AM, Marco Nenciarini
> <marco(dot)nenciarini(at)2ndquadrant(dot)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.
>

I've named it copydir_set_lsn and placed it as static function in
dbcommands.c. This lefts the copydir and copy_file functions in copydir.c
untouched. The copydir function in copydir.c is now unused, while the copy_file
function is still used during unlogged tables reinit.

> 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.
>

I've investigate a bit and I have not been able to find any problem here.

> The issues here are similar to those in
> http://www.postgresql.org/message-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.
>

As already said the copydir-LSN patch should be treated as a "temporary"
until a proper WAL logging of CREATE DATABASE and ALTER DATABASE SET
TABLESPACE will be implemented. At that time we could probably get rid
of the whole copydir.[ch] file moving the copy_file function inside reinit.c

Regards,
Marco

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

On Thu, Feb 12, 2015 at 10:50 PM, Marco Nenciarini
<marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
> Hi,
>
> I've attached an updated version of the patch.

basebackup.c:1565: warning: format '%lld' expects type 'long long
int', but argument 8 has type '__off_t'
basebackup.c:1565: warning: format '%lld' expects type 'long long
int', but argument 8 has type '__off_t'
pg_basebackup.c:865: warning: ISO C90 forbids mixed declarations and code

When I applied three patches and compiled the code, I got the above warnings.

How can we get the full backup that we can use for the archive recovery, from
the first full backup and subsequent incremental backups? What commands should
we use for that, for example? It's better to document that.

What does "1" of the heading line in backup_profile mean?

Sorry if this has been already discussed so far. Why is a backup profile file
necessary? Maybe it's necessary in the future, but currently seems not.
Several infos like LSN, modification time, size, etc are tracked in a backup
profile file for every backup files, but they are not used for now. If it's now
not required, I'm inclined to remove it to simplify the code.

We've really gotten the consensus about the current design, especially that
every files basically need to be read to check whether they have been modified
since last backup even when *no* modification happens since last backup?

Regards,

--
Fujii Masao

Il 02/03/15 14:21, Fujii Masao ha scritto:
> On Thu, Feb 12, 2015 at 10:50 PM, Marco Nenciarini
> <marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
>> Hi,
>>
>> I've attached an updated version of the patch.
>
> basebackup.c:1565: warning: format '%lld' expects type 'long long
> int', but argument 8 has type '__off_t'
> basebackup.c:1565: warning: format '%lld' expects type 'long long
> int', but argument 8 has type '__off_t'
> pg_basebackup.c:865: warning: ISO C90 forbids mixed declarations and code
>

I'll add the an explicit cast at that two lines.

> When I applied three patches and compiled the code, I got the above warnings.
>
> How can we get the full backup that we can use for the archive recovery, from
> the first full backup and subsequent incremental backups? What commands should
> we use for that, for example? It's better to document that.
>

I've sent a python PoC that supports the plain format only (not the tar one).
I'm currently rewriting it in C (with also the tar support) and I'll send a new patch containing it ASAP.

> What does "1" of the heading line in backup_profile mean?
>

Nothing. It's a version number. If you think it's misleading I will remove it.

> Sorry if this has been already discussed so far. Why is a backup profile file
> necessary? Maybe it's necessary in the future, but currently seems not.

It's necessary because it's the only way to detect deleted files.

> Several infos like LSN, modification time, size, etc are tracked in a backup
> profile file for every backup files, but they are not used for now. If it's now
> not required, I'm inclined to remove it to simplify the code.

I've put LSN there mainly for debugging purpose, but it can also be used to check the file during pg_restorebackup execution. The sent field is probably redundant (if sent = False and LSN is not set, we should probably simply avoid to write a line about that file) and I'll remove it in the next patch.

>
> We've really gotten the consensus about the current design, especially that
> every files basically need to be read to check whether they have been modified
> since last backup even when *no* modification happens since last backup?

The real problem here is that there is currently no way to detect that a file is not changed since the last backup. We agreed to not use file system timestamps as they are not reliable for that purpose.
Using LSN have a significant advantage over using checksum, as we can start the full copy as soon as we found a block whith a LSN greater than the threshold.
There are two cases: 1) the file is changed, so we can assume that we detect it after reading 50% of the file, then we send it taking advantage of file system cache; 2) the file is not changed, so we read it without sending anything.
It will end up producing an I/O comparable to a normal backup.

Regards,
Marco

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

On Tue, Mar 3, 2015 at 12:36 AM, Marco Nenciarini
<marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
> Il 02/03/15 14:21, Fujii Masao ha scritto:
>> On Thu, Feb 12, 2015 at 10:50 PM, Marco Nenciarini
>> <marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
>>> Hi,
>>>
>>> I've attached an updated version of the patch.
>>
>> basebackup.c:1565: warning: format '%lld' expects type 'long long
>> int', but argument 8 has type '__off_t'
>> basebackup.c:1565: warning: format '%lld' expects type 'long long
>> int', but argument 8 has type '__off_t'
>> pg_basebackup.c:865: warning: ISO C90 forbids mixed declarations and code
>>
>
> I'll add the an explicit cast at that two lines.
>
>> When I applied three patches and compiled the code, I got the above warnings.
>>
>> How can we get the full backup that we can use for the archive recovery, from
>> the first full backup and subsequent incremental backups? What commands should
>> we use for that, for example? It's better to document that.
>>
>
> I've sent a python PoC that supports the plain format only (not the tar one).
> I'm currently rewriting it in C (with also the tar support) and I'll send a new patch containing it ASAP.

Yeah, if special tool is required for that purpose, the patch should include it.

>> What does "1" of the heading line in backup_profile mean?
>>
>
> Nothing. It's a version number. If you think it's misleading I will remove it.

A version number of file format of backup profile? If it's required for
the validation of backup profile file as a safe-guard, it should be included
in the profile file. For example, it might be useful to check whether
pg_basebackup executable is compatible with the "source" backup that
you specify. But more info might be needed for such validation.

>> Sorry if this has been already discussed so far. Why is a backup profile file
>> necessary? Maybe it's necessary in the future, but currently seems not.
>
> It's necessary because it's the only way to detect deleted files.

Maybe I'm missing something. Seems we can detect that even without a profile.
For example, please imagine the case where the file has been deleted since
the last full backup and then the incremental backup is taken. In this case,
that deleted file exists only in the full backup. We can detect the deletion of
the file by checking both full and incremental backups.

>> We've really gotten the consensus about the current design, especially that
>> every files basically need to be read to check whether they have been modified
>> since last backup even when *no* modification happens since last backup?
>
> The real problem here is that there is currently no way to detect that a file is not changed since the last backup. We agreed to not use file system timestamps as they are not reliable for that purpose.

TBH I prefer timestamp-based approach in the first version of incremental backup
even if's less reliable than LSN-based one. I think that some users who are
using timestamp-based rsync (i.e., default mode) for the backup would be
satisfied with timestamp-based one.

> Using LSN have a significant advantage over using checksum, as we can start the full copy as soon as we found a block whith a LSN greater than the threshold.
> There are two cases: 1) the file is changed, so we can assume that we detect it after reading 50% of the file, then we send it taking advantage of file system cache; 2) the file is not changed, so we read it without sending anything.
> It will end up producing an I/O comparable to a normal backup.

Yeah, it might make the situation better than today. But I'm afraid that
many users might get disappointed about that behavior of an incremental
backup after the release...

Regards,

--
Fujii Masao

Hi Fujii,

Il 03/03/15 11:48, Fujii Masao ha scritto:
> On Tue, Mar 3, 2015 at 12:36 AM, Marco Nenciarini
> <marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
>> Il 02/03/15 14:21, Fujii Masao ha scritto:
>>> On Thu, Feb 12, 2015 at 10:50 PM, Marco Nenciarini
>>> <marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
>>>> Hi,
>>>>
>>>> I've attached an updated version of the patch.
>>>
>>> basebackup.c:1565: warning: format '%lld' expects type 'long long
>>> int', but argument 8 has type '__off_t'
>>> basebackup.c:1565: warning: format '%lld' expects type 'long long
>>> int', but argument 8 has type '__off_t'
>>> pg_basebackup.c:865: warning: ISO C90 forbids mixed declarations and code
>>>
>>
>> I'll add the an explicit cast at that two lines.
>>
>>> When I applied three patches and compiled the code, I got the above warnings.
>>>
>>> How can we get the full backup that we can use for the archive recovery, from
>>> the first full backup and subsequent incremental backups? What commands should
>>> we use for that, for example? It's better to document that.
>>>
>>
>> I've sent a python PoC that supports the plain format only (not the tar one).
>> I'm currently rewriting it in C (with also the tar support) and I'll send a new patch containing it ASAP.
>
> Yeah, if special tool is required for that purpose, the patch should include it.
>

I'm working on it. The interface will be exactly the same of the PoC script I've attached to

54C7CDAD(dot)6060900(at)2ndquadrant(dot)it

>>> What does "1" of the heading line in backup_profile mean?
>>>
>>
>> Nothing. It's a version number. If you think it's misleading I will remove it.
>
> A version number of file format of backup profile? If it's required for
> the validation of backup profile file as a safe-guard, it should be included
> in the profile file. For example, it might be useful to check whether
> pg_basebackup executable is compatible with the "source" backup that
> you specify. But more info might be needed for such validation.
>

The current implementation bail out with an error if the header line is different from what it expect.
It also reports and error if the 2nd line is not the start WAL location. That's all that pg_basebackup needs to start a new incremental backup. All the other information are useful to reconstruct a full backup in case of an incremental backup, or maybe to check the completeness of an archived full backup.
Initially the profile was present only in incremental backups, but after some discussion on list we agreed to always write it.

>>> Sorry if this has been already discussed so far. Why is a backup profile file
>>> necessary? Maybe it's necessary in the future, but currently seems not.
>>
>> It's necessary because it's the only way to detect deleted files.
>
> Maybe I'm missing something. Seems we can detect that even without a profile.
> For example, please imagine the case where the file has been deleted since
> the last full backup and then the incremental backup is taken. In this case,
> that deleted file exists only in the full backup. We can detect the deletion of
> the file by checking both full and incremental backups.
>

When you take an incremental backup, only changed files are sent. Without the backup_profile in the incremental backup, you cannot detect a deleted file, because it's indistinguishable from a file that is not changed.

>>> We've really gotten the consensus about the current design, especially that
>>> every files basically need to be read to check whether they have been modified
>>> since last backup even when *no* modification happens since last backup?
>>
>> The real problem here is that there is currently no way to detect that a file is not changed since the last backup. We agreed to not use file system timestamps as they are not reliable for that purpose.
>
> TBH I prefer timestamp-based approach in the first version of incremental backup
> even if's less reliable than LSN-based one. I think that some users who are
> using timestamp-based rsync (i.e., default mode) for the backup would be
> satisfied with timestamp-based one.

The original design was to compare size+timestamp+checksums (only if everything else matches and the file has been modified after the start of the backup), but the feedback from the list was that we cannot trust the filesystem mtime and we must use LSN instead.

>
>> Using LSN have a significant advantage over using checksum, as we can start the full copy as soon as we found a block whith a LSN greater than the threshold.
>> There are two cases: 1) the file is changed, so we can assume that we detect it after reading 50% of the file, then we send it taking advantage of file system cache; 2) the file is not changed, so we read it without sending anything.
>> It will end up producing an I/O comparable to a normal backup.
>
> Yeah, it might make the situation better than today. But I'm afraid that
> many users might get disappointed about that behavior of an incremental
> backup after the release...

I don't get what do you mean here. Can you elaborate this point?

Regards,
Marco

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

On Thu, Mar 5, 2015 at 1:59 AM, Marco Nenciarini
<marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
> Hi Fujii,
>
> Il 03/03/15 11:48, Fujii Masao ha scritto:
>> On Tue, Mar 3, 2015 at 12:36 AM, Marco Nenciarini
>> <marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
>>> Il 02/03/15 14:21, Fujii Masao ha scritto:
>>>> On Thu, Feb 12, 2015 at 10:50 PM, Marco Nenciarini
>>>> <marco(dot)nenciarini(at)2ndquadrant(dot)it> wrote:
>>>>> Hi,
>>>>>
>>>>> I've attached an updated version of the patch.
>>>>
>>>> basebackup.c:1565: warning: format '%lld' expects type 'long long
>>>> int', but argument 8 has type '__off_t'
>>>> basebackup.c:1565: warning: format '%lld' expects type 'long long
>>>> int', but argument 8 has type '__off_t'
>>>> pg_basebackup.c:865: warning: ISO C90 forbids mixed declarations and code
>>>>
>>>
>>> I'll add the an explicit cast at that two lines.
>>>
>>>> When I applied three patches and compiled the code, I got the above warnings.
>>>>
>>>> How can we get the full backup that we can use for the archive recovery, from
>>>> the first full backup and subsequent incremental backups? What commands should
>>>> we use for that, for example? It's better to document that.
>>>>
>>>
>>> I've sent a python PoC that supports the plain format only (not the tar one).
>>> I'm currently rewriting it in C (with also the tar support) and I'll send a new patch containing it ASAP.
>>
>> Yeah, if special tool is required for that purpose, the patch should include it.
>>
>
> I'm working on it. The interface will be exactly the same of the PoC script I've attached to
>
> 54C7CDAD(dot)6060900(at)2ndquadrant(dot)it
>
>>>> What does "1" of the heading line in backup_profile mean?
>>>>
>>>
>>> Nothing. It's a version number. If you think it's misleading I will remove it.
>>
>> A version number of file format of backup profile? If it's required for
>> the validation of backup profile file as a safe-guard, it should be included
>> in the profile file. For example, it might be useful to check whether
>> pg_basebackup executable is compatible with the "source" backup that
>> you specify. But more info might be needed for such validation.
>>
>
> The current implementation bail out with an error if the header line is different from what it expect.
> It also reports and error if the 2nd line is not the start WAL location. That's all that pg_basebackup needs to start a new incremental backup. All the other information are useful to reconstruct a full backup in case of an incremental backup, or maybe to check the completeness of an archived full backup.
> Initially the profile was present only in incremental backups, but after some discussion on list we agreed to always write it.

Don't we need more checks about the compatibility of the backup-target database
cluster and the source incremental backup? Without such more checks, I'm afraid
we can easily get a corrupted incremental backups. For example, pg_basebackup
should emit an error if the target and source have the different system IDs,
like walreceiver does? What happens if the timeline ID is different between the
source and target? What happens if the source was taken from the standby but
new incremental backup will be taken from the master? Do we need to check them?

>>>> Sorry if this has been already discussed so far. Why is a backup profile file
>>>> necessary? Maybe it's necessary in the future, but currently seems not.
>>>
>>> It's necessary because it's the only way to detect deleted files.
>>
>> Maybe I'm missing something. Seems we can detect that even without a profile.
>> For example, please imagine the case where the file has been deleted since
>> the last full backup and then the incremental backup is taken. In this case,
>> that deleted file exists only in the full backup. We can detect the deletion of
>> the file by checking both full and incremental backups.
>>
>
> When you take an incremental backup, only changed files are sent. Without the backup_profile in the incremental backup, you cannot detect a deleted file, because it's indistinguishable from a file that is not changed.

Yeah, you're right!

>>>> We've really gotten the consensus about the current design, especially that
>>>> every files basically need to be read to check whether they have been modified
>>>> since last backup even when *no* modification happens since last backup?
>>>
>>> The real problem here is that there is currently no way to detect that a file is not changed since the last backup. We agreed to not use file system timestamps as they are not reliable for that purpose.
>>
>> TBH I prefer timestamp-based approach in the first version of incremental backup
>> even if's less reliable than LSN-based one. I think that some users who are
>> using timestamp-based rsync (i.e., default mode) for the backup would be
>> satisfied with timestamp-based one.
>
> The original design was to compare size+timestamp+checksums (only if everything else matches and the file has been modified after the start of the backup), but the feedback from the list was that we cannot trust the filesystem mtime and we must use LSN instead.
>
>>
>>> Using LSN have a significant advantage over using checksum, as we can start the full copy as soon as we found a block whith a LSN greater than the threshold.
>>> There are two cases: 1) the file is changed, so we can assume that we detect it after reading 50% of the file, then we send it taking advantage of file system cache; 2) the file is not changed, so we read it without sending anything.
>>> It will end up producing an I/O comparable to a normal backup.
>>
>> Yeah, it might make the situation better than today. But I'm afraid that
>> many users might get disappointed about that behavior of an incremental
>> backup after the release...
>
> I don't get what do you mean here. Can you elaborate this point?

The proposed version of LSN-based incremental backup has some limitations
(e.g., every database files need to be read even when there is no modification
in database since last backup, and which may make the backup time longer than
users expect) which may disappoint users. So I'm afraid that users who can
benefit from the feature might be very limited. IOW, I'm just sticking to
the idea of timestamp-based one :) But I should drop it if the majority in
the list prefers the LSN-based one even if it has such limitations.

Regards,

--
Fujii Masao

On Thu, Mar 5, 2015 at 01:25:13PM +0900, Fujii Masao wrote:
> >> Yeah, it might make the situation better than today. But I'm afraid that
> >> many users might get disappointed about that behavior of an incremental
> >> backup after the release...
> >
> > I don't get what do you mean here. Can you elaborate this point?
>
> The proposed version of LSN-based incremental backup has some limitations
> (e.g., every database files need to be read even when there is no modification
> in database since last backup, and which may make the backup time longer than
> users expect) which may disappoint users. So I'm afraid that users who can
> benefit from the feature might be very limited. IOW, I'm just sticking to
> the idea of timestamp-based one :) But I should drop it if the majority in
> the list prefers the LSN-based one even if it has such limitations.

We need numbers on how effective each level of tracking will be. Until
then, the patch can't move forward.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Wed, Mar 4, 2015 at 11:42 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Thu, Mar 5, 2015 at 01:25:13PM +0900, Fujii Masao wrote:
>> >> Yeah, it might make the situation better than today. But I'm afraid that
>> >> many users might get disappointed about that behavior of an incremental
>> >> backup after the release...
>> >
>> > I don't get what do you mean here. Can you elaborate this point?
>>
>> The proposed version of LSN-based incremental backup has some limitations
>> (e.g., every database files need to be read even when there is no modification
>> in database since last backup, and which may make the backup time longer than
>> users expect) which may disappoint users. So I'm afraid that users who can
>> benefit from the feature might be very limited. IOW, I'm just sticking to
>> the idea of timestamp-based one :) But I should drop it if the majority in
>> the list prefers the LSN-based one even if it has such limitations.
>
> We need numbers on how effective each level of tracking will be. Until
> then, the patch can't move forward.

The point is that this is a stepping stone toward what will ultimately
be a better solution. You can use timestamps today if (a) whole-file
granularity is good enough for you and (b) you trust your system clock
to never go backwards. In fact, if you use pg_start_backup() and
pg_stop_backup(), you don't even need a server patch; you can just go
right ahead and implement whatever you like. A server patch would be
needed to make pg_basebackup do a file-time-based incremental backup,
but I'm not excited about that because I think the approach is a
dead-end.

If you want block-level granularity, and you should, an approach based
on file times is never going to get you there. An approach based on
LSNs can. If the first version of the patch requires reading the
whole database, fine, it's not going to perform all that terribly
well. But we can optimize that later by keeping track of which blocks
have been modified since a given LSN. If we do that, we can get
better reliability than the timestamp approach can ever offer, plus
excellent transfer and storage characteristics.

What I'm unhappy with about this patch is that it insists on sending
the whole file if a single block in that file has changed. That is
lame. To get something useful out of this, we should be looking to
send only those blocks whose LSNs have actually changed. That would
reduce I/O (in the worst case, the current patch each file in its
entirety twice) and transfer bandwidth as compared to the proposed
patch. We'd still have to read the whole database so it might very
well do more I/O than the file-timestamp approach, but it would beat
the file-timestamp approach on transfer bandwidth and on the amount of
storage required to store the incremental. In many workloads, I
expect those savings would be quite significant. If we then went back
in a later release and implemented one of the various proposals to
avoid needing to read every block, we'd then have a very robust and
complete solution.

But I agree with Fujii to the extent that I see little value in
committing this patch in the form proposed. Being smart enough to use
the LSN to identify changed blocks, but then sending the entirety of
every file anyway because you don't want to go to the trouble of
figuring out how to revise the wire protocol to identify the
individual blocks being sent and write the tools to reconstruct a full
backup based on that data, does not seem like enough of a win. As
Fujii says, if we ship this patch as written, people will just keep
using the timestamp-based approach anyway. Let's wait until we have
something that is, at least in some circumstances, a material
improvement over the status quo before committing anything.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi Robert,

2015-03-06 3:10 GMT+11:00 Robert Haas <robertmhaas(at)gmail(dot)com>:

> But I agree with Fujii to the extent that I see little value in
> committing this patch in the form proposed. Being smart enough to use
> the LSN to identify changed blocks, but then sending the entirety of
> every file anyway because you don't want to go to the trouble of
> figuring out how to revise the wire protocol to identify the
> individual blocks being sent and write the tools to reconstruct a full
> backup based on that data, does not seem like enough of a win.

I believe the main point is to look at a user interface point of view.
If/When we switch to a block level incremental support, this will be
completely transparent to the end user, even if we start with a file-level
approach with LSN check.

The win is already determined by the average space/time gained by users of
VLDB with a good chunk of read-only data. Our Barman users with incremental
backup (released recently - its algorithm can be compared to the one of
file-level backup proposed by Marco) can benefit on average of a data
deduplication ratio ranging between 50 to 70% of the cluster size.

A tangible example is depicted here, with Navionics saving 8.2TB a week
thanks to this approach (and 17 hours instead of 50 for backup time):
http://blog.2ndquadrant.com/incremental-backup-barman-1-4-0/

However, even smaller databases will benefit. It is clear that very small
databases as well as frequently updated ones won't be interested in
incremental backup, but that is never been the use case for this feature.

I believe that if we still think that this approach is not worth it, we are
making a big mistake. The way I see it, this patch follows an agile
approach and it is an important step towards incremental backup on a block
basis.

> As Fujii says, if we ship this patch as written, people will just keep
> using the timestamp-based approach anyway.

I think that allowing users to be able to backup in an incremental way
through streaming replication (even though based on files) will give more
flexibility to system and database administrators for their disaster
recovery solutions.

Thanks,
Gabriele

On Fri, Mar 6, 2015 at 9:38 AM, Gabriele Bartolini
<gabriele(dot)bartolini(at)2ndquadrant(dot)it> wrote:
> I believe the main point is to look at a user interface point of view.
> If/When we switch to a block level incremental support, this will be
> completely transparent to the end user, even if we start with a file-level
> approach with LSN check.

I don't think that's true. To have a real file-level incremental
backup you need the ability to take the incremental backup, and then
you also need the ability to take a full backup + an incremental
backup taken later and reassemble a full image of the cluster on which
you can run recovery. The means of doing that is going to be
different for an approach that only copies certain blocks vs. one that
copies whole files. Once we have the block-based approach, nobody
will ever use the file-based approach, so whatever code or tools we
write to do that will all be dead code, yet we'll still have to
support them for many years.

By the way, unless I'm missing something, this patch only seems to
include the code to construct an incremental backup, but no tools
whatsoever to do anything useful with it once you've got it. I think
that's 100% unacceptable. Users need to be able to manipulate
PostgreSQL backups using either standard operating system tools or
tools provided with PostgreSQL. Some people may prefer to use
something like repmgr or pitrtools or omniptr in addition, but that
shouldn't be a requirement for incremental backup to be usable.

Agile development is good, but that does not mean you can divide a big
project into arbitrarily small chunks. At some point the chunks are
too small to be sure that the overall direction is right, and/or
individually useless.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Il 05/03/15 05:42, Bruce Momjian ha scritto:
> On Thu, Mar 5, 2015 at 01:25:13PM +0900, Fujii Masao wrote:
>>>> Yeah, it might make the situation better than today. But I'm afraid that
>>>> many users might get disappointed about that behavior of an incremental
>>>> backup after the release...
>>>
>>> I don't get what do you mean here. Can you elaborate this point?
>>
>> The proposed version of LSN-based incremental backup has some limitations
>> (e.g., every database files need to be read even when there is no modification
>> in database since last backup, and which may make the backup time longer than
>> users expect) which may disappoint users. So I'm afraid that users who can
>> benefit from the feature might be very limited. IOW, I'm just sticking to
>> the idea of timestamp-based one :) But I should drop it if the majority in
>> the list prefers the LSN-based one even if it has such limitations.
>
> We need numbers on how effective each level of tracking will be. Until
> then, the patch can't move forward.
>

I've written a little test script to estimate how much space can be saved by file level incremental, and I've run it on some real backups I have access to.

The script takes two basebackup directory and simulate how much data can be saved in the 2nd backup using incremental backup (using file size/time and LSN)

It assumes that every file in base, global and pg_tblspc which matches both size and modification time will also match from the LSN point of view.

The result is that many databases can take advantage of incremental, even if not do big, and considering LSNs yield a result almost identical to the approach based on filesystem metadata.

== Very big geographic database (similar to openstreetmap main DB), it contains versioned data, interval two months

First backup size: 13286623850656 (12.1TiB)
Second backup size: 13323511925626 (12.1TiB)
Matching files count: 17094
Matching LSN count: 14580
Matching files size: 9129755116499 (8.3TiB, 68.5%)
Matching LSN size: 9128568799332 (8.3TiB, 68.5%)

== Big on-line store database, old data regularly moved to historic partitions, interval one day

First backup size: 1355285058842 (1.2TiB)
Second backup size: 1358389467239 (1.2TiB)
Matching files count: 3937
Matching LSN count: 2821
Matching files size: 762292960220 (709.9GiB, 56.1%)
Matching LSN size: 762122543668 (709.8GiB, 56.1%)

== Ticketing system database, interval one day

First backup size: 144988275 (138.3MiB)
Second backup size: 146135155 (139.4MiB)
Matching files count: 3124
Matching LSN count: 2641
Matching files size: 76908986 (73.3MiB, 52.6%)
Matching LSN size: 67747928 (64.6MiB, 46.4%)

== Online store, interval one day

First backup size: 20418561133 (19.0GiB)
Second backup size: 20475290733 (19.1GiB)
Matching files count: 5744
Matching LSN count: 4302
Matching files size: 4432709876 (4.1GiB, 21.6%)
Matching LSN size: 4388993884 (4.1GiB, 21.4%)

== Heavily updated database, interval one week

First backup size: 3203198962 (3.0GiB)
Second backup size: 3222409202 (3.0GiB)
Matching files count: 1801
Matching LSN count: 1273
Matching files size: 91206317 (87.0MiB, 2.8%)
Matching LSN size: 69083532 (65.9MiB, 2.1%)

Regards,
Marco

--
Marco Nenciarini - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
marco(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it

On Thu, Mar 5, 2015 at 11:10:08AM -0500, Robert Haas wrote:
> But I agree with Fujii to the extent that I see little value in
> committing this patch in the form proposed. Being smart enough to use
> the LSN to identify changed blocks, but then sending the entirety of
> every file anyway because you don't want to go to the trouble of
> figuring out how to revise the wire protocol to identify the
> individual blocks being sent and write the tools to reconstruct a full
> backup based on that data, does not seem like enough of a win. As
> Fujii says, if we ship this patch as written, people will just keep
> using the timestamp-based approach anyway. Let's wait until we have
> something that is, at least in some circumstances, a material
> improvement over the status quo before committing anything.

The big problem I have with this patch is that it has not followed the
proper process for development, i.e. at the top of the TODO list we
have:

Desirability -> Design -> Implement -> Test -> Review -> Commit

This patch has continued in development without getting agreement on
its Desirability or Design, meaning we are going to continue going back
to those points until there is agreement. Posting more versions of this
patch is not going to change that.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

Hi Bruce,

2015-03-08 5:37 GMT+11:00 Bruce Momjian <bruce(at)momjian(dot)us>:
>
> Desirability -> Design -> Implement -> Test -> Review -> Commit
>
> This patch has continued in development without getting agreement on
> its Desirability or Design, meaning we are going to continue going back
> to those points until there is agreement. Posting more versions of this
> patch is not going to change that.
>

Could you please elaborate that?

I actually think the approach that has been followed is what makes open
source and collaborative development work. The initial idea was based on
timestamp approach which, thanks to the input of several developers, led
Marco to develop LSN based checks and move forward the feature
implementation.

The numbers that Marco has posted clearly show that a lot of users will
benefit from this file-based approach for incremental backup through
pg_basebackup.

As far as I see it, the only missing bit is the pg_restorebackup tool which
is quite trivial - given the existing prototype in Python.

Thanks,
Gabriele

Hi Robert,

2015-03-07 2:57 GMT+11:00 Robert Haas <robertmhaas(at)gmail(dot)com>:

> By the way, unless I'm missing something, this patch only seems to
> include the code to construct an incremental backup, but no tools
> whatsoever to do anything useful with it once you've got it.

As stated previously, Marco is writing a tool called pg_restorebackup (the
prototype in Python has been already posted) to be included in the core. I
am in Australia now and not in the office so I cannot confirm it, but I am
pretty sure he had already written it and was about to send it to the list.

He's been trying to find more data - see the one that he's sent - in order
to convince that even a file-based approach is useful.

I think that's 100% unacceptable.

I agree, that's why pg_restorebackup written in C is part of this patch.
See: https://wiki.postgresql.org/wiki/Incremental_backup

> Users need to be able to manipulate
> PostgreSQL backups using either standard operating system tools or
> tools provided with PostgreSQL. Some people may prefer to use
> something like repmgr or pitrtools or omniptr in addition, but that
> shouldn't be a requirement for incremental backup to be usable.
>

Not at all. I believe those tools will have to use pg_basebackup and
pg_restorebackup. If they want to use streaming replication protocol they
will be responsible to make sure that - if the protocol changes - they
adapt their technology.

Agile development is good, but that does not mean you can divide a big
> project into arbitrarily small chunks. At some point the chunks are
> too small to be sure that the overall direction is right, and/or
> individually useless.
>

The goal has always been to provide "file-based incremental backup". I can
assure that this has always been our compass and the direction to follow.

I repeat that, using pg_restorebackup, this patch will transparently let
users benefit from incremental backup even when it will be moved to an
internal block-level logic. Users will continue to execute pg_basebackup
and pg_restorebackup, ignoring that with - for example 9.5 - it is
file-based (saving between 50-70% of space and time) of block level - for
example 9.6.

My proposal is that Marco provides pg_restorebackup according to the
initial plan - a matter of hours/days.

Cheers,
Gabriele

On Sun, Mar 8, 2015 at 09:26:38AM +1100, Gabriele Bartolini wrote:
> Hi Bruce,
>
> 2015-03-08 5:37 GMT+11:00 Bruce Momjian <bruce(at)momjian(dot)us>:
>
>         Desirability -> Design -> Implement -> Test -> Review -> Commit
>
> This patch has continued in development without getting agreement on
> its Desirability or Design, meaning we are going to continue going back
> to those points until there is agreement.  Posting more versions of this
> patch is not going to change that.
>
>
> Could you please elaborate that?
>
> I actually think the approach that has been followed is what makes open source
> and collaborative development work. The initial idea was based on timestamp
> approach which, thanks to the input of several developers, led Marco to develop
> LSN based checks and move forward the feature implementation.

OK, if you think everyone just going on their own and working on patches
that have little chance of being accepted, you can do it, but that
rarely makes successful open source software. You can do whatever you
want with your patch.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Sat, Mar 7, 2015 at 5:45 PM, Gabriele Bartolini
<gabriele(dot)bartolini(at)2ndquadrant(dot)it> wrote:
>> By the way, unless I'm missing something, this patch only seems to
>> include the code to construct an incremental backup, but no tools
>> whatsoever to do anything useful with it once you've got it.
>
> As stated previously, Marco is writing a tool called pg_restorebackup (the
> prototype in Python has been already posted) to be included in the core. I
> am in Australia now and not in the office so I cannot confirm it, but I am
> pretty sure he had already written it and was about to send it to the list.

Gabriele, the deadline for the last CommitFest was three weeks ago.
Having a patch that you are "about to send to the list" is not good
enough at this point.

>> I think that's 100% unacceptable.
>
> I agree, that's why pg_restorebackup written in C is part of this patch.
> See: https://wiki.postgresql.org/wiki/Incremental_backup

No, it *isn't* part of this patch. You may have a plan to add it to
this patch, but that's not the same thing.

> The goal has always been to provide "file-based incremental backup". I can
> assure that this has always been our compass and the direction to follow.

Regardless of community feedback? OK. Let's see how that works out for you.

> I repeat that, using pg_restorebackup, this patch will transparently let
> users benefit from incremental backup even when it will be moved to an
> internal block-level logic. Users will continue to execute pg_basebackup and
> pg_restorebackup, ignoring that with - for example 9.5 - it is file-based
> (saving between 50-70% of space and time) of block level - for example 9.6.

I understand that. But I also understand that in other cases it's
going to be slower than a full backup. This problem has been pointed
out several times, and you're just refusing to admit that it's a real
issue. A user with a bunch of tables where only the rows near the end
of the file get updated is going to repeatedly read those files until
it hits the first modified block and then rewind and reread the whole
file. I pointed this problem out back in early October and suggested
some ways of fixing it; Heikki followed up with his own suggestions
for modifying my idea. Instead of implementing any of that, or even
discussing it, you're still plugging away on a design that no
committer has endorsed and that several committers obviously have
concerns about.

It's also pretty clear that nobody likes the backup profile, at least
in the form it exists today. But it's still here, many patch versions
later.

I think there's absolutely no point in spending more time on this for
9.5. At least 4 committers have looked at it and none of them are
convinced by the current design; feedback from almost half a year ago
hasn't been incorporated; obviously-needed parts of the patch
(pg_restorebackup) are missing weeks after the last CF deadline.
Let's mark this Rejected in the CF app and move on.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Mar 10, 2015 at 1:50 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I think there's absolutely no point in spending more time on this for
> 9.5. At least 4 committers have looked at it and none of them are
> convinced by the current design; feedback from almost half a year ago
> hasn't been incorporated; obviously-needed parts of the patch
> (pg_restorebackup) are missing weeks after the last CF deadline.
> Let's mark this Rejected in the CF app and move on.

Agreed. I lost a bit interest in this patch lately, but if all the
necessary parts of the patch were not posted before the CF deadline
that's not something we should consider for integration at this point.
Let's give it a couple of months of fresh air and, Gabriele, I am sure
you will be able to come back with something far more advanced for the
first CF of 9.6.
--
Michael

On Tue, Mar 10, 2015 at 08:25:27AM +0900, Michael Paquier wrote:
> On Tue, Mar 10, 2015 at 1:50 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > I think there's absolutely no point in spending more time on this for
> > 9.5. At least 4 committers have looked at it and none of them are
> > convinced by the current design; feedback from almost half a year ago
> > hasn't been incorporated; obviously-needed parts of the patch
> > (pg_restorebackup) are missing weeks after the last CF deadline.
> > Let's mark this Rejected in the CF app and move on.
>
> Agreed. I lost a bit interest in this patch lately, but if all the
> necessary parts of the patch were not posted before the CF deadline
> that's not something we should consider for integration at this point.
> Let's give it a couple of months of fresh air and, Gabriele, I am sure
> you will be able to come back with something far more advanced for the
> first CF of 9.6.

What's the latest on this patch?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Tue, Jan 26, 2016 at 12:55 AM, David Fetter <david(at)fetter(dot)org> wrote:
> On Tue, Mar 10, 2015 at 08:25:27AM +0900, Michael Paquier wrote:
>> On Tue, Mar 10, 2015 at 1:50 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> > I think there's absolutely no point in spending more time on this for
>> > 9.5. At least 4 committers have looked at it and none of them are
>> > convinced by the current design; feedback from almost half a year ago
>> > hasn't been incorporated; obviously-needed parts of the patch
>> > (pg_restorebackup) are missing weeks after the last CF deadline.
>> > Let's mark this Rejected in the CF app and move on.
>>
>> Agreed. I lost a bit interest in this patch lately, but if all the
>> necessary parts of the patch were not posted before the CF deadline
>> that's not something we should consider for integration at this point.
>> Let's give it a couple of months of fresh air and, Gabriele, I am sure
>> you will be able to come back with something far more advanced for the
>> first CF of 9.6.
>
> What's the latest on this patch?

My guess is that Marco and Gabriele are working on something directly
for barman, the backup tool they use, with a differential backup
implementation based on tracking blocks modified by WAL records (far
faster for large data sets than scanning all the relation files of
PGDATA).
Regards,
--
Michael