Run multiple PostgreSQL instances on one machine

Recently, we have to do some examples about PostgreSQL multi-instance running. Generally, we need to install the PostgreSQL server on different machines and then configure it on different machines, for example, use plproxy to set some demos. Of course, this requirement can be achieved through multiple virtual machines, such as creating multiple VMWare virtual machines. However, this is a waste of resources and requires maintenance of different machines. Try to run multiple PostgreSQL server instances on the same machine.

Objectives:Run multiple PostgreSQL server instances on one machine.
Running Environment: ubnntu 8.04.1 (running in VMware)

1. First install PostgreSQL server through sudo apt-Get install PostgreSQL. During the installation process, it is recommended to install other related PKG, which can be installed. After the installation is complete, it is best to have the following package list:
PostgreSQL
Postgresql-8.3
Postgresql-client-8.3
Postgresql-client-common
Postgresql-common
After the installation is complete, Apt-Get will automatically initialize the PostgreSQL database based on the installation script, and start a PostgreSQL instance (called a cluster) with the name of main. remember to use main later.
2. Modify the file
2.1 Modify/usr/bin/pg_ctlcluster
/Usr/bin/pg_ctlcluster is a script used to control the startup, stop, and restart of the PostgreSQL server, facilitating the control of the PostgreSQL server. The default PostgreSQL cluster can only be accessed locally, but cannot be accessed through a network using TCP/IP. Modify the/usr/bin/pg_ctlcluster file and set row 253 to the following:

1. 253 my $ postmaster_opts = '-I ';
2. 254 If (! (Pgcommon: get_conf_value $ version, $ cluster, 'postgresql. conf', 'unix _ socket_directory ')){
3. 255 $ postmaster_opts. = '-C unix_socket_directory = "'. $ info {'etetdir '}.'"';
4. 256}

By adding the-I parameter, the PostgreSQL cluster started through pg_ctlcluster can accept network access.
2.2 modify the/etc/PostgreSQL/8.3/main/pg_assist.conf File
This is the configuration file for PostgreSQL server to complete customer identity authentication. For details, see here. Add the following line in the content of this file (if it is an online machine, do not do this, it is too dangerous .) :

1. Host All all 0.0.0.0 0.0.0.0 Trust

Then run Sudo-u Postgres/usr/bin/pg_ctlcluster 8.3 main restart to restart.
After the startup is complete, run PS-Ef | grep Postgres to view the following content in the console:

1. Postgres 4890 1 0 :38? 00:00:01/usr/lib/PostgreSQL/8.3/bin/Postgres-D/var/lib/PostgreSQL/8.3/Main-I-c config_file =/etc/PostgreSQL/8.3/main/ postgreSQL. conf
2. Postgres 4897 4890 0? 00:00:00 ipvs: writer Process
3. Postgres 4898 4890 0? 00:00:00 ipvs: Wal writer Process
4. Postgres 4899 4890 0? 00:00:00 Postgres: autovacuum launcher Process
5. Postgres 4900 4890 0? 00:00:00 Postgres: stats collector Process
6. Xuepeng 6068 5940 0 00:00:00 pts/0 grep Postgres
   

Our first pgcluster (named main) is running normally.

3. Create the second PG Cluster
The default PG cluster name is main, and the second cluster we created is pgd1 (of course, you can name it freely ). The first thing we can think of is that main and pgd1 should run on different port numbers. Main uses the default 5432. To facilitate memory usage, let pgd1 use 5433. Second, different clusters must also use different data file directories, that is, the values represented by-D/var/lib/PostgreSQL/8.3/main in PS results. Every running cluster needs to use its own private data files to store tables, views, functions, triggers, and other things. Therefore, we need a different data file directory. The above two aspects are the most important.
In fact, several pgcluster maintenance scripts have been installed during PostgreSQL installation. The above/usr/bin/pg_ctlcluster is only one of them. You can use/usr/bin/pg_createcluster to create a new cluster, which is very convenient.
Before creating an account, we use useradd pgd1 to create an Ubuntu account and use this account as the default owner and superuser of pgd1.

1. Sudo/usr/bin/pg_createcluster-u 1001-G 1001-D/var/lib/PostgreSQL/8.3/D1-S/var/run/pgd1 -- local zh_CN.UTF-8-e utf8- P 5433 -- start-conf auto 8.3 pgd1

After the preceding command is executed, the cluster named pgd1 is created. The-U and-G parameters indicate the default owner and group of the cluster. For other parameters, see the help of pg_createcluster, which is easy to understand.

After the pgd1 configuration file is created, it is in the/etc/PostgreSQL/8.3/pgd1/directory. For the same reason, we need to add the following content in/etc/PostgreSQL/8.3/pgd1/pg_hba.conf:

1. Host All all 0.0.0.0 0.0.0.0 Trust

Finally, run Sudo-u pgd1/usr/bin/pg_ctlcluster 8.3 pgd1 restart to restart the cluster named pgd1.

4. Follow these steps to create a new pgcluster, pgd2, pgd3 .....

1. $PS-Ef | grep Postgres
2. Postgres 4890 1 0 :38? 00:00:01/Usr/lib/PostgreSQL/8.3/bin/Postgres-D/var/lib/PostgreSQL/8.3/Main-I-c config_file =/etc/PostgreSQL/8.3/main/PostgreSQL. conf
3. Postgres 4897 4890 0? 00:00:01 ipvs: writer Process
4. Postgres 4898 4890 0? 00:00:01 ipvs: Wal writer Process
5. Postgres 4899 4890 0? 00:00:00 Postgres: autovacuum launcher Process
6. Postgres 4900 4890 0? 00:00:00 Postgres: stats collector Process
7. Pgd1 6304 1 0 11: 38? 00:00:00/Usr/lib/PostgreSQL/8.3/bin/Postgres-D/var/lib/PostgreSQL/8.3/D1-I-c config_file =/etc/PostgreSQL/8.3/pgd1/PostgreSQL. conf
8. Pgd1 6311 6304 0? 00:00:00 ipvs: writer Process
9. Pgd1 6312 6304 0? 00:00:00 ipvs: Wal writer Process
10. Pgd1 6313 6304 0? 00:00:00 Postgres: autovacuum launcher Process
11. Pgd1 6314 6304 0? 00:00:00 Postgres: stats collector Process
12. Pgd2 6570 1 3 11: 47? 00:00:00/Usr/lib/PostgreSQL/8.3/bin/Postgres-D/var/lib/PostgreSQL/8.3/D2-I-c config_file =/etc/PostgreSQL/8.3/pgd2/PostgreSQL. conf
13. Pgd2 6576 6570 0? 00:00:00 ipvs: writer Process
14. Pgd2 6577 6570 0? 00:00:00 ipvs: Wal writer Process
15. Pgd2 6578 6570 0? 00:00:00 Postgres: autovacuum launcher Process
16. Pgd2 6579 6570 0? 00:00:00 Postgres: stats collector Process