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

which
--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
make


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             127.0.0.1/32            trust
         to whatever your client's ip address is. Or you can say the whole subnet. In this case:
           host    all             all             192.168.0.2/24          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 = '192.168.0.1'
  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 192.168.0.1 -U postgres -d testdb
           which
           -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