Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.

On 3rd of April 2019, Tomas Vondra committed patch:

Add SETTINGS option to EXPLAIN, to print modified settings.
 
 
Query planning is affected by a number of configuration options, and it
may be crucial to know which of those options were set to non-default
values.  With this patch you can say EXPLAIN (SETTINGS ON) to include
that information in the query plan.  Only options affecting planning,
with values different from the built-in default are printed.
 
This patch also adds auto_explain.log_settings option, providing the
same capability in auto_explain module.
 
Author: Tomas Vondra
 
Discussion: https://postgr.es/m/e1791b4c-df9c-be02-edc5-7c8874944be0@2ndquadrant.com

Continue reading Waiting for PostgreSQL 12 – Add SETTINGS option to EXPLAIN, to print modified settings.

Waiting for PostgreSQL 12 – Add log_statement_sample_rate parameter

On 29th of November 2018, Alvaro Herrera committed patch:

Add log_statement_sample_rate parameter
 
This allows to set a lower log_min_duration_statement value without
incurring excessive log traffic (which reduces performance).  This can
be useful to analyze workloads with lots of short queries.
 
Author: Adrien Nayrat
 
Discussion: https://postgr.es/m/-ee1e-db9f-fa97-@anayrat.info

Continue reading Waiting for PostgreSQL 12 – Add log_statement_sample_rate parameter

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 PostgreSQL 10 – Support SCRAM-SHA-256 authentication (RFC 5802 and 7677).

On 7th of March 2017, Heikki Linnakangas committed patch:

Support SCRAM-SHA-256 authentication (RFC 5802 and 7677).
 
 
This introduces a new generic SASL authentication method, similar to the
GSS and SSPI methods. The server first tells the client which SASL
authentication mechanism to use, and then the mechanism-specific SASL
messages are exchanged in AuthenticationSASLcontinue and PasswordMessage
messages. Only SCRAM-SHA-256 is supported at the moment, but this allows
adding more SASL mechanisms in the future, without changing the overall
protocol.
 
Support for channel binding, aka SCRAM-SHA-256-PLUS is left for later.
 
The SASLPrep algorithm, for pre-processing the password, is not yet
implemented. That could cause trouble, if you use a password with
non-ASCII characters, and a client library that does implement SASLprep.
That will hopefully be added later.
 
Authorization identities, as specified in the SCRAM-SHA-256 specification,
are ignored. SET SESSION AUTHORIZATION provides more or less the same
functionality, anyway.
 
If a user doesn't exist, perform a "mock" authentication, by constructing
an authentic-looking challenge on the fly. The challenge is derived from
a new system-wide random value, "mock authentication nonce", which is
created at initdb, and stored in the control file. We go through these
motions, in order to not give away the information on whether the user
exists, to unauthenticated users.
 
Bumps PG_CONTROL_VERSION, because of the new field in control file.
 
Patch by Michael Paquier and Heikki Linnakangas, reviewed at different
stages by Robert Haas, Stephen Frost, David Steele, Aleksander Alekseev,
and many others.
 
Discussion: https://www.postgresql.org/message-id/CAB7nPqRbR3GmFYdedCAhzukfKrgBLTLtMvENOmPrVWREsZkF8g%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CAB7nPqSMXU35g%3DW9X74HVeQp0uvgJxvYOuA4A-A3M%2B0wfEBv-w%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/.6080106@iki.fi

Continue reading Waiting for PostgreSQL 10 – Support SCRAM-SHA-256 authentication (RFC 5802 and 7677).

Waiting for 9.5 – Support ALTER SYSTEM RESET command.

On 2nd of September, Fujii Masao committed patch:

Support ALTER SYSTEM RESET command.
 
This patch allows us to execute ALTER SYSTEM RESET command to
remove the configuration entry from postgresql.auto.conf.
 
Vik Fearing, reviewed by Amit Kapila and me.

Continue reading Waiting for 9.5 – Support ALTER SYSTEM RESET command.

Waiting for 9.5 – Add cluster_name GUC which is included in process titles if set.

On 29th of June, Andres Freund committed patch:

Add cluster_name GUC which is included in process titles if set.
 
When running several postgres clusters on one OS instance it's often
inconveniently hard to identify which "postgres" process belongs to
which postgres instance.
 
Add the cluster_name GUC, whose value will be included as part of the
process titles if set. With that processes can more easily identified
using tools like 'ps'.
 
To avoid problems with encoding mismatches between postgresql.conf,
consoles, and individual databases replace non-ASCII chars in the name
with question marks. The length is limited to NAMEDATALEN to make it
less likely to truncate important information at the end of the
status.
 
Thomas Munro, with some adjustments by me and review by a host of people.

Continue reading Waiting for 9.5 – Add cluster_name GUC which is included in process titles if set.

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