How to Install PostgreSQL on Ubuntu 20.04 LTS

Introduction

PostgreSQL is a fully featured database management system (DBMS) with a strong emphasis on extensibility and SQL compliance. It is backed by 20 years of open-source development, and supports both SQL (relational) and JSON (non-relational) querying. It is one of the most popular databases in the industry that is used for various web, mobile and analytics applications. Let‘s now go through a step-by-step guide of how to install PostgreSQL on Ubuntu 20.04 VPS machine.

Prerequisites

OS: Ubuntu 20.04

Resources: 2-core CPU & 2GB of RAM

Access: SSH connection to the server

Permissions: a user with ‘sudo’ privileges

Step 1. Install PostgreSQL on Ubuntu

It is always a good idea to download information about all packages available for installation from your configured sources before the actual installation.

$ sudo apt update

Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality:

$ sudo apt install postgresql postgresql-contrib

Ensure that the service is started:

$ sudo systemctl start postgresql

After the installation you may double-check that postgresql daemon is active.

$ service postgresql status

The output should look like this:

If the version included in your version of Ubuntu is not the one you want, you can use the PostgreSQL Apt Repository. This repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.

To use the apt repository, follow these steps:

# 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

For more information about the apt repository, including answers to frequent questions, please see the PostgreSQL Apt Repository page on the wiki.

Step 2. Using PostgreSQL Roles and Databases

When you install PostgreSQL on Ubuntu a default admin user “postgres” is created by the default. You must use it to log-in to your PostgreSQL database for the first time.

A “psql” command-line client tool is used to interact with the database engine. You should invoke it as a “postgres” user to start an interactive session with your local database.

The installation procedure created a user account called postgres that is associated with the default Postgres role. There are a few ways to utilize this account to access Postgres.

One way is to switch over to the postgres account on your server by running the following command:

$ sudo -i -u postgres

Then you can access the Postgres prompt by running:

$ psql

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away. To exit out of the PostgreSQL prompt, run the following:

\q

This will bring you back to the postgres Linux command prompt. To return to your regular system user, run the exit command:

exit

Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo:

$ sudo -u postgres psql

This will log you directly into Postgres without the intermediary bash shell in between.

After first launching psql, you may check the details of your connection by typing \conninfo into the interpreter.

You are now connected to database “postgres” as user “postgres”.

If you want to see a list of all the databases that are available on a server, use \l command.

And to see a list of all the users with their privileges use \du command.

Since the default “postgres” user does not have a password, you should set it yourself.

\password postgres

Again, you can exit the interactive Postgres session by running the following:

\q

Step 3. Creating a New Role

You can create a new role by running the following command:

$ sudo -u postgres createuser --interactive

Either way, the script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications.

Output
Enter name of role to add: tommy
Shall the new role be a superuser? (y/n) y

Step 4. Creating a New Database

Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.

This means that if the user you created in the last section is called tommy, that role will attempt to connect to a database which is also called “tommy” by default. You can create the appropriate database with the createdb command.

$ sudo -u postgres createdb tommy

Step 5. Opening a Postgres Prompt with the New Role

To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges:

$ sudo adduser tommy

Once this new account is available, you can either switch over and connect to the database by running the following:

sudo -u tommy psql

This command will log you in automatically, assuming that all of the components have been properly configured.

If you want your user to connect to a different database, you can do so by specifying the database like the following:

\c tommy

Once logged in, you can get check your current connection information by running:

tommy=# \conninfo
You are connected to database "tommy" as user "tommy" via socket in "/var/run/postgresql" at port "5432".

Conclusion

Congrats! You have successfully installed a PostgreSQL database on your Ubuntu 20.04 server. The tutorial is tested on VPS Mart, so it works fine on our Ubuntu VPS Hosting.

Table of Contents

Add a header to begin generating the table of contents

How to Install PostgreSQL on Ubuntu 20.04 LTS

Introduction

Prerequisites

Step 1. Install PostgreSQL on Ubuntu

The output should look like this:

To use the apt repository, follow these steps:

Step 2. Using PostgreSQL Roles and Databases

One way is to switch over to the postgres account on your server by running the following command:

Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo:

Step 3. Creating a New Role

Step 4. Creating a New Database

Step 5. Opening a Postgres Prompt with the New Role

Conclusion

About The Author

Feng

Introduction

Prerequisites

Step 1. Install PostgreSQL on Ubuntu

The output should look like this:

To use the apt repository, follow these steps:

Step 2. Using PostgreSQL Roles and Databases

One way is to switch over to the postgres account on your server by running the following command:

Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo:

Step 3. Creating a New Role

Step 4. Creating a New Database

Step 5. Opening a Postgres Prompt with the New Role

Conclusion

Related Artical

About The Author

Feng