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.
Best Answer
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.