How to configure PostgreSQL database cluster (PL/Proxy) in Ubuntu

How to configure PostgreSQL database cluster (PLProxy) in Ubuntu

How to configure PostgreSQL database cluster (PL/Proxy) in Ubuntu

The principle is just a concept, and the operability is not strong. I read N-plus semi-Principles and semi-Details related articles, but I am always confused by various details. In order to quickly use PostgreSQLProcedure for configuring a PostgreSQL cluster using a sequential account,Some conclusions are self-guessed or referenced.And fix the error later.
By the way, make some comments (skipping the following section is a digress, which is actually a nonsense ):
In fact, many things are simple in principle, and almost everyone can talk about them. However, in fact, theoretical articles are generally not very operable. I personally think that if the goal is to show to myself or the goal is to allow the team members to understand and operate the articles, we should still describe more details. From a literary point of view, that is, the expression must be more delicate.

Over, transfer subject:

FirstExplain the Database Cluster: Pile up N databases, find a header, manage all the databases and make them work together. Of course, you can discuss and agree on issues such as whether to find a header, a few headers, and how to collaborate. Therefore, different database clusters are formed.
If the database system is PostgreSQL, this cluster isPostgreSQL database cluster.
There are many methods to manage PostgreSQL database clusters. Some people have proposedCluster in PL/Proxy Mode(This is the focus of this Article ). This PL/Proxy mode cluster is like this: there are many computers installed with the PostgreSQl database, and one is the header. We call this header proxy, and the others are called database0, database1 ....... Of course, it doesn't matter what the name is. The key is that there are headers and other headers under command.

Let's take a look at the example:Example of a cluster with three machinesTo see what the PostgreSQL cluster architecture is.
Proxy Node: The proxy node is actually a PostgreSQL database node,All data is not stored on the proxy node.Mainly do three things:
1. Accept the user's SQL query;
2. Analyze Your SQL queries and convert them into SQL statements executed on the cluster;
3. Merge the cluster execution SQL results and return them to the user.
To put it bluntly, the user's SQL statement is handed over to database0 and database1 for execution, and then the combined execution result is returned to the user.
Database1 node and database2Node:
It is a common database node. It receives the SQL query request from the proxy node and returns the result to the proxy node. It is a node that truly stores data.
If there is no picture or truth, let's take a cluster structure diagram to make the structure clearer:

After reading the schematic diagram, do you think it is very simple? People with a little understanding of technology can think of this architecture. In this case, create a database cluster,The requirements are as follows::

1. Create a cluster as shown in the cluster structure. The database cluster operates on the same ser.(To put it bluntly, you need to create a table named user in the JiwanCluster of Database0 and Database1 through the cluster method );
3. the user operates the proxy node on the cluster through the clientInsert multiple recordsInformation to the cluster's user table (insert operations need to be distributed to two Database nodes );
4. the user operates the proxy node on the cluster through the clientQuery all user records on the Cluster;

The requirement is not difficult, but unfortunately, if you are not familiar with PostgreSQL, you still cannot build the database (I still cannot build it after reading the principle ). The principle is that this is too official. It is a very practical step (Pay attention to the version numberIf the version is different, the situation may be very different ):

Step 1: Create three empty virtual machinesSet the network cards of the three virtual machines (such as bridging or NAT). (If you have money, buy three computers, three network cables and one hub ).

Software that can create virtual machines, such as VMWare, VirtualPC, and Xen, is used in this experiment because I am familiar with VMWare;

Step 2: All three VMSInstall Ubuntu-10.04

There is nothing to say about this, but we have to talk about it again when we get to the West. You can find it on the official Ubuntu website. The download address is

Step 3: All three VMSInstall the PostgreSQL database system

For how to install and configure, see install and configure Postgresql-8.4 in Ubuntu

Step 4: perform additional actions, Set each server to allow any machine to access any database using any account(Experiment, it doesn't matter, just do it ):

Root @:~ #Vi/etc/postgresql/8.4/main/pg_assist.conf
-> Change rows:Host all 0.0.0.0 0.0.0.0Md5
-> Modify:Host all 0.0.0.0 0.0.0.0Trust
If this line does not exist, add this line. For more information, see install and configure the Postgresql-8.4 under Ubuntu.

Step 5: all three VMS Create Database JiwanCluster:

Root @:~ #Psql-U postgres-h 127.0.0.1
Postgres = #Create database "JiwanCluster ";

For more information about how to create a database, see install and configure a Postgresql-8.4 under Ubuntu.

--- The chapters are messy. Let's get a "perfect cut line" First "---
PS: currently, all three machines can provide database services, and you can freely access the "JiwanCluster" database in other database servers on any machine using the client. HoweverThey are not clusters yetOnly database servers that can access each other. There is no collaboration between them. The following describes how to create a PL/Proxy collaboration relationship ?! Create a PostgreSQL database cluster ).
--- The chapters are messy. Let's get a "perfect cut line" First "---

Step 6: The installation process language plproxy on the proxy Node(To install plproxy, you must use the root user. Otherwise, it cannot be installed normally. The plproxy version after installation is Plproxy-2.0.4):

Sudo apt-get install postgresql-8.4-plproxy

The following is an explanation. Skip it if you do not want to understand it.

This stepPurposeYes: Let the proxy node know how to command the cluster (in the experiment, it is to command database0 and database1). In other words, let prxoy know three things:
How to send user SQL requests to cluster nodes;
How to accept the processing results of the cluster;
How to return the processed result to the user.

This stepEssenceYes: CopyPlproxy. soCopy the file to the directory "/user/lib/postgresql/8.5/lib ".Plproxy. SQLFile to directory under "/usr/share/postgresql-8.4-plproxy" (This directory is created by the installer, plproxy. the SQL file is used to create the plproxy language for the next step. It is not necessary. You can write the corresponding SQL statement as needed ). The existence of these two files marks the successful installation of this step.
It should be noted that the installation process language is for the database system. It can be considered that only some required files are copied. before using this process language, we also need to create this language for each database that needs to use this programming language (to put it bluntly, we need to tell each database to find the corresponding file plproxy. so file, the next step is explained ).

After execution, the effect in the file browser is:

Step 7: Database on the proxy node "JiwanCluster"Create plproxy process Language(If you have installed this language before ):

Root @:~ #Psql-U postgres-h 127.0.0.1-f/usr/share/postgresql-8.4-plproxy/plproxy. SQL JiwanCluster
The essence of this statement is to add a row of records in the database system table pg_language.
After execution, you can see the following results on the pgAdmin3 client: