Step By Step – How To Setup Replication Manager(Repmgr) in PostgreSQL

 Step 1 - Make sure the replication configuration is setup correctly.

Step 2 – Install repmgr on primary and standby servers.

Make sure we install the same version of repmgr which is of our postgreSQL version. Since I am running postgreSQL version 13, I will be installing repmgr-13.

Note - We have to install it as a root user.

On Primary Server -

#yum -y install repmgr13*

On Standby Server -

#yum -y install repmgr13*

Step 3  – Configure postgresql.conf on Master and Standby nodes.

Since we have already configured replication, most of the parameters are already set. But just check on the below parameters and set whichever is missing.

shared_preload_libraries = 'repmgr'

max_wal_senders = 5

max_replication_slots = 10

wal_level = replica

hot_standby = on

archive_mode = on

archive_command = 'cp %p /home/postgres/arch_dir/%  - We can have the different archive locations.

Listen_address=’*’

Step 4 – Create a replication manager user as repmgr.

Psql$ create user repmgr;

Psql$ create database repmgr owner repmgr;

Psql$alter user repmgr with superuser;

Step 5 – Create repmgr.conf on Primary node.

Now we have to create repmgr.conf on Primary node. We can create it on any location but I have created under data directory only. My data directory is -

Create repmgr.conf with below parameters -

[postgres@Master master]$ cat repmgr.conf

cluster='failovertesting'

node_id=1

node_name=Master

conninfo='user=repmgr password=welcome host=192.168.204.133 dbname=repmgr port=5448 connect_timeout=2'

data_directory='/home/postgres/master/'

failover=automatic

promote_command='/usr/pgsql-13/bin/repmgr standby promote -f /home/postgres/master/repmgr.conf --log-to-file'

follow_command='/usr/pgsql-13/bin/repmgr standby follow -f /home/postgres/master/repmgr.conf --log-to-file --upstream-node-id=%n'

Parameter understanding –

failover='automatic'

The failover parameter is one of the mandatory parameters for the repmgr daemon. This parameter tells the daemon if it should initiate an automatic failover when a failover situation is detected. It can have either of two values: “manual” or “automatic”. We will set this to automatic in each node.

promote_command

promote_command= '/usr/pgsql-13/bin/repmgr standby promote  -f /home/postgres/master/repmgr.conf  --log-to-file'

This is another mandatory parameter for the repmgr daemon. This parameter tells the repmgr daemon what command it should run to promote a standby. The value of this parameter will be typically the “repmgr standby promote” command, or the path to a shell script that calls the command. For our use case, we set this to the following in each node:

follow_command

follow_command= '/usr/pgsql-13/bin/repmgr standby follow  -f  /home/postgres/master/repmgr.conf   --log-to-file  --upstream-node-id=%n'

This is the third mandatory parameter for the repmgr daemon. This parameter tells a standby node to follow the new primary. The repmgr daemon replaces the %n placeholder with the node ID of the new primary at run time.

priority

The priority parameter adds weight to a node’s eligibility to become a primary. Setting this parameter to a higher value gives a node greater eligibility to become the primary node. Also, setting this value to zero for a node will ensure the node is never promoted as primary.

Suppose we have two standbys: replica1  and replica2 and we want to promote replica2 as the new primary when master node goes offline, and replica2 to follow replica1 as its new primary. We set the parameter to the following values in the two standby nodes:

Node Name       Parameter Setting

replica1              priority=60

replica2              priority=40

monitor_interval_secs

This parameter tells the repmgr daemon how often (default is seconds) it should check the availability of the upstream node. In our case, there is only one upstream node i.e the master node. The default value is 2 seconds, but we will explicitly set this anyhow in each node like below

monitor_interval_secs=2

connection_check_type

 This parameter dictates the protocol repmgr daemon will use to reach out to the upstream node. This parameter can take three values:

ping: repmgr uses the PQPing() method.

connection: repmgr tries to create a new connection to the upstream node

query: repmgr tries to run a SQL query on the upstream node using the existing connection

Again, we will set this parameter to the default value of ping in each node:

connection_check_type='ping'

reconnect_attempts and reconnect_interval

When the primary becomes unavailable, the repmgr daemon in the standby nodes will try to reconnect to the primary for reconnect_attempts times. The default value for this parameter is 6. Between each reconnect attempt, it will wait for reconnect_interval seconds, which has a default value of 10.

For demonstration purposes, we will use a short interval and fewer reconnect attempts. We set this parameter in every node:

reconnect_attempts=4

reconnect_interval=8

primary_visibility_consensus

When the primary becomes unavailable in a multi-node cluster, the standbys can consult each other to build a quorum about a failover. This is done by asking each standby about the time it last saw the primary. If a node’s last communication was very recent and later than the time the local node saw the primary, the local node assumes the primary is still available, and does not go ahead with a failover decision.

