Copying databases with extensions - pg_dump question

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Copying databases with extensions - pg_dump question
Date: 2011-01-21 12:17:03
Message-ID: ihbtfv$rq7$1@dough.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

A fairly frequent operation I do is copying a database between servers,
for which I use pg_dump. Since the database contains some extensions -
most notably hstore and tsearch2, which need superuser privileges to
install, I have a sort of a chicken-and-egg problem: the owner of the
database (and all its objects) should be a non-superuser account so I
can't simply use the output from pg_dump and expect everything to be
correct after restoring it.

So far, I've used this workaround: install all superuser-requiring
extensions in template1 on the destination server and then restore from
pg_dump, ignoring the occasional "duplicate object" errors. This would
work out of the box but pg_dump's "create database" commands (outputted
with -C) includes the "WITH TEMPLATE=template0" clause so I made a small
utility which modifies these dumps to change the one byte so the
template becomes template1. (-C is useful so I can do "psql template1
pgsql < my_dump.sql" and get it all done).

Anyway, this works "good enough" but I wonder now if there is a better
solution for this? As a feature request, I'd like a "template database"
argument to use with "-C" so I don't have to modify the dumps, but there
could be a better solution which side-steps this.

Is there a canonical way to deal with this problem?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christian Ullrich 2011-01-21 12:43:02 Re: PostgreSQL 9.0.1 PITR can not copy WAL file
Previous Message orgilhp 2011-01-21 08:56:23 Read problem from Bytea column