How to Install and Use PostgreSQL on Ubuntu 18.04

PostgreSQL (Postgres in short) is an open source, powerful, advanced, high performance and stable relational-document database system. It uses and enhances the SQL language coupled with a large number of features for secure data storage and management.

It is efficient, reliable, and scalable for handling large, complicated volumes of data and setting up enterprise-level and fault-tolerant environments, while ensuring high data integrity. Postgres is also highly extensible with features such as indexes comes with APIs so that you can develop your own solutions to solve your data storage challenges.

In this article, we will explain how to install PostgreSQL on an Ubuntu 18.04 server (also works on older Ubuntu releases) and learn some basic ways to use it.

How to Install PostgreSQL on Ubuntu

First, create a file /etc/apt/sources.list.d/pgdg.list which stores the repository configuration, then import the repository key to your system, update your system packages list and install Postgres package using following commands.

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
$ sudo apt install wget ca-certificates
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt update
$ sudo apt install postgresql-10 pgadmin4 

Once postgres has been installed, the database service started automatically and you can confirm by typing following command.

$ sudo systemctl status postgresql.service
Check PostgreSQL Service
Check PostgreSQL Service

How to Use PostgreSQL Roles and Databases

In postgres, client authentication is controlled by the /etc/postgresql/10/main/pg_hba.conf configuration file. The default authentication method is “peer” for the database administrator, meaning it gets the client’s operating system user name from the operating system and checks if it matches the requested database user name to allow access, for local connections (as shown in the following screenshot).

During the installation process, a system user account called postgres was created without a password, this is also the default database administrator user name.

$ sudo vim /etc/postgresql/10/main/pg_hba.conf
PostgreSQL Configuration File
PostgreSQL Configuration File

In addition, under postgres database access permission management is performed via roles. A role can be considered as either a database user, or a group of database users, depending on how the role is set up.

The default role is also postgres. Importantly, database roles are conceptually fully unconnected to operating system users, but practically they may not be separate (for example when it comes to client authentication).

Importantly, roles can own database objects, and can assign privileges on those objects to other roles to control who has access to which objects. In addition, it is possible to grant membership in a role to another role.

To configure other roles to use encrypted passwords to manage databases assigned to them, apart from the default postgres role, you need to change the line to.

Configure Roles in PostgreSQL
Configure Roles in PostgreSQL
Then restart the postgresql service to apply the recent changes.
$ sudo systemctl restart postgresql

How to Use PostgreSQL on Ubuntu

Once everything setup, you can access the postgres system account with the following command, where the -i flag tells sudo to run the shell specified by the target user’s password database entry as a login shell.

$ sudo -i -u postgres 
$ psql		#to launch the postgres shell program  
postgres=#

To access the postgres shell directly, without first accessing the postgres user account, run the following command.

$ sudo -i -u postgres psql

You can quit/exit the postgres by typing the following command.

postgres=# \q

Create PostgreSQL Database Roles

Create a new user role using the following command.

postgres=# CREATE ROLE tecmint;

To create a role with a LOGIN attribute, use the following command (roles with the LOGIN attribute can be considered the same as a database users).

postgres=#CREATE ROLE tecmint LOGIN;
OR
postgres=#CREATE USER name;	#assumes login function by default

A role can also be created with a password, this is useful if you configured the client authentication method to ask users to supply an encrypted password when connecting to the database.

postgres=#CREATE ROLE tecmint PASSWORD 'passwd_here'

List Existing PostgreSQL Database Roles

To list the existing user roles, use any of these commands.

postgres=# \du 				#shows actual users
OR
postgres=# SELECT rolname FROM pg_roles;
List PostgreSQL Roles
List PostgreSQL Roles

Drop a PostgreSQL Database Role

To drop any existing user role use the DROP ROLE command as shown.

postgres=# DROP ROLE tecmint;

Create a PostgreSQL Database

Once you have created a role with a particular name (for instance tecmint user), you can create a database (with the same name as the role) which will be managed by that role as shown.

postgres=# CREATE DATABASE tecmint;

Now to manage the database tecmint, access the postgres shell as the tecmint role, provide your password as follows.

$ sudo -i -u tecmint psql

Create a PostgreSQL Table

Creating tables is so easy, we will create a test table called authors, which stores information about TecMint.com authors, as shown.

tecmint=>CREATE TABLE authors (
    code      char(5) NOT NULL,
    name    varchar(40) NOT NULL,
    city varchar(40) NOT NULL
    joined_on date NOT NULL,	
    PRIMARY KEY (code)
);
Create PostgreSQL Table
Create PostgreSQL Table

After creating a table, try to populate it with some data, as follows.

tecmint=> INSERT INTO authors VALUES(1,'Ravi Saive','Mumbai','2012-08-15');

To view the data stored in a table, you can run a SELECT command.

tecmint=> SELECT * FROM authors;
Insert Data in PostgreSQL Table
Insert Data in PostgreSQL Table

List PostgreSQL Database Tables

You can list all tables in the current database with the following command.

tecmint=>\dt
List PostgreSQL Database Tables
List PostgreSQL Database Tables

Delete/Drop a PostgreSQL Table

To delete a table in the current database, use the DROP command.

tecmint=> DROP TABLE authors;

List All PostgreSQL Databases

To list all databases, use any of the following commands.

tecmint=>SELECT datname FROM pg_database;
OR
tecmint=>\list	#shows a detailed description 
OR
tecmint=>\l
List PostgreSQL Databases
List PostgreSQL Databases

Delete/Drop a PostgreSQL Database

If you want to delete a database, use the DROP command, for example.

tecmint=>DROP DATABASE tecmint;

Switch to Another PostgreSQL Database

You can also switch from one database to another easily using the following command.

tecmint=>\connect database_name

For more information, refer to the PostgreSQL 10.4 Documentation.

That’s it for now! In this article, we have explained how to install and use PostgreSQL database management system on Ubuntu 18.04. You can send us your queries or thoughts in the comments.

Aaron Kili
Aaron Kili is a Linux and F.O.S.S enthusiast, an upcoming Linux SysAdmin, web developer, and currently a content creator for TecMint who loves working with computers and strongly believes in sharing knowledge.

Each tutorial at TecMint is created by a team of experienced Linux system administrators so that it meets our high-quality standards.

Join the TecMint Weekly Newsletter (More Than 156,129 Linux Enthusiasts Have Subscribed)
Was this article helpful? Please add a comment or buy me a coffee to show your appreciation.

Got something to say? Join the discussion.

Thank you for taking the time to share your thoughts with us. We appreciate your decision to leave a comment and value your contribution to the discussion. It's important to note that we moderate all comments in accordance with our comment policy to ensure a respectful and constructive conversation.

Rest assured that your email address will remain private and will not be published or shared with anyone. We prioritize the privacy and security of our users.