Getting started with PostgreSQL

PostgreSQL is an open-source and advanced object-oriented relational database which is also known as Postgres. This database management system is the first designed for the UNIX based environment. However, its design modified with time due to which PostgreSQL can run on other operating systems as well as Solaris, Mac OS X, and Windows. PostgreSQL is free software and its source code is freely available under the licence of PostgreSQL. You can modify and use PostgreSQL in any format according to your requirements.

In this article, you will learn how to install and get started with PostgreSQL on Ubuntu 20.04 system using the terminal.

Installation of PostgreSQL on Ubuntu 20.04

# 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:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql

After completing the installation of PostgreSQL, you will start, stop, and enable the PostgreSQL services using the following command:

sudo systemctl stop postgresql.service
sudo systemctl start postgresql.service
sudo systemctl enable postgresql.service

Set PostgreSQL user password

You can change or create the user password for PostgreSQL. Using the following command, you can change the default user password for PostgreSQL:

sudo passwd postgres

# ALTER USER command to change the password for the postgres user:
ALTER USER postgres PASSWORD 'myPassword';
ALTER ROLE;

Login to PostgreSQL

sudo -u postgres psql
# or run this command
sudo su -l postgres

Logout of psql

\q

List the databases

# Get list of databases
\l

# Get list of databases with size & description
\l+

Show database tables

\dt

Create database

create database database_name;

Select database

\c database_name

Show list of tables

\dt

Describe a single table

\d table_name

Show size of a single database

SELECT pg_database_size('database_name');

Create a database from bash

sudo -u postgres createdb database_name

Drop a database

sudo -u postgres dropdb

Login to a database

sudo -u postgres psql database_name

In this article, we implemented the installation of PostgreSQL on Ubuntu 20.04. Furthermore, you learned the different psql commands. In the future, you can explore more commands related to PostgreSQL using the command line.