How to install PostgreSQL on CentOS 6

Some of the more up-and-coming web development stacks such as LEPP, Gunicorn/Django, and Ruby/Rails prefer to use the PostgreSQL database platform instead of the more traditional MySQL configuration. This guide will assist you with installing and configuring PostgreSQL on your self-managed Linux CentOS 6 server.

Overview of PostgreSQL

PostgreSQL, or simply 'postgres,' is an open-source relational database management system, or RDBMS for short. Just like MySQL, SQLite, and Microsoft SQL Server, PostgreSQL uses the Structured Query Language (SQL) to manipulate data. PostgreSQL is cross-platform, and has packages for all major Linux distributions, as well as Windows Server platforms. While not as popular as MySQL, Postgres is arguably the more powerful database platform as it is truly ANSI/ISO compliant, unlike MySQL which is not due to some of its limitations. It also supports stored procedures, a feature that MySQL does not support. This isn't saying you should immediately dump MySQL for Postgres for all your applications or projects - but it may be a good fit for some.

Advantages of Postgres

An open-source SQL standard compliant RDBMS
Strong community
Strong third-party support:
Stored Procedures / Extensible
Object-Oriented

Disadvantages of Postgres

Struggles with large read-heavy operations against a database
Less popularity = less open-source documentation and collaboration
Different SQL syntax than traditional MySQL/Microsoft SQL solutions

Like many software solutions, which database system you should use depends entirely on the project at hand. However, in our research we have found that there are a few guidelines you can follow when making your decision:

If you want stronger security, compliance, stored procedures, and need a database for a strong back-end application - PostgreSQL may be the right fit for you
If you want to use the more ubiquitous platform, high security, and work mostly with website CMS platforms - MySQL is still probably the right fit for you

We highly suggest doing research on the different database platforms at their respective sites before making an informed decision. The rest of this guide will focus on the nitty-gritty - how to install and set up PostgreSQL on your server.

In order to perform this task, you’ll need an active CentOS 6 server, as well as an SSH client such as PuTTY (for Windows) or Terminal (Mac). We recommend a GoDaddy VPS if you’re just getting started, or a full dedicated server if you’re ready to take total control.

Installing PostgreSQL using YUM

By default, CentOS 6 includes PostgreSQL in the base yum repositories. This means that installing PostgreSQL will be a cinch.

1. Log into your server via SSH.

2. Access root by running the following command:

sudo su -

3. To install PostgreSQL, as well as the necessary libraries for Perl, Python and PostgreSQL server, run the following command:

yum -y install postgresql postgresql-libs postgresql-plperl postgresql-plpython postgresql-server

4. Initialize the database and data directories.

service postgresql initdb

5. Configure PostgreSQL to start up upon server boot.

chkconfig postgresql on

6. Start PostgreSQL.

service postgresql start

Connecting to PostgreSQL

By default, PostgreSQL sets up the user and database "postgres" upon a new installation. We interact with the postgres database software through an interface called "psql."

1. Log into the postgres user:

su - postgres

2. This will bring you to a new prompt. Log into the database by typing:

psql

3. You should now see a prompt for postgres=#. This means you are at a Postgres prompt. To exit the interface, you can type:

\q

From there, you can get back to root by typing:

exit

This will bring you back to the root user for your server. You now have PostgreSQL installed on your system!

You're now ready to start using PostgreSQL for your Database management. You can deploy Postgres for a variety of software solution stacks, or even configure a common application to work with Postgres if it has support for that platform. For more information on using PostgreSQL, syntax, and other utilities that are available, check out the documentation at PostgreSQL's official website.