Insights

Easy replication with Postgres 10

Author : Hari P S

Conceptual Overview:

Postgres replication is a builtin feature of PostgreSQL. Logical and streaming replications are available. In Logical, it replicates only a set of tables to other servers. Streaming replication methods are used to maintain a full copy of the entire data of a single cluster in another system.

The basis of streaming replication is the log shipping method of write ahead logs or WALs. In Log shipping any data that is updated on the primary server creates new log content in the WAL file. This can trigger the copy of the new wal content to another machine called the standby server, once the size of the wal file reaches the required size ( default is 16 MB and this can be changed using parameter with-wal-segsize in the server configuration). With streaming replication in PostgreSQL, high availability is simple to set up and maintain. When the master database becomes unusable, we can promote the replica database to become the master with small changes.

Physical Architecture

A standard setup will look like this, the master database accepting read /write connections from the application server and replica database receiving all WAL activity in near real-time, replaying all data change activities in server

Streaming Replication with PostgreSQL

Setting up streaming replication in PostgreSQL is very simple. Below steps explain how to setup a replication environment.

Configuration on Master Server

• 1. Initialize the database on the master node and create a role/user with replication privilege.

  “CREATE USER REPLICATION LOGIN ENCRYPTED PASSWORD’’;”

• 2. Configure replication configurations in master server “postgresql.conf” file.

  # Possible values are replica|minimal|logical
  wal_level = replica
  # required for pg_rewind capability when standby goes out of sync with master
  wal_log_hints = on
  # sets the maximum number of concurrent connections from the standby servers.
  max_wal_senders = 3
  # The below parameter is used to tell the master to keep the minimum number of
  # segments of WAL logs so that they are not deleted before standby consumes them.
  # each segment is 16MB
  wal_keep_segments = 8
  # The below parameter enables read only connection on the node when it is in
  # standby role. This is ignored when the server is running as master.
  hot_standby = on

• 3. Add replication entry in “pg_hba.conf” file to allow replication connections between the mater server and slave server
  # Allow replication connections from localhost,
  # by a user with the replication privilege.
  # TYPE DATABASE USER ADDRESS METHOD
  host replication repl_user IPaddress (CIDR) md5
• 4. Restart the postgresql service on the master server.

Configuration on Standby (replica)Server

• 1.Create the base backup of master node using pg_basebackup
  # sudo su postgres
  pg_basebackup –h Master server ip -D -p -U replication username –wal -method=stream
  . is location of postgresql database “/var/lib/postgresql/10/main”
  
• 2. Configure replication configurations in slave server “postgresql.conf” file.
  Hot_standby = on
• 3. Create a file recovery.conf file (/var/lib/postgresql/10/main/recovery.conf)
  # Specifies whether to start the server as a standby. In streaming replication,
  # this parameter must be set to on.
  standby_mode = ‘on’
  # Specifies a connection string which is used for the standby server to connect
  # with the primary/master.
  primary_conninfo = ‘host= port= user= password= application_name=”host_name”’
  # Specifies recovering to a particular timeline. The default is to recover along the
  # same timeline that was current when the base backup was taken.
  # Setting this to latest recovers to the latest timeline found
  # in the archive, which is useful in a standby server.

• 4. Start the postgresql service on the slave server.
  The slave server connects to master and start streaming logs and data update into slave server. Can be verified by running the sql statement “SELECT * FROM pg_stat_replication;”