Postgres Pro
Downloads
Home   >   mailing lists

Backup and restore postgreSQL - Mailing list pgsql-admin

From Bayan M. Almuqhim
Subject Backup and restore postgreSQL
Date
Msg-id 0EB54111E5F012439259BE380257F5D98F2388@ex-itu01-002
Whole thread Raw
Responses Re: Backup and restore postgreSQL
List pgsql-admin
Tree view
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.

تحذير: هذه الرسالة وما تحويه من مرفقات (إن وجدت) تمثل وثيقة سرية قد تحتوي على معلومات محمية بموجب القانون. إذا لم تكن الشخص المعني بهذه الرسالة فيجب عليك تنبيه المُرسل بخطأ وصولها إليك، وحذف الرسالة ومرفقاتها (إن وجدت)، ولا يجوز لك نسخ أو توزيع هذه الرسالة أو مرفقاتها (إن وجدت) أو أي جزء منها، أو البوح بمحتوياتها للغير أو استعمالها لأي غرض. علماً بأن فحوى هذه الرسالة ومرفقاتها (ان وجدت) تعبر عن رأي المُرسل وليس بالضرورة رأي مدينة الملك عبدالعزيز للعلوم والتقنية بالمملكة العربية السعودية، ولا تتحمل المدينة أي مسئولية عن الأضرار الناتجة عن ما قد يحتويه هذا البريد.

pgsql-admin by date:

Previous
From: David Johnston
Date:
Subject: Re: BI tools and PostgreSQL
Next
From: Steve Crawford
Date:
Subject: Re: Backup and restore postgreSQL