Thread: BUG #2645: pg_restore crashes

The following bug has been logged online:

Bug reference:      2645
Logged by:          Thomas Verchow
Email address:      thomas@verchow.de
PostgreSQL version: 8.1.4
Operating system:   GNU/Linux, Debian sarge
Description:        pg_restore crashes
Details:

pg_restore ends with ,,Segmentation fault'' or ,,out of memory'' while
trying to restore a large custom-archive-backup (~ 30 GB).

If i choose a table, witch is at the beginning of the backup (according to
pg_restore --list), it works fine.

With smalltable, at the end of the archive, it
ends with a SegFault. So does the same with a big table.
(pg_restore --table=smalltable backup > out.sql)

The Error ,,out of memory'' appears during a restore into the db (using
--dbname=...).

It seems, that pg_restore can't read data behind a limit (2 GB, 8 GB?).

I piped pg_restore through gzip. After a filesize of ~ 7 GB pg_restore
crashes with ,,out of memory''. If the custom archive has the same
compression level, the assumtion for a 8 GB restriction seems to be valid.

This is bad, because you can't use pg_dump & pg_restore for bigger dbs :(

"Thomas Verchow" <thomas@verchow.de> writes:
> pg_restore ends with ,,Segmentation fault'' or ,,out of memory'' while
> trying to restore a large custom-archive-backup (~ 30 GB).

Can you provide a test case for this?  Perhaps a SQL script to generate
a database full of junk data, which when dumped and restored causes the
failure?

            regards, tom lane

I observed this symptom with a 4Mb .backup file (details of commands issued below).

The restore always failed at the same rogue table.

On going back to the source DB, I experienced trouble on that table: vacuum worked, but vacuum FREEZE hung after which
SELECTcount(*) hung! 

In my case, the "workaround" was to stop the source DB server and redo the backup.

My (somewhat speculative) conclusion is that there may be a problem with performing hot-backups on a live database.

Andrew.

 BACKUP:
 pg_dump.exe -i -h localhost -p 5432 -U Administrator -F c -b -v -f "D:\dbname.backup" dbname
 Windows Server 2003, postgres 8.1.5, pgAdmin3 v1.4.3

 RESTORE:
  Windows XP SP2, postgres 8.1.1 pgAdmin3 v1.4.1:
 C:\Program Files\PostgreSQL\8.1\bin\pg_restore.exe -i -h localhost -p 5432 -U postgres -d dbname -v "Z:\dbname.backup"


---------------------------------
 All new Yahoo! Mail "The new Interface is stunning in its simplicity and ease of use." - PC Magazine