postgresql – Page 2 – select * from depesz;

Waiting for PostgreSQL 17 – Add support for incremental backup.

On 20th of December 2023, Robert Haas committed patch:

Add support for incremental backup.
 
To take an incremental backup, you use the new replication command
UPLOAD_MANIFEST to upload the manifest for the prior backup. This
prior backup could either be a full backup or another incremental
backup.  You then use BASE_BACKUP with the INCREMENTAL option to take
the backup.  pg_basebackup now has an --incremental=PATH_TO_MANIFEST
option to trigger this behavior.
 
An incremental backup is like a regular full backup except that
some relation files are replaced with files with names like
INCREMENTAL.${ORIGINAL_NAME}, and the backup_label file contains
additional lines identifying it as an incremental backup. The new
pg_combinebackup tool can be used to reconstruct a data directory
from a full backup and a series of incremental backups.
 
Patch by me.  Reviewed by Matthias van de Meent, Dilip Kumar, Jakub
Wartak, Peter Eisentraut, and Álvaro Herrera. Thanks especially to
Jakub for incredibly helpful and extensive testing.
 
Discussion: http://postgr.es/m/CA+TgmoYOYZfMCyOXFyC-P+-mdrZqm5pP2N7S-r0z3_402h9rsA@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Add support for incremental backup.

Proper support for I/O Timings on explain.depesz.com

Very long time ago (in PostgreSQL 9.2) we got ability to get, from EXPLAIN, information how long Pg spent on I/O operations. Reads and writes.

Over the years situation has changed.

Continue reading Proper support for I/O Timings on explain.depesz.com

Human/version sort in PostgreSQL – revisited

Couple of months ago I wrote how to do human sort in Pg by using arrays, and splitting string.

This works, but Matt mentioned in comments that it could be done with ICU collations.

So I looked into it …

Continue reading Human/version sort in PostgreSQL – revisited

Waiting for PostgreSQL 17 – Add support event triggers on authenticated login

On 16th of October 2023, Alexander Korotkov committed patch:

Add support event triggers on authenticated login
 
This commit introduces trigger on login event, allowing to fire some actions
right on the user connection.  This can be useful for logging or connection
check purposes as well as for some personalization of environment.  Usage
details are described in the documentation included, but shortly usage is
the same as for other triggers: create function returning event_trigger and
then create event trigger on login event.
 
In order to prevent the connection time overhead when there are no triggers
the commit introduces pg_database.dathasloginevt flag, which indicates database
has active login triggers.  This flag is set by CREATE/ALTER EVENT TRIGGER
command, and unset at connection time when no active triggers found.
 
Author: Konstantin Knizhnik, Mikhail Gribkov
Discussion: https://postgr.es/m/0d46d29f-4558-3af9-9c85-7774e14a7709%40postgrespro.ru
Reviewed-by: Pavel Stehule, Takayuki Tsunakawa, Greg Nancarrow, Ivan Panchenko
Reviewed-by: Daniel Gustafsson, Teodor Sigaev, Robert Haas, Andres Freund
Reviewed-by: Tom Lane, Andrey Sokolov, Zhihong Yu, Sergey Shinderuk
Reviewed-by: Gregory Stark, Nikita Malakhov, Ted Yu

Continue reading Waiting for PostgreSQL 17 – Add support event triggers on authenticated login

Waiting for PostgreSQL 17 – Allow \watch queries to stop on minimum rows returned

On 29th of August 2023, Daniel Gustafsson committed patch:

Allow \watch queries to stop on minimum rows returned
 
When running a repeat query with \watch in psql, it can be
helpful to be able to stop the watch process when the query
no longer returns the expected amount of rows.  An example
would be to watch for the presence of a certain event in
pg_stat_activity and stopping when the event is no longer
present, or to watch an index creation and stop when the
index is created.
 
This adds a min_rows=MIN parameter to \watch which can be
set to a non-negative integer, and the watch query will
stop executing when it returns less than MIN rows.
 
Author: Greg Sabino Mullane <htamfids@gmail.com>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Reviewed-by: Daniel Gustafsson <daniel@yesql.se>
Discussion: https://postgr.es/m/CAKAnmmKStATuddYxP71L+p0DHtp9Rvjze3XRoy0Dyw67VQ45UA@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Allow \watch queries to stop on minimum rows returned

Waiting for PostgreSQL 17 – Generate new LOG for “trust” connections under log_connections

On 26th of August 2023, Michael Paquier committed patch:

Generate new LOG for "trust" connections under log_connections
 
Adding an extra LOG for connections that have not set an authn ID, like
when the "trust" authentication method is used, is useful for audit
purposes.
 
A couple of TAP tests for SSL and authentication need to be tweaked to
adapt to this new LOG generated, as some scenarios expected no logs but
they now get a hit.
 
Reported-by: Shaun Thomas
Author: Jacob Champion
Reviewed-by: Robert Haas, Michael Paquier
Discussion: https://postgr.es/m/CAFdbL1N7-GF-ZXKaB3XuGA+CkSmnjFvqb8hgjMnDfd+uhL2u-A@mail.gmail.com

Continue reading Waiting for PostgreSQL 17 – Generate new LOG for “trust" connections under log_connections

Waiting for PostgreSQL 17 – Add to_bin() and to_oct().

On 23rd of August 2023, Nathan Bossart committed patch:

Add to_bin() and to_oct().
 
This commit introduces functions for converting numbers to their
equivalent binary and octal representations.  Also, the base
conversion code for these functions and to_hex() has been moved to
a common helper function.
 
Co-authored-by: Eric Radman
Reviewed-by: Ian Barwick, Dag Lem, Vignesh C, Tom Lane, Peter Eisentraut, Kirk Wolak, Vik Fearing, John Naylor, Dean Rasheed
Discussion: https://postgr.es/m/Y6IyTQQ/TsD5wnsH%40vm3.eradman.com

Continue reading Waiting for PostgreSQL 17 – Add to_bin() and to_oct().

Human/version sort in PostgreSQL

Ever been in situation where you had to sort data that is partially text, and partially numerical?

Like invoice numbers: prefix-9, prefix-10, prefix-11, other-5, other-20 ? Normally you can't do order by as you will get them in wrong order: other-20 ⇒ other-5 ⇒ prefix-10 ⇒ prefix-11 ⇒ prefix-9. Can something be done with it?

Continue reading Human/version sort in PostgreSQL

Why is my WAL directory so large?

This problem happened recently to couple of people on various Pg support channels, so I figured I can write a bit more about it, so that in future I have a place where I can refer people to 🙂

Continue reading Why is my WAL directory so large?

A tale about (incomplete) upgrade from PostgreSQL 12 to 14

This might not interest many of you, but I recently heard about at least two people that stumbled upon the problems I did, so I figured I can write about problems we discovered, and how we solved them (or not).

When we began our journey, the latest Pg was 14.x, that's why we're upgrading to 14, not 15. But I suspect upgrading to 15 wouldn't change much …

Continue reading A tale about (incomplete) upgrade from PostgreSQL 12 to 14

=$
|

Tag: postgresql