Geek Answers Handbook

PostgreSQL - backup and restore partitioned database tables

I am working on PostgreSQL 8.4 and I would like to backup and restore (from Ubuntu 11.10 to Ubuntu 12.4)

I want to include all sections, clusters, roles, and more.

My commands: Backup:

dumb_all > filename

Squeeze:

zip -f mybackup

Remove and restore:

sudo gunzip -c /home/ubuntu/Desktop/backupFile.zip | psql -U postgres

The problem is in the recovery 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

In addition, partition tables have not been restored. also some tables are restored without any data!

Please, help!

EDIT

I used pgAdmin for backup using the "backup server" option.

backup

+4
source share
1 answer

zip , unzip, , gunzip, /.

gzip gunzip. , mybackup.sql, :

gzip mybackup.sql

mybackup.sql.gz. , , :

gunzip -c mybackup.sql.gz | psql -U postgres

, pgAdmin . , , , pg_dumpall :

pg_dumpall -U postgres -f mybackup.sql

pipe:

pg_dumpall -U postgres | gzip -c > mybackup.sql.gz

, pg_dumpall pg_dump , . pg_dumpall .

+3

More articles:


All Articles