To enable this consensus model, the primary_visibility_consensus parameter needs to be set to “true” in each node – including the witness:

primary_visibility_consensus=true

standby_disconnect_on_failover

When the standby_disconnect_on_failover parameter is set to “true” in a standby node, the repmgr daemon will ensure its WAL receiver is disconnected from the primary and not receiving any WAL segments. It will also wait for the WAL receivers of other standby nodes to stop before making a failover decision. This parameter should be set to the same value in each node. We are setting this to “true”.

standby_disconnect_on_failover=true

Setting this parameter to true means every standby node has stopped receiving data from the primary as the failover happens. The process will have a delay of 5 seconds plus the time it takes the WAL receiver to stop before a failover decision is made. By default, the repmgr daemon will wait for 30 seconds to confirm all sibling nodes have stopped receiving WAL segments before the failover happens.

 Step 6 – Register Primary database cluster in the repmgr configuration

Since we have created the repmgr.conf file with the minimum required parameters. Now we have to register our primary database cluster in repmgr configuration.

[postgres@Master master]$ /usr/pgsql-13/bin/repmgr  -f  /home/postgres/master/repmgr.conf  primary register

We can see primary node is registered successfully using below command –

[postgres@Master master]$ /usr/pgsql-13/bin/repmgr  -f  /home/postgres/master/repmgr.conf  cluster show

Step 7 – Create repmgr.conf file on standby node.

We can create it in any location but I have created in the data directory only. My data directory is –

[postgres@Replica1 replica1]$ cat repmgr.conf

cluster='failovertesting'

node_id=2

node_name=Replica1

conninfo='host=192.168.204.134 user=repmgr password=welcome dbname=repmgr port=5448 connect_timeout=2'

data_directory='/home/postgres/replica1/'

failover=automatic

promote_command='/usr/pgsql-13/bin/repmgr  standby promote  -f /home/postgres/replica1/repmgr.conf --log-to-file'

follow_command='/usr/pgsql-13/bin/repmgr standby follow -f  /home/postgres/replica1/repmgr.conf --log-to-file  --upstream-node-id=%n'

Step 8 – Register Standby Database Cluster

Now we have to register our standby database cluster in the repmgr configuration.

[postgres@Replica1 replica1]$ /usr/pgsql-13/bin/repmgr  -f  /home/postgres/replica1/repmgr.conf  standby  register

Getting below error –

Then I had to force register it like below –

[postgres@Replica1 replica1]$ /usr/pgsql-13/bin/repmgr  -f  /home/postgres/replica1/repmgr.conf  standby  register –force

But when I was showing cluster status on primary it was giving below warning –

[postgres@Master ~]$ /usr/pgsql-13/bin/repmgr -f /home/postgres/master/repmgr.conf  cluster  show

So I had to run below command on standby database.

[postgres@Replica1 ~]$ /usr/pgsql-13/bin/repmgr -f /home/postgres/replica1/repmgr.conf  standby  follow

After than when I checked the cluster status on primary it is running without any error –

I checked both the nodes and it is running fine –

/usr/pgsql-13/bin/repmgr -f /home/postgres/master/repmgr.conf  node  check

$/usr/pgsql-13/bin/repmgr  –f  /home/postgres/master/repmgr.conf  cluster  crosscheck

 Step 9 – Start repmgrd process on master and standby

[postgres@Master ~]$ /usr/pgsql-13/bin/repmgrd  -f  /home/postgres/master/repmgr.conf

[postgres@Replica1 ~]$ /usr/pgsql-13/bin/repmgrd -f /home/postgres/replica1/repmgr.conf

We can see the series of events using cluster events commands –

When it starts monitoring then we will see the below details –

Repmgr Command Options –

                          ====================The End================

Step By Step - (Sync)How To Setup Physical Steaming Replication in Sync Mode in PostgreSQL

  Step By Step – (Sync)Physical Streaming Replication(Sync) in PostgreSQL

Here I am using two nodes and I have named them as Master and Replica respectively.

To setup physical streaming in Sync mode first we have to configure it in Async mode and then we will convert it into Sync mode.

Step 1 - Initialize  a database cluster on master node using initdb utility. I have initialized a cluster named primary on master node.

Step 2 – Change the port number in postgresql.conf file and start it. I have used the port 5448.

 

 Step 3 – Start the cluster using pg_ctl cluster utility.

 

Step 4 - Prepare the Master Server- Now we have to prepare the master server for physical            replication. For this we need to change below parameters on postgresql.conf of primary db cluster.

1.     Primary db cluster must be in archive log mode so the parameters should be set as below

1.     Wal_level=replica

2.     Archive_mode=on

