Simply Install: PostgreSQL

Sriram Baskaran

·

Follow

Published in

Insight

·

4 min read

·

Apr 5, 2019

--

1

Listen

Share

Simply Install is a series of blogs covering installation instructions for simple tools related to data engineering. This blog covers basic steps to install and configure PostgreSQL (a popular relational database) and expose it as a service. To provide consistent installation, all instructions are written after testing on Ubuntu 18.04.

Installing PostgreSQL

I use a m5a.large instance in AWS with Ubuntu 18.04 AMI with 20G of disk space. We can directly install PostgreSQL using apt-get.

ubuntu@ip-10-0-0-13:$ sudo apt update
ubuntu@ip-10-0-0-13:$ sudo apt install postgresql postgresql-contrib

PostgreSQL is installed as a service, configured at /etc/init.d. It can be accessed using the service command.

ubuntu@ip-10-0-0-13:$ sudo service postgresql [start|stop|restart|status]

When you install this you automatically setup a superuser postgres and a database postgres associated with that user. Currently, PostgreSQL is set up as a peer authentication, which means it uses client OS’s user profile to authenticate the user. In order to access the database, we need to log in as that user. By default, the installation creates a new OS user postgres inside the Ubuntu OS.

First step, we log into postgres user.

ubuntu@ip-10-0-0-13:$ sudo -u postgres -i

PostgreSQL provides a command line interface psql to access the tables in the database.

postgres@ip-10-0-0-13:$ psql 
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1))
Type "help" for help.postgres=#

Here is a detailed list of psql command options. The most common options are listed below.

-U username (default = postgres)
-W access with password (default = none)
-h hostname (default = localhost)
-p port (default = 5432)
-d databases name (default = postgres)

Here is a visual representation of what happened here.

We have a running database that allows connections from a user postgres.

Configuration files: Setup as a service

All configuration files are stored at the following location:

/etc/postgresql/10/main

postgresql.conf and pg_hba.conf are most relevant to us for configuring the database. Have a rough estimate of the number of parallel connections that you need to create.

#postgresql.conf
#listen_addresses = 'localhost' # what IP address(es) to listen on;
                                # comma-separated list of addresses;
                                # defaults to 'localhost'; 
                                # use '*' for all
                                # (change requires restart)
port = 5432                     # (change requires restart)
max_connections = 100           # (change requires restart)
...

Best practice: Change the default port number.

Changes to port and max_connections require a restart (using the service command).

In order to expose it as a service, “uncomment” the line listen_address and set the value to *. Any changes to listen_address also requires a restart.

listen_addresses = '*'

If you are setting it in a production setup, you can have additional security by specifying a comma-separated list of IP-addresses instead of allowing all connections using ‘*’.

pg_hba.conf contains control over which hosts are allowed to connect, how clients are authenticated, which PostgreSQL user names they can use, and which databases they can access. Records take one of these forms:

# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

By default, the superuser postgres can be used only by peer authentication. To expose it as an external service and allow client connections to authenticate using a password, add the following line to pg_hba.conf. Refer to the documentation for more details and info on authentication methods that postgres offers to each client.

 host    <database>      <user>       0.0.0.0/0        md5

Best practice: To ensure secure setup, specify a database, user and a source IP.

Recommended next steps

Create a new user with a password for application access

To let an external application like a web-app access postgres, we need to create a user (with appropriate permissions).

Users are also called as Roles in Postgres

postgres=# CREATE USER db_select WITH PASSWORD '<setpassword>';

Grant permissions using the GRANT command. Permissions can be granted only at a schema level or at a relation level.

This setup installs a PostgreSQL on a Ubuntu machine, and configures it as a service that can be accessed using clients written in Python (like psycopg2), Java (PostgreSQL JDBC) or pgAdmin. Thoughts, questions or comments are welcome.

Interested in transitioning to a career in Data Engineering or DevOps Engineering?
Find out more about the Insight Fellows Program, and sign up for program updates.