Skip site navigation (1) Skip section navigation (2)

Re: PG_DUMP backup

From: "Tomeh, Husam" <HTomeh(at)facorelogic(dot)com>
To: "Renato Oliveira" <renato(dot)oliveira(at)grant(dot)co(dot)uk>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: PG_DUMP backup
Date: 2010-02-12 19:31:08
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
Backing up a 170GB in 28 hours definitely doesn't sound right and I
almost certain has nothing to do with pg_dump, but rather your hardware,
ie, server, disk, etc. with a 170GB, backup should be done in a couple
of hours in my opinion. Seems to be more like a system resource issue.


-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Renato Oliveira
Sent: Friday, February 12, 2010 1:59 AM
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] PG_DUMP backup
Importance: High

Dear all,

I have a server running 8.2.4 and has a database 170GB in size.
Currently I am backing it up using pg_dump and it takes around 28 hours,
I was asked to check and compare the newly created DUMP file to the live
database and compare records.

I personally cannot see an easy or quick way of doing this, and even the
point in doing so.
I am already restoring the full database to a separate server and no
errors were reported.

How far can I trust pg_dump, can I trust a restore of the full DUMP to a
separate server will be good enough?
The time it takes for me to:
1 - Backup it up
2 - Transfer the dump (12.7GB compressed) to the office across the
3 - Decompress the full dump locally which will be 105GB raw file
4 - Restore the full dump to a test server

It is easily a week to do all of that, when I have finished doing all of
that if a problem has developed with my live server, what good that test
will be? How relevant that will be? How helpful?

My question is:
1 - Is there a more efficient way of backing up such large database,
using pg_dump or any other tool?
2 - Is there an easy way to compare the live database with the DUMP file
just created?
3 - If I restore the database to a separate server, is there a point to
do such a check, especially if it is going to take suck a long time to
even start doing such a check?

Pg_dump to split the file into smaller usable chuncks, which could be
restored one at time, is that possible?

PS I know about PITR but I can't implement it yet as I am still figuring
out certain things with the restore process.

I would really appreciate helps, please?

Thank you very much in advance


Renato Oliveira
Systems Administrator
e-mail: renato(dot)oliveira(at)grant(dot)co(dot)uk

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410

Grant Instruments (Cambridge) Ltd

Company registered in England, registration number 658133

Registered office address:
29 Station Road,

P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is
confidential. It is intended only for the named recipients(s). If you
are not the named recipient please notify the sender immediately and do
not disclose the contents to another person or take copies.

VIRUSES: The contents of this e-mail or attachment(s) may contain
viruses which could damage your own computer system. Whilst Grant
Instruments (Cambridge) Ltd has taken every reasonable precaution to
minimise this risk, we cannot accept liability for any damage which you
sustain as a result of software viruses. You should therefore carry out
your own virus checks before opening the attachment(s).

OpenXML: For information about the OpenXML file format in use within
Grant Instruments please visit our

Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
To make changes to your subscription:
This message may contain confidential or proprietary information intended only for the use of the 
addressee(s) named above or may contain information that is legally privileged. If you are 
not the intended addressee, or the person responsible for delivering it to the intended addressee, 
you are hereby notified that reading, disseminating, distributing or copying this message is strictly 
prohibited. If you have received this message by mistake, please immediately notify us by  
replying to the message and delete the original message and any copies immediately thereafter. 

Thank you. 

In response to


pgsql-admin by date

Next:From: DMDate: 2010-02-12 20:04:16
Subject: Postgres log help - Postgresql-8.3.5
Previous:From: dx k9Date: 2010-02-12 17:54:37
Subject: Postgresql & PAM & active directory

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group