3 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

The permanent settings for both databases and roles are stored in the pg_db_role_settings system cluster-wide table.

Only altered settings are present. If the search path was never modified for a database or a role, I guess it can be assumed to be "$user",public.

  • The value of the setting prior to any change, including at the cluster level (through the global configuration postgresql.conf) can be queried from the database with:

     SELECT boot_val FROM pg_settings WHERE name='search_path';
    
  • The value of the setting prior to any change within the session (through the SET command) can be queried from the database with:

     SELECT reset_val FROM pg_settings WHERE name='search_path';
    
  • When it's set a non-default value in postgresql.conf, it's not straightforward to obtain that value in SQL independently of the current session. pg_settings.boot_val won't do because it ignores changes in the configuration file, and pg_settings.reset_val won't either, because it's influenced by the database/user settings potentially set through ALTER USER/ALTER DATABASE. The simplest way for a DBA to get the value is to just look it up in postgresql.conf. Otherwise, see http://dba.stackexchange.com/questions/145280Reset search_path to the global, cluster default which covers this topic in detail.

The permanent settings for both databases and roles are stored in the pg_db_role_settings system cluster-wide table.

Only altered settings are present. If the search path was never modified for a database or a role, I guess it can be assumed to be "$user",public.

  • The value of the setting prior to any change, including at the cluster level (through the global configuration postgresql.conf) can be queried from the database with:

     SELECT boot_val FROM pg_settings WHERE name='search_path';
    
  • The value of the setting prior to any change within the session (through the SET command) can be queried from the database with:

     SELECT reset_val FROM pg_settings WHERE name='search_path';
    
  • When it's set a non-default value in postgresql.conf, it's not straightforward to obtain that value in SQL independently of the current session. pg_settings.boot_val won't do because it ignores changes in the configuration file, and pg_settings.reset_val won't either, because it's influenced by the database/user settings potentially set through ALTER USER/ALTER DATABASE. The simplest way for a DBA to get the value is to just look it up in postgresql.conf. Otherwise, see http://dba.stackexchange.com/questions/145280 which covers this topic in detail.

The permanent settings for both databases and roles are stored in the pg_db_role_settings system cluster-wide table.

Only altered settings are present. If the search path was never modified for a database or a role, I guess it can be assumed to be "$user",public.

  • The value of the setting prior to any change, including at the cluster level (through the global configuration postgresql.conf) can be queried from the database with:

     SELECT boot_val FROM pg_settings WHERE name='search_path';
    
  • The value of the setting prior to any change within the session (through the SET command) can be queried from the database with:

     SELECT reset_val FROM pg_settings WHERE name='search_path';
    
  • When it's set a non-default value in postgresql.conf, it's not straightforward to obtain that value in SQL independently of the current session. pg_settings.boot_val won't do because it ignores changes in the configuration file, and pg_settings.reset_val won't either, because it's influenced by the database/user settings potentially set through ALTER USER/ALTER DATABASE. The simplest way for a DBA to get the value is to just look it up in postgresql.conf. Otherwise, see Reset search_path to the global, cluster default which covers this topic in detail.

2 expand on boot_val and reset_val, add link to newer question following comments
source | link

The permanent settings for both databases and roles are stored in the pg_db_role_settings system cluster-wide table.

Only altered settings are present. If the search path was never modified for a database or a role, I guess it can be assumed to be "$user",public.

Or this could be queried from the database with:

SELECT boot_val FROM pg_settings WHERE name='search_path';
  • The value of the setting prior to any change, including at the cluster level (through the global configuration postgresql.conf) can be queried from the database with:

     SELECT boot_val FROM pg_settings WHERE name='search_path';
    
  • The value of the setting prior to any change within the session (through the SET command) can be queried from the database with:

     SELECT reset_val FROM pg_settings WHERE name='search_path';
    
  • When it's set a non-default value in postgresql.conf, it's not straightforward to obtain that value in SQL independently of the current session. pg_settings.boot_val won't do because it ignores changes in the configuration file, and pg_settings.reset_val won't either, because it's influenced by the database/user settings potentially set through ALTER USER/ALTER DATABASE. The simplest way for a DBA to get the value is to just look it up in postgresql.conf. Otherwise, see http://dba.stackexchange.com/questions/145280 which covers this topic in detail.

The permanent settings for both databases and roles are stored in the pg_db_role_settings system cluster-wide table.

Only altered settings are present. If the search path was never modified for a database or a role, I guess it can be assumed to be "$user",public.

Or this could be queried from the database with:

SELECT boot_val FROM pg_settings WHERE name='search_path';

The permanent settings for both databases and roles are stored in the pg_db_role_settings system cluster-wide table.

Only altered settings are present. If the search path was never modified for a database or a role, I guess it can be assumed to be "$user",public.

  • The value of the setting prior to any change, including at the cluster level (through the global configuration postgresql.conf) can be queried from the database with:

     SELECT boot_val FROM pg_settings WHERE name='search_path';
    
  • The value of the setting prior to any change within the session (through the SET command) can be queried from the database with:

     SELECT reset_val FROM pg_settings WHERE name='search_path';
    
  • When it's set a non-default value in postgresql.conf, it's not straightforward to obtain that value in SQL independently of the current session. pg_settings.boot_val won't do because it ignores changes in the configuration file, and pg_settings.reset_val won't either, because it's influenced by the database/user settings potentially set through ALTER USER/ALTER DATABASE. The simplest way for a DBA to get the value is to just look it up in postgresql.conf. Otherwise, see http://dba.stackexchange.com/questions/145280 which covers this topic in detail.

1
source | link

The permanent settings for both databases and roles are stored in the pg_db_role_settings system cluster-wide table.

Only altered settings are present. If the search path was never modified for a database or a role, I guess it can be assumed to be "$user",public.

Or this could be queried from the database with:

SELECT boot_val FROM pg_settings WHERE name='search_path';