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:
- Object-Relational DBMS
- Capable of handling complex routines and rules
- Declarative SQL queries
- Multi version concurrency control
- Multi user support
- Transactions
- Query optimization
- Inheritance
- Arrays
- Highly extensible
- Comprehensive SQL support (support SQL99, SQL92)
- Referential integrity (insure the validity of database's data)
- 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
- 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)
- MVCC (Multi-version Concurrency Control is the technology that Posrgresql uses to avoid unnecessary locking)
- Server/Client
- 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
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
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."
8. We need to do regression test. This is optional but really recommended it.
make check
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"
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"
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"
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.
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
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"
Now, to start and stop PostgreSQL, run the following commands:
service postgresql stop
service postgresql start
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
15. In order to connect to database server through network from client side, we need to do the followings:
- Insatll PostgreSQL Client with yum command in the client machine(it's different than server machine):
yum install postgresql-client - 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 - 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' - 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" - Restart postgresql service:
service postgresql stop
service postgresql start - 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
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
