[ADMIN] Errors recovering database

[Henrique Teófilo]

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

[Tom Lane]

Post by Henrique Teófilo
pg_dump -p -U'postgres' -C -f ./dumpbkp mydatabase
tar -cvzf dumpbkp.tar.gz dumpbkp
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

[Henrique Teófilo]

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.

Post by Tom Lane

Post by Henrique Teófilo
pg_dump -p -U'postgres' -C -f ./dumpbkp mydatabase
tar -cvzf dumpbkp.tar.gz dumpbkp
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