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