How to Install PostgreSQL on Ubuntu Linux: The Easy Way

PostgreSQL is a top ranked open source Relational Database Management System that was created in 1996 originally at the University of California, Berkeley and now developed by the PostgreSQL Development Group and licensed on the PostgreSQL License which is a permissive license similar to the MIT License.

In this article I will show you how to install and setup PostgreSQL the easy way on Ubuntu Linux. In order to use “The Easy Way”, it implies that will use the version of PostgreSQL that comes with the Ubuntu distribution and not get picky about specifying a different version. So lets get started.

Run apt-get to install postgresql package for Ubuntu as such:

sudo apt-get install postgresql

After the command completes PostgreSQL software will be installed and configured to an initial running and usable state. To verify what has been done try the following commands:

ps -ef | grep postgres

sudo su – postgres
pwd
# psql -l

Now check the output form the ps command that was done earlier and notice where is the location of the config_file. In my example the following arg was added on the command line:

-c config_file=/etc/postgresql/9.6/main/postgresql.conf

Let’s open the postgresql.conf configuration file to see what we can learn. The following interesting entries were specified which will help us understand how PostgreSQL was installed on this system:

data_directory = ‘/var/lib/postgresql/9.6/main’ # use data in another directory
# (change requires restart)
hba_file = ‘/etc/postgresql/9.6/main/pg_hba.conf’ # host-based authentication file
# (change requires restart)
port = 5432 # (change requires restart)

From the above we can see some critical directories. The data_directory is where the data we insert into the database is actually stored, we should not need to play around with that at all. The hba_file is where we will update our access permissions for new connections to the database. hba file is certainly something we will want to modify when we setup more robust security. By default password’s are used, but LDAP or Kerberoros are probably desired in a more secure setting. And the port is set to 5432, which is the standard port. If we wanted to be more secure we could modify to use a different port, but I don’t think it really helps too much anyway against sophisticated attackers.

Before making any other configurations lets do some simple queries and see the output to get a feeling for what is setup.

$ psql postgres
postgres=# SELECT * FROM pg_user;
postgres=# SELECT * FROM pg_database;

Next let us create a new user that can login to the database that is not the postgres superuser. Use the following command:

createuser -EPd sysadmin

‘E’ means store password for this user encrypted, ‘P’ means prompt now for a new password for this new user, and ‘d’ means allow the new user to create databases in the system. Now you can exit out from the linux user ‘postgres’ and from the command prompt of a regular user let us connect to the database:

psql -U sysadmin -h127.0.0.1 postgres

To make this easier to use we can set a few environment variables as shown below:

export PGUSER=sysadmin
export PGHOST=127.0.0.1

And finally before getting started, let us create a new database that we can use for our data with the createdb command:

createdb mywebstore

The command above will create a new database in the system called ‘mywebstore’ which can be used for storing your user data. And with that we have installed and setup PostgreSQL on Ubuntu “The Easy Way”.