FeedRSSLast fetchedNext fetched after
2ndQuadrant XML 17:01, domenica, 18 novembre 18:01, domenica, 18 novembre
Luca Ferrari XML 17:01, domenica, 18 novembre 18:01, domenica, 18 novembre
Planet PostgreSQL – Denis Gasparin XML 17:01, domenica, 18 novembre 18:01, domenica, 18 novembre
PostgreSQL – Blog di Diego Cinelli XML 17:01, domenica, 18 novembre 18:01, domenica, 18 novembre

lunedì, 24 settembre


pgenv get configuration! [Luca Ferrari]

I have already written about a very useful and powerful small pearl by theory: pgenv. Now the tool does support for user configuration!

pgenv get configuration!

I spent some time implementing a very rudimental approach to configuration for pgenv. The idea was simple: since the program is a single Bash script, the configuration can be done using a single file to source variables in.

But before this was possible, I had to do a little refactoring over here and there in order to make all the commands behave smooth across the configuration. And at least, it seems to work, with some parts that can be improved and implemented better (as always it is!). However, I designed from scratch to support every single version of PostgreSQL, that means configuration could be different depending on the specific version you are running. This allows, for example, to set particular flags for ancient versions, without having to get crazy when switching to more recent ones.

Now pgenv supports a config command that, in turn, support for several subcommands:

  • write to store the configuration in an hidden file named after the PostgreSQL version (e.g., .pgenv.10.5.conf);
  • edit just to launch you $EDITOR to manipulate the configuration;
  • delete to remove a configuration file;
  • show to dump the configuration.

The idea is simple: each time a new PostgreSQL version is built, a configuration file is created for such instance. You can then customize the file in order to make pgenv behave differently for that particular version of PostgreSQL. As an example, you can set different languages (e.g., PL/Perl) or different startup/stop modes. If the configuration file for a particular version is not found, a global configuration is loaded. If neither that is...

giovedì, 30 agosto


Managing Multiple PostgreSQL Installations with pgenv [Luca Ferrari]

pgenv is a shell script that allows you to quickly manage multiple PostgreSQL installations within the same host. It reminds somehow perlbrew (for Perl 5) and systems like that. In this post I briefly show how to use pgenv as well as I explain which changes I made to it.

Managing Multiple PostgreSQL Installations with pgenv

pgenv is another pearl from theory. It is a bash single script that allows you to download, build, start and stop (as well as nuke) several PostgreSQL installations within the same host.
It is worth noting that pgenv is not, at least now, an enterprise-level PostgreSQL management tool, rather an easy way to keep test instances clean and organized. It can be very useful to keep several clusters on which doing experiments, testing, and so on.

I first discovered pgenv reading this blog post by David, and I thought it was cool to have a single script to help me manage several environments. I must be honest, this is not the first tool like this I have seen for PostgreSQL, but somehow it caught my attention. I then cloned the repository and start using it. And since I’m curious, I read the source code. Well, ehm, bash? Ok, it is not my favourite shell anymore, but surely it can speed up development while shorting the code with respect to more portable shells.

pgenv works with a command-oriented interface: as in git or other developer-oriented tools you specify a command (e.g., build) and optionally a specific PostgreSQL version to apply the command to. pgenv works on a single cluster at time, by linking and unlinking the specific instance...

martedì, 14 agosto


PostgreSQL negative PID [Luca Ferrari]

PostgreSQL uses a simple trick to recognize if the server has started in single-user mode.

PostgreSQL negative PID

First of all, what is /single user mode/? It is a special way to connect directly, as a single user, to the PostgreSQL storage, that is the database. Since each conenction is server by a postgres process, single user mode means that a single process attached to the current terminal will run. The server will not accept any incoming connection, the user can interact with the server and its storage being sure he is the only user connected to the system.

How can PostgreSQL knows if it is running in single user mode?

Let’s start from the bottom: query a server about its status and see if it is in single user mode.

% sudo -u postgres pg_ctl -D /mnt/data1/pgdata11b3 status pg_ctl: single-user server is running (PID: 1291) 

So the above server is running in single user mode, with a PID of 1291. Let’s see how PostgreSQL has stored such information into the postmaster.pid text file:

% sudo -u postgres head -n 1 /mnt/data1/pgdata11b3/postmaster.pid -1291 

And it’s that simple! PostgreSQL stores a negative PID into the pid file in order to inform other tools that the server is running in single user mode. It is quite easy to test it with a normal server start-up, that will produce a regular (positive) PID:

% sudo -u postgres pg_ctl -D /mnt/data1/pgdata11b3 start waiting for server to start....2018-08-13 19:49:11.901 CEST [1335] LOG: listening on IPv6 address "::1", port 5432 2018-08-13 19:49:11.901 CEST [1335] LOG: listening on IPv4 address "", port 5432...

lunedì, 13 agosto


An example of PostgreSQL rules: updating pg_settings [Luca Ferrari]

Rules are a powerful mechanism by which PostgreSQL allows a statement to be transformed into another. And PostgreSQL itself does use rules in order to make your life easier.

An example of PostgreSQL rules: updating pg_settings

When asked for a quick and sweet example about rules I often answer with the pg_settings example.

The special view pg_settings offers a tabular decodification of the current cluster settings, in other words allows you to see postgresql.conf (and friends) as a table to run queries against.

But there is more than that: you can also issue UPDATE commands against such table and get the configuration updated on the fly (this does not mean applied, it depends on the parameter context). Internally, PostgreSQL uses a very simple rule to cascade updates to pg_settings into the run-time configuration. The rule can be found in the system_views.sql files inside the backend source code and is implemented as:

