How can I reset it back to whatever the default that a pristine,
unaltered database on this cluster would have?
You can read
pg_settings, but that's actually the compiled-in factory default, not the setting in
WHERE name LIKE 'search_path';
You aren't overlooking the simple
To just reset the setting (remove it), you can simply:
ALTER DATABASE my_db RESET search_path;
But that doesn't necessarily give you what "a pristine, unaltered database on this cluster would have". It just removes the custom setting for the database. There are several ways to set the search path:
And it does not change the
search_path of your current session. The effect (if any) is visible in your next session.
You could get the current setting from a connection to
template1, like @Abelisto suggested. But you probably want to stay within your current session. You could use dblink for that:
SELECT * FROM dblink('hostaddr=127.0.0.1 dbname=template1 user=postgres password=you_wish'
, $$SELECT boot_val, source FROM pg_settings
WHERE name LIKE 'search_path'$$
) AS t(search_path text, source text);
search_path | source
"$user",public | default
This has a couple of flaws:
You need the additional module
dblink installed. That's simple:
You need the privilege to connect to
template1. And you need to supply the user pw.
template1 the setting might stem from settings to the role or database (or another instance) as well (even though they probably don't). To make sure check the columns
source. If it says 'default', you got it. If it says 'database' or 'user' you are back to square 1 ...
Read actual setting in
pg_read_file(), but this is also subject to some important restrictions. The manual:
Use of these functions is restricted to superusers.
Only files within the database cluster directory and the
log_directory can be accessed.
Bold emphasis mine.
If your config files are somewhere else (like in default installations on Debian and friends) you would have to create a symbolic link in the file system in your data or log directory to your actual config file.
SELECT substring(txt, $$\nsearch_path\s*=\s*'([^']+)'$$) AS search_path
FROM pg_read_file((SELECT setting FROM pg_settings WHERE name = 'config_file')
, 0, 10000000) AS txt; -- arbitrary 10 MB max.
Note how I fetch the path to the config file dynamically with:
SELECT setting FROM pg_settings WHERE name = 'config_file'
Then I use a regular expression with substring() to extract the setting:
That should work, I didn't spend much time to make the regexp bullet-proof, though. Might be fooled by a commented setting that matches first or something. You might want to test some more.
Finally, since Postgres 9.4, there is also the
ALTER SYSTEM command to override settings in a
postgresql.auto.conf file. You'll have to check that, too.