Restore Postgres 9.0 backup into Postgres 9.3

Question

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 !

• mysqlrockstar: Probably that is normal Check stackoverflow.com/questions/20427689/…
• jask: @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.

Answer 1

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

• jask: 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

Answer 2

The problem on my side was the encoding format difference between the dump file and the destination database I created. Therefore, creating a database using the source databases' encoding, i.e, UTF-8, resolved the issue.

Example:

CREATE DATABASE database_name
WITH
OWNER = database_owner
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1
TEMPLATE template0;

Then

psql -p 6473 -U database_owner -Fct -v -d database_name < dbname_db_dump.sql