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 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
isql freeswitch -v