Postgresql Installation and Settings

| 
# The gnupg package is installed to import the keys of the
repository.
apt-get install -y gnupg
# A pgdg.list file is created to enter repository information.
sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt $(lsb_
release -cs)-pgdg main” > /etc/apt/sources.list.d/pgdg.list’
# Import the signature of the repository.
wget --quiet -O - https://www.postgresql.org/media/keys/
ACCC4CF8.asc | sudo apt-key add -
# The package list is updated.
sudo apt-get update
# To install the latest PostgreSQL version
sudo apt-get -y install postgresql

The gnupg package is needed to avoid getting errors about security keys during
installation. In the first step, we install this package.
In the second step, we create the pgdg.list file in the repositor directory and
provide postgresql link.
Then we enter the security key, update the package list and install.

Note: If you want to install a different version, a version such as ‘postgresql-15’
must be specified.

postgres@srv1:~$ sudo su - postgres
postgres@srv1:~$ psql
psql (14.5 (Debian 14.5-1.pgdg100+1))
Type “help” for help.
postgres=# \l
List of databases
Name      | Owner    | Encoding | Collate     | Ctype       |
-----------+----------+----------+-------------+-------------+
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

The point to note here is that in versions before postgresql 13, it was necessary
to create the database (cluster -this concept was explained in the architecture
module-) with initdb after installation.

Example
initdb -D /var/lib/postgresql/9.6/data -E unicode

In this article, we saw the installation of the Postgresql database on Debian-based
Linux.
Redhat/Centos/Oracle Linux systems are installed with yum instead of apt. Apart from
these, it can be installed by compiling from source code. In the installation from the
source code, we will have to do all the operations one by one.

  • Creating the Postgres user.
  • Preparation of directories.
  • Granting of rights.
  • Downloading and compiling source codes.
  • Initialization of the cluster.
  • Writing scripts and creating services, etc.

All these processes are done manually.

Giving a Postgres User a Password

# Updating a password to the postgres user in the operating
system.
# With root user
passwd postgres
# Password does not appear on the screen while entering, it
prompts us to enter the same password second time.
# Changing the password of the postgres user in the database
# Login to the operating system with the postgres user.
psql
alter user postgres password ‘password’;

When connecting to the server with SSH (ssh username@192.168.134.71), the
user in the operating system is logged in. An SSH connection is an operating systemlevel
connection (VNC, RDP, etc.).
After connecting to the server with SSH, you can connect to the database with psql.

Connecting to Postgresql

# We set the IP / IPs that the Postgresql daemon will listen
to.
# Postgresql DB server’s IP is 192.168.134.71
psql
alter system set listen_addresses=’192.168.134.71’;
# Listens only to IP address provided.
# If your server has more than 1 ethernet and/or IP address and
all IPs are requested to be listened.
alter system set listen_addresses=’*’ ;
# 2. In the pg_hba.conf file, we need to add the IPs to connect
to the database. (It can be added to the end of the page.)
vim /etc/postgresql/13/main/pg_hba.conf
host all all 192.168.134.0/24 md5
# Restart Postgresql for the settings to take effect.
sudo systemctl restart postgresql.service

After these settings, from your host (desktop/laptop) machine you can connect with a
postgres client like pgadmin, psql etc.
Connection via terminal:
psql -h 192.168.134.71 -d db_ismi -U postgres -W

pgAdmin

Enter the name of the database to be connected in the Maintenance database box. If
you want to connect with super user and access all databases, “postgres” can be used.
Since this is a short guide, not much detail has been provided.

Installation and Management of Extensions

# To install the latest package
apt install postgresql-contrib
# If a different version is required, it can be queried with teh following
parameters.
apt list |grep postgresql-contrib
# Query
psql
SELECT * FROM pg_available_extensions;
# List of installed extensions
\dx
# Activate
create extension adminpack ;
# Delete
drop extension adminpack ;

Extensions were previously called add-on and contrib. Now they are called extension n
general.
The functionality of postgrsql can be increased with extensions. A feature that does not
exist in the default can be developed as open source and included in these extensions.
Currently, there are 44 extensions by default.
The adminpack extension provides additional features for remote database
management. For example, management of remote log files (with superuser).