How to Install PostgreSQL 9.3 on Oracle Linux

For whom are not familiar with PostgreSQL, it is an open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. Like all other enterprise companies, we use different databases beside Oracle to lower the costs. We prefer PostgreSQL instead of Microsoft SQL Server, because it supports PL/SQL for some degree.

In this blog post, I’ll try to show how to install PostgreSQL using YUM. YUM (Yellowdog Updater, Modified) is an open-source command-line package-management utility for Linux operating systems using the RPM Package Manager. It handles library dependencies, so it’s the easiest and maybe the best way to install any RPMs. In this blog post, I’ll install PostgreSQL on Oracle Linux because I use Oracle Linux when I need a Linux OS, and its support costs are lower than Redhat Enterprise Linux. In next weeks, I’m also gonna blog about backup and replication for PostgreSQL, and maybe (not promising) blog about some key concepts of PostgreSQL such as MVCC, vacuum and vacuum freeze.

I assume that you can install Redhat or Oracle Linux. You may read my old post about installing Oracle Linux, hopefully it would be useful. Anyway, if our Linux server is ready, we can download and install Repository RPM for our server. Visit http://yum.postgresql.org.

postgresdownload

Because 9.4 is still beta, I’ll install 9.3. So I click the link of 9.3 series, and find the link for “Oracle Enterprise Linux 6 – x86_64” (my distro is 64bit). Instead of downloading it to my desktop, I’ll copy the link and download it on my Linux server. I connect my server using SSH as root user, and run the following commands:

I shortened the URL to make it fit on page. You should use the link you get from the website. When this package is installed, it will add YUM repository of postgresql and gpg key (so yum can validate packages). Let’s check if new YUM repository working:

If you see the packages from pgdg93 repository, you can use YUM to install PostgreSQL by issuing the following command (as root):

A little reminder, we’ll use root user to install packages, create directories etc, and then we’ll switch to postgres user (it’s created by postgressql package) and complete the initialization of database server. I also install postgresql93-contrib because it provides some useful utilities such as pgbench, pg_upgrade, pg_archivecleanup and some extensions. After you install PostgreSQL, let’s create a directory for our databases and change ownership of the folder to postgres user, and change the password of postgres user:

Now we can switch to postgres user to initialize and start our PostgreSQL service. Initialization needs to be done only for one time on the server. It will create the configuration, information schema (metadata) and folders for PostgreSQL.

Now PostgreSQL server is ready to start. Before we start it, let’s modify .bash_profile file (in postgres home folder) and make it similar to the below:

Now let’s start the server. If you declared the PATH and PGDATA variables, you can just start it with “pg_ctl start”. PG_CTL is the tool you need to use to stop, start, reload (to read configuration changes) PostgreSQL server. If you didn’t declare the variables yet, run the following command to start the server:

It will show you a warning about logging and it will look like it will not get back to command line. But in fact it just doesn’t show the command line prompt. Press enter again to see the prompt. Let’s check the processes:

Everything seems OK. Let’s create a test database and login it via PSQL. PSQL is a PostgreSQL client similar to Oracle SQL/Plus and it provides some useful meta-commands in addition to run SQL commands. The -d parameter says which database we want to connect. We can create databases using psql or command line tool “createdb”:

Now we have a working PostgreSQL server to explore. When you connect to PostgreSQL using PSQL, you can get help for SQL commands by typing \h (or \help). You can also get help for meta-commands by typing \?. Most people prefer GUI for running queries, checking tables etc. Although there are several GUI based clients for PostgreSQL, I recommend you to use pgAdmin. It runs on Linux, Mac OS X and Windows. If you’ll connect this server from a remote client, you need to make sure that PostgreSQL listens the network (not only localhost), so go to our database directory, edit postgresql.conf file and restart the server:

You also need to give permissions to the IP address of your computer, so you need to edit pg_hba.conf:

This will let us connect to our server from 10.0.0.1 without password. You can read more about pg_hba.conf and client authentication in PostgreSQL documents.

I hope this helps.

Please share this post Share on Facebook6Share on Google+0Share on LinkedIn26Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database architect who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

3 Comments

  1. Ariel D.

    Hi thanks you for this guide.

    There is one thing missing which is to startup the database automatically upon reboot. Im using Oracle Linux 7.1.

    Please help.

    • Giovani

      You can put the following command in the file /etc/rc.local:

      su – postgres -c “/usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data/”

      You must give permission to execute as below:

      chmod +x /etc/rc.d/rc.local

Leave Comment

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