create user foouser;
create tablespace temptblspc location '/tmp/tmptblspc';
alter user foouser set temp_tablespaces='temptblspc';
Run pg_dumpall. It will produce a dump like:
...
CREATE ROLE foouser;
ALTER ROLE foouser WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB
LOGIN NOREPLICATION;
ALTER ROLE foouser SET temp_tablespaces TO 'temptblspc';
...
CREATE TABLESPACE temptblspc OWNER heikki LOCATION '/tmp/tmptblspc';
That latter ALTER ROLE statement fails at restore:
ERROR: tablespace temptblspc does not exist
The problem here is that the ALTER ROLE statement refers to the
tablespace, which is created afterwards. There's two possible solutions
to this that I can see:
1. Teach pg_dumpall to dump the ALTER ROLE statement after creating
tablespaces.
2. Relax the check on ALTER ROLE to not throw an error when you set
temp_tablespaces to a non-existent tablespace.
There's another GUC that has the same problem:
default_text_search_config. Only that is worse, because text search
configurations are local to a database, so reordering the statements in
the pg_dumpall output won't help. So I'm leaning towards option 2, also
because moving the ALTER ROLE statement in the dump would make it less
readable. Relaxing the check would be consistent with setting
search_path, where you get a NOTICE rather than an ERROR if you refer to
a non-existent schema in the ALTER ROLE statement.
Barring objections, I'll write a patch to relax the checking on
default_text_search_config and temp_tablespaces to match search_path.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
