How to edit `postgresql.conf` with pgAdmin 4?

by Basil Bourque   Last Updated October 22, 2018 05:06 AM

Same Question as this: How to edit postgresql.conf with pgAdmin 3? but updated for the completely new (total re-write) pgAdmin 4.

➥ How does one edit the configuration files with the pgAdmin 4 app?

  • postgresql.conf
  • pg_hba.conf

I added the adminpack extension as discussed here. And I restarted pgAdmin 4. Yet I cannot identify any menu item or other interface item for editing this files.



Answers 1


Workaround: ALTER SYSTEM

As a partial workaround, Postgres 9.4 added a feature to dynamically set the properties seen in postgresql.conf: ALTER SYSTEM commands.

ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

ALTER SYSTEM RESET configuration_parameter
ALTER SYSTEM RESET ALL  -- Clears all the settings set via `ALTER SYSTEM SET`. 

This provides a convenient alternative to manually editing the postgresql.conf file.

Example:

ALTER SYSTEM SET wal_level = replica;

How does it work?

  • A postgresql.auto.conf file is written by the ALTER SYSTEM SET command.
  • This file and postgresql.conf are both read, so you can combine these approaches if desired. The auto file trumps the hand-edited file for coinciding properties.
  • For reliability, a postgresql.auto.conf.temp file is created to rollback to the original state in case of error.

Caveats:

  • Settings do not take effect immediately. Read the doc.
  • Must be superuser to execute.
  • Cannot be called in a transaction block or function.
  • Cannot set data_directory.
  • Obviously misuse can be dangerous to your database system, so be careful.

Tip: To read the value of the settings, see this Question, Query for all the Postgres configuration parameters‘ current values?

Workaround: Use command-line editor as postgres user

You can run a simple text-editor like nano from the command-line run with the privileges of the postgres user. Open the pg_hba.conf file, edit, and save.

For example, on a Unix/POSIX-oriented OS such as BSD or macOS:

sudo -u postgres nano /Library/PostgreSQL/11/data/pg_hba.conf

For more info, see the Question, Switch user to 'postgres' user on macOS results in “su: Sorry” error

Basil Bourque
Basil Bourque
October 23, 2018 18:25 PM

Related Questions



save/export PGAdmin server list configuration settings

Updated December 14, 2017 08:06 AM


How to connect heroku database with pgadmin 4?

Updated July 13, 2018 07:06 AM