Introducing SCRAM-SHA-256

Most people use passwords to connect to the database and manage security. Traditionally, people utilized md5. However, md5 is not safe anymore and therefore new authentication methods are needed. Starting with version 10.0, PostgreSQL supports SCRAM-SHA-256, which is far safer than the previous authentication method.

The old way of doing it is still supported. However, it is strongly recommended to move to SCRAM-SHA-256 in favor of md5.

Improving support for replication

The introduction of PostgreSQL also saw the introduction of logical replication, which has not been in the core before.

Understanding logical replication

Since version 8.0, PostgreSQL has supported binary replication (also often referred to as WAL-shipping). The ability to distribute transaction log ( WAL) has been improved steadily over the years.

With the introduction of PostgreSQL 10.0, a new feature has been added to PostgreSQL—Logical replication. How does it work? Logical replication allows you to publish a set of tables on one server and ask other servers to subscribe to the changes.

To publish data, the new CREATE PUBLICATION command has been introduced:

test=# \h CREATE PUBLICATION  
Command:  CREATE PUBLICATION 
Description: define a new publication 
Syntax: 
CREATE PUBLICATION name 
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...] 
      | FOR ALL TABLES ] 
    [ WITH ( publication_parameter [= value] [, ... ] ) ] 

Once the data has been published, remote servers can subscribe to these changes and receive information about what has happened to those published data sets:

test=# \h CREATE SUBSCRIPTION  
Command:  CREATE SUBSCRIPTION 
Description: define a new subscription 
Syntax: 
CREATE SUBSCRIPTION subscription_name 
    CONNECTION 'conninfo' 
    PUBLICATION publication_name [, ...] 
    [ WITH ( subscription_parameter [= value] [, ... ] ) ] 

CREATE SUBSCRIPTION is used on the slave side to attach to these changes. The beauty of the concept is that a server can publish one set of tables while subscribing to some other tables at the same time—there is no such thing as always master or always slave anymore. Logical replication allows you to flexibly distribute data.