postgresql streaming replication
Base information for both primary and standby
Create user dedicate for replication
Grant connection accessibility
After creation of user, you need to modify the
$PGCONF/pg_hba.conf file to explicitly grant connection accessibility for the user
According to Official Document, we need to set
hot_standby so that PostgreSQL will generate WAL that contains enough information for standby to reconstruct the status of running transactions from the WAL.
We also need to set
max_wal_senders to a positive number to accept connection from replicators. This is the number of sender process to send streaming data, so it is better to put a bigger number like 5 or 10.
Restart primary server
To enable streaming function, we need to restart Postgresql server, this will also reload the
pg_hba.conf so that remote replicator could connect to this server.
Now your primary server is ready to accept streaming replication connection.
For beginner who does not know about connecting to remote server, please refer to this article.
Restore base data files
To achieve this, it is better to use
pg_basebackup as it is more convenient than scp them manually.
After a while, you will find the primary server data folder is replicated to standby server.
The most important configuration comes, you need to create a
recovery.conf file in you
Up to now, the standby server could start replication if you restart it. But this standby server is not connectable, which means no connection allowed to this standby server, thus if you try to connect to it, you will get:
psql: FATAL: the database system is starting up FATAL: the database system is starting up
But be easy, even it is not connectable from you, it could replicate content from primary and also dispatch them to slavers depending on your configuration.
Hot standby server
So to allow connections to standby server rather than leave it unconnectable, you need to modify or add one configuration in standby server, refer to Official Document:
You can find this entry under
standby section. This attribute means to allow connection and run read-only queries while the server is in archive recovery or standby mode. In our case, in standby mode but accepts read only connection.
Such configuration could also be used as failover setting, which I will introduce later on.
Now try to create some tables or do some DML on Primary server and see if they will be synchronized to Standby server.
Those configuration above are the minimum for launch a streaming replication between primary and standby.
For more high availability information, please refer to failover, WAL archive, HA.
I will keep posting tutorial.