pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dick Visser <dick.visser@tienhuis.nl>
To: pgsql-admin@postgresql.org
Subject: binary vs. txt dumps with pg
Date: Sat, 17 Dec 2005 12:28:54 +0100
Message-ID: <43A3F676.5000109@tienhuis.nl> (raw)

Hi all

This is my first post to the pgsal-admin list.
I am doing IT support for a small company, and we have been running PG
for a few years now.

I have recently upgraded our PG-8.0.3 server to 8.1.0.
Everything went OK, and now I am working on the backup schema.

I seems that since 8.1 pg_dump does not include a switch to dump blobs
anymore. As a consequence, it is now also possible to dump db's with
blobs in txt format. This is great, only the dumps become rather large
because the blobs have to be escaped etc etc.

Since I use rysnc to a remote host for backups, I have created a
script hat dumps each db to a file, and then checks if the dump from 
yesterday is different. If not, the new dump is deleted and the old dump 
stays in place. This means a significant smaller backup, since most of 
our db's are pretty big but do not change that much.

I have been fiddling with all kinds of techniques to accomplish this.
The only way that seems to work is to make txt based dumps, and strip 
out all the comments and empty lines:

pg_dump -Fp db_name | sed 's/^--//g' > db_name.txt.dump

If no data has changed, doing this one day later will yield a dump that 
is 100% the same (as per md5sum).

Since the txt dump is really big (in my case way over 2 Gb) I decided to 
compress it as well:

pg_dump -Fp db_name | sed 's/^--//g' | gzip -n > db_name.txt.dump.gz

This yields a file of 850 Mb, and the md5sums of unchanged db's are stil 
the same.

When I try to dump to custom format, the dumps are *not* the same 
anymore, even though the data has *not* changed. Example: dumping a db 
that is not in use twice will yielf different files:

$ pg_dump -Fc test > test1.dump
$ pg_dump -Fc test > test2.dump
$ md5sum test*.dump
ec158ad86aabc7322c4b4dca58d6e4de  test1.dump
9156ce40ae5f7c54c64382e078a2bb67  test2.dump


I suspect it is because the -Fc format also contains comments etc.


Is there a way to make -Fc dumps that do not contain any comments, 
timestamps or other stuff that makes consequent dumps of the same db 
different? This would be very helpfull. The txt dumps I use now are OK 
but are really big and slow to dump/restore.

Best regards,


-- 
   *    ***     Dick Visser         TIENHUIS Networking
  **   *   *    Touwbaan 68             P: +31206843731
   *   * ***    1018 HS Amsterdam       F: +31208641420
   *   * * *    The Netherlands         M: +31622698108
   *   **  *    IP-phone (SIP)/email:  dick@tienhuis.nl
   *   *   *    PGP-key: http://www.tienhuis.nl/gpg.txt
   *   *   *    Webcam: http://www.tienhuis.nl/cam2.asx
  ***   ***




view thread (5+ messages)  latest in thread

Message-ID: <43A3F676.5000109@tienhuis.nl>
Permalink:  ../43A3F676.5000109@tienhuis.nl/
Also on:    postgresql.org/message-id/43A3F676.5000109@tienhuis.nl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: dick.visser@tienhuis.nl
  Subject: Re: binary vs. txt dumps with pg
  In-Reply-To: <43A3F676.5000109@tienhuis.nl>
  References: <43A3F676.5000109@tienhuis.nl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox