searchpath reverts to default after each server restart
When i run the command
SET search_path TO custom,idsystems, clientdata, configs, replication,
structure, archive;
then when I run
SHOW search_path;
it does show those schemas as the search_path. however when i restart the
postgresql service, the search_path has reverted to $User, public.
when i put the SET search_path function in the postgresql.conf file (as
below) the service wouldn't restart. i think because it doesn't like the
"public" schema (which i don't use).
#[postgresql.conf file:]
# the postgresql service wouldn't start with this setting.
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
--- # - Statement Behavior -
search_path =
custom,idsystems,clientdata,configs,replication,structure,archive,public;
#schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only
On Wed, Sep 15, 2010 at 4:12 PM, Jonathan Brinkman
<jonathanbrinkman@yahoo.com> wrote:
When i run the command
SET search_path TO custom,idsystems, clientdata, configs, replication,
structure, archive;
then when I run
SHOW search_path;
it does show those schemas as the search_path. however when i restart the
postgresql service, the search_path has reverted to $User, public.
when i put the SET search_path function in the postgresql.conf file (as
below) the service wouldn't restart. i think because it doesn't like the
"public" schema (which i don't use).#[postgresql.conf file:]
# the postgresql service wouldn't start with this setting.# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
---# - Statement Behavior -
search_path =
custom,idsystems,clientdata,configs,replication,structure,archive,public;
the path needs to be single quoted.
merlin
alter database "MYDATABASE" SET search_path TO custom, clientdata, configs,
replication, structure, archive;
that seems to fix it.
Thank you!
-----Original Message-----
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Wednesday, September 15, 2010 4:40 PM
To: Jonathan Brinkman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] searchpath reverts to default after each server
restart
On Wed, Sep 15, 2010 at 4:12 PM, Jonathan Brinkman
<jonathanbrinkman@yahoo.com> wrote:
When i run the command
SET search_path TO custom,idsystems, clientdata, configs, replication,
structure, archive;
then when I run
SHOW search_path;
it does show those schemas as the search_path. however when i restart the
postgresql service, the search_path has reverted to $User, public.
when i put the SET search_path function in the postgresql.conf file (as
below) the service wouldn't restart. i think because it doesn't like the
"public" schema (which i don't use).#[postgresql.conf file:]
# the postgresql service wouldn't start with this setting.# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
---
# - Statement Behavior -
search_path =
custom,idsystems,clientdata,configs,replication,structure,archive,public;
the path needs to be single quoted.
merlin
Greetings
I have a customized search_path for my database. When I backup (pgdump) the
database and restore it to another server, the search_path must be reset,
since it reverts to $User, public upon restore.
Why is the search_path info not being retained in the backup?
Thank you!
Jonathan