How to Install and Connect to PostgreSQL on CentOS 7

PostgreSQL (pronounced ‘post-gres-Q-L’) is a free, open-source object-relational database management system (object-RDBMS), similar to MySQL, and is standards-compliant and extensible. It is commonly used as a back-end for web and mobile applications. PostgreSQL, or ‘Postgres’ as it is nicknamed, adopts the ANSI/ISO SQL standards together, with the revisions.

• These instructions are intended specifically for installing PostgreSQL on CentOS 7.
• I’ll be working from a Liquid Web Self Managed CentOS 7 server, and I’ll be logged in as root.

In this case we want to install PostgreSQL 9.3 directly from the Postgres repository. Let’s add that repo:

rpm -iUvh http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-1.noarch.rpm

First, you’ll follow a simple best practice: ensuring the list of available packages is up to date before installing anything new.

yum -y update

Then it’s a matter of just running one command for installation via apt-get:

yum -y install postgresql93 postgresql93-server postgresql93-contrib postgresql93-libs

PostgreSQL should now be installed.

Configure Postgres to start when the server boots:

systemctl enable postgresql-9.3

Start Postgres:

/usr/pgsql-9.3/bin/postgresql93-setup initdb

systemctl start postgresql-9.3

As part of the installation Postgres adds the system user postgres and is setup to use “ident” authentication. Roles internal to Postgres (which are similar to users) match with a system user account.

Let’s switch into that system user:

su – postgres

And then connect to the PostgreSQL terminal (in the postgres role):

psql

That’s it! You’re connected and ready to run commands in PostgreSQL as the postgres role. In future (Sept 2014) articles we’ll delve into creating new roles, changing a role’s password, creating/deleting databases, backups, etc.