pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBackup and restore postgreSQL
2+ messages / 2 participants
[nested] [flat]
* Backup and restore postgreSQL
@ 2013-11-05 12:17 Bayan M. Almuqhim <balmuqhim@kacst.edu.sa>
0 siblings, 1 reply; 2+ messages in thread
From: Bayan M. Almuqhim @ 2013-11-05 12:17 UTC (permalink / raw)
To: 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.
?????: ??? ??????? ??? ????? ?? ?????? (?? ????) ???? ????? ???? ?? ????? ??? ??????? ????? ????? ???????. ??? ?? ??? ????? ?????? ???? ??????? ???? ???? ????? ??????? ???? ?????? ????? ???? ??????? ????????? (?? ????)? ??? ???? ?? ??? ?? ????? ??? ??????? ?? ???????? (?? ????) ?? ?? ??? ????? ?? ????? ?????????? ????? ?? ????????? ??? ???. ????? ??? ???? ??? ??????? ????????? (?? ????) ???? ?? ??? ??????? ???? ???????? ??? ????? ????? ????????? ?????? ???????? ???????? ??????? ????????? ??? ????? ??????? ?? ??????? ?? ??????? ??????? ?? ?? ?? ?????? ??? ??????.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Backup and restore postgreSQL
@ 2013-11-05 18:01 Steve Crawford <scrawford@pinpointresearch.com>
parent: Bayan M. Almuqhim <balmuqhim@kacst.edu.sa>
0 siblings, 0 replies; 2+ messages in thread
From: Steve Crawford @ 2013-11-05 18:01 UTC (permalink / raw)
To: Bayan M. Almuqhim <balmuqhim@kacst.edu.sa>; pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-11-05 18:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-05 12:17 Backup and restore postgreSQL Bayan M. Almuqhim <balmuqhim@kacst.edu.sa>
2013-11-05 18:01 ` Steve Crawford <scrawford@pinpointresearch.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox