How to install PostgreSQL 16 and pgAdmin4

Table of Contents

PostgreSQL 16 and pgAdmin4

What is PostgreSQL

PostgreSQL, often referred to as Postgres, is a powerful, open-source relational database management system known for its robust features and extensibility. It has gained popularity for its ability to handle complex data and querying requirements, making it a suitable choice for a wide range of applications, from small projects to large enterprise systems. PostgreSQL supports SQL standards, ACID compliance, and offers advanced features like support for JSON data, spatial data, and extensibility through custom functions and extensions. It is renowned for its data integrity, reliability, and community-driven development, making it a preferred database solution for organizations and developers seeking a stable and versatile data storage and retrieval system.

Steps for Installation of PostgreSQL

Create the postgres respository file `/etc/apt/sources.list.d/pgdg.list` using following commmand

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

Import the repository signing key, and update the package lists:

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

Install PostgreSQL Server 16

apt update
apt -y install postgresql

What is pgAdmin4

pgAdmin 4 is an open-source, web-based administration and management tool designed for PostgreSQL, the powerful relational database management system. It provides a user-friendly interface for developers and database administrators to interact with their PostgreSQL databases, offering features like SQL query writing, schema and table management, user access control, and visual query builders. pgAdmin 4 is cross-platform, allowing users to access and manage their databases from various operating systems, making it a valuable tool for simplifying database administration tasks and streamlining the database development process.

Steps for Installation of pgAdmin4

Install the public key for the repository (if not done previously):

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

Create the repository configuration file

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Install pgAdmin4

# Install for both desktop and web modes:
sudo apt install pgadmin4

# Install for desktop mode only:
sudo apt install pgadmin4-desktop

# Install for web mode only: 
sudo apt install pgadmin4-web 

# Configure the webserver, if you installed pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.sh

Create roles for FreeSWITCH

[root@master]# su postgres -
[postgres@master]# psql 
psql (12)
Type "help" for help.

postgres=# create role freeswitch with password 'freeswitch!' login;
CREATE ROLE
postgres=# create role fsadmin with password 'fsadmin' login superuser createdb;
CREATE ROLE

Create a database (for FreeSWITCH)

[root@master]# su postgres -
[postgres@master]# psql 
psql (12)
Type "help" for help.

postgres=# create database freeswitch owner freeswitch;
CREATE DATABASE

postgres=# \q

Install Pg ODBC Driver

apt install -y odbc-postgresql unixodbc

Open `/etc/odbc.ini` in your favorite editor and add following content

[freeswitch]
Description         = PostgreSQL
Driver              = PostgreSQL Unicode
Trace               = No
TraceFile           = /tmp/psqlodbc.log
Database            = freeswitch
Servername          = 127.0.0.1
UserName            = freeswitch
Password            = freeswitch!
Port                = 5432
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
#ConnSettings        = set search_path to coredb,public

Verify if our ODBC Connector for PG is working