CREATE RULE pg_settings_u AS
    ON UPDATE TO pg_settings
    WHERE new.name = old.name DO
    SELECT set_config(old.name, new.setting, 'f');

It simply reads as: whenever there is an update keeping untouched the parameter name, invoke the special function set_config with the parameter name and its new value (the flag f means to keep changes not local to session). For more information about set_config see the function official documentation.

How cool!

martedì, 07 agosto


pgxnclient and beta version [Luca Ferrari]

pgxnclient is a wonderful cpan like tool for the PGXN extension network. Unlickily, the client cannot handle PostgreSQL beta version, so I submitted a really small patch to fix the issue.

pgxnclient and beta version

If you, like me, are addicted to terminal mode, you surely love a tool like pgxnclient that allows you to install extension into PostgreSQL from the command line, much like cpan (and friends) does for Perl.

A few days ago, I run into a problem: the `load** command cannot work against a PostgreSQL 11 beta 2 server. At first I reported it with a [ticket])https://github.com/dvarrazzo/pgxnclient/issues/29), but then curiosity hit me and I decided to give a look at very well written source code.

Warning: I’m not a Python developer, or better, I’m a Python-idiot! This means the work I’ve done, even if it seems it works, could be totally wrong, so reviews are welcome.

First I got to the regular expression used to parse a version() output:

m = re.match(r'\S+\s+(\d+)\.(\d+)(?:\.(\d+))?', data) 

where data is the output of a SELECT version();. Now, this works great for a version like 9.6.5 or 10.3, but does not work for 11beta2. Therefore, I decided to implement a two level regular expression check: at first search for a two or three numbers, and if it fails, search for two numbers separated by the beta text string.

 m = re.match(r'\S+\s+(\d+)\.(\d+)(?:\.(\d+))?', data) if m is None: m = re.match( r'\S+\s+(\d+)beta(\d+)', data ) is_beta...

mercoledì, 25 luglio


PostgreSQL 11 and PL/Java: it can work! [Luca Ferrari]

PL/Java is a wonderful piece of code that allows the definition of functions in Java directly within PostgreSQL. Unluckily, PostgreSQL 11 introduced a few changes that made PL/Java not compiling. But keep calm, experts are already working on this!

PostgreSQL 11 and PL/Java: it can work!


If you are in a rush and want to experiment with PL/Java 1.5.1 Beta and PostgreSQL 11, have a look at this pull request.

Trying to compile PL/Java against PG11b2

First of all, I’m used to serve PostgreSQL over FreeBSD, and this is not the optimal situation because a lot of code is written with Linux in mind and requires some adjustements when compiled/ported over other Unix implementations. PL/Java is an example: it compiles thru Apache Maven, that in turn seems to require GCC, that is not the default compiler on FreeBSD and … you get the point.

However, with a little work, it is possible to compile PL/Java even on FreeBSD (as you can imagine) and this is what I’ve done so far. But last monday, trying to compile it against PostgreSQL 11 beta 2, quickly resulted in frustation.

Luckily, and thanks to the great PL/Java, I found that due to a change in the PostgreSQL 11 GUC definition, things could have been adjusted to make it compiling. After one day, all of my PL/Java code seemed to be running fine with this simple workaround (and yes, I don’t have code that complex, so this does not mean the workaround is production safe!). I therefore decided to open a pull request about it.

Shame on me!
My pull request resulted in a mess, because I made it against the right branch and tag....

giovedì, 28 giugno


PostgreSQL Extended Statistics [Luca Ferrari]

PostgreSQL 10 allows users to define extended statistics to help the planner understand data dependencies.

PostgreSQL Extended Statistics

PostgreSQL 10 defines a set of extented statistics, mainly for intra-column dependencies and distinct values. New commands have been added to create and rop such extended statistics, and this post just covers the surface of this new feature directly as long as I was experimenting with that (and as usual, comments are welcome!).

A sample data set

Assume you have a table defined as follows:

CREATE TABLE expenses( pk int GENERATED ALWAYS AS IDENTITY, value money, day date, quarter int, year int, incoming boolean DEFAULT false, account text DEFAULT 'cash', PRIMARY KEY ( pk ), CHECK( value <> 0::money ), CHECK( quarter = EXTRACT( quarter FROM day ) ), CHECK( year = EXTRACT( year FROM day ) ) ); 

clearly there are a few dependencies:

  • column quarter depends on the value of column day;
  • column year depends on the value of day too,
  • column incoming is true when the value is greater than zero, false otherwise.

And since there are these dependencies, there must be something that ensures us the columns move together, so for instance there is a simple trigger as follows:


martedì, 26 giugno


Sqitch and Sqitchers [Luca Ferrari]

Sqitch has nothing particular to do with PostgreSQL, except it does support our beloved database!

Sqitch and Sqitchers

Long story short: if you are not using sqitch you should give it a look.

sqitch does not ties itself to only PostgreSQL, but it does support a lot of relational engines. However, if you want to know how to start using Sqitch over PostgreSQL go read the excellent Introduction to Sqitch on PostgreSQL.

I’ve already written about sqitch in the past (in italian).
sqitch is a great tool to manage database changes, mainly schema changes. The idea is to provide a git-like interface to manage changes, a change is made by three scripts appropriately written for the backend database:

  • a deploy script (what to do);
  • a revert script (how to undo);
  • a test script (how to check the deploy succeeded).

Introducing sqitchers.

Around a month ago, the sqitch creator, David E. Wheeler, created a GitHub Organization named sqitchers that now holds all the Sqitch related stuff including, obviously, the codebase for the project. At the same time, the Sqitch steering committee grown, and this is a good thing since this project quickly became an handy tool for database management.

In conclusion, sqitch is growing and getting more free every day. If you are curious about project history and explaination by its own creator David E. Wheeler, I suggest you listening to this (old) FLOSS Weekly podcast.

lunedì, 25 giugno


PostgreSQL: cannot alter type of a column used by a view or rule [Luca Ferrari]

How to cascade changes to a view over a table?

PostgreSQL: cannot alter type of a column used by a view or rule

In a lectur on PostgreSQL a participant came up with a puzzling question: how to cascade an ALTER COLUMN from a table to a view? There are two main approaches: using the power of DDL transactionable commands or alter the system catalog. In this post I explain how to do both.

Imagine you have a table and a view (either dynamic or materialized) built on top of it:

> CREATE TABLE t( pk smallint, t char(2) ); > CREATE VIEW vw_t AS SELECT * FROM t; 

and of course, both the view and the table reflect the same field structure

> \d t Table "public.t" Column | Type | Collation | Nullable | Default --------|--------------|-----------|----------|--------- pk | smallint | | | t | character(2) | | | > \d vw_t View "public.vw_t" Column | Type | Collation | Nullable | Default --------|--------------|-----------|----------|--------- pk | smallint | | | t | character(2) | | | 

What happens if the t table changes the structure? PostgreSQL does not allow this since there is a dependency from...

mercoledì, 20 giugno


PostgreSQL BSD Magazine course ebook [Luca Ferrari]

I’ve completed another short course on PostgreSQL, this time online with written material.

PostgreSQL BSD Magazine course ebook is now available!

I’ve completed the material for the BSD Magazine PostgreSQL intermediate course and a final ebook has been produced and is available for purchase: Improve your PostgreSQL Skills ebook.

If you missed the course and want to get the material, including exercises and solutions, you can buy the ebook form the BSD Magazine Web Site.

In the case you do purchase the ebook, please send me a feedback to allow me and the magazine to improve a possible future version.

And if you are guessing, all the written material has been produced using Emacs org-mode against a PostgreSQL 10.3 database.

venerdì, 04 maggio


plperl: invoking other subroutines [Luca Ferrari]

plperl does not allow direct sub invocation, so the only way is to execute a query.

plperl: invoking other subroutines

The official plperl documentation shows you a way to use a subref to invoke code shared across different plperl functions via the special global hash %_SHARED. While this is a good approach, it only works for code attached to the hash, that is a kind of closure (e.g., a dispatch table), and requires each time an initialization of the %_SHARED hash since plperl interpreters does not share nothing across sections.

The other way, always working, is to execute a query to perform the SELECT that will invoke the function. As an example:

CREATE OR REPLACE FUNCTION plperl_trampoline( fun_name text ) RETURNS TEXT AS $PERL$ my ( $fun_name ) = @_; return undef if ( ! $fun_name ); elog( DEBUG, "Calling [$fun_name]" ); my $result_set = spi_exec_query( "SELECT $fun_name() AS result;" ); return $result_set->{ rows }[ 0 ]->{ result }; $PERL$ LANGUAGE plperl; 

so that you can simply do:

> select plperl_trampoline( 'now' ); plperl_trampoline ------------------------------ 2018-05-04 13:09:17.11772+02 

The problem of this solution should be clear: it can work only for a...

PostgreSQL online course via BSD Magazine [Luca Ferrari]

I’m preparing another short course on PostgreSQL, this time online with written material.

PostgreSQL online course via BSD Magazine

BSD Magazine is delivering a PostgreSQL intermediate course with my own material.

I’ve been writing articles for BSD Magazine for a lot now, and many of them with the PostgreSQL subject, so according with the editors, we decided to create a full course with 5 modules and written material to allow readers to get a more detailed view on PostgreSQL capabilities.

The course will be performed by presenting attendees written material with examples, exercises and offering online support for doubts and questions. This course is an intermediate one, meaning it will not cover basic concepts like installation, SQL basic statements, psql and connection strings, and so on. The topic list is available on the course page.

I would like to thank BSD Magazine editors for the great opportunity to spread again the word of PostgreSQL!

giovedì, 19 aprile


Generating an italian 'Codice Fiscale' via plpgsql or plperl [Luca Ferrari]

PostgreSQL built-in plpgsql can be used to build stored procedure and, with a few tricks, to consume data and translate it into other forms. It is also possible to generate a so known codice fiscale, the italian string that represents the tax payer number based on the person’s name, birth date and place. This posts will show some concepts about how to generate the single pieces of the codice fiscale via plpgsql. And why not? Let’s compare it to a plperl implementation.

Generating an italian codice fiscale

In order to provide a quite complet example of usage of plpgsql for a course of mine, I developed a few functions to build up an italian codice fiscale (tax payer number). The idea is not to have a fully working implementation, rather to demonstrate usage of different operators and functions in plpgsql. And to compare its implementation with a plperl one.

The full rules for building up a codice fiscale are available here in italian. The code shown below is freely available on my GitHub PostgreSQL-related repository, and in particular there are two scripts:

In order to generate a full “codice fiscale” you need to extract some letters from the surname and the name, build a string representing both the date of birth and gender, a code representing the birth place and last comes a character that works as a checksum of all the previous parts.

In order to obtain the full result the following function can be used:

CREATE OR REPLACE FUNCTION cf.cf( surname text, name text, birth_date date

venerdì, 13 aprile


PostgreSQL 10 short course in Modena [Luca Ferrari]

The local Linux Users’ Group ConoscereLinux is delivering a six-part course on PostgreSQL 10. Guess who’s lecturing…

PostgreSQL 10 short course in Modena

I’m doing a short course on PostgreSQL, with particular regard to the 10 edition, in Modena. Thanks to the local Linux Users’ Group (LUG) /ConoscereLinux/, that provided all the infrastructure for the course, I wiil introduce attendees at basic SQL concepts and how PostgreSQL works.

The course schedule is available at the official course page, and the course will be based on 6 lessons (2 already done). Attendees can come with their own laptops, and lessons will be “live”: I will show concepts while explaining on my own laptop running PostgreSQL 10.1.

All the slides will be available for free on the course page, and are based on my work available on my github repository. The course will take place every week on Tuesday evening. So far the attendees are very interested in PostgreSQL and its technology, and are curious about evaluating all its features as a

I really have to thank the ConoscereLinux LUG, with particular regard to Luca and Massimiliano, for both giving me such chance and, most notably, for waiting me to be ready after my last eye-surgery, and driving me home!

giovedì, 25 gennaio


Replica Logica su PostgreSQL 10 [2ndQuadrant]

Logical Replication

Tra le novità introdotte da PostgreSQL 10 sicuramente una delle più importanti è l’implementazione della replica logica nel core di Postgres.

Molti di voi avranno già provato o almeno sentito parlare di pglogical, estensione di PostgreSQL che fornisce il supporto per la replica logica, che, grazie al logical decoding dei WAL (presente dalla 9.4), permette di replicare una selezione di tabelle da un database ad un altro.

In questo articolo vedremo come adesso questo è possibile con PostgreSQL 10 e quali sono le differenze con pglogical. Inoltre, viene assunto che il lettore abbia una preparazione di base sulle nozioni di amministrazione di PostgreSQL e di manipolazione dei file di configurazione e di sicurezza.



La replica logica si basa sull’architettura Pubblicazione/Sottoscrizione, si differenziano quindi due ruoli: un publisher e un subscriber. A differenza di quella fisica nella quale vengono replicate tutte le modifiche a livello binario, la replica logica permette di filtrare i cambiamenti da replicare scegliendo le tabelle e una combinazione di operazioni su di esse tra UPDATE, INSERT e/o DELETE. Per esempio è possibile replicare solo le UPDATE di una singola tabella da un server ad un altro a scopo di analisi e/o di aggregazione dei dati.

Come funziona

Sul publisher viene definita una “pubblicazione”, ovvero una selezione delle tabelle (o tutte) di un database a cui vengono associate le operazioni da replicare ai subscriber. Grazie alla decodifica logica dei WAL le operazioni sono ricostruite traducendo il codice binario e vengono selezionate solo quelle definite nella “pubblicazione” per essere trasmesse ai subscriber.


L’installazione di default predispone già impostate alcune delle opzioni necessarie a configurare la replica logica.

Per quanto riguarda il publisher:

  • max_replication_slots = 10
    Almeno uno per ogni subscriber più alcuni per l’inizializzazione delle tabelle.
  • max_wal_senders = 10
    Almeno uno per ogni replication_slot più quelli necessari per le repliche fisiche (es.: Barman).

Per quanto riguarda il subscriber:

  • max_logical_replication_workers = 4
    Uno per sottoscrizione, più alcuni da considerare per la sincronizzazione delle tabelle.
  • max_worker_processes = 10
    Almeno uno per ogni replication workers più uno.

[NOTA] Queste però non bastano per attivare la replica logica. Quello che manca, infatti, sono le informazioni all’interno del codice dei WAL necessarie per ricostruire le operazioni da filtrare e inviare ai subscriber. Per ottenere queste informazioni è necessario impostare sul publisher il parametro wal_level a logical e avviare (o riavviare) il servizio.

L’unico cambiamento che dobbiamo apportare è il seguente parametro sul publisher pippo-pg10:

  • wal_level = logical

I valori preimpostati sono più che sufficienti per questo test. Tuttavia, va tenuto conto che sono parametri che vengono valorizzati all’avvio del servizio e quindi ogni ulteriore cambiamento viene applicato solo dopo un nuovo riavvio. In questa ottica è consigliabile impostare valori adeguati al numero di pubblicazioni e sottoscrizioni previsti, prima di dover riavviare il servizio in produzione.


Consideriamo un caso reale di utilizzo della replica logica, prendendo di esempio un cluster con due server PostgreSQL 10:

  • pippo-pg10, ip:, publisher
  • paperino-pg10, ip:, subscriber

Sul server pippo-pg10 sono presenti le tabelle che devono essere replicate su un database del server paperino-pg10, precedentemente create con pgbench, un tool molto utile per misurare le prestazioni di PostgreSQL.

[root@pippo-pg10 ~]# sudo -iu postgres
-bash-4.2$ psql
psql (10beta4)
Type "help" for help.
postgres=# \c pgbench
You are now connected to database "pgbench" as user "postgres".
pgbench=# \d
               List of relations
 Schema |       Name       | Type  |   Owner
 public | pgbench_accounts | table | user_bench
 public | pgbench_branches | table | user_bench
 public | pgbench_history  | table | user_bench
 public | pgbench_tellers  | table | user_bench
(4 rows)

Creazione Tabelle

A causa delle restrizioni sulla replicazione non è possibile replicare la definizione di una tabella tanto meno di un database. Per questo occorre replicare manualmente la definizione sul server di sottoscrizione, per esempio partendo da un dump dello schema del database pgbench dal publisher:

-bash-4.2$ pg_dump -s -f pgbench.sql

Quindi creare l’utente e il database di destinazione nell’istanza PostgreSQL di paperino-pg10 (operazioni assenti nello script appena estratto):

[root@paperino-pg10 ~]# sudo -iu postgres
-bash-4.2$ createuser user_bench
-bash-4.2$ createdb pgbench -O user_bench

[NOTA] Non è necessario che l’utente e il database abbiano lo stesso nome, ma in questo caso si utilizza lo stesso nome per semplicità.

Adesso è possibile applicare lo script SQL sul server paperino-pg10:

-bash-4.2$ psql -d pgbench  < pgbench.sql

Creazione Ruolo Replicazione

Nell’istanza pippo-pg10 è necessario creare il ruolo dedicato alla replicazione e definire opportunamente la regola nel pg_hba.conf che permetta la connessione dal subscriber paperino-pg10.

pgbench=# create role replica superuser replication;

Creazione Pubblicazione

Una pubblicazione è definita come un set di cambiamenti su una o più tabelle appartenenti ad un solo database. Si possono creare più pubblicazioni in un solo database scegliendo di replicare una tra le operazioni di INSERT, UPDATE e DELETE o una loro combinazione. Ogni tabella può appartenere a più pubblicazioni e una singola pubblicazione può avere più sottoscrizioni.

In questo esempio assumiamo di voler replicare tutte le operazioni di modifica su tutte le tabelle di un database. Per creare questo tipo di pubblicazione è necessario essere superutenti (maggiori dettagli):

pgbench=# CREATE PUBLICATION pgbench_alltables FOR ALL TABLES;

Creazione Sottoscrizione

Una sottoscrizione viene definita su un nodo PostgreSQL, chiamato subscriber, con una connection string verso il server publisher e una lista di una o più pubblicazioni alle quali sottoscriversi. Il nome della sottoscrizione viene trasmesso al publisher come riconoscimento della connessione se non è definito il campo application_name nella stringa di connessione.

Per creare una sottoscrizione è necessario essere superutenti (maggiori dettagli).

Su paperino-pg10:

pgbench=# CREATE SUBSCRIPTION pgbench_rep CONNECTION 'dbname=pgbench host= user=replica' PUBLICATION pgbench_alltables;
NOTICE:  created replication slot "pgbench_rep" on publisher

[NOTA] Un replication slot viene creato per ogni sottoscrizione, più uno temporaneo per la sincronizzazione iniziale dei dati.

Adesso è possibile controllare che l’inizializzazione dei dati nel subscriber rispecchi la situazione nel publisher:

sul publisher pippo-pg10

postgres=# \c pgbench
You are now connected to database "pgbench" as user "postgres".
pgbench=# select * from pg_publication;
      pubname      | pubowner | puballtables | pubinsert | pubupdate | pubdelete
 pgbench_alltables |       10 | t            | t         | t         | t
(1 row)
pgbench=# select * from pgbench_branches limit 5;
 bid | bbalance | filler
   1 |        0 |
   2 |        0 |
   3 |        0 |
   4 |        0 |
   5 |        0 |
(5 rows)

pgbench=# select * from pgbench_tellers limit 5;
 tid | bid | tbalance | filler
   1 |   1 |        0 |
   2 |   1 |        0 |
   3 |   1 |        0 |
   4 |   1 |        0 |
   5 |   1 |        0 |
(5 rows)

e sul subscriber paperino-pg10

postgres=# \c pgbench
You are now connected to database "pgbench" as user "postgres".
pgbench=# select * from pg_subscription;
 subdbid |   subname   | subowner | subenabled |                   subconninfo                   | subslotname | subsynccommit |   subpublications
   16532 | pgbench_rep |       10 | t          | dbname=pgbench host= user=replica | pgbench_rep | off           | {pgbench_alltables}
(1 row)
pgbench=# select * from pgbench_branches limit 5;
 bid | bbalance | filler
   1 |        0 |
   2 |        0 |
   3 |        0 |
   4 |        0 |
   5 |        0 |
(5 rows)

pgbench=# select * from pgbench_tellers limit 5;
 tid | bid | tbalance | filler
   1 |   1 |        0 |
   2 |   1 |        0 |
   3 |   1 |        0 |
   4 |   1 |        0 |
   5 |   1 |        0 |
(5 rows)

Le tabelle sul server paperino-pg10 sono state inizializzate!

Test Replica

Il test consiste nel verificare che le modifiche apportate alle tabelle sul server pippo-pg10 siano effettivamente replicate su paperino-pg10.

Su pippo-pg10:

pgbench=# BEGIN;
pgbench=# SELECT * FROM pgbench_accounts WHERE aid = 1;
 aid | bid | abalance |                                        filler
   1 |   1 |        0 |
(1 row)

pgbench=# UPDATE pgbench_accounts SET filler = 'Jonny was here' WHERE aid = 1;
pgbench=# select * from pgbench_accounts where aid = 1;
 aid | bid | abalance |                                        filler
   1 |   1 |        0 | Jonny was here
(1 row)
pgbench=# COMMIT;

Una volta eseguito il COMMIT nel WAL viene scritta l’operazione UPDATE appena eseguita e replicata al subscriber:

pgbench=# select * from pgbench_accounts where aid = 1;
 aid | bid | abalance |                                        filler
   1 |   1 |        0 | Jonny was here
(1 row)

Attenzione! Le operazioni di UPDATE e DELETE sono replicate al subscriber solo sulle tabelle che possiedono una REPLICA IDENTITY, che di default è la chiave primaria. La tabella history di pgbench non sarebbe stata replicata in caso avessimo creato una pubblicazione limitata alle UPDATE e/o DELETE, perché non possiede una chiave primaria.

Considerazioni Conclusive

Replica Logica vs. Replica Fisica

La replica logica a differenza della replica fisica di un’istanza in hot-standby mode, non è da considerarsi un’architettura Master/Standby in quanto il subscriber è egli stesso un master, permettendo quindi le scritture su tutte le tabelle. Può essere comunque utilizzata in maniera simile usando utenti con permessi di sola lettura sul subscriber fino al prossimo switchover.

Per l’architettura multi-master, nella quale le modifiche vengono effettuate concorrentemente su più nodi, si rimanda alla lettura del sistema BDR.

Questa tecnologia permette di migrare da una major version di PostgreSQL ad un’altra, al contrario della replica fisica a causa dell’incompatibilità binaria tra differenti versioni.

Differenze con pglogical

L’implementazione nel core di PostgreSQL 10 della replica logica è un’eredità del progetto pglogical, che, essendo più maturo, possiede molte funzionalità in più. Tra le caratteristiche più importanti di pglogical vi è la possibilità di replicare le modifiche allo schema di un database e le DDL, quindi le definizioni delle tabelle. Inoltre è possibile configurare la risoluzione dei conflitti, mentre con PostgreSQL 10, in caso di conflitto la replica logica si interrompe ed è necessario l’intervento umano per la sua risoluzione. Un’altra funzionalità che rende pglogical molto potente è la possibilità di replicare specifiche righe e/o colonne di una tabella.


L’inizializzazione dei dati nelle tabelle del subscriber è una funzionalità molto utile, ma è possibile scegliere di non effettuarla durante la creazione della sottoscrizione.

Anche la replica logica può essere sottoposta alle stesse configurazioni di sincronizzazione delle transazioni tra standby e master, in modo da avere una perdita dati zero. Sono sufficienti gli stessi parametri della replica fisica per ottenere la sincronizzazione tra master e standby.

Nell’esempio di questo articolo, per attivare la replica logica sincrona è sufficiente configurare i seguenti parametri:

synchronous_commit = remote_apply
synchronous_standby_names = pgbench_rep

e riavviare PostgreSQL.

Il valore del parametro synchronous_standby_name corrisponde al nome della sottoscrizione. Maggiori dettagli sulla configurazione del parametro synchronous_standby_names.

Adesso mi rimane una domanda: qual è la differenza di velocità fra la replica fisica e quella logica? Potrebbe essere l’interessante argomento del mio prossimo articolo.

domenica, 01 ottobre


Ansible Pgsql Role [Planet PostgreSQL – Denis Gasparin]

rtshome.pgsql is a role available in Ansible Galaxy that provides four new ansible modules for Postgresql:

  • postgresql_table: ensure that a table is present (or absent) in database
  • postgresql_row: ensure that a row is present (or absent) in a table
  • postgresql_query: execute an arbitrary query in database and return results
  • postgresql_command: execute an arbitrary query in database

For additional docs look project’s wiki: https://github.com/rtshome/ansible_pgsql/wiki


$ ansible-galaxy install rtshome.pgsql


It requires psycopg2 installed as per Ansible’s PostgreSQL modules: http://docs.ansible.com/ansible/latest/list_of_database_modules.html#postgresql

Example Playbook

Sample playbook that:

  • creates the table config in acme database
  • ensures that a row is present in config table
  • performs a SELECT query on config and stores results in query var
  • execute a command removing all records in logs table
- hosts: servers
        - postgresql_table:
            database: acme
            name: config
            state: present
              - {
                name: key,
                type: text,
                null: False
              - {
                name: value,
                type: text,
                null: False
              - key
        - postgresql_row:
            database: acme
            table: config
              key: env
              value: production

        - postgresql_query:
            database: acme
            query: SELECT * FROM config WHERE env = %(env)s
              env: production 
          register: query

        - postgresql_command:
            database: acme
            command: "TRUNCATE logs"
         - rtshome.pgsql

sabato, 24 giugno


Ulteriori considerazioni sul planet PostgreSQL italiano [Luca Ferrari]


Qualche mese fa avevo espresso brevemente alcune considerazioni sul futuro dell'aggregatore di blog dell'associazione ITPUG, ovvero il Planet PostgreSQL Italiano.
La mia preoccupazione era dovuta al fatto che nei primi mesi dell'anno corrente non vi erano stati post relativi all'associazione e al mondo PostgreSQL in generale, e infatti facevo notare come solo io avessi pubblicato 13 post fra Gennaio e Aprile.

Ad oggi, giro di boa della metà anno, la situazione non è migliorata, e ancora una volta pare che il planet sia utilizzato solo per aggregare i miei post:

Ancora una volta sento la necessità di sollecitare l'associazione e il consiglio a valutare l'utilizzo di questo strumento di aggregazione e informazione, che risulta ormai evidentemente abbandonato a se stesso e in rapido declino di contenuti (a differenza del sempre aggiornato Planet PostgreSQL.

venerdì, 19 maggio


PostgreSQL 10 beta 1! [Luca Ferrari]


Ci siamo!
PostgreSQL 10 fa finalmente capolino nel mondo con il rilascio, ieri, della prima beta release.
Il download comprende pacchetti binari per le maggiori distribuzioni, oltre ovviamente alla possibilità
di compilare i sorgenti, anch'essi scaricabili come archivi.

lunedì, 10 aprile


Considerazioni sul planet italiano di PostgreSQL [Luca Ferrari]


Ho fatto caso che ormai sul planet ufficiale dell'associazione ITPUG, ovvero www.planetpostgresql.it, sto scrivendo
sporadicamente solo io. Questo secondo me è un campanello di allarme: io non sono certo migliore o piu' bravo di altri
soci e componenti dell'associazione, ma questa assenza dell'associazione dal planet indica che forse non si crede piu'
in questo strumento. Il fatto però è che nemmeno sul planet ufficiale si leggono post di ITPUG, e quindi non è tanto
la piattaforma italiana ad essere trascurata, ma il sistema di pubblicazione di notizie e articoli in generale.

Tornando al planet italiano, è facile verificare che su un periodo abbastanza ampio gli unici post
aggregati sono miei, e spesso risultano a loro volta dei link ad altre notizie ed articoli:

  1. 6 Gennaio 2017
  2. 13 Gennaio 2017
  3. 18 Gennaio 2017
  4. 27 Gennaio 2017
  5. 3 Febbraio 2017
  6. 21 Febbraio 2017
  7. 23 Febbraio 2017
  8. 24 Marzo 2017 (a) e (b)
  9. 2 Aprile 2017
  10. 5 Aprile 2017 (a) e (b)
  11. 8 Aprile 2017

Possibile che in un periodo di circa 3 mesi ITPUG non abbia avuto nessuna notizia da pubblicare?
Perfino in questi giorni, dove si richiede la regolarizzazione della quota per l'anno 2017 in vista
dell'imminente assemblea ordinaria, non vi sono notizie a riguardo.

Il consiglio che mi sento di dare al futuro consiglio è quello di prendere una decisione in merito al planet: se non lo si
vuole aggiornare allora tanto vale "spegnerlo", ma se lo si mantiene (come è tradizione anche nell'ambiente PostgreSQL e non solo)
allora lo si deve popolare periodicamente.

martedì, 21 marzo


SpeakerFight & PGDay.IT: è possibile? [Luca Ferrari]


Sono venuto a conoscenza per caso di un progetto interessante: SpeakerFight.
L'idea è abbastanza semplice, e l'implementazione mantiene la semplicità: si inviano dei contributi di talk (per conferenze ed eventi) e si lascia che le persone li votino, in un meccanismo stile "star" ben noto da altre piattaforme. I talk/contributi che hanno ricevuto il maggior numero di voti vengono selezionati per l'evento.

Un paio di giorni fa ho proposto di valutare questo meccasnimo nell'ambito del PGDay.IT. Da tempo sono sostenitore di una call-for-papers piu' aperta e con selezione maggiormente trasparente rispetto a quanto è avvenuto nelle ultime edizioni. Anzi, a dire il vero ho anche proposto piu' volte di fare un "speaker fight" del poveraccio addirittura per il keynote, proponendo di chiedere alla community chi fosse interessato a fare un keynote speech invece che andare su singolo invito diretto.

Ora sistemi come quello qui descritto hanno, ovviamente, i loro svantaggi: per esempio si potrebbe votare molto un talk tenuto da un perfetto incompetente che risulterebbe in uno speech di pessima qualità, trascurando magari talk meno "accattivanti" ma di sicuro successo ed impatto.
E forse alcune persone non vogliono selezionare di propria volontà i talk, quanto lasciare che siano gli organizzatori a "stupirli" con contenuti all'altezza di stimolare la curiosità e l'intelletto.
Tuttavia è difficile rimanere in un ambito o nell'altro se non si hanno dati alla mano circa il gradimento delle precedenti edizioni (questione che spesso ho sollevato).

Personalmente ritengo che aprire almeno una porzione del PGDay.IT ad un sistema di votazione diretta possa dare quella spinta ad autori e partecipanti per sentirsi maggiormente coinvolti e, soprattutto, per poter decidere il livello dei contenuti da visionare, garantendo quindi una maggiore partecipazione (almeno in teoria).
Se poi il tutto viene accompagnato anche da un...

martedì, 21 febbraio


PostgreSQL @ Python [Luca Ferrari]


Il legame tra Python e PostgreSQL appare sempre piu' forte.
Se da un lato ITPUG sarà anche per questa edizione media partner della conferenza italiana PyCon 8 , la community PostgreSQL viene citato nella sezione Community per l'internazionale PyCon 2017.

venerdì, 27 gennaio


Reboot ITPUG (Le mie dimissioni da ITPUG...parte 3) [Luca Ferrari]


Il consiglio di ITPUG è nuovamente in forze: dopo le mie dimissioni di circa un mese fa è stato eletto un nuovo socio per sostituirmi.
Questo permetterà ad ITPUG di proseguire le normali attività fino alle prossime elezioni di fine biennio, direi Aprile.
Presto sparirà il mio nome dalla lista dei consiglieri , immagino ci vorrà tempo perché si dia la giusta visione al mio sostituto.

A seguito delle mie dimissioni ho assistito ad un approccio abbastanza roccambolesco nella gestione del da farsi. Premesso che non ho intenzione di polemizzare e che quanto qui descritto rappresenta solo (e ovviamente) il mio punto di vista, ecco cosa è successo.

Anzitutto un po' di delusione perché le mie dimissioni non sono nemmeno state verbalizzate. Non è mania di protagonismo, ma un consiglio che vuoel una gestione trasparente deve quanto meno prendere atto di quanto sta accadendo al suo interno e "scolpirlo" nella pietra. Amarezza anche per la presidenza, che nonostante io avessi chiesto si pronunciasse, si è limitata a convocare l'assemblea straordinaria.

Stupore anche per la decisione, quasi unilaterale e a colpi di rima baciata (ossia in modo molto prolisso), di delegittimazione del consiglio stesso. Essendone io uscito si è deciso, applicando in maniera molto rigida e forse non regolare lo statuto, di delegittimare il consiglio stesso. A nulla è servito portare alla memoria dei consiglieri (alcuni storici quasi quanto me) e dei soci che già in passato, in analoga situazione e con dimissioni presentate, il consiglio aveva comunque proseguito la sua attività. E a nulla è servito ricordare che anche i soci pendenti potevano essere ammessi all'associazione (anche perché lo statuto stesso prevede 30 giorni per una decisione).

I soci stessi sono stati posti in discussione, utilizzando una...

mercoledì, 18 gennaio


Quanto ITPUG? (Le mie dimissioni da ITPUG parte 2) [Luca Ferrari]


Avendo marcato la fine della mia attività da consigliere con le dimissioni di circa un mese fà, ho deciso, quasi per curiosità, di cercare di quantificare il lavoro svolto da ITPUG e dal suo consiglio negli ultimi due mandati.
Si tratta di dati ovviamente indicativi, visto che strumenti diversi offrono opzioni di statistica differenti, ma possono essere utilizzati per un grezzo lavoro di analisi quantitativa.
E' bene sottolinearlo: si parla di attività quantitativa, non qualitativa!
Tuttavia l'attività quantitativa spesso indica e sottointende la presenza in associazione e la vitalità della stessa, da qui il mio interesse per questi semplici dati.

Ovviamente non sto svelando alcun segreto, questi dati sono comunque visibili e calcolabili da ogni consigliere e socio, con un po' di impegno e pazienza. Potrei anche aver commesso qualche errore di computazione, nel qual caso ogni correzione è ben accetta.

Considerando quindi la data del 30 aprile come termine di un biennio (e il relativo inizio del successivo), e sottolineando come il biennio 2015-2017 non sia ancora giunto al termine (e quindi i dati di tali biennio si riferiscono alla data attuale), si ha che:
  • biennio 2013-2015
  1.  301 commits
  2. 281 tickets
  3. 19 verbali riunioni di consiglio
  4. 108 thread in lista itpug-consiglio@
  5. 170 thread in lista itpug-soci@
  • biennio 2015-2017
  1. 103 commits
  2. 190 tickets
  3. 7 verbali riunioni di consiglio
  4. 160 thread in lista itpug-consiglio@ 
  5. 130 thread in lista itpug-soci@

L'attività del consiglio può essere quantificata con il numero di commits nel repository documentale, ovvero quanti documenti il consiglio ha ritenuto di inserire fra quelli ufficiali (fra questi, i verbali delle riunioni di consiglio), nonché dal numero ti tickets (ovvero di problematiche e task da affrontare). Come si può notare il valore di entrambi è drasticamente calato...

venerdì, 06 gennaio


Le mie dimissioni da ITPUG [Luca Ferrari]


Cari soci,
anzitutto buon anno (anche se in ritardo)!
E' con dispiacere e un po' di imbarazzo che vi informo che qualche giorno addietro, precisamente prima di Natale, ho presentato le mie dimissioni dal ruolo di consigliere.
La mia partecipazione in ITPUG si è ridotta per cause esterne, e ultimamente mi trovo anche in difficoltà nel rappresentare l'associazione.
In considerazione di tutto ciò, sperando nel meglio per l'associazione, e senza voler occupare un ruolo in modo "sterile", mi faccio quindi da parte.

Preciso, qualora necessario, che dalla data della presentazione delle mie dimissioni mi sto astenendo da esprimere ogni opinione in consiglio, se non espressamente richiesto.

Ho seguito e servito ITPUG dalla sua costituzione, la ritengo una esperienza interessante ed utile, grazie anche a voi.

Ad ogni modo non vi sbarazzerete facilmente di me, e ci ritroveremo a conferenze e sui vari canali informatici come al solito!


Questa è la lettera che ho inviato nella mailing list soci di ITPUG per confermare le mie dimissioni dal ruolo di consigliere.
Niente di drammatico, né tanto meno melodrammatico, ma spero con questo mio post di poter chiarire meglio la situazione e la mia decisione, che sono certo potrà essere interpretata negativamente, molto piu' di quanto non lo sia.

La parola chiave di tutto questo è coerenza.
Coerenza con me stesso, con gli impegni presi, con l'associazione che ho contribuito a creare e che ho risollevato.
Non posso piu' onorare gli impegni presi, perché vicissitudine esterne ad ITPUG mi tengono lontano dall'associazione, e di questo ne sono mortificato.
Quindi, per coerenza con l'impegno preso,...

mercoledì, 21 dicembre


PGDay.IT 2016 [2ndQuadrant]

img_6093-jpgAnche quest’anno si è svolto il PGDay, evento annuale organizzato dall’ITPUG, l’associazione italiana di PostgreSQL. Nonostante i membri dell’associazione siano sparsi per tutto il territorio nazionale, la decima edizione si è svolta nella città che ha dato i natali all’associazione ITPUG, Prato, la mia città.

Durante la manifestazione ho avuto il piacere di dare il mio contributo alla comunità, presentando un talk riguardo l’installazione di PostgreSQL su Network File System. Nel talk ho raccontato una mia esperienza professionale condivisa con il mio collega Giuseppe Broccolo, nel quale testavamo l’affidabilità di Postgres su NFS.

Condividere la mia esperienza su PostgreSQL con gli altri partecipanti all’evento è stato piacevole e costruttivo.

Ho potuto apprezzare i talk di Denis Gasparin sui diversi tool per l’upgrade tra major release di PostgreSQL oltre al keynote tenuto da Giorgio Roncolato, Direttore Sistemi Informativi ULSS 5 di Arzignano, sulla migrazione all’Open Source, dimostrando quanto i tempi siano maturi anche per le istituzioni, sempre più sensibili all’argomento Software Libero.

Come non menzionare i divertenti talk animati di Mladen Marinovic, coi quali è riuscito a trasmettere l’importanza del testing e della code review intrattenendo la platea e regalando momenti di ilarità.

Manifestazioni di questo tipo permettono la condivisione della conoscenza attraverso i dibattiti e le discussioni che nascono sia al termine di ogni talk che durante i momenti di networking.

L’evento mi ha dato l’oppurtunità di stringere nuove amicizie e di rafforzarne altre già confermate. La giornata si è conclusa con un aperitivo che ha portato ad uno scambio di opinioni, prolungando il tempo di permanenza di alcuni partecipanti.