Migrating data from Postgres8.2 to Postgres8.3
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. Here are the results:
1. I strongly suggest if you have a big dump file (mine is 500MB) to split the schema from the data.
pg_dump -C -s -h 127.0.0.1 -U lbs -W beijing > beijing_20080507_schema.sql pg_dump -a -d -h 127.0.0.1 -U lbs -W beijing > beijing_20080507_schema.sql
2. 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
3. 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
4. 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.
5. Let’s load the data.
psql -h 127.0.0.1 -U beijing_4326 < beijing_20080507_data.sql