Hi all,

We're using pg_dump to backup our databases.  The actual pg_dump
appears to work fine.  On smaller (< approx. 100 Meg) data sets, the
restore also works, but on larger data sets the restore process
consistently fails.

Other facts that may be of interest:

  * We're running Postgres 7.2.3 on a more-or-less stock Red Hat 7.3
platform.
  * Backup is done with "pg_dump -c -U postgres", then gzip
  * Restore is via "cat <archive_file> | gunzip | psql "

The particular file I'm wrestling with at the moment is ~2.2 Gig
unzipped.  If you try to restore using pg_restore, the process
immediately fails with the following:

    pg_restore: [archiver] could not open input file: File too large

When the data file is gzip'd, you can at least get the restore process
started with the following:

     cat archive_file.gz | gunzip | psql dbname

The above command line starts OK, but eventually fails with:

     server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
        connection to server was lost

7 Apr 2004 12:12:45 -0700
s_hawkins@mindspring.com (S. Hawkins)
restore of large databases failing--any ideas?
s_hawkins@mindspring.com (S. Hawkins) writes:
>   * We're running Postgres 7.2.3 on a more-or-less stock Red Hat 7.3
> platform.

Both the database and the platform are seriously obsolete :-(

> The particular file I'm wrestling with at the moment is ~2.2 Gig
> unzipped.  If you try to restore using pg_restore, the process
> immediately fails with the following:
>     pg_restore: [archiver] could not open input file: File too large

It appears that you're working with a pg_restore binary that doesn't
support access to files larger than 2G.  This is mostly an issue of what
the platform's libc can handle; and on many platforms it depends on
build or link options.  I no longer recall whether RH 7.3 supported
largefile access at all, let alone what build-time pushups were needed
to make it happen if it could happen.

My recommendation would be to get hold of a current PG version, dump
using the current version's pg_dump, then install and reload into the
current version.

			regards, tom lane

Fri, 09 Apr 2004 00:09:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: restore of large databases failing--any ideas? 