Take the 2-minute tour ×
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. It's 100% free, no registration required.

My server is working on Ubuntu with PostgreSQL 8.4 and the development machine I'm using has Debian Lenny with PostgreSQL 8.3. My machine can't be upgraded to PostgreSQL 8.4 and I need to restore a dump file from my server.

The dump sentence I used was:

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f "/home/damian/backups/mybase.backup" mybase

To restore I'm using:

pg_restore -i -h localhost -p 5432 -U postgres -d mybase -v "/home/damian/backups/mybase.backup"

How can I restore a dump file from a newer version of PostgreSQL into an older version of PostgreSQL?

Thanks for any help in advance.

Edit: I tried to use a different parameter in the option -F, this time I tried to make a dump file in plain text but some of the information didn't restore correctly, how can I solve this?

Edit: I thought the problem was the encoding my database was using but I changed it to UTF8 -which is my dump file encoding- but I got the same error as before:

error: syntax error at or near «1»
LINE 1: 1 TELÃFONO NÃMERO TELEFÃNICO MEDIANTE EL CUAL SE PUEDE ESTAB...

I'm really lost at what is causing these errors.

share|improve this question

migrated from stackoverflow.com Apr 11 '12 at 20:25

This question came from our site for professional and enthusiast programmers.

    
It looks like you edited to mention using plain format while I was typing up an answer suggesting just that. You need to look at the errors and see what they are to decide what to do. If you're using features in 8.4 which just don't exist in 8.3, you may need to get a bit creative. –  kgrittn Apr 11 '12 at 17:34
    
You need to sort out your character encoding issues. Be aware that if the database you dumped is using UTF8 encoding and you don't create your database with UTF8 as its encoding scheme, you may not be able to store all the characters from the dump. You also need to make sure the client is doing the right thing; if it's reading a file in UTF8 encoding under the assumption that it's in LATIN9 encoding, it will mangle things. I recommend you look at this page in the docs, and search for "encoding": postgresql.org/docs/9.1/interactive/app-psql.html –  kgrittn Apr 11 '12 at 20:34

1 Answer 1

PostgreSQL really doesn't put much effort into supporting moves from newer major versions back to older ones. One big reason for that is that newer versions usually support features which you just can't install on older versions; although any given database might not be using any of the new features, the project philosophy is that they would rather not support it than to support it in a buggy or incomplete fashion.

Your best bet might be to generate a "plain" format dump rather than a "custom" format dump. This is just a text file of SQL commands. If you aren't using any new features, it might just apply cleanly. If not, you can look into the errors and decide what to do for a back-port.

share|improve this answer
    
I tried my luck with the plain text format dump, but many tables weren't created and none of them used anything new, I used the next command to restore it: \i backup-file –  Uriel Apr 11 '12 at 17:38
    
An example or two of what errors you got would go a long way. –  kgrittn Apr 11 '12 at 17:45
    
just figured out the issue, the machine I'm working on has LATIN9 as its default character set, so I just need to set it to UTF-8 –  Uriel Apr 11 '12 at 17:53

Your Answer

 
discard

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