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