Backup and restore postgreSQL

From: "Bayan M(dot) Almuqhim" <balmuqhim(at)kacst(dot)edu(dot)sa>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Backup and restore postgreSQL
Date: 2013-11-05 12:17:41
Message-ID: 0EB54111E5F012439259BE380257F5D98F2388@ex-itu01-002
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Dear all,

I have 8.4 postgreSQL on Ubuntu 11.10 and I would like to upgrade my server to Ubuntu 12.04 and use 9.1 postgreSQL.
I have Slony-| replication cluster in my database, also there is a table with many partitions.

In order to do that, I started by backing up my database and restore it in a test server (Ubuntu 12.04, 9.1 postgreSQL) to make sure that I can get back the data if something wrong happened. My database have geometry data, so I enabled postgis on it.

I tried pg_dump and pg_dumpall to do the backup, then restore it but neither ways worked for me in the restore process !
pg_dump: sudo pg_dump -i -U postgres mydb | gzip > /$path/database-backup.gz
and restore it with: sudo gunzip -c /$path/database-backup.gz | psql mydb -U postgres

pg_dumpall: sudo pg_dumpall -U postgres | gzip -c > mybackup.sql.gz
and restore it: sudo gunzip -c mybackup.sql.gz | psql -U postgres

The tables were restored but with no data inside them, and I got "out of memory error" and the restore process stops.
Here is the error that I encounter with my restore (in terminal):

--------------------------
.
.
.
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \.
ERROR: syntax error at or near "1"
LINE 1: 1 $someNumbers 2013-02-26 04:52:27.410494 $someNumbers $someNumbers ...
^
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: 1 $someNumbers $someNumbers $someNumbers D "ColName"='VALUE' and "Col"='VAlue...
^
out of memory
-----------------------

Is that syntax error means there is a real error in my data which cause failure to the restore process?
I learned that system generated date\time may stored in the database in the format "2013-02-26 04:52:27.410494" which will cause error in
backup and restore processes, is that correct? although I searched my database for that specific date and couldn't find it in any table !

If those were not the problems, would you help me to identify and solve the problem? Is there special configuration I should do to the new Serve/Database in order to restore the data in it correctly ?
Or is there a better way to do the backup and restore to my database?

Thank you and best regards,
Bayan Almuqhim

Warning: This message and its attachment, if any, are confidential and may contain information protected by law. If you are not the intended recipient, please contact the sender immediately and delete the message and its attachment, if any. You should not copy the message and its attachment, if any, or disclose its contents to any other person or use it for any purpose. Statements and opinions expressed in this e-mail and its attachment, if any, are those of the sender, and do not necessarily reflect those of King Abdulaziz city for Science and Technology (KACST) in the Kingdom of Saudi Arabia. KACST accepts no liability for any damage caused by this email.

?????: ??? ??????? ??? ????? ?? ?????? (?? ????) ???? ????? ???? ?? ????? ??? ??????? ????? ????? ???????. ??? ?? ??? ????? ?????? ???? ??????? ???? ???? ????? ??????? ???? ?????? ????? ???? ??????? ????????? (?? ????)? ??? ???? ?? ??? ?? ????? ??? ??????? ?? ???????? (?? ????) ?? ?? ??? ????? ?? ????? ?????????? ????? ?? ????????? ??? ???. ????? ??? ???? ??? ??????? ????????? (?? ????) ???? ?? ??? ??????? ???? ???????? ??? ????? ????? ????????? ?????? ???????? ???????? ??????? ????????? ??? ????? ??????? ?? ??????? ?? ??????? ??????? ?? ?? ?? ?????? ??? ??????.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Steve Crawford 2013-11-05 18:01:30 Re: Backup and restore postgreSQL
Previous Message David Johnston 2013-11-04 20:49:49 Re: BI tools and PostgreSQL