Use pg_rewind to avoid rebuilding standby from scratch after promote

Some time it happens like Master is up & running but Standby gets promoted due to false failover under this situation first question raises is "how to rebuild the standby"?

Now, let's rewind the promoted cluster to make old standby as new standby of current Master.


pg_rewind: pg_rewind is a tool for synchronizing a PostgreSQL cluster with another copy of the same cluster after the clusters' timelines have diverged. A typical scenario is to bring an old master server back online after failover as a standby that follows the new master.


pg_rewind requires that the target server either has the wal_log_hints option enabled in postgresql.conf or data checksums enabled when the cluster was initialized with initdb.


How it works:

copies file system-level changes from the source cluster to the target cluster:

It will check the divorced point of Master and Standby and from depending on divorced point it will decide from where it has to apply the changes on target cluster.
Apply the WAL from the source cluster, starting from the checkpoint created at failover.



Lets do the setup and test the pg_rewind:


On Master Set parameters:

wal_level = replica
archive_mode='on'
archive_command='cp %p /tmp/archive/%f'
wal_keep_segments = 500
max_wal_senders=4
wal_log_hints = on

Initially, I have built the replication Master:5432 and Standby:5433.

I have promoted the standby and deleted records from Standby

Master is Master and Standby is also accepting the DML's (Replication is broken)

Now instead of taking the full base backup and building the replication again just use pg_rewind and build the replication in short time.

Once pg_rewind is successfully completed then you will see the backup_label file in Standby's data directory.

I have created the recovery.conf file in Standby cluster and started it

Now let's check the status of replication?

Data is also streamed to Standby Cluster.

This ways pg_rewind helps you to rebuild the false promoted Standby or Master as standby.


In an Earlier version of PostgreSQL < 9.6 pg_rewind use to work only in one direction i.e If Standby got promoted then using pg_rewind user can build the Master as new standby for new Master.
From 9.6 user can use pg_rewind in both the direction.


Always try to Keep your Environments up-to-date to use this kind of features.

PostgreSQL Replication using SSL

Ohohohoh!!! I can set up the replication using SSL That's Cool. 

Let's Do it then what we are waiting for ??????

What is SSL and why it is used?

SSL stands for Secure Sockets Layer and, in short, it's the standard technology for keeping a connection secure and protecting any sensitive data that is being sent between two servers, preventing criminals from reading and modifying any information transferred.

The two systems can be a server and a client or server to server.

SSl in PostgreSQL :

https://www.postgresql.org/docs/9.5/static/ssl-tcp.html

Installed PostgreSQL-9.6 from the pgdg repo on Centos7.

First, create the certificates on the Master.

Create Directory for SSL

mkdir /var/lib/pgsql/9.6/data/ssl

cd /var/lib/pgsql/9.6/data/ssl

openssl req -new -text -out server.req

openssl rsa -in privkey.pem -out server.key

rm privkey.pem

openssl req -x509 -in server.req -text -key server.key -out server.crt

chmod og-rwx server.key

chown -R postgres  /var/lib/pgsql/9.6/data/ssl

Always it is recommended to create replication user for replication.

psql -c "create role replica with login replication password 'replication';"

Now set parameters in postgresql.conf and add replication entry in pg_hba.conf on Master.

postgresql.conf :

ssl = on

ssl_cert_file = '/var/lib/pgsql/9.6/data/ssl/server.crt'

ssl_key_file = '/var/lib/pgsql/9.6/data/ssl/server.key'

wal_level = hot_standby 

archive_mode='on'

archive_command='cp %p /var/lib/pgsql/9.6/archive/%f'

max_wal_senders = 5 

wal_keep_segments = 150

hot_standby_feedback = on

hot_standby = on           #this parameter will not effect on Master. It is for standby server.

pg_hba.conf

hostssl replication     replica        192.168.56.102/32          md5

Now restart the Master and take pg_basebackup to setup the replication.

systemctl restart postgresql-9.6

Backup :

pg_basebackup -D /var/lib/pgsql/9.6/replica -U replica -v -h 192.168.56.102 -R -xP

Start the replica cluster.

pg_ctl -D /var/lib/pgsql/9.6/replica/ start

recovery.conf  of replica cluster

standby_mode = 'on'

primary_conninfo = 'user=replica password=replication host=192.168.56.102 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'

restore_command='cp /var/lib/pgsql/9.6/archive/%f %p'

Here is the status of replicaion :

-[ RECORD 1 ]----+------------------------------

pid              | 8054

usesysid         | 16449

usename          | replica

application_name | walreceiver

client_addr      | 192.168.56.102

client_hostname  |

client_port      | 59200

backend_start    | 2018-02-01 00:30:16.148848-05

backend_xmin     |

state            | streaming

sent_location    | 0/5013A98

write_location   | 0/5013A98

flush_location   | 0/5013A98

replay_location  | 0/5013A98

sync_priority    | 0

sync_state       | async

Now let's try to connect to Master using replica user and check if certificates are been used or not?

Create extension sslinfo; 

postgres=# create extension sslinfo ;

CREATE EXTENSION

postgres=# \dx+ sslinfo

   Objects in extension "sslinfo"

         Object Description

------------------------------------

 function ssl_cipher()

 function ssl_client_cert_present()

 function ssl_client_dn()

 function ssl_client_dn_field(text)

 function ssl_client_serial()

 function ssl_extension_info()

 function ssl_issuer_dn()

 function ssl_issuer_field(text)

 function ssl_is_used()

 function ssl_version()

(10 rows)

Now connect using replica user over SSL.

psql (9.6.6)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Type "help" for help.

postgres=> select ssl_is_used();

 ssl_is_used

-------------

 t

(1 row)

postgres=> \q

WOW!!!! Great Replicaion setup is ready and it is Secure .....!!