What is replication?<\/strong><\/h2>\nReplication is the process of copying data from a primary database to one or more backup databases. In replication, the primary database is responsible for read-write operations, while the backup database is responsible for read-only operations.
\nIn this article, we will use the terms \u201cprimary\u201d or \u201cmaster\u201d database as \u201cana veritaban\u0131\u201d, and \u201csecondary\u201d or \u201cstandby\u201d database as \u201cyedek veritaban\u0131\u201d.<\/p>\n
What is EnterpriseDB Failover Manager?<\/strong><\/h2>\nThe EFM high availability module is a tool developed by EnterpriseDB that automatically makes one of the standby databases the primary database\u2014that is, performs the \u201cPromote\u201d operation\u2014in the event of issues that may arise in the primary database due to software and\/or hardware reasons.<\/span><\/p>\nInformation we will provide in this document about the function of the EFM tool and how to install it PostgreSQL<\/a> or EDB Postgres Advanced Server (EPAS)<\/a> Applies to version 9.6 and later of databases.<\/span><\/p>\nTypical EFM cluster elements consist of the components specified below.<\/strong><\/p>\n\n- Master node: It is the primary database server in the replication structure.<\/span><\/li>\n
- Standby node: A standby node is a database server that uses streaming replication with the primary node.<\/span><\/li>\n
- Witness node: Following a disaster scenario, it witnesses the new role definitions and statuses assigned to two or more nodes. If the cluster contains three or more nodes (provided that the total number of nodes in the cluster is an odd number), a separate witness node is not needed in the cluster, as one of the nodes can fulfill the witness duty. However, if there is no third cluster member node containing a database, a witness node must be added. Furthermore, the cluster may also contain multiple witness nodes (again, provided that the total number of nodes is an odd number).<\/span><\/li>\n<\/ul>\n
Normally, the term cluster describes a single PostgreSQL installation managing multiple databases. However, the term cluster used in this article series will refer to the EFM cluster. <\/span><\/p>\nThe EFM cluster can reside on servers in the cloud or in a traditional network. It provides communication via the JGroups library, which is written to enable one-to-one and one-to-many communication. This cluster can consist of a primary EFM agent, one or more backup EFM agents, and optionally a witness EFM agent.<\/span><\/p>\n![\"EFMSS\"](\"https:\/\/profelis.com.tr\/wp-content\/uploads\/2022\/09\/efmss.png\" "\\"\\"")
<\/p>\n
The above figure shows an EFM cluster using a Virtual IP address. The Virtual IP address ensures that all requests directed to the EFM cluster arrive at a single IP address and are distributed from there. If these changes can be reported to the load balancer as databases are added to or removed from the cluster online, software acting as a load balancer can also assume the virtual IP address function. We will use pgpool as the load balancing tool. We will demonstrate how to perform the pgpool-EFM integration by leaving the task of managing the Virtual IP to pgpool and editing the configuration file provided by EFM to notify EFM as databases are added to or removed from the cluster.<\/span><\/p>\nWhen any EFM agent other than the witness is started, the agent connects to the database and checks its status.<\/span><\/p>\n\n- If it cannot reach the database, it starts as IDLE.<\/span><\/li>\n
- During the database recovery process, the database on the agent's node assumes the role of the standby database. <\/span><\/li>\n
- If the database is not in recovery, the database on the agent node assumes the primary database role.<\/span><\/li>\n<\/ul>\n
In the event of a failover, EFM ensures that the standby server designated as the primary database is the most up-to-date standby server in the cluster. However, if the primary node and the standby node are not in sync, EFM does not automatically perform the promote operation.<\/span><\/p>\nWhat are the EnterpriseDB Failover Manager Requirements?<\/strong><\/h2>\nBefore configuring the EFM cluster, the system must meet the following requirements.<\/span><\/p>\n\n- Java 1.8 (or later) Installation<\/span>\n
\n- Before using EFM, we must have Java installed (version 1.8 or later). <\/span><\/li>\n
- EFM has been tested with OpenJDK, and it is recommended to install the specified version. <\/span><\/li>\n
- Instructions for Java installation are platform-specific.<\/span><\/li>\n<\/ul>\n<\/li>\n
- SMTP Server Configuration (Optional)<\/span>\n
\n- When using EFM, it is possible to choose to receive notifications by user-defined script, email, or both. <\/span><\/li>\n
- If any of these methods are to be used, the SMTP server must be active.<\/span><\/li>\n<\/ul>\n<\/li>\n
- Streaming Replication Configuration<\/span>\n
\n- For EFM, PostgreSQL streaming replication (SR) must be configured between the primary and standby nodes. <\/span><\/li>\n
- EFM does not support other replication types.<\/span><\/li>\n<\/ul>\n<\/li>\n
- Editing the pg_hba.conf file<\/span>\n
\n- The pg_hba.conf files on the primary and standby nodes must be configured to allow communication between all nodes in the cluster. <\/span><\/li>\n
- Below is an example of the lines that must be in the pg_hba.conf file on the primary node.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n
\n\n\n\nFor access to #\n\nhost database_name efm 127.0.0.1\/32 md5\n\nFor access to the # backup server\n\nhost database_name efm 192.168.1.1\/32 md5\n\nTo access the # witness server\n\nhost database_name efm 192.168.1.2\/32 md5<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n
\n- After modifying the pg_hba.conf file, the system needs to be reloaded (using pg_ctl reload or \u201cselect pg_reload_conf();\u201d) for the changes to take effect. <\/span><\/li>\n<\/ul>\n
\n- Ensuring communication between firewalls<\/span>\n
\n- If the Linux firewalls on the EFM nodes are enabled, rules granting the necessary permissions must be added to the firewall configurations.<\/span><\/li>\n
- The following command opens port 7800. EFM establishes the connection using the port number specified in the configuration file. If firewalls are not active, this process is not necessary.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n
\n\n\n\niptables -I INPUT -p tcp --dport 7800 -j ACCEPT \/sbin\/service iptables save<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n
\n- The database user having the necessary permissions<\/span>\n
\n- the database user specified in the db.user parameter in the efm.properties file,\u00a0<\/span>\n
\n- pg_current_wal_lsn()<\/span><\/li>\n
- pg_last_wal_replay_lsn()<\/span><\/li>\n
- pg_wal_replay_resume()<\/span><\/li>\n
- pg_wal_replay_pause()<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
must have sufficient database privileges to be able to call its functions.<\/span><\/p>\n\n- reconfigure.num.sync<\/b> or<\/span> reconfigure.sync.primary<\/b> if the parameters are set to \u2018true\u2019:<\/span>\n
\n- For version 9.6 <\/span>db.user<\/b> The user specified in the parameter must be a superuser.<\/span><\/li>\n
- For versions after 9.6, the specified database user <\/span>pg_read_all_stats<\/b> and to his authority <\/span>pg_reload_conf()<\/b>\u2019must have permission to run it.<\/span><\/li>\n<\/ul>\n<\/li>\n
- Additionally, the specified user must have the privilege to read the configuration values. The authorized user in the database can grant the necessary permissions to the user using the GRANT command.<\/span><\/li>\n<\/ul>\n\n
\n\n\n\nGRANT pg_read_all_settings TO ;<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\nHow to Install EnterpriseDB Failover Manager?<\/strong><\/h2>\nEFM installation, <\/span>root <\/b>must be performed by the user. During installation, a user named efm is created, which has the authority to monitor the EFM service for databases (clusters) authorized by the enterprisedb (EPAS superuser) or postgres (PostgreSQL superuser) user and has the authority to use the scripts. The installation steps for CentOS 7 are as follows.<\/span><\/p>\n\u00a0<\/span>1. To create repo configuration files, superuser privileges are required. Then, the following command is run.<\/span><\/p>\n\n\n\n\n\nyum -y install https:\/\/yum.enterprisedb.com\/edbrepos\/edb-repo-latest.noarch.rpm<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n\u00a0<\/span>2. Following the installation in the first step, the edb.repo file will be created under the \/etc\/yum.repos.d directory. The USERNAME and PASSWORD information in this file is replaced with the username and password information obtained from the EnterpriseDB website. This process can be done by opening the file with any editor, or <\/span><\/p>\n\n\n\n\n\nsed -i "s@:@USERNAME:PASSWORD@" \/etc\/yum.repos.d\/edb.repo<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\nThis can also be done by replacing the USERNAME and PASSWORD parts with the information obtained from EDB and running the command.<\/span><\/p>\n\u00a0<\/span>3. The EPEL repository is installed.<\/span><\/p>\n\n\n\n\n\nyum -y install epel-release<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n\u00a0<\/span>4. The EFM package is installed. To avoid errors during this installation, the changes specified in step 2 must be made correctly in the edb.repo file.<\/span><\/p>\n\n\n\n\n\nyum -y install edb-efm44<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n\u00a0<\/span>5. After installation, the files will be created in the directories specified below.<\/span><\/p>\n\n- \n
\n- Executable files<\/span>\n
\n- \/usr\/edb\/efm-4.4\/bin<\/span><\/li>\n<\/ul>\n<\/li>\n
- Libraries<\/span>\n
\n- \/usr\/edb\/efm-4.4\/lib<\/span><\/li>\n<\/ul>\n<\/li>\n
- Configuration files<\/span>\n
\n- \/etc\/edb\/efm-4.4<\/span><\/li>\n<\/ul>\n<\/li>\n
- Logs<\/span>\n
\n- \/var\/log\/efm-4.4<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
\u00a0<\/span>6. For each node after installation<\/span><\/p>\n\n- \n
\n- The efm.properties file is edited.<\/span><\/li>\n
- The efm.nodes file is edited.<\/span><\/li>\n
- If the Virtual IP address and code snippet are defined in the efm.properties file, configuration and testing procedures must be performed.<\/span><\/li>\n
- EFM agents on each node of the cluster are started.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
\nIn this article, we will use the terms \u201cprimary\u201d or \u201cmaster\u201d database as \u201cana veritaban\u0131\u201d, and \u201csecondary\u201d or \u201cstandby\u201d database as \u201cyedek veritaban\u0131\u201d.<\/p>\n
The EFM high availability module is a tool developed by EnterpriseDB that automatically makes one of the standby databases the primary database\u2014that is, performs the \u201cPromote\u201d operation\u2014in the event of issues that may arise in the primary database due to software and\/or hardware reasons.<\/span><\/p>\n Information we will provide in this document about the function of the EFM tool and how to install it PostgreSQL<\/a> or EDB Postgres Advanced Server (EPAS)<\/a> Applies to version 9.6 and later of databases.<\/span><\/p>\n Typical EFM cluster elements consist of the components specified below.<\/strong><\/p>\n Normally, the term cluster describes a single PostgreSQL installation managing multiple databases. However, the term cluster used in this article series will refer to the EFM cluster. <\/span><\/p>\n The EFM cluster can reside on servers in the cloud or in a traditional network. It provides communication via the JGroups library, which is written to enable one-to-one and one-to-many communication. This cluster can consist of a primary EFM agent, one or more backup EFM agents, and optionally a witness EFM agent.<\/span><\/p>\n The above figure shows an EFM cluster using a Virtual IP address. The Virtual IP address ensures that all requests directed to the EFM cluster arrive at a single IP address and are distributed from there. If these changes can be reported to the load balancer as databases are added to or removed from the cluster online, software acting as a load balancer can also assume the virtual IP address function. We will use pgpool as the load balancing tool. We will demonstrate how to perform the pgpool-EFM integration by leaving the task of managing the Virtual IP to pgpool and editing the configuration file provided by EFM to notify EFM as databases are added to or removed from the cluster.<\/span><\/p>\n When any EFM agent other than the witness is started, the agent connects to the database and checks its status.<\/span><\/p>\n In the event of a failover, EFM ensures that the standby server designated as the primary database is the most up-to-date standby server in the cluster. However, if the primary node and the standby node are not in sync, EFM does not automatically perform the promote operation.<\/span><\/p>\n Before configuring the EFM cluster, the system must meet the following requirements.<\/span><\/p>\n must have sufficient database privileges to be able to call its functions.<\/span><\/p>\n EFM installation, <\/span>root <\/b>must be performed by the user. During installation, a user named efm is created, which has the authority to monitor the EFM service for databases (clusters) authorized by the enterprisedb (EPAS superuser) or postgres (PostgreSQL superuser) user and has the authority to use the scripts. The installation steps for CentOS 7 are as follows.<\/span><\/p>\n \u00a0<\/span>1. To create repo configuration files, superuser privileges are required. Then, the following command is run.<\/span><\/p>\n \u00a0<\/span>2. Following the installation in the first step, the edb.repo file will be created under the \/etc\/yum.repos.d directory. The USERNAME and PASSWORD information in this file is replaced with the username and password information obtained from the EnterpriseDB website. This process can be done by opening the file with any editor, or <\/span><\/p>\n This can also be done by replacing the USERNAME and PASSWORD parts with the information obtained from EDB and running the command.<\/span><\/p>\n \u00a0<\/span>3. The EPEL repository is installed.<\/span><\/p>\n \u00a0<\/span>4. The EFM package is installed. To avoid errors during this installation, the changes specified in step 2 must be made correctly in the edb.repo file.<\/span><\/p>\n \u00a0<\/span>5. After installation, the files will be created in the directories specified below.<\/span><\/p>\n \u00a0<\/span>6. For each node after installation<\/span><\/p>\n\n
<\/p>\n\n
What are the EnterpriseDB Failover Manager Requirements?<\/strong><\/h2>\n
\n
\n
\n
\n
\n
\n\n
\n \n For access to #\n\nhost database_name efm 127.0.0.1\/32 md5\n\nFor access to the # backup server\n\nhost database_name efm 192.168.1.1\/32 md5\n\nTo access the # witness server\n\nhost database_name efm 192.168.1.2\/32 md5<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n
\n
\n
\n
\n\n
\n \n iptables -I INPUT -p tcp --dport 7800 -j ACCEPT \/sbin\/service iptables save<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n
\n
\n
\n
\n
\n
\n\n
\n \n GRANT pg_read_all_settings TO ;<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n
How to Install EnterpriseDB Failover Manager?<\/strong><\/h2>\n
\n\n
\n \n yum -y install https:\/\/yum.enterprisedb.com\/edbrepos\/edb-repo-latest.noarch.rpm<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n
\n\n
\n \n sed -i "s@:@USERNAME:PASSWORD@" \/etc\/yum.repos.d\/edb.repo<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n
\n\n
\n \n yum -y install epel-release<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n
\n\n
\n \n yum -y install edb-efm44<\/pre>\n<\/th>\n<\/tr>\n<\/thead>\n<\/table>\n<\/div>\n
\n
\n
\n
\n
\n
\n
\n
\n