In addition to databases, there are services that maintain databases. These services are called 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 about Tutorial Install PostgreSQL 11 on Ubuntu 20.04, 21.04 step by step. You can also see and purchase our Linux VPS packages.

Top Way to Install PostgreSQL 11 on Ubuntu Linux

Introduction to PostgreSQL

PostgreSQL is a database management system that easily shares its popularity with the MySQL database. This DBMS is an object-relational database management system in which user-defined objects and table approaches are combined to build more complex data structures. One of its goals is to strengthen compliance and development standards. This DBMS is developed by PostgreSQL Global Development Group and is belongs to this group and has open-source capabilities. This database management system is mostly used in Microsoft, iOS, Android, and other platform systems.

PostgreSQL Features

– Designing: PostgreSQL design is integrated.

– Ease of use: Ease of use PostgreSQL is for professional users.

– Reliability: PostgreSQL is more secure using the ACID structure.

– Speed: A common part of PostgreSQL and Mysql. How to improve the speed of the two differs due to their different algorithms.

How to Install PostgreSQL 11 on Ubuntu 20.04

First, you need to install prerequisites. A machine with a fresh install of Ubuntu Server 20.04 for example A virtual machine locally using Virtualbox, VMWare workstation or VMWare Fusion, KVM, Xen, or a virtual machine.

Note: If you’re using 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:

RELEASE=$(lsb_release -cs)
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee /etc/apt/sources.list.d/pgdg.list

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

cat /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

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 = '*'

Note: 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
[email protected]:~$ psql -c "alter user postgres with password 'StrongPassword'"
ALTER ROLE

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

createuser dbuser1

Run the following command to add the test database:

[email protected]:~$ 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';
ALTER ROLE

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 );
CREATE TABLE
testdb=# insert into test_table (id,first_name,last_name) values (1,'Linda','Evanty');
INSERT 0 1

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:

[email protected]:~$ dropdb testdb;

Conclusion

In this article, we introduce PostgreSQL and list its features. With our complete tutorials, you can easily install PostgreSQL 11 on Ubuntu 20.04.