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:
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’;
postgres=# grant all on database marydb to mary;
postgres=# alter database marydb owner to mary;
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.