How to Install PostgreSQL on Ubuntu, pgAdmin Configuration, and Useful Tips

Any modern application or system requires a database to run correctly. Unlike NoSQL solutions, such as MongoDB, most people choose a relational database management system. Relational databases work by providing a logical structure to the data. Thus such data can be queried and accessed at any time.

One of the most popular relational database management systems is PostgreSQL. It is an open-source, community-driven, and scalable product that is an excellent choice for small businesses and big enterprises.

In this tutorial, we will show you two ways how to install PostgreSQL on Ubuntu 18.04, 20.04, and 22.04. We will also cover the pgAdmin installation process and provide valuable tips for using PostgreSQL databases.

3 Easy Steps to Install PostgreSQL on Ubuntu

In order to install the PostgreSQL server on Ubuntu, you will need to have an already running virtual private server (VPS) with Ubuntu operating system installed.

With all the prerequisites done, connect to SSH and start with the installation.

Step 1 – Install PostgreSQL

Start by choosing one of the following options – installation from the APT repository or by using a local Ubuntu repository. We recommend sticking with the APT repository method, as you will always get the newest version of the PostgreSQL server.

Option 1 – Installation From APT Repository

The most popular way of installing PostgreSQL is by using the APT package manager. Start with importing the key from the official PostgreSQL repository. Run the following commands one at a time:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Next, refresh the APT command to synchronize all repositories:

sudo apt-get update

Install PostgreSQL with APT and sudo privileges. This command will install the latest version of PostgreSQL:

sudo apt-get -y install postgresql

In order to confirm that the installation was successful, run the following command:

sudo -u postgres psql

Then, filter out the version:

SELECT version();

Here’s a visual example of how the output should look:

Option 2 – Installation Using Local Ubuntu Repository

Another option to install PostgreSQL is by using the local Ubuntu repository. Keep in mind that this method does not guarantee the newest version available. First, verify which PostgreSQL apt repository versions are available:

apt show postgresql

Then proceed with the installation:

sudo apt install postgresql postgresql-contrib

In our case, we installed the 12.11 version. If a 404 error shows up during the installation process, run this command to try again:

sudo apt-get update

To confirm that the installation was successful, run the following command:

sudo -u postgres psql

Then, filter out the version:

SELECT version();

A command-line example should look like this:

As shown in the example, PostgreSQL server version 12.11 was successfully installed.

Step 2 – Connect to PostgreSQL

Now that the PostgreSQL installation is complete, go ahead and switch to the postgres user with this command:

sudo su postgres

In order to start PostgreSQL, type:

psql

Step 3 – Change User Password

To change the postgres database user password, connect to PostgreSQL using this command:

sudo -u postgres psql

Then, change the password:

ALTER USER postgres PASSWORD 'NewPassw0rd';

Make sure to replace “NewPassw0rd” with an actual password. To double-check if the change was successful, use the following command:

\p

Once that’s done, restart the PostgreSQL service.

sudo service postgresql restart

Using PostgreSQL on Ubuntu

With the PostgreSQL database management system installed, it’s time to go over the most popular configuration options. We will go through the steps of creating a new user and a new database.

Creating a User on PostgreSQL

The easiest way to create a new PostgresSQL user account is to use the interactive mode. Start by switching to the postgres user:

su - postgres

Then, create a new user:

createuser --interactive

In our example, we called it TheNewUser:

To validate the new role, log in to PostgreSQL with the postgres account:

sudo -u postgres psql

Lastly, check all of the currently created users with this command:

\du

As shown in the example above, TheNewUser was successfully created.

Creating a Database on PostgreSQL

PostgreSQL assumes that the role name should be the same as the database name. If you create a role named TheNewUser, PostgreSQL will try to access a database with the same TheNewUser name.

To create a new database, proceed with the following command:

sudo -u postgres createdb TheNewUser

To log in to the newly created database, you must create a Linux user account with the same name. Since we use capital letters for the TheNewUser username, we will also use the –force-badname option.

sudo adduser TheNewUser --force-badname

Then, log in to the PostgreSQL prompt.

sudo -u TheNewUser psql

Lastly, confirm the connection:

\conninfo

Great work, TheNewUser user is now connected to the TheNewUser database.

How to Install pgAdmin on Ubuntu

Similar to phpMyAdmin, pgAdmin is a web-based GUI tool to manage your PostgreSQL databases. To install it, we will use the same APT package manager.

Start by adding the pgAdmin repository by running the following commands consecutively:

curl -fsSL https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgadmin.gpg

sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'

Now, update the package lists:

sudo apt update

Once complete, install phAdmin:

sudo apt install pgadmin4

After installation, initiate the primary configuration with the following command:

sudo /usr/pgadmin4/bin/setup-web.sh

Fill in your email address and password. Your end result should look something like this:

After installation, open your web browser and enter pgAdmin with the following address:

185.185.185.185/pgadmin4

Make sure to replace the IP with an actual address. Then, enter your email address, and password and click Login:

You will be redirected to the main pgAdmin dashboard. To connect PostgreSQL – select Add New Server and proceed with the configuration process.

Conclusion

If you want a robust and reliable database management system, consider using PostgreSQL. It will allow you to quickly scale your projects and manage them with a convenient pgAdmin GUI.

In this tutorial, we showed two ways how to install PostgreSQL on an Ubuntu system. We also covered how to:

One of the main advantages of using PostgreSQL 14 is better connection scaling. That means that large enterprises with a lot of database connections will benefit the most from PostgreSQL 14.

Other than that, the performance is similar in other areas. However, we recommend always sticking to the newest version because such software is generally safer and updated more frequently.

We hope that you found this tutorial helpful. If you have any questions, feel free to leave us a comment.

How to Install PostgreSQL FAQ