One of my hurdles recently is migrating data from my Debian Lenny Desktop Box (Postgres8.2.7/Postgis1.3.1) to my new MacBookPro Leopard(Postgres8.3.1/Postgis1.3.3). I found it out the hard way by inspecting the dump files manually.

  1. pg_dump is your friend.
pg_dump --help
  1. I strongly suggest if you have a big dump file (mine is 500MB) to split the schema from the data.

Add “-s” to create the schema:

pg_dump -C -s -h 127.0.0.1 -U lbs -W beijing > beijing_20080507_schema.sql

Add “-a” to dump the data only:

pg_dump -a -h 127.0.0.1 -U lbs -W beijing > beijing_20080507_schema.sql

  1. pg_dump from an 8.2 would have statically linked liblwgeom to /usr/lib/postgresql/8.2/liblwgeom. You should change that to whereever your liblwgeom resides, mine is on /usr/local/pgsql/lib/liblwgeom. Just do a simple search and replace using vim on your file_schema.sql

  2. After editing the schema, we can now restore the structure of the database. Check for errors and manually update the schema if needs be.

psql -h 127.0.0.1 -U beijing_4326 < beijing_20080507_schema.sql

  1. Ok, so now we have the structure ready, we can also check this from pgAdmin3. Have a good look on the functions and table structures if they are fully restored.

  2. Let’s load the data.

psql -h 127.0.0.1 -U beijing_4326 < beijing_20080507_data.sql