One of my colleagues had developed a Ruby-on-Rails application locally on a Mac, using Postgres-9.2.3, but the app was using the 'postgres' super user account.
The app needed to be migrated to the 'real' server, which is running Ubuntu 12.04, which has Postgres-9.1.4.
The trouble was that the database makes use of the 'hstore' datatype (link). This is implemented as an extension in Postgres, and can only be created as the database superuser.
The original pg_dump contains statements statements such as "CREATE EXTENSION hstore" , which does not work with the regular Postgres account that was created for the application.
In the end, I create the extension in template0, so that it is available in all new databases, and excluded the "CREATE EXTENSION" statements while restoring the database on the Ubuntu server.
Export the old database on the Mac and the resulting SQL file to the production server:
macbook:postgres postgres$/opt/local/lib/postgresql92/bin/pg_dump -x -O --column-inserts mydatabase | grep -v EXTENSION > mydatabase.sql scp mydatabase.sql productionhost:
Then on the production server, install the postgresql-contrib package, which contains the hstore definitions:
sudo apt-get install postgresql-contrib
Then sudo to the postgres user, add the extension to template0, create the empty database, and import the dump:
createuser -D -I -R -S -P specific_postgres_user createdb -e -O specific_postgres_user mydatabase cat mydatabase.sql | psql -U specific_postgres_user mydatabase