Starting with Pg – where/how can I set configuration parameters?

Previously I wrote about locating config files.

The thing is – postgresql.conf is not the only place you can set your configuration in.

In here, I'll describe all the places that can be used, why do we even have more than one place, and finally – how to find out where given value comes from.

Continue reading Starting with Pg – where/how can I set configuration parameters?

Starting with Pg – where is the config?

Over the years I saw some people find themselves in position where they have to start dealing with PostgreSQL with minimal, or none, prior exposure. This leads to problems with seemingly easy tasks – how to change config? How to find stuff in logs?

So I decided to write some blogposts to be able to point such people to pre-made tutorials.

And I start today, with information on how to find PostgreSQL config files.

Continue reading Starting with Pg – where is the config?

Waiting for PostgreSQL 12 – Integrate recovery.conf into postgresql.conf

On 25th of November 2018, Peter Eisentraut committed patch:

Integrate recovery.conf into postgresql.conf
 
recovery.conf settings are now set in postgresql.conf (or other GUC
sources).  Currently, all the affected settings are PGC_POSTMASTER;
this could be refined in the future case by case.
 
Recovery is now initiated by a file recovery.signal.  Standby mode is
initiated by a file standby.signal.  The standby_mode setting is
gone.  If a recovery.conf file is found, an error is issued.
 
The trigger_file setting has been renamed to promote_trigger_file as
part of the move.
 
The documentation chapter "Recovery Configuration" has been integrated
into "Server Configuration".
 
pg_basebackup -R now appends settings to postgresql.auto.conf and
creates a standby.signal file.
 
Author: Fujii Masao
Author: Simon Riggs
Author: Abhijit Menon-Sen
Author: Sergei Kornilov
Discussion: https://www.postgresql.org/message-id/flat/@web3g.yandex.ru/

Continue reading Waiting for PostgreSQL 12 – Integrate recovery.conf into postgresql.conf

Waiting for 9.6 – Add new system view, pg_config

On 17th of February, Joe Conway committed patch:

Add new system view, pg_config
 
Move and refactor the underlying code for the pg_config client
application to src/common in support of sharing it with a new
system information SRF called pg_config() which makes the same
information available via SQL. Additionally wrap the SRF with a
new system view, as called pg_config.
 
Patch by me with extensive input and review by Michael Paquier
and additional review by Alvaro Herrera.

Continue reading Waiting for 9.6 – Add new system view, pg_config

Waiting for 9.5 – Replace checkpoint_segments with min_wal_size and max_wal_size.

On 23rd of February, Heikki Linnakangas committed patch:

Replace checkpoint_segments with min_wal_size and max_wal_size.
 
Instead of having a single knob (checkpoint_segments) that both triggers
checkpoints, and determines how many checkpoints to recycle, they are now
separate concerns. There is still an internal variable called
CheckpointSegments, which triggers checkpoints. But it no longer determines
how many segments to recycle at a checkpoint. That is now auto-tuned by
keeping a moving average of the distance between checkpoints (in bytes),
and trying to keep that many segments in reserve. The advantage of this is
that you can set max_wal_size very high, but the system won't actually
consume that much space if there isn't any need for it. The min_wal_size
sets a floor for that; you can effectively disable the auto-tuning behavior
by setting min_wal_size equal to max_wal_size.
 
The max_wal_size setting is now the actual target size of WAL at which a
new checkpoint is triggered, instead of the distance between checkpoints.
Previously, you could calculate the actual WAL usage with the formula
"(2 + checkpoint_completion_target) * checkpoint_segments + 1". With this
patch, you set the desired WAL usage with max_wal_size, and the system
calculates the appropriate CheckpointSegments with the reverse of that
formula. That's a lot more intuitive for administrators to set.
 
Reviewed by Amit Kapila and Venkata Balaji N.

Continue reading Waiting for 9.5 – Replace checkpoint_segments with min_wal_size and max_wal_size.

Waiting for 9.4 – Add ALTER SYSTEM command to edit the server configuration file.

On 18th of December, Tatsuo Ishii committed patch:

Add ALTER SYSTEM command to edit the server configuration file.
 
Patch contributed by Amit Kapila. Reviewed by Hari Babu, Masao Fujii,
Boszormenyi Zoltan, Andres Freund, Greg Smith and others.

On the next day, Fujii Masao committed patch:

Add tab completion for ALTER SYSTEM SET in psql.

Continue reading Waiting for 9.4 – Add ALTER SYSTEM command to edit the server configuration file.

Write Ahead Log + Understanding postgresql.conf: checkpoint_segments, checkpoint_timeout, checkpoint_warning

While there are some docs on it, I decided to write about it, in perhaps more accessible language – not as a developer, but as PostgreSQL user.

Some parts (quite large parts) were described in one of my earlier posts, but I'll try to concentrate on WAL itself, and show a bit more in here.

Continue reading Write Ahead Log + Understanding postgresql.conf: checkpoint_segments, checkpoint_timeout, checkpoint_warning

Understanding postgresql.conf : checkpoint_completion_target

Starting new blog series – explanation of various configuration parameters.

I will of course follow no schedule or order – if I'd had to – it would be my job, and in this way – it's fun.

First configuration parameter to write about is checkpoint_completion_target.

Continue reading Understanding postgresql.conf : checkpoint_completion_target