Thread: pg_restore problem

pg_restore problem

From
"lists"
Date:
Hi,
 
I've a dump I made with the command:
 
pg_dump -a -b -Fc -f DB.dump -o -v DB
 
then I try to restore with:
 
pg_restore -a -d DB2 -Fc -o -v DB.dump
 
This database is about 1.5Gb big.
 
The dump takes abou 1 hour, but the restore takes forever!
 
Last time I ran it, it takes more than 10 hours, when I gave up
and stopped the process.
 
Tried with both Ft and Fc.
 
Another doubt: Why a dump of this DB with Ft has about the
same size of the original DB(1.5GB) and with Fc it has about 50MB?
 
My system is:
P3 1Ghz, 1GB ram, Win2k, Postgres 7.2.2
Free space ~5GB.
 
TIA,
 
Ricardo.

Re: pg_restore problem

From
"lists"
Date:
> Are you sure your restoring to an empty database? If you try to restore
> your dump into the database you dumped it from (without first
> drop/create) it will take forever as you'll have all kinds of data
> collisions (imagine every insert into a table erroring out due to
> duplicate primary key).

Yes, I'm sure it is an empty database. I didn't mention in the e-mail,
but the complete script I did is:

$pg_dump  -s DB1 > DB1.sql

$pg_dump -a -b -Fc -f  DB1.dump -o -v DB1

$dropdb DB2

$createdb DB2

$psql DB2 <DB1.sql

$pg_restore -a -d DB2 -Fc -o -v DB1.dump

So it's a brand new DB I'm restoring to.

> As for the -Ft vs. -Fc size question, the tar format is not a compressed
> format, while the "c"ustom format is.
Thanks.

Ricardo.


Re: pg_restore problem

From
Robert Treat
Date:
Are you sure your restoring to an empty database? If you try to restore
your dump into the database you dumped it from (without first
drop/create) it will take forever as you'll have all kinds of data
collisions (imagine every insert into a table erroring out due to
duplicate primary key).

As for the -Ft vs. -Fc size question, the tar format is not a compressed
format, while the "c"ustom format is.

Robert Treat


On Fri, 2003-03-28 at 07:35, lists wrote:
> Hi,
>
> I've a dump I made with the command:
>
> pg_dump -a -b -Fc -f DB.dump -o -v DB
>
> then I try to restore with:
>
> pg_restore -a -d DB2 -Fc -o -v DB.dump
>
> This database is about 1.5Gb big.
>
> The dump takes abou 1 hour, but the restore takes forever!
>
> Last time I ran it, it takes more than 10 hours, when I gave up
> and stopped the process.
>
> Tried with both Ft and Fc.
>
> Another doubt: Why a dump of this DB with Ft has about the
> same size of the original DB(1.5GB) and with Fc it has about 50MB?
>
> My system is:
> P3 1Ghz, 1GB ram, Win2k, Postgres 7.2.2
> Free space ~5GB.
>
> TIA,
>
> Ricardo.


Re: pg_restore problem

From
"lists"
Date:
> $pg_restore -a -d DB2 -Fc -o -v DB1.dump

As I'm running pg_restore like this and is taking to long,
is there any other way to restore OIDs/lo objects?

TIA
Ricardo