Copying databases with extensions - pg_dump question
От | Ivan Voras |
---|---|
Тема | Copying databases with extensions - pg_dump question |
Дата | |
Msg-id | ihbtfv$rq7$1@dough.gmane.org обсуждение исходный текст |
Ответы |
Re: Copying databases with extensions - pg_dump question
|
Список | 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?
В списке pgsql-general по дате отправления: