Change configuration parameters from command-line or programatically

Question

How can I change settings in pg_hba.conf and postgresql.conf either from the command-line or programatically (especially from fabric or fabtools)?

I already found set_config, but that does not seem to work for parameters which require a server restart. The parameters to change are listen_addresses in postgresql.conf and a new line in pg_hba.conf, so connections from our sub-network will be accepted.

This is needed to write deployment scripts using fabric. It is not an option to copy template-files which then override the existing *.conf files, because the database server might be shared with other applications which bring their own configuration parameters. Thus, the existing configuration must be altered, not replaced.

postgresql.org/docs/current/static/sql-altersystem.html

user330315
– user330315

2015-09-28 08:49:38 +00:00
Commented Sep 28, 2015 at 8:49 — user330315
– user330315, Commented Sep 28, 2015 at 8:49
That solved the listen_addresses part, thanks!

schreon
– schreon

2015-09-28 09:02:06 +00:00
Commented Sep 28, 2015 at 9:02 — schreon
– schreon, Commented Sep 28, 2015 at 9:02

schreon · Accepted Answer · 2015-09-28 09:44:45Z

Here is the currently working solution, incorporating the hint from a_horse_with_no_name. I paste a snippet from our fabfile.py (it uses require from fabtools, and it runs against Ubuntu):

db_name = env.variables['DB_NAME']
db_user = env.variables['DB_USER']
db_pass = env.variables['DB_PASSWORD']

# Require a PostgreSQL server.
require.postgres.server(version="9.4")
require.postgres.user(db_user, db_pass)
require.postgres.database(db_name, db_user)

# Listen on all addresses - use firewall to block inadequate access.
sudo(''' psql -c "ALTER SYSTEM SET listen_addresses='*';" ''', user='postgres')

# Download the remote pg_hba.conf to a temp file
tmp = tempfile.NamedTemporaryFile()
with open(tmp.name, "w") as f:
    get("/etc/postgresql/9.4/main/pg_hba.conf", f, use_sudo=True)

# Define the necessary line in pg_hba.conf.
hba_line = "host    all     all     {DB_ACCEPT_IP}/0   md5".format(**env.variables)

# Search the hba_line in the existing pg_hba.conf
with open(tmp.name, "ra") as f:
    for line in f:
        if hba_line in line:
           found = True
           break
    else:
        found = False

# If it does not exist, append it and upload the modified pg_hba.conf to the remote machine.
if not found:
    with open(tmp.name, "a") as f:
        f.write(hba_line)
    put(f.name, "/etc/postgresql/9.4/main/pg_hba.conf", use_sudo=True)

# Restart the postgresql service, so the changes take effect.
sudo("service postgresql restart")

The aspect I don't like with this solution is that if I change DB_ACCEPT_IP, this will just append a new line and not remove the old one. I am sure a cleaner solution is possible.

Collectives™ on Stack Overflow

Change configuration parameters from command-line or programatically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related