What’s new in PostgreSQL 10? Part 4 – Replication and Scaling

What’s new in PostgreSQL 10? Part 4 – Replication and Scaling

Agenda

  • Quorum Commit for Synchronous Replication
  • Connection “Failover” in libpq
  • Traceable Commit
  • Physical Replication
  • Temporary replication slot

Quorum Commit for Synchronous Replication

PostgreSQL 10 implements Quorum-based synchronous replication, which arbitrarily selects an instance to perform synchronous replication. The synchronous replication environment is set by the synchronous_standby_names configuration parameter as before.

synchronous_standby_names = ANY 2(node1,node2,node3);
synchronous_standby_names = FIRST 2(node1,node2);

FIRST was the previous behaviour, and the nodes priority is following the list order in order to get a quorum. ANY now means that any node in the list is now able to provide the required quorum. This will give extra flexibility to complex replication setups.

If ANY is specified for the configuration parameter synchronous_standby_names, “quorum” is output in the sync_state column of the pg_stat_replication.

postgres=> SELECT application_name,sync_state, sync_priority
FROM pg_stat_replication ;
client_addr | application_name | sync_state | sync_priority
------------------+------------+------------+---------------
10.0.0.191  | sub_points_1 | quorum | 1
10.0.0.192 | sub_points_2 | quorum | 1
(2 rows)

Connection “Failover” in libpq

In Postgres 10, a patch for the libpq library is commited. Now it is possible to specify several alternative connection addresses.

A new parameter called target_session_attrs is added, and it can use the following values:

  • “any”, meaning that any kind of servers can be accepted. This is as well the default value.
  • “read-write”, to disallow connections to read-only servers, hot standbys for example.

The strings using this parameter can have the following format:

psql -d "postgresql://10.0.0.190:5432/?target_session_attrs=read-write"
psql -d "postgresql://10.0.0.190:5432,10.0.0.191:5432/?target_session_attrs=any"

Traceable Commit

PostgreSQL 10 now supports finding out the status of a recent transaction for recovery after network connection loss or crash.

The new txid_status(...) function addresses this by allowing applications to look up a recent transaction-id to find out if it committed or aborted, either due to crash or explicit rollback.

The extra round-trip is now only necessary during recovery after a failure, and there’s no extra server-side workload from two-phase commit.

The application must obtain the transaction ID of any transaction it wants to look up using txid_current() or txid_current_if_assigned() before it sends the COMMIT.

It can avoid an extra round-trip for this query by:

  • Issuing a “multi-statement query”, e.g. INSERT INTO ...; SELECT txid_current(); if it doesn’t need the result of the prior query;
  • Combining it with another query, e.g. INSERT INTO ... RETURNING txid_current();
  • Using client driver support for batching queries to dispatch the txid_current() query along with other queries without waiting for a reply for each query.

In a future version PostgreSQL may automatically report the transaction ID when it is assigned to make this easier for applications.

Physical Replication

Improved performance of the replay of 2-phase commits.

Improved performance of replay when access exclusive locks are held on objects on the standby server. This can significantly improve performance in cases where temporary tables are being used.

Temporary replication slot

Replication slots can be used for building a streaming replication environment or for the pg_basebackup command. In PostgreSQL 10, temporary replication slots can now be created. A temporary replication slot is the same as a normal replication slot except that it is automatically deleted by session termination. To create a temporary replication slot, specify true for the third parameter of the pg_create_physical_replication_slot function or pg_create_logical_replication_slot function:

postgres=# SELECT pg_create_physical_replication_slot('temp_slot_1', true, true);
 pg_create_physical_replication_slot
-------------------------------------
 (temp_slot_1,2/C37338E8)
(1 row)

Along with this, “temporary” column has been added to the pg_replication_slots catalog:

postgres=# SELECT * FROM pg_replication_slots ;
-[ RECORD 1 ]-------+------------
slot_name | sub_points
plugin | pgoutput
slot_type | logical
datoid | 14035
database | postgres
temporary | f
active | t
active_pid | 21110
xmin |
catalog_xmin | 690
restart_lsn | 2/C3733990
confirmed_flush_lsn | 2/C37339C8
-[ RECORD 2 ]-------+------------
slot_name | temp_slot_1
plugin |
slot_type | physical
datoid |
database |
temporary | t
active | t
active_pid | 11147
xmin |
catalog_xmin |
restart_lsn | 2/C37338E8
confirmed_flush_lsn |

Sources

New in postgres 10

Traceable commit postgresql 10

Postgresql 2/Postgres 10 libpq read-wrte

Emil Shkolnik
No Comments

Post a Comment

Comment
Name
Email
Website

This site uses Akismet to reduce spam. Learn how your comment data is processed.