Migrating Data from Postgres 7 to 8

Posted by Johan Cyprich on 10 Dec 2007 | Tagged as: Tech Tips

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.

  1. Use pg_dump to export the database:

    pg_dump database > database.sql

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

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


Related posts:
    PostgreSQL Using Too Much Memory
    Postgres: column “field” does not exist
    Moving the My Documents Folder
    The Internet at Warp 9

Share this post:

del.icio.us:Migrating Data from Postgres 7 to 8 digg:Migrating Data from Postgres 7 to 8 spurl:Migrating Data from Postgres 7 to 8 wists:Migrating Data from Postgres 7 to 8 simpy:Migrating Data from Postgres 7 to 8 newsvine:Migrating Data from Postgres 7 to 8 blinklist:Migrating Data from Postgres 7 to 8 furl:Migrating Data from Postgres 7 to 8 reddit:Migrating Data from Postgres 7 to 8 fark:Migrating Data from Postgres 7 to 8 blogmarks:Migrating Data from Postgres 7 to 8 Y!:Migrating Data from Postgres 7 to 8 smarking:Migrating Data from Postgres 7 to 8 magnolia:Migrating Data from Postgres 7 to 8 segnalo:Migrating Data from Postgres 7 to 8 gifttagging:Migrating Data from Postgres 7 to 8

Follow Me:

Did you find this post interesting and useful? You can keep up to date on this blog by subscribing to my RSS feed, or you can have new posts sent to you by e-mail. You can also follow me on Twitter.


1 Comment »

One Response to “Migrating Data from Postgres 7 to 8”

  1. on 10 Dec 2007 at 4:12 pm 1.Johan Cyprich said …

    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

Trackback This Post | Subscribe to the comments through RSS Feed

Leave a Reply