PostgreSQL Backup – How to Restore Postgres 9.0 Backup into Postgres 9.3

backuppostgresqlrestore

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 !

Best Answer

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

Related Question