bstaz.me

Problem:

Let’s say we have a database, mydb, with languages installed – I’ll use PL/pgSQL – and multiple schemas:

CREATE DATABASE mydb;
\c mydb
CREATE LANGUAGE plpgsql;
CREATE SCHEMA schema1;
CREATE SCHEMA schema2;
CREATE SCHEMA schema3;

And I want to make a database dump but only want a specific schema or set of schemas. I might use a dump command like so:

$ pg_dump -U postgres -nschema1 -npublic -Fc  mydb > mydb.dump

Even though I’ve included the public schema this dump will not contain my PL/pgSQL language:

$ pg_restore -l mydb.dump
;
; Archive created at Fri Aug 27 10:30:01 2010
;     dbname: mydb
;     TOC Entries: 6
;     Compression: -1
;     Dump Version: 1.10-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 8.3.9
;     Dumped by pg_dump version: 8.3.9
;
;
; Selected TOC Entries:
;
3; 2615 2200 SCHEMA - public postgres
1731; 0 0 COMMENT - SCHEMA public postgres
1732; 0 0 ACL - public postgres
6; 2615 66304 SCHEMA - schema1 postgres

Let’s include the meta schemas just for completeness:

$ pg_dump -U postgres -nschema1 -npublic -npg_catalog -npg_toast -ninformation_schema -Fc  mydb > mydb.dump

Nope, still not there:

$ pg_restore -l mydb.dump | grep -i language
1506; 1259 11439 TABLE information_schema sql_languages postgres
2048; 0 0 ACL information_schema sql_languages postgres
1427; 1259 2612 TABLE pg_catalog pg_language postgres
2084; 0 0 ACL pg_catalog pg_language postgres
1908; 0 11439 TABLE DATA information_schema sql_languages postgres
1890; 0 2612 TABLE DATA pg_catalog pg_language postgres
1835; 1259 2681 INDEX pg_catalog pg_language_name_index postgres
1836; 1259 2682 INDEX pg_catalog pg_language_oid_index postgres

So what’s the answer?

When creating the dump you will need to either

1. Create the dump excluding schemas (-N) you do not want instead of including the ones you do, or
2. Manually create the languages you’ll need with CREATE LANG in any new databases before restoring a dump created this way.

Unless you have more than a handful of schemas, option 1 is probably the best:

$ pg_dump -U postgres -Nschema2 -Nschema3 -Fc  mydb > mydb.dump

And now we have what we want:

$ pg_restore -l mydb.dump | grep -i language
292; 2612 66309 PROCEDURAL LANGUAGE - plpgsql postgres

Update: PostgreSQL 9 includes PL/pgSQL in new databases by default. Read more at PostgreSQL.org.