DEV Community

Cover image for Getting started with PostgreSQL
Manoj Kumar
Manoj Kumar

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Login to PostgreSQL

sudo -u postgres psql
# or run this command
sudo su -l postgres
Enter fullscreen mode Exit fullscreen mode

Logout of psql

\q
Enter fullscreen mode Exit fullscreen mode

List the databases

# Get list of databases
\l

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

Enter fullscreen mode Exit fullscreen mode

Show database tables

\dt
Enter fullscreen mode Exit fullscreen mode

Create database

create database database_name;
Enter fullscreen mode Exit fullscreen mode

Select database

\c database_name
Enter fullscreen mode Exit fullscreen mode

Show list of tables

\dt
Enter fullscreen mode Exit fullscreen mode

Describe a single table

\d table_name
Enter fullscreen mode Exit fullscreen mode

Show size of a single database

SELECT pg_database_size('database_name');
Enter fullscreen mode Exit fullscreen mode

Create a database from bash

sudo -u postgres createdb database_name
Enter fullscreen mode Exit fullscreen mode

Drop a database

sudo -u postgres dropdb
Enter fullscreen mode Exit fullscreen mode

Login to a database

sudo -u postgres psql database_name
Enter fullscreen mode Exit fullscreen mode

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.

Discussion (0)