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?