Thread: How to verify pg_dump files

Can anyone suggest how I could verify that the files created by pg_dump
are "okay"? They are being created for backup purposes, and the last
thing I want to do is find out that the backups themselves are in some
way corrupt.

I know I can check the output of the command itself, but what if.. I
don't know... if there are problems with the disc it writes to, or
something like that. Is there any way to check whether the output file
is "valid" in the sense that it is complete and syntactically correct?

--
Gary        Please do NOT send me 'courtesy' replies off-list.

On 07/11/2012 13:01, Gary wrote:
> Can anyone suggest how I could verify that the files created by pg_dump
> are "okay"? They are being created for backup purposes, and the last
> thing I want to do is find out that the backups themselves are in some
> way corrupt.
>
> I know I can check the output of the command itself, but what if.. I
> don't know... if there are problems with the disc it writes to, or
> something like that. Is there any way to check whether the output file
> is "valid" in the sense that it is complete and syntactically correct?

Reload it and test your application against it?

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

> -----Original Message-----
> From: Gary [mailto:listgj-pg@yahoo.co.uk]
> Sent: Wednesday, November 07, 2012 8:02 AM
> To: pgsql-general@postgresql.org
> Subject: How to verify pg_dump files
>
> Can anyone suggest how I could verify that the files created by pg_dump
> are "okay"? They are being created for backup purposes, and the last
> thing I want to do is find out that the backups themselves are in some
> way corrupt.
>
> I know I can check the output of the command itself, but what if.. I
> don't know... if there are problems with the disc it writes to, or
> something like that. Is there any way to check whether the output file
> is "valid" in the sense that it is complete and syntactically correct?
>
> --
> Gary        Please do NOT send me 'courtesy' replies off-list.
>


The only 100% fool-proof test would be to restore from your backup files.

Regards,
Igor Neyman

On 07/11/2012 18:57, Ryan Delaney wrote:
> On Wed, Nov 7, 2012 at 10:11 AM, Raymond O'Donnell <rod@iol.ie
> <mailto:rod@iol.ie>> wrote:
>
>     On 07/11/2012 13:01, Gary wrote:
>     > Can anyone suggest how I could verify that the files created by
>     pg_dump
>     > are "okay"? They are being created for backup purposes, and the last
>     > thing I want to do is find out that the backups themselves are in some
>     > way corrupt.
>     >
>     > I know I can check the output of the command itself, but what if.. I
>     > don't know... if there are problems with the disc it writes to, or
>     > something like that. Is there any way to check whether the output file
>     > is "valid" in the sense that it is complete and syntactically correct?
>
>     Reload it and test your application against it?
>
>     Ray.
>
>
>     --
>     Raymond O'Donnell :: Galway :: Ireland
>     rod@iol.ie <mailto:rod@iol.ie>
>
>
> Would that entail over-writing good data with possibly bad data?

No, reload your database into a newly-created database - on a test
server, not on the production server!

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 11/07/2012 11:56 AM, Igor Neyman wrote:
> The only 100% fool-proof test would be to restore from your backup files.
>
> Regards,
> Igor Neyman
Our internal process is to back up production databases regularly, and
then use the backups offsite to populate  copies of databases for
developer use. This allows us to test with real data, identifying real
world bugs that would not appear with often-limited, manually created,
"sample data", as well as verify our backups on a regular, daily basis.

I'd strongly recommend something similar if it works for you.

-Ben