PostgreSQL 9.4 for administrators (part one)

Version 9.4 of PostgreSQL, soon to be released, has many innovations for administrators, including the introduction of support for logical replication, which is the first step towards the integration of multi-master replication into core PostgreSQL. In this two-part article we will show you the main new features for administrators; we begin with logical replication, and describe the following concepts:

• Physical replication slots
• WAL level “logical”
• Logical replication slots
• Logical decoding
• Replica identity

The development of these features is a direct result of the work carried out by 2ndQuadrant, in particular by Andres Freund, the main developer of Bi-Directional Replication (BDR). BDR is an open source solution of multi-master replication based on PostgreSQL, whose code is being progressively included in the core of PostgreSQL with the aim of becoming an integral part in the coming years.

In the next article we will talk about other new features dedicated to administrators.

Physical replication slots

Physical replication slots are a data-structure that keep track of the state of the standby and the WALs it needs, even when the standby is offline. In this way it is no longer necessary to estimate wal_keep_segments or have to configure continuous archiving. Therefore they are not only useful for physical replication, but are essential for logical replication where only a subset of the data are being replicated between servers.

More information can be found in an article by Craig Ringer Replication slots of PostgreSQL 9.4. – https://www.2ndquadrant.com/postgresql-9-4-slots/

Wal level “logical”

The wal_level configuration parameter can now be set to the value “logical”. Using this setting, the WAL files will have a size slightly larger than they have now with the value “hot_standby”, but will contain the information required to run logical decoding.

Logical replication slots

Once you have set wal_level = logical in the postgresql.conf, you can start using logical replication slots. These are similar in concept but, unlike the physical replication slots, these operate on a single data base, and stream changes to the replication server.

Logical decoding

Logical decoding uses the replication slots and decoding plugins to send the changes in the db and make them understandable to external agents. To see the changes, one can use the pg_logical_slot_get_changes and pg_logical_slot_peek_changes functions. The difference between the two functions is that the first consumes changes in the queue and the second reads them only, without removing them. The output of the function depends on the plugin used to create the slot. Three plugins were developed:

• test_decoding – the default plugin;
• wal2json – shows the changes in JSON format ;
• decoder_raw – reconstructs the query that has applied the change.

Replica identity

REPLICA IDENTITY is a new table-level parameter that can be used to control the information written to WAL to identify tuple in which data has changed.  There are 4 possible values:

• DEFAULT:  writes the old value of the primary key if it has been changed.
• USING INDEX: writes the values of the index defined with this option. The index needs to be unique, not partial and not deferrable, and its columns must be NOT NULL.
• FULL: all the column values are written in the WALs – useful if there is no primary key on the table.
• NOTHING: does not write information on the old record. This is the default for the system tables.

Using the functions and the decoding plugins, you can write your own consumers and remove your database dependencies on trigger-based replication solutions bulkier and slower.

The blog of Michael Paquier contains an example of SQL code showing the use of logical replication.

Conclusion

In the next instalment, we will take a look at the other main new PostgreSQL 9.4 features in the field of Operations, including pg_prewarm , easier tablespace management, time delayed standbys, WAL management … See you next time!