Tutorial Install PostgreSQL 11 on Ubuntu 20.04

Apart from databases, there are services that maintain them, known as database management systems or DBMS. One of these DBMSs is called PostgreSQL, which, with its full knowledge, will help you make better use of PostgreSQL. If you are looking for a comprehensive article on PostgreSQL cognition, you can get help from this article. This article also teaches you how to Install PostgreSQL 11 on Ubuntu 20.04, and 21.04 step by step.

What’s PostgreSQL?

In the rest of this article, we will first introduce you to the concept of PostgreSQL. Then we will discuss its features and finally, we will teach you how to install PostgreSQL 11 on Ubuntu. Before continuing the article, we strongly recommend you use the Linux VPS server plans provided on our website.

PostgreSQL is a powerful open-source relational database management system (RDBMS) known for its robustness, scalability, and extensibility. It provides a wide range of features that make it a preferred choice for many applications. With its support for ACID (Atomicity, Consistency, Isolation, Durability) properties, PostgreSQL ensures data integrity and reliability.

Additionally, it offers advanced indexing techniques, full-text search capabilities, and support for complex queries, making it suitable for handling large amounts of data efficiently. As for installation, to install PostgreSQL 11 on Ubuntu 20.04, you can follow the necessary steps to set up the database system and its dependencies, ensuring a smooth and hassle-free installation process.

How to Install PostgreSQL 11 on Ubuntu 20.04

To begin, it is essential to ensure that you have a machine installed with the latest version of Ubuntu Server 20.04. For this purpose, you can opt for a virtual machine using platforms such as Virtualbox, VMWare Workstation, VMWare Fusion, KVM, Xen, or any other suitable virtual machine software.

Tip: If you’re using a root user remove Sudo from each command on this tutorial.

Now, you must update your system using the following command and install the dependencies:

sudo apt update

sudo apt -y upgrade

sudo reboot

At this point, if you have not previously installed Wget and Vim, you will need to install them after rebooting the system:

sudo apt install -y wget vim

Now use the following command to import the repository signing key:

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

You can add repository contents to the Ubuntu 20.04 system:

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null

Verify the contents of the repository file by entering the following command:

cat /etc/apt/sources.list.d/pgdg.list

Now install PostgreSQL 11 on Ubuntu 20.04 by running the following command:

sudo apt update

sudo apt -y install postgresql-11

At this point, you need to allow access to PostgreSQL via remote hosts.

Note that this tutorial assumes that access to the PostgreSQL database server is from Localhost only.

sudo ss -tunelp | grep 5432
tcp LISTEN 0 128 127.0.0.1:5432 0.0.0.0:* users:(("postgres",pid=15785,fd=3)) uid:111 ino:42331 sk:6 <->

How to configure PostgreSQL 11 on Ubuntu 20.04

Now you must edit the configuration file using the following command to be allowed access to the network:

sudo vim /etc/postgresql/11/main/postgresql.conf

You must add the following command in the CONNECTIONS AND AUTHENTICATION section:

listen_addresses = '*'

Tip: You can also specify the IP address of the server in the above command.

After making the change, you must restart the PostgreSQL service by entering the following command:

sudo systemctl restart postgresql

Enter the following command to confirm the bind address for PostgreSQL:

sudo ss -tunelp | grep 5432
tcp LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=16066,fd=3)) uid:111 ino:42972 sk:8 <-> tcp LISTEN 0 128 [::]:5432 [::]:* users:(("postgres",pid=16066,fd=6)) uid:111 ino:42973 sk:9 v6only:1 <->

You should allow port 5432 if your UFW firewall is enabled:

sudo ufw allow 5432/tcp

In this step, you must set the password for the default admin user by executing the following command:

sudo su - postgres
postgres@os1:~$ psql -c "alter user postgres with password 'StrongPassword'"

If you want to add other database users, enter the following command:

createuser dbuser1

Run the following command to add the test database:

postgres@ubuntu-01:~$ createdb testdb -O dbuser1

To log in to dbuser1 and operate on testdb, you must do a test operation:

~$ psql -l | grep testdb
testdb | dbuser1 | LATIN1 | en_US | en_US |

Now it is time to set the user password. You can do this by running the following command:

psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.
postgres=# alter user dbuser1 with password 'DBPassword';

Use the following command to create a table and add dummy data:

testdb=# create table test_table ( id int,first_name text, last_name text );
testdb=# insert into test_table (id,first_name,last_name) values (1,'Linda','Evanty');

By entering the following command, the table data is shown:

testdb=# select * from test_table;
id | first_name | last_name
----+------------+-----------
1 | Linda | Evanty
(1 row)

You should now drop your test table:

testdb=# DROP TABLE test_table;
DROP TABLE
testdb=# \q

Finally, drop the test database:

postgres@ubuntu-01:~$ dropdb testdb;

There you have it! PostgreSQL is now installed on your machine.

Troubleshooting Common Issues

• Permission issues: Ensure you have administrative privileges by running the installation with the sudo command. Use:

"sudo apt-get install postgresql-11"

Check file and directory permissions using “ls -l” and ensure the user running the installation has the necessary read and write permissions.

• Port conflicts: Check if another service is using the default PostgreSQL port (5432) with:

 "sudo netstat -tulpn | grep 5432"

If a service is using the port, stop it or change the PostgreSQL port in the configuration file (postgresql.conf).

• Dependencies not met: Check the error message during installation to identify missing dependencies. Use:

"sudo apt-get install" 

followed by the missing package name to install them. Install each missing dependency until all requirements are met.

Refer to official documentation or community forums for more specific troubleshooting steps for your setup.

Conclusion

As you may have noticed, the PostgreSQL database has become one of the best, most efficient, and most popular databases that are used in various fields due to its unique features. In this article, you learned how to install PostgreSQL and use its features. Knowing how to use tools like this can really help you excel in this field. If you have any questions, don’t hesitate to ask us in the comments section.