Dec 10 2007
Migrating Data from Postgres 7 to 8
I’ve been having a very difficult time trying to migrate data from a Postgres 7 database to Postgres 8. I used the pg_dump utility to export a database, but I got numerous errors when trying to import into a Postgres 8 server.
Most of the errors were invalid UTF-8 sequences that the older Postgres allowed to be entered in tables. Version 8.1.x of Postgres only accepts valid UTF-8 sequences, so this effectively prevented me from importing data to it.
With myself not being too familiar with Postgres, I turned to the #postgresql chatroom on irc.freenode.net. I’ve had good success on chatrooms when searching for a quick response to technical issues I’ve had in the past.
After talking to several people, I learned about iconv. This is a utility that converts text from one encoding type to another. This is what will fix the bad UTF-8 sequences in Postgres 8.
The following procedure will export data from Postgres 7 to 8.
- Use pg_dump to export the database:
pg_dump database > database.sql
- Fix the invalid UTF-8 sequences with iconv:
iconv -c -f UTF-8 -t UTF-8 -o cleanfile.sql database.sql
where cleanfile.sql is the fixed database, database.sql is the database with the invalid UTF-8 sequences
- Import the data into Postgres 8:
psql -d database -f cleanfile.sql
where database is the name of the database where the data is being imported to, cleanfile.sql is the data with the correct UTF-8 sequencing
This procedure will import data from a Postgres 7 database to a Postgres 8 database.
Dec 10, 2007 @ 16:12:57
I’ve written 2 scripts for exporting and importing databases using the previous technique. You may need to find the path of the sh utility with the which command (i.e. which sh).
export.sh
=========
#! /bin/sh
pg_dump database>backup/database.sql
import.sh
=========
#! /bin/sh
iconv -c -f UTF-8 -t UTF-8 -o clean_database.sql database.sql
psql -d database -f clean_database.sql