Thread: Errors recovering database

Hello,

I'm having issues restoring a dumped database with the following commands:

Dumping:
pg_dump -p -U'postgres' -C -f ./dumpbkp mydatabase
tar -cvzf dumpbkp.tar.gz dumpbkp


Restoring:
cat dumpbkp.tar.gz | gunzip | psql -U'postgres'

The database is quite big, so it takes a lot of time to restore. The process outputs some normal messages and, in the end, after a lot of time, it prints out some strangers errors like "duplicate key", "constraint already exists", and other similar...

at the end, I've got a database 10 GB smaller than the original, which clearly indicates that some data was lost.

The versions are 8.4.5 (original) and 8.4.7 (restored).

What should I do? Am I doing something wrong?

Thanks in advance.

Henrique Teófilo

=?ISO-8859-1?Q?Henrique_Te=F3filo?= <henriquetft@gmail.com> writes:
> Dumping:
> pg_dump -p -U'postgres' -C -f ./dumpbkp mydatabase
> tar -cvzf dumpbkp.tar.gz dumpbkp

> Restoring:
> cat dumpbkp.tar.gz | gunzip | psql -U'postgres'

AFAICS that ought not work at all, not even a little bit.  You've
wrapped the dump script into a tar archive, and then not unwrapped it.

The pg_dump argument list seems a bit less than kosher, as well ...
doesn't -p require an argument?

            regards, tom lane

Hi Tom.

Actually the -p paremeter has the argument, but I think I've removed it to clarify the command, because it comes from a variable $pgport in my script.

Concerning to tar command, I think you're right, I didn't notice... but it did work great until pop up these erros in the end, so I've googled and I've seen other people with the same problem, what led me to think it was a postgres error.

I'll try again unwrapping the file properly

Thanks.