I'm working on PostgreSQL 8.4 and I'd like to do backup and restore (from Ubuntu 11.10 to Ubuntu 12.4)
I want to include all partitions, clusters, roles and stuff.
My commands: Back up:
dumb_all > filename
Compress:
zip -f mybackup
Uncompress and restore:
sudo gunzip -c /home/ubuntu/Desktop/backupFile.zip | psql -U postgres
The issue is in the restore process, I got an error
invalid command \.
ERROR: syntax error at or near "2"
LINE 1: 2 2 1
^
invalid command \.
ERROR: syntax error at or near "1"
LINE 1: ...
^
out of memory
Plus, the tables with partitions did not restored. also some tables restored without any data!
Please help!
EDIT
I used pgAdmin to do the back up, using the "backup server" option.
If you did used zip
to compress the output, then you should use unzip
do uncompress it, not gunzip
, they use different formats/algorithms.
I'd suggest you to use gzip
and gunzip
only. For instance, if you generated a backup named mybackup.sql
, you can gzip it with:
gzip mybackup.sql
It will generate a file named mybackup.sql.gz
. Then, to restore, you can use:
gunzip -c mybackup.sql.gz | psql -U postgres
Also, I'd suggest you to avoid using pgAdmin to do the dump. Not that it can't do, it is just that you can't automatize it, you can easily use pg_dumpall
the same way:
pg_dumpall -U postgres -f mybackup.sql
You can either dump and compress without intermediate files using pipe:
pg_dumpall -U postgres | gzip -c > mybackup.sql.gz
BTW, I'd really suggest you avoiding pg_dumpall
and use pg_dump
with custom format for each database, as with that you already get the result compressed and easier to use latter. But pg_dumpall
is ok for small databases.