The Ultimate Guide To Installing PostgreSQL on Ubuntu

install postgresql ubuntu
Share

PostgreSQL, aka Postgres, is an open source relational database management system (RDBMS) that’s become very popular in the IT industry, particularly in the app and web development.

Thanks to its open source background, Postgres works excellently on all platforms, including Linux, Windows, and macOS. In fact, it is the default database for macOS powered servers. It comes with a standard implementation of SQL, support for concurrency, and very reliable transactions.

This tutorial covers the process of installing PostgreSQL on Ubuntu servers.

Why Should You Consider PostgreSQL for Your Projects

The popularity of PostgreSQL is because of the following factors that come together and make it a dependable RDBMS for your projects:

  • A diverse community that helps you set up and use PostgreSQL for your projects.
  • Support for Stored Procedures (SQL functions) speeds up data operations
  • PostgreSQL supports multiple indexing techniques, including GIN and GiST
  • Simple full-text search for strings
  • PostgreSQL is an excellent option for geographically distributed data stores.

Prerequisites

To install PostgreSQL on Ubuntu, you’ll need the following:

  • A server running Ubuntu 22.04 (or the latest stable version)
  • A non-root user with administrator (sudo) privilege
  • A basic firewall on the server

As you can see, security is always an important consideration whenever you do anything related to data. If you’re setting up PostgreSQL for your active projects, you should consider more robust security measures.

Install PostgreSQL on Ubuntu

Now that you have the basic information about PostgreSQL and the prerequisites of the process, let’s dive into the details of the process.

Step 1: Update the Server Packages

Start by updating the package index of the Ubuntu server with the following command:

sudo apt update

Step 2: Install the PostgreSQL Package

Next, we’ll download and install the PostgreSQL package. This download will also include utilities that would help us better use PostgreSQL.

Use the following command to download the postgresql-contrib package:

sudo apt install postgresql postgresql-contrib

During the process, you’ll be prompted to confirm that you want to install the package and restart the service after installation.

Install-Postgresql

Step 3: Confirm PostgreSQL Service is Up and Running

We recommend you check if the PostgreSQL service is running. For this check the status with the following command:

sudo service postgresql status

postgres status

Access PostgreSQL Database

Now that PostgreSQL is installed and running, you can access the PostgreSQL database.

During the installation process, PostgreSQL creates a user named postgres. We’ll use this user to access the database engine. To switch over to the PostgreSQL user, use the following command:

sudo -i -u postgres

Next, access the PostgreSQL prompt with the command:

psql

This launches the PostgreSQL command line utility that allows you to access the databases and users.

access postgres

Check Available Databases and Users

The first thing you should do after installing PostgreSQL is to check what databases and users are available.

First, to see the list of all databases created on the server, run the following command:

 \l

List of all databases in postgres

Next, check the list of all the users with their privileges using the following command:

   \du

List of all users and their roles in postgres

You should note that the default user postgres has no password. If you want to set it up, you can use the following command:

  \password postgres

You will be prompted to set up the password. Remember to press ENTER to complete the process at the second entry.

postgres setup password for default user

Finally, to quit the PostgreSQL prompt, run the following command:

   \q

quit postgres prompt

You’ll be returned to the PostgreSQL user command prompt. If you wish to return to the logged-in user, run the exit command:

  exit

Add A New User

When using PostgreSQL, you might want to add a new user in addition to the default postgres user. This is often a requirement when you want to add users with different roles and access rights. PostgreSQL has a simple process of adding new users and defining their roles.

Use the following command to add a new user:

  createuser --interactive

Because of the –interactive switch, the script will prompt you to answer a question. Based on your responses, the script executes the correct commands to create a user to your specifications.

createuser postgresNow to log in to PostgreSQL using the credentials for the redswitches user, you need to have a registered user. Fortunately, you can add a user.

Important: You need to be logged in with the non-root account with the sudo rights we mentioned in the prerequisites.

sudo adduser redswitches

postgres add new user

Once the account has been added, you can switch over to it and connect to the database:

sudo -i -u redswitches

psql

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

Once logged in as redswitches, you can get check your current connection information by running the following command:

\conninfo

postgres check current connections

Create a New Database

Now that you have added a user, it’s time to add a new database.

Notice that we added a user named redswitches to PostgreSQL. To add a new database, you must be logged in with this user to conform to the PostgreSQL authentication processes.

We’ll now add a new database (also named redswitches) with the following command.

createdb redswitches

createdb postgresql

This will create a database with the name redswitches.

Conclusion

We hope you now have a clear idea of how to install PostgreSQL on Ubuntu server. We also covered the process of adding a new user and a database. We highly recommend using strong passwords and usernames to improve database security. If you need further help in setting up PostgreSQL on your Ubuntu server, post your question in the comments for our experts.