3.     Archive_command= cp %p <arch_dir>/%f  where arch_dir is the archvie location directory which we have set.

4.     Max_wal_sender=3 or more

5.     Listen_address=’*’ for connection from any user.

Note – After changing the parameters we need to restart the cluster to take into effect.

 

[postgres@Master ~]$ pg_ctl -D primary restart

After restart my parameter setting looks like below  –

 

Step 5 – Create a user with replication privilege or user super user

        

Step 6 – Create a Physical replication slot

           postgres=# select * from pg_create_physical_replication_slot('standby1');

Step 7 – Add entries into pg_hba.conf: Here you have add entries into 2 sections  -

1)# IPv4 local connections – In this section you need to add entries for your standby server(s).

2) # replication privilege – In this section you have to add entries for your replication user(s).

Example – I have added entries which are highlighted in blues lines.

 Note – Restart the cluster after changing the pg_hba.conf file to take it into effect.

Step 8 – Take a bp_basebackup – We can take pg_basebackup from primary and also from standby node. If we are taking backup on primary node then we have to transer this backup to standby node using SCP or any other file transfer method.

In my case I am taking pg_basebackup from standby node. My standby node name is Replica.

Note – Before taking backup make sure you have same archive directory present in the standby node also. In my Master node archive directory is arch_dir so I have created the same directory arch_dir in the standby node also.

I have used below command to take pg_basesbackup from standby server-

[postgres@Replica ~]$ pg_basebackup -h 192.168.204.133 -U postgres -p 5448 -D /home/postgres/replica1 -P -Xs –R

Here I have used used to options in the pg_basebackup –

-R – This option is used to create the standby.signal file on the standby database.

-Xs – This option is used to include the WAL files with sepcified method. Here I have used s which means it will be used for streaming.

Once backup is successful we can see all the required directories and configuration files are created under /home/postgres/replica1 directory.

Step 9 – Prepare Standby Server :   Open postgresql.conf on the standby server and modify the below parameters –

1.     Port – we need to change the port if we are running the primary and standby in the same node. But in my case I have used different machines so I am keeping the same port number as 5448.

2.     Hot_standby=on.  By default it is off and it will not allow queries to run into read only mode. It will just be used for high availability.

3.     Primary_conninfo= 'user=repl password=welcome host=192.168.204.133 port=5448'. Here we have to specify through which user we are going to connect to the primary host. Also we have to mention primary hostname and port number which we will be connecting.

4.     primary_slot_name = 'standby1'

5.     recovery_target_timeline = 'latest'

Save the postgresql.conf and start the standby cluster.

[postgres@Replica ~]$ pg_ctl -D replica1 start

Once it is started we can see the walreceiver process on the standby server –

Also we can verify in the log file 

We can also verify from primary server by querying pg_stat_replication view –

Step 10 – Prepare Standby Server 

Add below parameter in standby server –

-          Add application name in the primary_conninfo of standby server.

-           

primary_conninfo = 'user=repl password=welcome host=192.168.204.133 port=5448 application_name=replica1'

Step 11 – Prepare Primary Server  -

Add below parameters in the primary server :

1.       Synchronous_commit=on

2.       Synchronous_standby_names= replica1

Note – Sometime there is problem with Synchronous_standby_names which simply does not take the value of application name so in that case we use –

             synchronous_standby_names = '*'                               

After making the changes just restart Primary and standby servers.

                    ======================THE END=============================

Step By Step - (Async)How To Setup Physical Steaming Replication in Async Mode in PostgreSQL

 

Step By Step – (Async)Physical Streaming Replication(Async) in PostgreSQL

Here I am using two nodes and I have named them as Master and Replica respectively.

Step 1 - Initialize  a database cluster on master node using initdb utility. I have initialized a cluster named primary on master node.

Step 2 – Change the port number in postgresql.conf file and start it. I have used the port 5448.

 

 Step 3 – Start the cluster using pg_ctl cluster utility.

 

Step 4 - Prepare the Master Server- Now we have to prepare the master server for physical            replication. For this we need to change below parameters on postgresql.conf of primary db cluster.

1.     Primary db cluster must be in archive log mode so the parameters should be set as below

1.     Wal_level=replica

2.     Archive_mode=on

3.     Archive_command= cp %p <arch_dir>/%f  where arch_dir is the archvie location directory which we have set.

4.     Max_wal_sender=3 or more

5.     Listen_address=’*’ for connection from any user.

Note – After changing the parameters we need to restart the cluster to take into effect.

 

[postgres@Master ~]$ pg_ctl -D primary restart

After restart my parameter setting looks like below  –

 

Step 5 – Create a user with replication privilege or user super user

        

Step 6 – Create a Physical replication slot

           postgres=# select * from pg_create_physical_replication_slot('standby1');

