Load Balancing for PostgreSQL database servers

Operating System: Debian 3.0 r2
Kernel used: linux-2.4.23
The module used in the experiment: ipvsadm-1.21, PostgreSQL (detailed usage of ipvsadm command please refer to here)
Function Description: This is a simple experiment on linuxvirtualserver. It is about how to achieve load balancing between two PostgreSQL databases. This experiment has three purposes:
1. A friend's needs;
2. I haven't looked at LVS for a while. I 'd like to take this opportunity to review it;
3. This is the simplest LVS experiment, which is helpful for beginners of LVS.

Installation steps:
1. First, you need to re-compile the kernel. If you do not know how to compile the kernel, you can go to our "Basic Knowledge" section to see it. Compile the netfilter and LVS options into your kernel. You can refer to the three pictures: Network compilation options, netfilter compilation options, and LVS compilation options.
2. After the kernel is compiled, start the system with the new kernel, and then install ipvsadm
Tar zxvf ipvsadm-1.21.tar.gz
CD ipvsadm-1.21
Make
Make install
Installation Complete

3. Now we have the following network structure to achieve Load Balancing for the two PostgreSQL servers,
Http://www.linux.gov.cn/himages/Linux/lvs_001.gif
It is easy to implement Load Balancing for the two PostgreSQL servers: 192.168.3.2 and 192.168.3.3. Use the following commands:

Echo 1>/proc/sys/NET/IPv4/ip_forward
Ipvsadm-a-t 192.168.1.100: 5432-s rr
Ipvsadm-a-t 192.168.1.100: 5432-r 192.168.3.2: 5432-m-W 1
Ipvsadm-a-t 192.168.1.100: 5432-r 192.168.3.3: 5432-m-W 1

Explanation:
Echo 1>/proc/sys/NET/IPv4/ip_forward: Enable the routing function of Linux LVS router. Otherwise, data packets will not be forwarded.

Ipvsadm-a-t 192.168.1.100: 5432-s RR: Add a virtual server. the IP address of the virtual server is 192.168.1.100 and the port is 5432.AlgorithmRound Robin)

Ipvsadm-a-t 192.168.1.100: 5432-r 192.168.3.2: 5432-m-W 1
Add the actual server 192.168.3.2: 5432 to the virtual server 192.168.1.100;

Ipvsadm-a-t 192.168.1.100: 5432-r 192.168.3.3: 5432-m-W 1
Add the Real Server 192.168.3.3: 5432 to the virtual server 192.168.1.100.

The above is all the content of this experiment. Now we have a small discussion on it:

1. First, this experiment is for PostgreSQL clusters. The default port number of PostgreSQL is 5432. If you want to create clusters of other services, such as HTTP, FTP, and SMTP, you only need to change the port number 5432 in the command to the corresponding port number.

2. the PostgreSQL cluster is not ideal. Two situations are worth mentioning:
(A) when one of the two PostgreSQL servers fails, ipvsadm cannot be detected automatically and will not remove the faulty servers from the cluster, ipvsadm will still perform load balancing on the two servers according to the set rules, which will cause the postgresql service to be good or bad.

(B) Because we only use one Linux LVS router, when the Linux LVS router fails, the entire postgresql service will be stopped.

To solve these two problems, you can use one method to monitor the health status of all machines in the cluster, including servers and LVS router. If a server fails, the cluster system automatically removes the host from the system, and then prepares more than two LVS router instances, one of which is the master instance and the other is used as backups, when the master LVS router fails, the backed up LVS router takes over the work of the master LVS router in a timely manner. In the LVS project, there is a component named keepalived. keepalived is dedicated for this purpose. For the application of keepalived, please refer to this experiment: load balancer system with high stability, availability, and efficiency. Keepalived is also a solution for vrrp in Linux.

3. In practical applications, we generally do not use such a network architecture. In most cases, we use an architecture as shown in the following figure:
Http://www.linux.gov.cn/himages/Linux/lvs_002.gif
The database write operations are input by the "database editors" in the background, and the database content is stored in the "Network Storage Device. The database access personnel access the database content through the PostgreSQL cluster.

A few tips about PostgreSQL installation and startup:
1. Many times, when installing a gnu software that has never been used before, we will always encounter some minor problems. This requires us to go back and read his documents and check his configuration. I encountered a small problem when installing PostgreSQL for the first time. I will write it here for your reference:
The installation of PostgreSQL is still relatively simple. You can simply follow the install file in his document. The following are my installation steps:

./Configure cprefix =/usr/local/pgsql
Gmake
Su
Gmake install
Adduser Postgres
Mkdir/usr/local/pgsql/Data
Chown Postgres/usr/local/pgsql/Data
Su-Postgres
/Usr/local/pgsql/bin/initdb-D/usr/local/pgsql/Data

2. Start PostgreSQL:
Su-Postgres
/Usr/local/pgsql/bin/postmaster-D/usr/local/pgsql/data> logfile 2> & 1 &
In this way, the installation is complete. My problem occurs at startup. during startup, I checked the processes in the system and found that the PostgreSQL process is already running, but port 5432 is not started, finally, we found the problem with the configuration file:
In the/usr/local/pgsql/data/PostgreSQL. conf file
# Change tcpip_socket = false
Tcpip_socket = Yes

To allow other machines to access the posgresql service, modify/usr/local/pgsql/data/pg_hba.conf.
Add the following lines:
# Type Database User IP-ADDRESS IP-MASK Method
Host All all 0.0.0.0 0.0.0.0 Trust
The meaning of this statement is to allow all machines to access PostgreSQL.

This experiment is not dedicated to PostgreSQL, so we will not talk much about it.

Finally, linuxvirtualserver and PostgreSQL are the pride of Chinese in the free software market !!

By lna@networksbase.com