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

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.

Pro Tip

If you haven’t launched your server yet, learn how to setup a VPS with Hostinger’s step-by-step guide.

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

Pro Tip

If you want to install a specific version, proceed with the following command sudo apt-get -y install postgresql-12. In this example, PostgreSQL 12 version was installed.

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:

PostgreSQL query to check its version. Red border indicating the latest installed version – PostgreSQL 14.4

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
Checking the PostgreSQL version that will be installed within the local Ubuntu repository

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:

PostgreSQL query to check its version

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
Terminal commands to switch to the postgres user and open PostgresSQL management

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
PostgreSQL query to change user password

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:

A PostgreSQL command to create a new user

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
PostgreSQL command to display all of the currently created users with their respective permissions

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.

Important! PostgreSQL uses Linux information for authentication. For the previous example, you need to have a user called TheNewUser.

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
PostgreSQL command to check the connection information

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:

Bash script to initiate pgAdmin configuration

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:

Login screen for pgAdmin GUI interface. Red border indicating Login button

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

Main management window for pgAdmin. Red border indicating the Add New Server option

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

Where Is Postgres Installed on Ubuntu? 

The main PostgreSQL server files are installed under /usr/lib/postgresql/<version/bin/postgres directory. All database clusters are stored in /var/lib/postgresql//main directory. If users need to access the PostgreSQL configuration file, they can open the /etc/postgresql//main/postgresql.conf file.

How Do I Know if PostgreSQL Is Installed on Ubuntu?

The easiest way to check if PostgreSQL is installed is to run any PostgreSQL-specific command, like sudo -u postgres psql. Alternatively, use the which psql command. You should see an output with the installation path if it is installed.

Should I Use PostgreSQL 13 or 14?

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. We recommend sticking to the newest version because such software is generally safer and updated frequently. 

Author
The author

Ignas R.

Ignas takes great satisfaction in helping people tackle even the most complex technical issues. His current goal is to write easy-to-follow articles so that these issues will not happen at all. During his free time, Ignas likes to play video games and fix up things around his house.