Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009

    Question Unanswered: howto backup a postgre db from server1 to server2 with different charsets?

    I have a problem, I want to move a postgre database from server1 to server2. The charset of the server1 is latin1 and the new server runs needs to run with UTF-8. The database I want to move must run on the server2 with UTF8 as well. So I have to convert the old latin1 to utf8. Is that possible to handle that case with pg_dump and pg_restore?
    By the way: there are also some psql FUNCTION and LANGUAGE creations within the database, but I don't have to move these, because I already recreated them manual on the new server2. I think that makes it easier.

    My first tries doing that by phpPgAdmin failed.

    Can you give me an advice? A short code sample would be great, because I'm new to postgre.

    I thank you very much in advance!


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    As Latin1 is convertible to UTF8, a simple pg_dump and pg_restore should be enough

    Check the manual for details:

  3. #3
    Join Date
    Dec 2009
    Thanks für your reply shammat.
    In the meantime I've solved the problem! I forget to tell you the postgre versions do not comply and this seems to make troubles with differen pg_dump / pg_restore versions.

    For anyone who need the solution which worked for my way:

    1. If postgres 7.x, install 8.x in addition (yeas you have 2 postgres server in this case). Start the postgres 7 server.

    server1# /etc/init.d/postgres-8.x stop
    server1# /etc/init.d/postgres-7.x start

    2. use the pg_dump of postgres 8.x and dump the databases by using the -E option:

    server1# pg_dump DB1 -C -E UTF8 -f BACKUPFILE -Fp

    // will dump the DB1 to an UTF8 encoded file named BACKUPFILE in plaintext Format with
    // the CREATE TABLE statements.

    3. maybe you need to edit your dump file or like to view at it? do it in this step

    4. copy the BACKUPFILE to the filesystem on your target server

    server1# scp BACKUPFILE root@server2:/tmp/.

    5. import the BACKUPFILE to the current running Postgre Server as user postgres

    server2# su postgres
    postgres@server2# psql -f /tmp/BACKUPFILE

    By the way: pg_dumpall does not support -E option! You need to dump all the databases manually (or write your own script).
    If you want to check the import result before you import it to the server2 database, you can also import it for testing on server1 postgre8 database of course. For this, stop the postgre7 server and start postgre8 server and do the step 5)

    I hope it helps for someone.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts