How to install PostgreSQL on CentOS 7

Post up!

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 article will assist you with installing and configuring PostgreSQL on your self-managed Linux CentOS 7 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 might be a good fit for some.

Advantages of PostgreSQL

  • An open-source SQL standard compliant RDBMS
  • Strong community
  • Strong third-party support
  • Stored procedures / extensible
  • Object-oriented

Disadvantages of PostgreSQL

  • 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, Postgres might 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. Now let’s focus on the nitty-gritty — how to install and setup PostgreSQL on your server.

In order to perform this task, you’ll need an active CentOS 7 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 7 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.

postgresql-setup initdb

5. Configure PostgreSQL to start up upon server boot.

chkconfig postgresql on

6. Start PostgreSQL.

service postgresql start

Connecting to PostgreSQL

By default, Postgres 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 PostgreSQL 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.

Image by: rhoftonphoto via Compfight cc

Chris Dean

Chris is a technical writer with a background in hosting support and server administration. He has a passion for empowering customers to succeed in the world of web hosting by educating them on the complexities of the technology in a language that anybody can understand. He is also an aspiring programmer who is currently self-teaching Ruby and Python in his spare time to advance his skills as a web professional. When not helping his customers succeed on the web, Chris enjoys Magic: The Gathering, online gaming, camping, and reading science fiction novels. Chris currently lives in the plains of Iowa with his wife – Kara.