Streaming replication is asynchronous by default. This means that if a user commits a transaction on the master, it gets the confirmation of the commit immediately, and the replication will happen only afterward. This means that it is still possible that a transaction that was finished on the master is not replicated to the standby in case the master crashes right after commit and the WAL records were not sent yet.
When high availability is a requirement in a sense that no data loss is acceptable, streaming replication can be set to synchronous mode.
To enable it on the master, in the file postgresql.conf, the configuration parameter synchronous_standby_names should be set to the name identifying the standby server, for example, synchronous_standby_names = 'standby1'.
Then on the slave, the same name should be used in the connection string in the recovery.conf file, as follows:
primary_conninfo = 'host=172.16.0.1 port=5432 user=streamer password=secret application_name=standby1'
After this is done, the master server will wait for the standby confirming that it has received and processed every WAL record before confirming the commit request. This will make commits slightly slower of course. In case the standby server is disconnected, all the transactions on the master will be blocked. It will not fail though, it will only wait until the is standby connected again. The read only queries will work normally.
The benefit of the synchronous replication is that it is guaranteed that if a transaction is finished and the commit command returned, the data is replicated to the standby server. The drawback is the performance overhead and the dependency of the master on the standby.
There is a Docker composition in the attached media that implements a streaming replication set up. To try it on Linux, change directory to streaming_replication and bring up the composition executing the command docker-compose up as follows:
user@host:~/learning_postgresql/scalability/streaming_replication$ docker-compose up
Creating network "streamingreplication_default" with the default driver
Creating streamingreplication_master_1
Creating streamingreplication_standby_1
Attaching to streamingreplication_master_1, streamingreplication_standby_1
...
Now there are two database instances running in two Docker containers named master and standby. Synchronous replication is already enabled. The database on master is empty. The log output from both servers will be printed in that terminal window. Open another terminal window, change directory to streaming_replication, connect to the container master, start psql console, and create the database car_portal:
user@host:~/learning_postgresql/scalability/streaming_replication$ docker-compose exec master bash
root@master:/# psql -h localhost -U postgres
psql (10.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# i schema.sql
...
car_portal=> i data.sql
...
Now the database is created on the master and it is already replicated to the standby. Let's check. Exit the shell session in the container master, connect to the container standby, start psql, and run some queries:
user@host:~/learning_postgresql/scalability/streaming_replication$ docker-compose exec standby bash
root@standby:/# psql -h localhost -U car_portal_app car_portal
psql (10.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
car_portal=> SELECT count(*) FROM car;
count
-------
229
(1 row)
car_portal=> UPDATE car set car_id = 0;
ERROR: cannot execute UPDATE in a read-only transaction
The data is replicated but it is read only; the server that is running in recovery mode does not allow any changes.
More on this topic can be found in the PostgreSQL documentation at https://www.postgresql.org/docs/current/static/high-availability.html.