PSQL14-ubuntu 20.04

https://www.atlantic.net/dedicated-server-hosting/how-to-install-and-configure-postgres-14-on-ubuntu/ 

PostgreSQL, a popular open-source object-relational database system, is renowned for its reliability, feature robustness, and performance, facilitating the development of complex, fault-tolerant applications.

This post will explain how to install and configure PostgreSQL 14 on Ubuntu. This procedure is compatible with Ubuntu 20.04 and Ubuntu 22.04.

Step 1 – Update Server and Install Pre-Requisites

Once you are logged in to your server, run the following command to update your base system with the latest available packages.

apt-get update -y

Install the following prerequisites:

apt install gnupg gnupg2 gnupg1 -y

Step 2 – Install PostgreSQL 14

The latest version of PostgreSQL is not included in the Ubuntu default repository, so you will need to add the PostgreSQL official repository to the APT.

You can add it with the following command:

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Next, download and add the PostgreSQL GPG key using the following command:

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

Next, update the repository cache and install PostgreSQL 14 using the following command:

apt-get update -y
apt-get install postgresql-14 -y

Please be aware that the configured PostgreSQL repository mentioned above is capable of downloading the most recent version of PostgreSQL. As of this update, PostgreSQL is operating on version 15, with the release of version 16 imminent. To ensure the installation of the latest version at any time, modify the command above as follows:

apt-get -y install postgresql

After installing PostgreSQL, check the status of the PostgreSQL with the following command:

systemctl status postgresql

You will get the following output:

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2021-11-26 04:57:30 UTC; 43s ago
   Main PID: 17268 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 2353)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Nov 26 04:57:30 ubuntu2004 systemd[1]: Starting PostgreSQL RDBMS...
Nov 26 04:57:30 ubuntu2004 systemd[1]: Finished PostgreSQL RDBMS.

You can also verify the PostgreSQL version using the command below:

sudo -u postgres psql -c "SELECT version();"

You will get the following output:

                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.1 (Ubuntu 14.1-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

Step 3 – Create a Database and User in PostgreSQL

First, you can connect to PostgreSQL with the following command:

su - postgres
psql

You will get the following shell:

psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# 

Next, create a superuser named root with the following command:

CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'securepassword';

Run the following command to verify the superuser:

\du

You will get the following output:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser, Create role, Create DB                          | {}

To create a database named dbname, run:

create database dbname;

To create a new user named dbuser, run:

create user dbuser with encrypted password 'dbpassword';

To grant all privileges to the dbname to dbuser, run:

grant all privileges on database dbname to dbuser;

To list all databases, run:

\l

You will get the following output:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 dbname    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | dbuser=CTc/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

To exit from the PostgreSQL shell, run:

\q
exit

Step 4 – Configure PostgreSQL for Remote Access

By default, PostgreSQL is configured for local access only. If your application and database servers are hosted on a different server, then you must configure PostgreSQL for remote access. You can configure it by editing the file pg_hba.conf:

nano /etc/postgresql/14/main/pg_hba.conf

Find the following line:

local   all             all                                     peer

And replace it with the following line:

local   all             all                                     trust

Next, add the following line:

host    all             all             0.0.0.0/0                md5

Save and close the file when you have finished.

Next, you will also need to edit the PostgreSQL main configuration file and change the listening port:

nano /etc/postgresql/14/main/postgresql.conf

Change the following line:

listen_addresses='*'

Save and close the file, then restart the PostgreSQL service to apply the changes:

systemctl restart postgresql

You can now check the PostgreSQL listening port using the following command:

ss -antpl | grep 5432

You will get the following output:

LISTEN    0         244                0.0.0.0:5432             0.0.0.0:*        users:(("postgres",pid=19030,fd=5))                                            
LISTEN    0         244                   [::]:5432                [::]:*        users:(("postgres",pid=19030,fd=6)) 

Now, go to the remote system and connect to the PostgreSQL instance using the following command:

psql 'postgres://root:securepassword@104.245.34.223:5432/postgres?sslmode=disable'

If everything is fine, you will get the following shell:

psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.

postgres=# 

Conclusion

You learned how to install and configure PostgreSQL 14 on Ubuntu in the above post. You can now start developing high-performance and complex applications with the PostgreSQL database backend. Try it on dedicated hosting from Atlantic.Net!