current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 0 down vote favorite

I'm having a problem restoring my Postgres 9.0 database into a 9.3 version.

I did the backup with this command:

pg_dump -U user database -f /root/mydb.sql

And when I send the backup to the new server and try to restore it I get this error:

psql.bin:/root/mydb.sql:5628157: invalid command \n
psql.bin:/root/mydb.sql:5628158: invalid command \n
psql.bin:/root/mydb.sql:5628159: invalid command \n
psql.bin:/root/mydb.sql:5628160: invalid command \n
psql.bin:/root/mydb.sql:5628161: invalid command \n
psql.bin:/root/mydb.sql:5628162: invalid command \n
psql.bin:/root/mydb.sql:5628163: invalid command \n
psql.bin:/root/mydb.sql:5628164: invalid command \n
psql.bin:/root/mydb.sql:5628165: invalid command \n
psql.bin:/root/mydb.sql:5628166: ERROR:  syntax error at or near "PageRange"
LINE 1: PageRange=1-2</PARAMETERS><image_type>high</image_type></SPO...
        ^
psql.bin:/root/mydb.sql:5628166: invalid command \n
psql.bin:/root/mydb.sql:5628167: invalid command \n
psql.bin:/root/mydb.sql:5628168: invalid command \n
psql.bin:/root/mydb.sql:5628169: invalid command \n
psql.bin:/root/mydb.sql:5628170: invalid command \n
psql.bin:/root/mydb.sql:5628171: invalid command \n
psql.bin:/root/mydb.sql:5628172: invalid command \n
psql.bin:/root/mydb.sql:5628173: invalid command \n
psql.bin:/root/mydb.sql:5628174: invalid command \n
psql.bin:/root/mydb.sql:5628175: invalid command \n
psql.bin:/root/mydb.sql:5628176: invalid command \n
psql.bin:/root/mydb.sql:5628177: invalid command \n
psql.bin:/root/mydb.sql:5628178: invalid command \n
psql.bin:/root/mydb.sql:5628179: invalid command \n
psql.bin:/root/mydb.sql:5628180: invalid command \n
psql.bin:/root/mydb.sql:5628181: invalid command \n
psql.bin:/root/mydb.sql:5628182: invalid command \n
psql.bin:/root/mydb.sql:5628183: invalid command \n
psql.bin:/root/mydb.sql:5628184: invalid command \n
psql.bin:/root/mydb.sql:5628185: invalid command \n
psql.bin:/root/mydb.sql:5628186: invalid command \n
psql.bin:/root/mydb.sql:5628187: invalid command \n
psql.bin:/root/mydb.sql:5628188: invalid command \n
psql.bin:/root/mydb.sql:5628189: invalid command \n
psql.bin:/root/mydb.sql:5628190: invalid command \n
^CCancel request sent
psql.bin:/root/mydb.sql:5628191: ERROR:  syntax error at or near "n"
LINE 1: n Berrocal_Ausonia3.psd</name><type>Foto_ALTA</type><mimetyp...

I'm trying to restore the database with this command:

psql -U user -d database -f /root/mydb.sql

When the restore finish I could see that there are a lot of information that doesn't exist so it's a big problem !

Can anybody help me?

Thanks a lot !

share|improve this question
    
Probably that is normal Check stackoverflow.com/questions/20427689/… – Kaka DBA Aug 26 at 14:32
    
@KakaDBA I've read this comment before ask my question but when the restore finish I can't see content in more than one table. For example, the sql backup has a lot of users but not the restore. So, I think there are something wrong. – jask Aug 29 at 11:22
up vote 0 down vote accepted

So...

Does any of the data structure exist?

I could be wrong but it may be that your backup format. It depends on how you backed up, using pg_dump etc.

try using pg_restore filename -d database

pg_restore /root/mydb.sql -d database

It could also be the database encoding.

The database you are dumping into, what encoding is it?

UTF-8, SQL_ASCII ?

try createdb database -E utf-8

then psql -f /root/mydb.sql -d databse

share|improve this answer
    
Thanks ! I can solve mi problem doing this: Backup pg_dump -i -h localhost -p 5432 -U <user> -Ft -b -v -f /path/to/backup.tar <db-name> Restore pg_restore -U <user> -Ft -v -h localhost -W -d <db-name> /path/backup.tar – jask Aug 29 at 11:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.