In this tutorial, we will see how to install PostgreSQL 12 on Ubuntu. We will learn PostgreSQL Database installation, configuration and create a user and grant privileges on a newly created database.
- Install PostgreSQL 12 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
- Verify the permission of user on a database in PostgreSQL Server
- Consuming the PostgreSQL server database in to Python3 Application
- 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
Install 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
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();'
q to quit and return to access the command prompt again.
Also verify the version of
psql client by running the following command.
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
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 role named windy in PostgreSQL Server
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
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'
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
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() # ('PostgreSQL 12.4 (Ubuntu 12.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit',) exit()
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