Is it safe to call pg_ctl reload while doing heavy writes?

I am running Postgres 8.4, and I would like to change the authentication method for a user. It's my understanding that I have to do this in pg_hba.conf, and then reload the configuration files with pg_ctl reload (or, in this case, service postgresql reload) for the settings to take effect.

However, I am also importing a ton of data from flat files using multiple processes.

Is it safe to do this?

Yes, it is safe, at least on 9.2, and I'm not aware of any major changes in that regard since 8.4. If absolute certainty is required you should initdb a test instance to experiment with before changing the production system.

In the following tests with Pg 9.3-pre, if PostgreSQL cannot re-read the config file due to a syntax error or other serious problem it'll skip the reload, eg:

LOG:  received SIGHUP, reloading configuration files
LOG:  syntax error in file "..../postgresql.conf" line 215, near token "!"
LOG:  configuration file "..../postgresql.conf" contains errors; no changes were applied

If an individual parameter is invalid it'll apply the other changes but skip that param:

LOG:  received SIGHUP, reloading configuration files
LOG:  -10 is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100)
LOG:  configuration file "..../postgresql.conf" contains errors; unaffected changes were applied

Even if you somehow managed to find a case where PostgreSQL would shut down or even crash, the worst that you'd have to do would be start it back up again and resume your writes from the last commited point. Not-yet-committed work is guaranteed to be rolled back and committed work is guaranteed to be applied to the database after disconnect, a database restart or even after a full system crash.

If your bulk writes don't periodically commit their work and write a local checkpoint, they probably should so you can resume them if they're interrupted. Reducing transaction lengths also makes some admin tasks easier and more efficient for the database.

StackExchange Database Administrators Q#41517, answer score: 6