Install PostgreSQL 12 on Ubuntu

In this tutorial, we will see how to install PostgreSQL 12 on Ubuntu. We will cover installation, configuration and create a user and grant privileges on a newly created database.

Table of Contents

• Prerequisites
• Installing PostgreSQL 12 on Ubuntu
  • Verify PostgreSQL 12 Installation on Ubuntu
• Configure PostgreSQL 12 Database Server on Ubuntu
• Create user and database in PostgreSQL
  • Create a database in PostgreSQL Server
  • Create an User role in PostgreSQL Server
  • Grant User all the privileges on Database in PostgreSQL server
• Consuming the PostgreSQL server database in to Python3 Application
  • Install python3-psycopg2 driver
  • Connect and Query to PostgreSQL Database from Python
• Summary

Prerequisites

• User with sudo privileges to access Ubuntu OS
• SSH access to Command Terminal via Putty of the Ubuntu OS in case of remote VM
• Internet Connection
• Knowledge of shell scripting command (not mandatory)

First of all you need to be able to access the OS either via GUI by logging in as a SUDO user. Or you should be able to SSH to the Ubuntu OS.

For production facing deployments the GUI is not enabled as it is not required, so you at least should be able to SSH in to the box as a SUDO user. Once you are able to successfully access the terminal, run the following commands to install postgresql-12.

Installing PostgreSQL 12 on Ubuntu

Run the following command to create the file repository configuration:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Import the repository signing key by running the following command

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update the package lists apt a.k.a Advanced Packaging Tool by running the following command

sudo apt update

Install the latest version of PostgreSQL. If you want a specific version, use postgresql-12 or similar instead of postgresql:

sudo apt -y install postgresql-12

Verify PostgreSQL 12 Installation on Ubuntu

Verify the installation to see if the PostgreSQL database service is up and running successfully. Run the following command to see PostgreSQL server’s status if its active.

systemctl status postgresql

Verify the version of PostgreSQL server by typing the following command

sudo -u postgres psql -c 'select version();'

Type q to quit and return to access the command prompt again.

Also verify the version of psql client by running the following command.

psql -V

Configure PostgreSQL 12 Database Server on Ubuntu

All the PostgreSQL configuration files are located in the /etc/postgresql/12/main/postgresql.conf file. You can play with various settings for logs, log rotations, timezones, max connections by altering the values in postgresql.conf.

The pg_hba.conf is located in /etc/postgresql/12/main/. We won’t be changing any of these files but we are interested in knowing that these files will allow you to custom configure the installation of PostgreSQL database server.

Also it is to be noteworthy to understand that a reload of database server is required that makes any changes are effective. This can be done by running the following command

sudo systemctl daemon-reload
sudo systemctl restart postgresql

Create user and database in PostgreSQL

Once we successfully install PostgreSQL 12 on Ubuntu, a default user for PostgreSQL is created with the username postgres without any password. It is recommended not to enable password for this user. Because this user will have the default access privileges to all the default databases, templates in the PostgreSQL server. It is recommended to create a new user and grant privileges as needed for the databases that we consume.

Now we will proceed with creating a user named windy in PostgreSQL. We will then create a database named tutlinksdb. Finally we grant all privileges on database to the user that we created.

Create a database in PostgreSQL Server

Create a database named tutlinksdb in PostgreSQL server

sudo su - postgres -c "createdb tutlinksdb"

Create an User role in PostgreSQL Server

Now, create an user named windy

sudo su - postgres -c "createuser -P -s -e windy"
# Enter password for new role: 
# Enter it again:

sudo -u postgres runs the commands in the context of the user postgres that is created when we install PostgreSQL.

Grant User all the privileges on Database in PostgreSQL server

Now that we have created a new user with password and a new database, lets grant the user to perform all the operations possible on the database. To do that, Activate the psql command terminal interface for PostgreSQL by running the following command.

sudo -u postgres psql

Up on successful activation of psql command prompt, the command line in the terminal will be prefixed with postgresql=#.

Now run the following command to provide all the permissions for the user windy on the database tutlinksdb

grant all privileges on database tutlinksdb to windy;

At anytime to switch back or exit out of the psql command line interface type \q as shown below and hit return.

# exit from psql prompt
\q

Verify the permission of user on a database in PostgreSQL Server To check the permissions or grant of user on a database, run the command \l to show list of databases and see the database that you want to verify has access to the specific user you are looking for.

sudo -u postgres psql -c '\l'

Output:

                              List of databases
    Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
------------+----------+----------+---------+---------+-----------------------
 postgres   | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
            |          |          |         |         | postgres=CTc/postgres
 template1  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
            |          |          |         |         | postgres=CTc/postgres
 tutlinksdb | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +
            |          |          |         |         | postgres=CTc/postgres+
            |          |          |         |         | windy=CTc/postgres
(4 rows)

You can notice that the user windy has been in the list of users in the list of Access privileges column for the tutlinksdb

We are done with the administrative set up of installing PostgreSQL. We saw how to create a new password enabled user in PostgreSQL server and created a new database.

We also understood how to grant all privileges to a user on a database and verified it in psql.

Consuming the PostgreSQL server database in to Python3 Application

Install python3-psycopg2 driver

Now we will connect to this database from any application let’s say from Python programming language. We will see how a Python programing will establish the connection to the database tutlinksdb and creates a table.

By default Python3.6.9 is available on Ubuntu Operating System. We need a module name psycopg2 which drives the connection from Python program to the PostgreSQL server. Run the following command to install psycopg2 for python3

sudo apt install -y python3-psycopg2

Connect and Query to PostgreSQL Database from Python

Switch to Python from the terminal by typing the command python3 and hit enter

python3

You should see something like below which indicates the Python3 terminal is activated.

Python 3.6.9 (default, Jul 17 2020, 12:50:27)
[GCC 8.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.

Now import the psycopg2 module, establish connection to the PostgreSQL server database tutlinksdb by providing the credentials of the user windy.

import psycopg2
con = psycopg2.connect(database="tutlinksdb", user="windy", password="windy", host="127.0.0.1", port="5432")
cur = con.cursor()
cur.execute("select version();")
cur.fetchone()

exit()

Summary

Hope you are able to successfully install PostgreSQL 12 on Ubuntu. To summarize we understood the following:

• Install PostgreSQL 12 on Ubuntu
• Verified the installation and other services related to PostgreSQL database server
• Configuring the Installation of PostgreSQL 12 on Ubuntu
• Created a new database in PostgreSQL
• Created a new database user and granted privileges on a database in PostgreSQL
• Connected to PostgreSQL database from Python application using python3-psycopg2 module

Congratulations 🎉, you have mastered the PostgreSQL basic administration. Bookmark (Ctrl+D) this page as a quick reference to install PostgreSQL 12 on Ubuntu.

You can continue programming and development related to PostgreSQL, so here are some references

• Implementing Async REST APIs in FastAPI with PostgreSQL CRUD
• Install PostgreSQL Without Admin Rights on Windows 10 OS