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.
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.
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)