Postgresql – How to pg_dump the database while excluding extensions

herokupg-dumppostgresql

I am trying to migrate my PostgreSQL 9.3 database to heroku.

My database uses the UUID-ossp contrib extension

I am not able to load my db onto Heroku because 

ERROR:  permission denied for language c

on 

-- Name: uuid_generate_v1(); Type: FUNCTION; Schema: public; Owner: -
--
CREATE FUNCTION uuid_generate_v1() RETURNS uuid
    LANGUAGE c STRICT
    AS '$libdir/uuid-ossp', 'uuid_generate_v1';

I understand I can simply 

CREATE EXTENSION "uuid-ossp";

in my database. But how can i exclude the various UUID functions from my pg_dump in the first place?

Best Answer

It looks like you've installed the ossp-uuid extension from the sql scripts directly, rather than with create extension.

You should CREATE EXTENSION "uuid-ossp" FROM unpackaged; on your local DB, before creating the dump. That'll convert it into an extension, which is dumped as CREATE EXTENSION ... instead of individual CREATE FUNCTION ... commands.

Related Question