Wednesday, November 30, 2022

Step-by-step installation of PostgreSQL in Ubuntu 18.04

 

Open-source object-relational database system (ORDBMS) PostgreSQL leverages and expands the SQL language with a vast array of functionality. One of the most sought-after RDBMSs, it has a number of cutting-edge features like transactions and concurrency, no read locks, and more.

Developers can create apps quickly thanks to PostgreSQL’s feature-richness. Additionally, PostgreSQL administrators may simply build fault-tolerant PostgreSQL settings, safeguard data integrity, and manage datasets effectively.

The installation and configuration of PostgreSQL and PgAdmin settings in an Ubuntu 18.04 instance will be covered in this article.

Prerequisites

  • Using root or sudo user, you can SSH into your Ubuntu 18.04 instance.

Why PostgreSQL instead of MySQL

Although PostgreSQL has more advanced capabilities than MySQL and is utilized as Oracle’s Open source version, MySQL is still a commonly used relational database management system that is available for free and widely used in both large and small businesses.

Let’s quickly identify some of the areas where PostgreSQL has an advantage over MySQL.

  1. While MySQL does not support analysis, PostgreSQL does, allowing users to analyze and contrast data saved in the database.
  2. In contrast to MySQL, PostgreSQL has a data type called “network rk address type” for storing IPv4, IPv6, and MAC addresses.
  3. Materialized views are a feature of PostgreSQL that let you physically store query results and reload data on a regular basis.
  4. With the use of boolean expression-based table constraints, PostgreSQL offers CHECK constraints whose values are columns.
  5. When requesting data from two or more tables, PostgreSQL enables FULL OUTER JOIN.
    Partial index, bitmap index, and expression index are supported by PostgreSQL.
  6. Other NoSQL features supported by PostgreSQL include native XML support, key-value pairs, and the indexing of JSON data for quicker access. While MySQL does not support NoSQL, it does support the JSON data type. Additionally, JSON indexes are not supported by MySQL.

So it makes sense to utilize PostgreSQL to benefit from relational database management systems’ superior features.

Add a PostgreSQL repository

When installing PostgreSQL, the first step is to add a dedicated repository. Run the following command in the terminal to accomplish this.:

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

import signing key

The signature key must then be obtained and imported into the system.

# wget https://www.postgresql.org/media/keys/ACCC4CF8.asc
# apt-key add ACCC4CF8.asc

Install PostgreSQL

The system is lastly updated, and PostgreSQL 11 is installed.

# apt update
# apt install postgresql-11

The Postgres service will immediately launch after PostgreSQL has been successfully installed. To check the PostgreSQL server’s status, execute the command below.

# systemctl status postgresql

During installation, the following users and databases will be created.

  • There will be a postgres user established on Linux. This user is the owner of all data files and processes used to execute the PostgreSQL server.
  • created the postgres database.
  • created the postgres user for PostgreSQL.

) Obtain a PostgreSQL server connection (*) Use a PostgreSQL client, such as

Use psql or another program of your choosing. Using one of the IPC protocols, either through TCP sockets or Unix domain sockets, clients connect to the PostgreSQL server. The ability to authenticate the system user ID for client connections is a benefit of using Unix domain sockets.

) The PostgreSQL server only accepts connections over Unix domain sockets by default. Let’s utilize the system user postgres to establish a connection to the PostgreSQL server.

To access the shell of the system user (Linux) postgres, enter the following command.

# su – postgres

 now enter psql in the postgres shell. .

:~$ psql
psql (11.4 (Ubuntu 11.4-1.pgdg18.04+1))
Type “help” for help.

postgres=#

[email protected] In PostgreSQL, there are three methods for authenticating users.:

A peer-to-peer system to verify each Linux user’s identification. Thus, it does not request a password.

  • The default technique for local authentication (Linux) users is the MD5 method, which always requests a password and validates after hashing with MD5 trust.
  • MD5 method never requests a password and always trusts every connection. As a result, we had success earlier connecting to the PostgreSQL server.
  • A peer authentication error will appear if you try to connect to the PostgreSQL server at this point via a password-based method.

:~# psql -U postgres
psql: FATAL: peer authentication failed for user ‘postgres’

[email protected] First, change the password for the PostgreSQL user postgres in order to adopt a password-based authentication method.

Run the following command in the terminal to accomplish this.

:~# su – postgres

[email protected]:~$psql
postgres=# AGE USER postgres PASSWORD 'myPassword';
ALTER ROLE[email protected] Next, modify the PostgreSQL configuration file and switch the user postgres' connection mode from peer to MD5.

# vi /etc/postgresql/11/main/pg_hba.conf


# Database administration login using local all postgres md5 Unix domain sockets


 PostgreSQL should be restarted after saving the file. Your server will be more secure going forward if you connect using a password-based authentication method.

# systemctl postgresql restart
# psql -U postgres

password for user postgres:

psql (11.4 (Ubuntu 11.4-1.pgdg18.04+1))
Type “help” for help.

pgAdmin

is a free, open-source GUI management tool for managing PostgreSQL instances over the web. To set up PosrgreSQL database settings, track performance, and even create SQL queries, use pgAdmin. Simply enter the following command in the terminal to install pgAdmin 4. Other supplementary items and dependencies will be installed as a result.

# apt install pgadmin4 pgadmin4-apache2

 The email address that is provided during installation will be used as the admin login ID for pgAdmin 4's online interface.

The installation will subsequently request the administrator password.

Within a minute, pgAdmin 4 will have been installed. You can access a login page by pointing your preferred web browser to http://your ip address/pgadmin4.

Click the “Sign In” button after entering your email address and the password you chose during installation. Your browser will take you to the PgAdmin4 dashboard.

Install pgAdmin 4 Ubuntu

Select the “Add new server” option in the dashboard to add a PostgreSQL instance. The page will pop up. On the General tab, give the connection a name.

Install pgAdmin 4 Ubuntu

Enter the PostgreSQL server hostname or address, port number, username, and password on the Connection tab. The password has not changed from the one we previously updated using PSQL. To complete the process, select “Save.”.

PostgreSQL instances that are newly added are immediately added to the Servers area of the left sidebar.

Install pgAdmin 4 Ubuntu 18

From the online interface of PgAdmin 4, you can easily add new PostgreSQL instances and manage them.

Final Reflections

In this lesson, we looked at the benefits of PostgreSQL over MySQL before moving on to a comprehensive installation explanation.

PostgreSQL(*) Set up a database on a server running Ubuntu 18.04.Along with installing PostgreSQL, we also demonstrate how to set up the PostgreSQL client, pgAdmin 4, and demonstrate how to use pgAdmin 4 to manage a PostgreSQL database on Ubuntu 18.04. You may always look up information on PostgreSQL on the official

documentation

Additionally, this PostgreSQL installation runs and functions on Ubuntu 16.x and earlier, sometimes requiring only minimal installation-related tweaks.

  

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay Connected

0FansLike
3,586FollowersFollow
0SubscribersSubscribe

Latest Articles

- Advertisement -