Thread: pg_dump custom format without timestamp?

Hello,

I was looking into using hardlinks to reduce the storage of keeping and 
syncing periodic database dumps when they are identical. This works fine 
with the textual format, but not for the custom format because the file 
header includes a timestamp set by function WriteHead in file 
"src/bin/pg_dump/pg_backup_archiver.c".

In order to circumvent this issue, I would think of adding a 
"--no-timestamp" option to pg_dump and put zeros everywhere in place of 
the actual timestamp in such case, and possibly ignoring the said 
timestamp in function ReadHead.

Any thoughts about this?

-- 
Fabien.

Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I was looking into using hardlinks to reduce the storage of keeping and 
> syncing periodic database dumps when they are identical. This works fine 
> with the textual format, but not for the custom format because the file 
> header includes a timestamp set by function WriteHead in file 
> "src/bin/pg_dump/pg_backup_archiver.c".

I'm not sure about this goal ...

> In order to circumvent this issue, I would think of adding a 
> "--no-timestamp" option to pg_dump and put zeros everywhere in place of 
> the actual timestamp in such case, and possibly ignoring the said 
> timestamp in function ReadHead.

... and quite dislike this solution.  pg_dump has way too many bizarre
options already.  Perhaps you should consider making a bit of code that
knows how to compare two custom dumps ignoring the timestamp.
        regards, tom lane

Dear Tom,

thanks for your answer,

> Fabien COELHO <coelho@cri.ensmp.fr> writes:
>> I was looking into using hardlinks to reduce the storage of keeping and
>> syncing periodic database dumps when they are identical. This works fine
>> with the textual format, but not for the custom format because the file
>> header includes a timestamp set by function WriteHead in file
>> "src/bin/pg_dump/pg_backup_archiver.c".
>
> I'm not sure about this goal ...

That may be debatable. I just want "easy longterm dumps" with rotations on 
small databases, and I can do that in a few line of shell using links, 
something like:
 # on every hour pg_dump <some options> base > $current # is it identical to the previous one? cmp $current $previous
&¤t=$previous ln $current $(date +H%H) # H00 .. H23 / hourly, daily rotation ln $current $(date +%a)  # Mon ..
Sun/ daily, weekly rotation ln $current $(date +W%D) # W01 .. W53 / weekly, yearly rotation ln $current $(date +%b)  #
Jan.. Dec / monthly, yearly rotation ln $current $(date +Y%Y) # Y2012 .. Y20XX / yearly, no rotation mv $current
$previous

>> In order to circumvent this issue, I would think of adding a
>> "--no-timestamp" option to pg_dump and put zeros everywhere in place of
>> the actual timestamp in such case, and possibly ignoring the said
>> timestamp in function ReadHead.
>
> ... and quite dislike this solution.

I agree that it is a little bit ugly. I'm not sure that it was a good idea 
add a timestamp in the dump format. From a system perspective, the file is 
already timestamped when created, so this somehow is redundant. Well, one 
may lost the timestamps.

> pg_dump has way too many bizarre options already.  Perhaps you should 
> consider making a bit of code that knows how to compare two custom dumps 
> ignoring the timestamp.

I could do that, but I like a simple "cmp" in a simple shell script, 
rather than a custom comparison command. The backup is really to do a "cmp 
-i XX" to blindly skip part of the header.

-- 
Fabien.