CentOS Install Postgres 9.3

Postgresql is a great SQL database with lots of great features that you can install for free with just a few quick tasks.  First, we need to get the yum repo from postgresql website, use yum to install it and then a few cleanup tasks afterwards.

Download the Postgresql Repository

The repository for postgresql makes installing their database much easier.  Download the Postgres 9.3 repository and install it using rpm using the following steps:

# wget http://yum.postgresql.org/9.3/redhat/rhel-6.4-x86_64/pgdg-centos93-9.3-1.noarch.rpm
rpm -ivh ./pgdg-centos91-9.1-4.noarch.rpm


Use Yum to Install Postgresql 9.3

# yum list postgres* | grep server
postgresql93-server.x86_64                 9.3.1-1PGDG.rhel6             @pgdg93# yum install postgresql93-server.x86_64

That should install postgresql into the /var/lib/pgsql directory.  If you wish to move the directory where your postgresql files are stored, now is your chance.  If you wish to move the files, here’s how:

# cd /var/lib# mv pgsql /data

Next, to continue changing the install directory of postgresql, edit the /etc/init.d/postgresql-9.3 file and change these so that they point to the directory where you want to move the postgresql files:

PGDATA=/data/pgsql/9.3/data
PGLOG=/data/pgsql/9.3/pgstartup.log

Now, it is time to Initialize the database and start it up.

Initialize the Postgresql Database and Then Start the Database

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

The database should be started up now and should be set to startup at boot time.

Configure the Postgresql Database and User Access

After the Postgresql database is installed, you will want to configure a database as well as access and other configuration options.

Create a Postgresql database and User Role

Login to the operating system as the postgres user the do the following:

postgres=# create database marydb;
CREATE DATABASEpostgres=# create user mary  password ‘marypassword’;
CREATE ROLE
postgres=# grant all on database marydb to mary;
GRANT
postgres=# alter database marydb owner to mary;
ALTER DATABASE

The above commands create a database named mary, a user named mary and then grants permissions and database ownership of database mary to the user named mary.

Allow Postgresql to Listen on All Network Interfaces

Edit the postgresql.conf and change this line:

listen_addresses = ‘localhost’

to this line:

listen_addresses = ‘*’

which will allow the postgresql database to listen on all of the network interfaces on the machine.  We need to restart the postgresql database to make this take effect, but we will wait until after the next step because it also needs a restart to take effect.

Configure pg_hba.conf File

Although we created a user to login to postgresql and set the database to listen on all network interfaces, we still need to configure pg_hba.conf file to allow access to the database for the user we want to use from the location that we want to use it.  The pg_hba.conf file looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD# “local” is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all             0.0.0.0/0            md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

If we want to login as the mary user from ip address 10.11.12.13, using our md5 password, we would add a line like the following:

host    mary             mary             10.11.12.13/32            md5

After adding the above line to the pg_hba.conf file, we restart the database

# service postgresql-9.3 restart
Stopping postgresql-9.3 service:                           [  OK  ]
Starting postgresql-9.3 service:                           [  OK  ]

That should be it.  We installed postgresql 9.3, moved the database files to another location, initialized the database, set it to startup at boot time, created a database, a user, granted permissions, set the database to listen on all network interfaces, and configured the pg_hba.conf file to allow access for the user from her ip address.

The following two tabs change content below.
Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: http://uptimemadeeasy.com. You can find Jeff on or LinkedIn at: LinkedIn or Twitter at: Twitter

Latest posts by Jeff Staten (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *