PostgreSQL - Backup and Restore Database Tables with Partitions

Question

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.

backup

You could post here the commands that you really used, as the problem may reside on them... Seriously, just copy and paste here... — MatheusOl
– MatheusOl, Commented Oct 8, 2013 at 14:43
How about the restore, how did you do? After pgAdmin have you compressed the result? How? — MatheusOl
– MatheusOl, Commented Oct 9, 2013 at 12:28
the same commands in the question. I did the backup using dump_all then compressed the result using zip -f mybackup then uncompressed and restored it in another DB using one command sudo gunzip -c /home/ubuntu/Desktop/backupFile.zip | psql -U postgres Do you have a better method? — Shadin
– Shadin, Commented Oct 10, 2013 at 7:35
No, you didn't!!! How zip -f mybackup would generate a file named backupFile.zip? Anyway, I think I guessed your problem, see my answer... — MatheusOl
– MatheusOl, Commented Oct 10, 2013 at 12:20

MatheusOl · Accepted Answer · 2013-10-10 12:19:24Z

3

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.

answered Oct 10, 2013 at 12:19

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kjosh Over a year ago

@Shadin @MetheusOl I have tried all suggestions above but still an error. gzip: /users/srv-postgresql/backup/bmipgsqld1-testdb-2016-05-31-23-00-05.psql.gz: Permission denied

MatheusOl Over a year ago

@kjosh: your problem is permission on the OS for the user you are running the backup. I suggest create a new question if you want more help on that.

Collectives™ on Stack Overflow

PostgreSQL - Backup and Restore Database Tables with Partitions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related