Say I permanently alter my database's search_path
like this:
ALTER DATABASE my_db SET search_path TO "$user",public,other_schema;
How can I reset it back to whatever the default that a pristine, unaltered database on this cluster would have?
This is usually "$user",public
, but that can be changed from the configuration file. Having a way to read the value of parameters that come from the configuration file would work (so I could build a dynamic query at worst), but I haven't had any luck finding anything of the sort.
Best Answer
Factory defaults
You can read
boot_val
frompg_settings
, but that's actually the compiled-in factory default, not the setting inpostgresql.conf
.You aren't overlooking the simple
RESET
?To just reset the setting (remove it), you can simply:
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.Related:
Connect to
template1
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: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.Even in
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 columnssource
. If it says 'default', you got it. If it says 'database' or 'user' you are back to square 1 ...Read actual setting in
postgresql.conf
.. using
pg_read_file()
, but this is also subject to some important restrictions. The manual:And:
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.
Note how I fetch the path to the config file dynamically with:
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 apostgresql.auto.conf
file. You'll have to check that, too.