Tuesday, August 27, 2013

Installing PostgreSQL Database Server/Client on RedHat Linux Families (RedHat,Fedora,CentOS,SELinux)

Hello everyone,

Today, I would like to talk about a very very good open source Database Server called PostgreSQL. Recently, I've started to work with Postgresql and really love it. I would say it has all features of commercial databases such as DB2 and Oracle and even more. Some of core features are:

  1.  Object-Relational DBMS
  2.  Capable of handling complex routines and rules
  3.  Declarative SQL queries
  4.  Multi version concurrency control
  5.  Multi user support
  6.  Transactions
  7.  Query optimization
  8.  Inheritance
  9.  Arrays
  10.  Highly extensible
  11. Comprehensive SQL support (support SQL99, SQL92)
  12. Referential integrity (insure the validity of database's data)
  13. Flexible API (so many vendors such as Object Pascal, Python, Perl, PHP, ODBC, Java/JDBC, Ruby, TCL, C/C++, and Pike have deployment support for PostgreSQL RDBMS
  14. Procedural Languages (it supports internal procedural native language called PL/pgSQL, which comparable to the Oracle procedural language PL/SQL, and also it has ability to use Perl, Python, and/or TCL as an embedded procedural language)
  15. MVCC (Multi-version Concurrency Control is the technology that Posrgresql uses to avoid unnecessary locking)
  16. Server/Client
  17. Write Ahead Logging (WAL), ability to write the changes to log file before writing to database.(In case of unlikely crash, there will be a record of transaction to restore)

Installing PostgreSQL

The following shows how to install PostgreSQL from source code. Although you could install PostgreSQL server and client easily with yum command (yum install postgresql-server postgresql-client), I would recommend to install from source code because it is so flexible to adding/removing features and to customize it even after compiling source code. For example, you can add more features to Postgresql by reconfiguring and compiling the source code again without losing your data and databases.

1. Installing the required/optional packages:

yum install gcc make kernel-devel perl-ExtUtils-MakeMaker perl-ExtUtils-Embed readline-devel zlib-devel openssl-devel pam-devel libxml2-devel openldap-devel tcl-devel python-devel flex bison

2. Download the source code from command line: (PostgreSQL-9.2.4 is the current stable version at the time of writing this)

wget http://ftp.postgresql.org/pub/source/v9.2.4/postgresql-9.2.4.tar.gz

3. Creating the "postgres" user:
It is always a good idea to create a PostgreSQL superuser to own and manage the PostgreSQL database files rather than using "root" account as the PostgreSQL superuser because of security purposes. This user can be named anything and I named it "posrgres":

su -     --> switch to root account 
useradd postgres  --> create user
passwd postgres   --> set password

                                                                     Figure 1

4. Move and unpack the Postgresql source package:

cp postgresql-9.2.4.tar.gz /usr/local/src/
cd /usr/local/src/
tar -xzvf postgresql-9.2.4.tar.gz

5. Grant the ownership of the Postgresql source directory to "postgres" user. It enables you to compile PostgreSQL as the "postgres" user.

chown -R postgres.postgres postgresql-9.2.4

6. Configuring the source.Now, switch to postgresql-9.2.4 directory:

cd postgresql-9.2.4
and run
./configure --help
to see all available options to customize your PostgreSQL

                                                                             Figure 2

It's pretty self explanatory. For our purpose, I am going to use the options below and leave other options as default:

./configure --mandir=/usr/local/pgsql/man --with-tcl --with-perl --with-python --with-pam --with-ldap --with-openssl --with-libxml

--mandir=DIR     is       man documentation [DATAROOTDIR/man]
--with-tcl             is       build Tcl modules (PL/Tcl); if you plan to use pl/Tcl procedural language
--with-perl           is       build Perl modules (PL/Perl); if you plan to use pl/Perl procedural language
--with-python      is       build Python modules (PL/Python); if you plan to use pl/Python procedural language
--with-pam          is       build with PAM support
--with-ldap          is       build with LDAP support
--with-openssl     is       build with OpenSSL support
--with-libxml      is       build with XML support

7. Now, run the "make" command after switching to "postgres" user:

su postgres

After compiling source, you should see the following message:
"All of PostgreSQL successfully made. Ready to install."

                                                                               Figure 3

8. We need to do regression test. This is optional but really recommended it.

make check

                                                                              Figure 4

                                                                              Figure 5

8. Now, you need to install compiled programs and libraries and "su -" command save your time to log in as root user for command's execution:

su -c "make install"

                                                                              Figure 6

Don't forget to change the owner of PostgreSQL installation directory, in this case /usr/local/pgsql, to "postgres" user:

su -c "chown -R postgres.postgres /usr/local/pgsql"

9. Then, install documentation:

su -c "make install-docs"

                                                                             Figure 7

10. Next, we need to set environment variables. I am going to set environment variables for man page and bin directory. In order to do that, add the following lines to the end of /etc/profile

echo 'PATH=$PATH:/usr/local/pgsql/bin' >> /etc/profile
echo 'MANPATH=$MANPATH:/usr/local/pgsql/bin' >> /etc/profile
echo 'export PATH MANPATH' >> /etc/profile

Don't forget to log out and log in again to take effect the new variables.
Now try "man psql"

                                                                               Figure 8

11. Now, we need to initialize and start PostgreSQL. Make sure you logged in as postgres user. Then run the following command:

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

The path after the -D option is up to you. You can put any path BUT make sure on that path the user "postgres" has write access on it.

                                                                            Figure 9

12. To start the database server in the background, run the following command:

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /tmp/logfile-pgsql.log start

to make sure that server is running, use the following commands:

cat /tmp/logfile-pgsql.log
netstat -antp

                                                                           Figure 10

13. Next, we need to configure PostgreSQL in SysV Script so that we can gracefully control PostgreSQL database though the use of SysV runlevel system. In order to do that, we need to copy a script called "linux" to init.d directory. I also renamed it to "postgresql" to be more meaningful. Run the following commands:

su -c "cp /usr/local/src/postgresql-9.2.4/contrib/start-scripts/linux /etc/rc.d/init.d/postgresql"
su -c "chmod a+x /etc/rc.d/init.d/postgresql"   
--> make the script executable

If you wish for the script to startup PostgreSQL automatically when the machine boots up, run the following command:

su -c "chkconfig --add postgresql"

                                                                            Figure 11

Now, to start and stop PostgreSQL, run the following commands:

service postgresql stop
service postgresql start

                                                                            Figure 12

14. Let's create a test database, we need it when we want to try to connect from client side(another machine) to database server:

createdb testdb
psql testdb

                                                                             Figure 13

15. In order to connect to database server through network from client side, we need to do the followings:

  1.   Insatll PostgreSQL Client with yum command in the client machine(it's different than server machine):
           yum install postgresql-client 
  2. Go back to server, and open pg_hba.conf in vi:
           vi /usr/local/pgsql/data/pg_hba.conf
         then, change this line:
           host    all             all               trust
         to whatever your client's ip address is. Or you can say the whole subnet. In this case:
           host    all             all             trust
  3. Next, open postgresql.conf in vi:
           vi /usr/local/pgsql/data/postgresql.conf
         and uncomment this line:
          #listen_addresses = 'localhost'
         and change 'localhost' to the ip address of server, in this case:
          listen_addresses = ''
  4. Open the PostgreSQL server port, run the below command:
          su -c "iptables -I INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT"
  5.  Restart postgresql service:
          service postgresql stop
          service postgresql start
  6. Finally, try to connect to server by the following command in clinet machine:
          psql -h -U postgres -d testdb
           -h means host
           -U means user
           -d means database name

                                                                      Figure 14

And that's all. I am going to post more blog about PostgreSQL since I've been liked it so far. Hope you enjoyed.

Khosro Taraghi


  1. Waoou... What a tutorial you made? I think any body will be clear after seen this. Just not only looking nice , also
    more helpful.
    Emergency Exchange Support

  2. nice post. I will try this and I hope it help me. Thanks.

  3. This is excellent tutorial. It helped to setup Postgres on deferent versions of Linuxes.

  4. Well explained the matter and you can find more relevant content on the same here below.

    Reading data from CSV file and writing it into the PostgreSQl database in C++

  5. The most effective method to comprehend if Cassandra "Hub Tool Status" flops through DB Installation Support
    Scanning for Cassandra Support if hub apparatus status is falling flat? Or on the other hand if some other issue in your Cassandra Database. Wow at Cognegic your pursuit is closes here in light of the fact that we give DB Configuration Support or Database Administration Tool Support to take care of this above issue. Here you will get DB Installation and Configuration Support through completely prepared and talented specialists who just point is to give top help to their clients. Our help master's runs profound and in addition wide to expel your specialized hiccups.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

  6. Look at with Cognegic's Relational Database Service to illuminate your Postgres Issues

    The encoding is a champion among the most no doubt in the world got a handle on issues which every so often happen when clients begin with new PostgreSQL Setup. For single, get a handle on this issue isn't a spoon reinforcing errand; here you need to incite with most committed and the star gathering of bosses who comprehend this Postgres encoding issue. Engage us to let you know, the Cognegic's Postgres SQL Support for Windows is one the best stages who investigate this sort of screw up and raise you hellfire free. Dial this number 1-800-450-8670 and bearing with Postgres SQL Support for Linux and PostgreSQL Remote Database Service.

    For More Info: https://cognegicsystems.com/

    Contact Number: 1-800-450-8670

    Email Address-info@cognegicsystems.com

    Company's Address-507 Copper Square Drive Bethel Connecticut (USA) 06801

  7. Unfit to Start Postgres After Changing to Nologin? Contact to Postgres SQL Support for Windows

    There might be where you find that your Postgres database can't begin as a result of some specialized issues. Be that as it may, with Cognegic's PostgreSQL Remote Database Service or Postgres SQL Support for Linux. In the event that you found that you Postgres Database is running gradually or neglecting to serve questions for any reason at that point don't squander your valuable time in scanning for another help supplier. Basically contact our specialized specialists and get the most confided in help which you never found in some other help.

    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801