btober@xxxxxxxxxxxxxxxxxxxxx wrote:
> I've run into a problem while migrating an existing 8.2.7 data base to a
> new server running 8.3.3 (although I think the version numbers may not
> matter -- I think I've seen this problem in the past and just lived with
> it since so much of Postgresql is so great!).
>
> The problem stems from the fact that for certain roles, I have defined
> default search paths, and because the output of pg_dumpall includes role
> definitions first, then data base definitions, then schema definitions.
>
> Thus, when piping the output (from legacy host 192.168.2.2) to populate
> the newly initialized cluster, by way of running (on the new host
> 192.168.2.3)
>
>
> pg_dumpall -h 192.168.2.2|psql
>
>
> an error occurs in that first section when the script attempts to set a
> role-specific search path ... because the schema named in the search
> path hasn't been created yet.
>
> Not sure if there is some different way I should be using these tools to
> accomplish this, or if there is a work-around, or if this feature needs
> improvement.
I tested this on 8.3.3:
test=> CREATE USER test;
CREATE ROLE
test=> ALTER USER test SET search_path = 'asdf';
NOTICE: schema "asdf" does not exist
ALTER ROLE
test=> SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+--------------------
postgres | 10 | t | t | t | ******** |
|
test | 16385 | f | f | f | ******** |
| {search_path=asdf}
(2 rows)
The ALTER SET command is performed; it only generates a NOTICE. Is
that the problem?
(FYI, you emailed the Postgres general _owner_; that is not the right
address for posting questions.)
--
Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
