2

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.

Improve this question
2
  • 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, 2012 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, 2012 at 20:34

1 Answer 1

Reset to default
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.

Improve this answer
3
  • 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, 2012 at 17:38
  • An example or two of what errors you got would go a long way.
    – kgrittn
    Apr 11, 2012 at 17:45
  • 1
    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, 2012 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.