Step 7 – Add entries into pg_hba.conf: Here you have add entries into 2 sections  -

1)# IPv4 local connections – In this section you need to add entries for your standby server(s).

2) # replication privilege – In this section you have to add entries for your replication user(s).

Example – I have added entries which are highlighted in blues lines.

 Note – Restart the cluster after changing the pg_hba.conf file to take it into effect.

Step 8 – Take a bp_basebackup – We can take pg_basebackup from primary and also from standby node. If we are taking backup on primary node then we have to transer this backup to standby node using SCP or any other file transfer method.

In my case I am taking pg_basebackup from standby node. My standby node name is Replica.

Note – Before taking backup make sure you have same archive directory present in the standby node also. In my Master node archive directory is arch_dir so I have created the same directory arch_dir in the standby node also.

I have used below command to take pg_basesbackup from standby server-

[postgres@Replica ~]$ pg_basebackup -h 192.168.204.133 -U postgres -p 5448 -D /home/postgres/replica1 -P -Xs –R

Here I have used used to options in the pg_basebackup –

-R – This option is used to create the standby.signal file on the standby database.

-Xs – This option is used to include the WAL files with sepcified method. Here I have used s which means it will be used for streaming.

Once backup is successful we can see all the required directories and configuration files are created under /home/postgres/replica1 directory.

Step 9 – Prepare Standby Server :   Open postgresql.conf on the standby server and modify the below parameters –

1.     Port – we need to change the port if we are running the primary and standby in the same node. But in my case I have used different machines so I am keeping the same port number as 5448.

2.     Hot_standby=on.  By default it is off and it will not allow queries to run into read only mode. It will just be used for high availability.

3.     Primary_conninfo= 'user=repl password=welcome host=192.168.204.133 port=5448'. Here we have to specify through which user we are going to connect to the primary host. Also we have to mention primary hostname and port number which we will be connecting.

4.     primary_slot_name = 'standby1'

5.     recovery_target_timeline = 'latest'

Save the postgresql.conf and start the standby cluster.

[postgres@Replica ~]$ pg_ctl -D replica1 start

Once it is started we can see the walreceiver process on the standby server –

Also we can verify in the log file 

We can also verify from primary server by querying pg_stat_replication view –

                                 ======================The END=============================

Step by step - ASM Architecture, ASM Instance, ASM Diskgroups, ASM Disks

In the video we can learn below points in detail:

ASM Architecture
ASM instance
parameters in the AMS parameter file
understaning of ASM_POWER_LIMIT and disk Rebalancing
understanding the ASM_Diskstring parameter
ASM_diskgroups and its it redundency levels
Status of ASM disks
startup sequecne of an ASM Instance

Step by step - RAID, ASM Introduction, differences and its Benefit

This is the best video I have seen on ASM so just sharing it. Explanation is very good.

Session - 1: In this session we can learn what are the different RAID levels and how it works.
                    It also explains what are the benefits of ASM and its different redundancy levels.

Step by step - How to resolve ORA-01555 from the EXPDP

Scenario - we have around 2 TB of database and daily export job is scheduled to run at 12 AM EST. But it is failing with the below error. There are multiple occurrence of this error. I am just putting out one to show the exact error.

ORA-31693: Table data object "RDR"."REGW_EXTRACT" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 10 with nam"_SYSSMU10_3196945471$" too small 

So we need to heck the few things:

1.      Check the total duration of the export job.

2.      Find the undo retention of the database.

3.      Find the undo table space size

4.      Find the job running timing

Solution:

1.      Change the undo_retention to the total time the export is taking. But remember to add atlease 30 mins more. Suppose export is taking 6 hours then keep the undo_retention to atleast 6:30 hours. Mostly it will resolve the issue.

2.      Also make sure we have enough space on the undo table space.

3.      Also make sure to run the export in non-business hours.

WARNING: Subscription for node down event still pending

SYMPTOMS

We are receiving the following warning messages in the listener log file constantly:

'WARNING: Subscription for node down event still pending'

CHANGES

This may be a new installation or a recent upgrade to 11g or newer.

CAUSE

These messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). In a non-RAC environment it is recommended to disable this subscription.   This feature was introduced in Oracle 10g.

SOLUTION

Set the following parameter in the listener.ora:

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF

Where <listener_name> should be replaced with the actual listener name configured in the

LISTENER.ORA file.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> parameter is to be placed by itself on an empty line.

It will be necessary to restart or reload the listener following the addition of this parameter.

This will prevent the messages from being written to the log file and may also prevent the TNS

Please Note: Setting SUBSCRIBE_FOR_NODE_DOWN_<listener_name> to OFF disables a necessary RAC functionality. The above workaround is recommended only for non-RAC environments.

The issue may be present in all 11g and newer installations.