Install PostgreSQL on CentOS 6

Install PostgreSQL on CentOS 6

Hi readers, I recently started playing with Postgresql database on CentOS machine and wanted to share the installation & set up process with you all. In this article I will show you how to install Postgresql on CentOS 6.5 machine.

PostgreSQL

From PostgreSQL site:

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

Install PostgreSQL

You can either install the packages provided by the default repositories or go for the Postgresql repository for the latest version. Here I will go with the default respository.

Install the following packages

• postgresql – includes client programs and libraries needed to access a PostgreSQL server
• postgresql-server - main server package which includes programs needed to create and run a postgresql server
• postgresql-contrib – contains contributed packages that are included in the PostgreSQL distribution

Use the following command to install all of them

# yum install postgresql postgresql-server postgresql-contrib

Installing postgresql will create a user and group named postgres. Verify it with the below commands.

# grep postgres /etc/{passwd,group}
/etc/passwd:postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
/etc/group:postgres:x:26:

Initialize Databases

We have got the server installed but before we can start using the server, the default databases needs to be initialized.

Use the following command to initialize the database

# service postgresql initdb

Set up services

By default the postgresql service will be stopped and not enabled. Check this using the below commands.

# chkconfig postgresql --list
postgresql      0:off   1:off   2:off   3:off   4:off   5:off   6:off
# service postgresql status
postmaster is stopped

Start the service and enable it on boot time using the below commands

# service postgresql start
# chkconfig postgresql on
# chkconfig postgresql --list
postgresql      0:off   1:off   2:on   3:on   4:on   5:on   6:off

Verify the service using netstat command as shown below

# netstat -nltp | grep post
tcp   0      0 0.0.0.0:5432     0.0.0.0:*    LISTEN      2022/postmaster
tcp   0      0 :::5432          :::*         LISTEN      2022/postmaster

Lets play with the database

First we will switch to postgres user account to connect to the database.

# su - postgres

Now type psql to connect to the postgresql server

[postgres@cent1: ~] $ psql
psql (8.4.20)
Type "help" for help.

postgres=#

We are now within psql prompt. Lets try some commands.

Create a database:

postgres=# create database demodb;

New database is created and you can list all databases using the \l command. For help type \? to see list of commands.

postgres=# \l

Note: You can also create database from the terminal using createdb command without logging into psql prompt. Check man createdb for more information

Create a user/role:

postgres=# create role testuser;

This will just create a new role but won’t allow login and doesn’t have a password. Check list of users with \du command

postgres=# \du

To create a user with login rights and password set, use below form

postgres=# create role testuser login password 'secret-password';

Note: You can also create a new user from the terminal using the createuser command without logging into psql prompt. Check man createuser for more information

Changing user password

You can use the \password command to change user password.

postgres=# \password testuser
Enter new password:
Enter it again:
postgres=#

Simple tweak

To be able to log into postgresql server using the new user/role that we created earlier, we also need the equivalent unix system account. So you need to create a new user account named testuser.

There is a workaround or alternate way of logging into postgresql server without having system user account. Use the below command instead.

# psql -U postgres -h localhost -W

You will see an error. Don’t panic. There is one more step to do. Open /var/lib/pgsql/data/pg_hba.conf

# vi /var/lib/pgsql/data/pg_hba.conf

Look for the below lines

local   all      all                    ident
host    all      all    127.0.0.1/32    ident
host    all      all    ::1/128         ident

Change ident to trust on all these lines so that it looks like below.

local   all      all                    trust
host    all      all    127.0.0.1/32    trust
host    all      all    ::1/128         trust

Restart the postgresql server

# service postgresql restart

Now you should be able to use the above command to login.

Enable remote connections

By default the postgresql server will listen on 127.0.0.1 and will only accept connections from localhost. To enable remote connections, you need to do few configuration changes.

Step #1: Update postgresql.conf

Find the main configuration file and open it for editing using the below command

# vi $(find / -iname postgresql.conf)

Look for the below line

#listen_addresses = 'localhost'

Uncomment and change it as follows

listen_addresses = '*'

Step #2: Update pg_hba.conf

Find and open this file for editing

# vi $(find / -iname pg_hba.conf)

Append the following line to it. Change the network address range as per your need.

host    all     all     192.168.122.0/24    trust

Step #3: Adjust firewall rules

If you want to access this postgresql server from other machines and you have firewall enabled,  you need to add the following rule to allow connections.

# iptables -I INPUT -m tcp -p tcp --dport 5432 -j ACCEPT

Step #4: Restart services

# service iptables save
# service postgresql restart

Now from any of the client machine you can access this postgresql server using the following command. Note that you need to install postgresql to be able to use psql command on client machines.

# psql -U postgres -h [postgresql-server-ip] -W

That’s it for this tutorial. Thanks for reading.