Postgres Pro
Downloads
Home   >   mailing lists

Thread: Backup and restore postgreSQL

Backup and restore postgreSQL

From
"Bayan M. Almuqhim"
Date:
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.

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

Re: Backup and restore postgreSQL

From
Steve Crawford
Date:
On 11/05/2013 04:17 AM, Bayan M. Almuqhim wrote:
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.

Question one: did you use the *new* version of pg_dump and psql or the old one? You should always do your dump and restore using the client tools from the new version, i.e. use the 9.1 pg_dump to dump the 8.4 database.

As long as you are going through the effort of upgrading why not go straight to 9.3 and get all the newer features, fixes and improvements along with two more years of support before EOL?

Cheers,
Steve