{"id":2219,"date":"2019-08-05T14:17:50","date_gmt":"2019-08-05T08:47:50","guid":{"rendered":"http:\/\/blog.tenthplanet.in\/?p=2219"},"modified":"2026-07-03T13:38:53","modified_gmt":"2026-07-03T08:08:53","slug":"postgresql-installation-and-configuration-on-single-node-and-multi-node-clustering","status":"publish","type":"post","link":"https:\/\/tenthplanet.in\/blogs\/postgresql-installation-and-configuration-on-single-node-and-multi-node-clustering\/","title":{"rendered":"PostgreSQL Installation and Configuration on Single Node and Multi Node Clustering"},"content":{"rendered":"

Installation of Postgresql through Postgres Enterprise Manager tool:<\/h3>\n

1. Download Postgres Enterprise Manager tool from the link https:\/\/www.enterprisedb.com\/software-downloads-postgres<\/p>\n

2. And Install Postgresql first with Graphical wizard as below,<\/p>\n

\"\"<\/p>\n

3. Click Next to continue. The Installation Directory window opens.<\/p>\n

\"\"<\/p>\n

4. The Data Directory window opens<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

5. Now, single node cluster is installed and configured. So you can log on to pg Admin for UI based transaction or terminal based transaction.<\/p>\n

$ sudo -i -u postgres\n\n$ psql\n\n#postgres:+ SELECT * FROM pg_databases;<\/pre>\n
Multi DB clustering:<\/h3>\n
1. Repeat the same procedure in another system for multi node creation.<\/p>\n
\"\"<\/p>\n
2. From now on, I assume that database clusters are located at \/home\/postgres\/data and are owned by postgres user:<\/p>\n
$ initdb -D \/home\/postgres\/data<\/pre>\n
3. Next add followings to \/home\/postgres\/data\/postgresql.conf. “logging_collector” and below are not really relevant to Streaming replication but they are my favorites to make my life easier. You might want to remove “log_statement = ‘all'” in production environment however.<\/p>\n
listen_address = '*'\nhot_standby = on\nwal_level = hot_standby\nmax_wal_senders = 1\nlogging_collector = on\nlog_filename = '%A.log'\nlog_line_prefix = '%p %t '\nlog_truncate_on_rotation = on\nlog_statement = 'all'<\/pre>\n
4. Put pg_hba.conf to \/home\/postgres\/data. Of course you need to replace “\/some\/where\/” with actual directory where you downloaded the scripts. Caution: settings here allows to access from any IP address. Please apply appropriate setting for your real world systems.<\/p>\n
$ cp \/etc\/postgresql\/pg_hba.conf\" \/home\/postgres\/data\"<\/pre>\n
5. Start PostgreSQL server on server2 and server3. At this point, those PostgreSQL servers will run as primary server, thus no streaming replication is working.<\/p>\n
$ pg_ctl -D \/home\/postgres\/data start<\/pre>\n
6. Next you need to allow postgres user on server2 and server3 can access each other without password. Execute ssh-keygen command as postgres and append the contents of \/home\/postgres\/.ssh\/id_rsa.pub to \/home\/postgres\/.ssh\/authorized_keys of other server. After this we recommend to test the setting by executing ls command via ssh, for example.<\/p>\n
$ ssh server2 ls<\/pre>\n
Installing pgPool II<\/h3>\n
1. Run the below commands in the server1 postgres server,<\/p>\n
$ tar xfz \/etc\/postgresql\/pgpool-II-3.3.3.tar.gz\n$ cd pgpool-II-3.3.3\n$ .\/configure\n$ make\n$ sudo make install\n$ cp \/etc\/postgresql\/\/pgpool-II-3.3.3\/install-functions.sh\n$ sh install-functions.sh<\/pre>\n
2. Next you need to install pgpool-II configuration files onto server1. The main configuration file is pgpool.conf. The other one is the pcp.conf. You will need to execute followings as root.<\/p>\n
$ cp \/etc\/postgresql\/pgpool-II-3.3.3\/pgpool.conf \/usr\/local\/etc\n$ chown apache \/usr\/local\/etc\/pgpool.conf\n$ cp \/etc\/postgresql\/pgpool-II-3.3.3\/pcp.conf \/usr\/local\/etc\n$ chown apache \/usr\/local\/etc\/pcp.conf<\/pre>\n
3. Install basebackup.sh and pgpool_remote_start, necessary for online recovery onto server2 and server3. Note that in pgpool_remote_start the path to pg_ctl command is specified. You might want to change it to an appropriate path according to your PostgreSQL installation.<\/p>\n
$ cp \/etc\/postgresql\/pgpool-II-3.3.3\/baseback.sh \/home\/postgres\/data\n$ chmod 755 basebackup.sh\n$ cp \/etc\/postgresql\/pgpool-II-3.3.3\/pgpool_remote_start \/home\/postgres\/data\n$ chmod 755 pgpool_remote_start<\/pre>\n
4. Install failover.sh for automatic failover onto server1.<\/p>\n
$ sudo cp \/etc\/postgresql\/pgpool-II-3.3.3\/failover.sh \/usr\/local\/etc\n$ chmod 755 failover.sh<\/pre>\n
5. Create neccessary directories on server2 and server3. Execute followings as root.<\/p>\n
$ mkdir \/var\/run\/pgpool\n$ chown apache \/var\/run\/pgpool\n$ mkdir \/var\/log\/pgpool\n$ chown apache \/var\/log\/pgpool\n$ mkdir \/var\/log\/pgpool\/trigger\n$ chown postgres \/var\/log\/pgpool\/trigger<\/pre>\n
6. Add \/var\/www\/.ssh\/id_rsa.pub to \/home\/postggres\/.ssh\/authorized_keys on server2 and server3. After this we recommend to test the setting by executing ls command via ssh, for example do followings as apache user.<\/p>\n
$ ssh postgres@server2 ls\n$ ssh postgres@server3 ls<\/pre>\n
Installing pgpoolAdmin<\/h3>\n
1. Login to pgpoolAdmin and start pgpool-II from “pgpool status” menu. You see server2 port 5432 PostgreSQL is running as a primary server. You should be able to connect to server3 port 5432 by using psql. Let’s try to create a table.<\/p>\n
$ cd \/var\/www\/html\/pgpoolAdmin-3.3.1\n$ chmod 777 templates_c\n$ chown apache conf\/pgmgt.conf.php\n$ chmod 644 conf\/pgmgt.conf.php\n\n$ createdb -h server1 test\n$ psql -h server1 test\ntest=# create table t1(i int);\nCREATE TABLE\ntest=#<\/pre>\n
2. Starting standby server.To start standby server, just click “Recovery” button. “basebackup.sh” will be executed and the standby server will be automatically started.<\/p>\n
$ insert into t1 via pgpool-II.\n-- it will be executed on primary server\n\npsql -h server1 test\ntest=# insert into t1 values(1);\ntest=# \\q\n\npsql -h server3 test\n-- now connected to standby server\ntest=# select * from t1;\ni\n---\n1\n(1 row)<\/pre>\n
3. If standby goes down, it is disconnected from pgpool-II. Users can issue SQL via pgpool-II as usual. Just streaming replication is stopped. To recover standby server, click “Recovery” button.<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL Installation and Configuration on Single Node and Multi Node Clustering for Load Balancing, Database deployment, cluster control fail over.<\/p>\n","protected":false},"author":23,"featured_media":2320,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[424],"tags":[556,27],"class_list":["post-2219","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-pentaho","tag-pentaho-and-postgresql","tag-postgresql"],"acf":[],"_links":{"self":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts\/2219","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/users\/23"}],"replies":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/comments?post=2219"}],"version-history":[{"count":1,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts\/2219\/revisions"}],"predecessor-version":[{"id":11358,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/posts\/2219\/revisions\/11358"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/media\/2320"}],"wp:attachment":[{"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/media?parent=2219"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/categories?post=2219"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tenthplanet.in\/blogs\/wp-json\/wp\/v2\/tags?post=2219"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}