How to install and set up PostgreSQL

PostgreSQL Installation

Introduction

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

Steps to install PosterSQL:

1. Update system packages

Sudo apt update

Using the following command to upgrade the installed packages:

sudo  apt -y upgrade

2. Install PostgreSQL on Ubuntu 20.04

You can install PostgreSQL on Ubuntu 20.04 without configuring the
Ubuntu repository. Use the following command to install PostgreSQL on
Ubuntu 20.04

sudo apt install postgresql postgresql-client

You need to press ‘y’ to continue the installation.

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

Use the following command to check the service status:

sudo systemctl status postgresql.service

3. 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

4. Access PostgreSQL shell

You can log in to PostgreSQL as a user to access the databases and working shell using the following command:

sudo su -l postgres

5. Create a database and user roles

You can create new databases and users using the interactive Postgresql shell as follows:

psql -c "alter user postgres with password 'mypass123'"

Now create a new user and database using the following command:

createuser testuser createdb
testdb -O testuser psql testdb

Change the role and password using the following command. The \q is used to quite from the shell.

psql -c "alter user testuser with password 'testpass123'"

Use the following command to list databases:

psql -l

The PostgreSQL by default listens at the local interface which is 127.0.0.1. But, you can open its remote access by doing some changes in the configuration file. To access the configuration file of PostgreSQL, you will use the following command:

6. Remote connection[Optional]

sudo nano /etc/postgresql/12/main/postgresql.conf

In the file set, listen_addresses = ‘*’

Then save file and restart PostgreSQL service.

sudo service postgresql restart

Conclusion

This tutorial showed you how to set up PostgreSQL on a Ubuntu 20.04 server. PostgreSQL is a complex application with many new and advanced features, such as improved space management of standard B-tree indexes, and support for concurrent index rebuilding and covering index creation.
We have covered the installation basics here, and also provided some instructions for general database administration. We hope you found this guide useful.