PostgreSQL Installation And Uninstallation

In this article, I have explained about PostgreSQL Database 12.1 installation on Red Hat Linux / CentOS

  • PostgreSQL is an open-source database management system.
  • It is designed to run on different platforms.
  • Its source code is available under PostgreSQL license.
  • PostgreSQL supports both SQL for relational and NoSQL for non-relational queries.
  • Rpm, yum, source installation methods are explained in PostgreSQL database

The Hardware Requirement
 

Memory Requirement

The minimum requirement of physical RAM is 2GB.

RAM Memory

The minimum requirement for memory.

$ grep MemTotal /proc/meminfo

Swap Memory

$ grep SwapTotal /proc/meminfo

System architecture

The system architecture of the OS is 64bit.

$ uname -m

Disk space Requirement

The tmp directory should contain a free disk space of 1GB or more.

$ df -h /tmp 

The Software Requirement

Operating system Requirement

The OS version is Red Hat Enterprise Linux 7/8 and centos.

The kernel version is 2.6.32.

$ cat /proc/version

Package Requirement

The following are the installed packages with version.

(In PostgreSQL Database 12 prior to release 12.1.0, package both 64-bit and 32-bit shown in the following table is required.)

$ Rpm-q package postgres
rpm -ivh postgresql12-libs-12.1-1 PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql12-12.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql12-contrib-12.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql12-server-12.1-1PGDG.rhel7.x86_64.rpm

PostreSQL Installation

We can install PostgreSQL using any one of the below types.

RPM-Manual Installation

If you cannot, or do not want to, use the yum-based installation method, all the RPMs that are in the yum repository are available for direct download and manual installation as well.

YUM -Online Installation

The PostgreSQL Yum Repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the supported lifetime of PostgreSQL.

The PostgreSQL Yum Repository currently supports,

  • Red Hat Enterprise Linux
  • CentOS
  • Scientific Linux
  • Postgres Linux
  • Fedora*

SOURCE - Requirement Installation

Using the make, make all commands we can create a new source based on our requirements

Rpm Installation
 

Pre-request (Enter the root user)

Data path and user creation,

Create “postgres“user
Useradd postgres
Passwd postgres
Create data path /var/lib/data12pgsql
Chmod -R 755 data12pgsql/
Chown -R postgres. data12pgsql/
bin path creation:

Created bin path automatically in /usr while initdb start.

Postgresql 12.1 Installation steps

Step 1 - Download and install Repo

Link: https://yum.postgresql.org/12/redhat/rhel-7-x86_64/

[root@svgen Software]# rpm -ivh pgdg-redhat-repo-42.0-11.noarch.rpm

Preparing... ################################# [100%]

Step 2 - Download the needed “Rpm”from below link

Link: https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/

[root@gokul Postgres_sw]# Rpm are saved in the “Postgres_sw” directory
rpm -ivh postgresql12-libs-12.1-1 PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql12-12.1-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql12-contrib-12.1-1PGDG.rhel7.x86_64.rpm 
rpm -ivh postgresql12-server-12.1-1PGDG.rhel7.x86_64.rpm

Step 3 - Switch user su - postgres

[postgres@gokul ~]$
/usr/pgsql-12/bin/initdb -D /var/lib/data12pgsql

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

Success. You can now start the database server using,

/usr/pgsql-12/bin/pg_ctl -D /var/lib/data12pgsql -l logfile start

Step 4 - Start the service

/usr/pgsql-12/bin/pg_ctl -D /var/lib/data12pgsql status
/usr/pgsql-12/bin/pg_ctl -D /var/lib/data12pgsql start
/usr/pgsql-12/bin/pg_ctl -D /var/lib/data12pgsql stop
[postgres@gokul ~]$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/data12pgsql start

server started.

Step 5 - Connect the database

Post Installation – Configuration

cd /var/lib/data12pgsql
vi pg_hba.conf
# IPv4 local connections:
host all all ::1/128 ident
host all all 0.0.0.0/0 trust
vi postgresql.conf
listen_addresses = 'db.gokulakrishna.me' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)

Note
After the configuration file is edited we must restart the service for changes replication.

Step 7 - Remote Access

Yum Installation

To use the PostgreSQL Yum Repository, follow these steps,

Postgresql Installation and Uninstallation

/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12.service
systemctl start postgresql-12.service

​​​​​​​​​​​​​​Post Installation – Configuration

cd /var/lib/psql/12/datal
vi pg_hba.conf
# IPv4 local connections:
host all all ::1/128 ident
host all all 0.0.0.0/0 trust
vi postgresql.conf
listen_addresses = '10.1.4.78' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 300 # (change requires restart)

Note
After the configuration file is edited we must restart the service for changes replication.

Remote Access

​​​​​​​Source Installation

Step 1

Step 2

Postgresql Installation and Uninstallation

STEP 3

Place the file in /opt/source location.

[root@postgres source]# pwd
/opt/source

Unzip the tar file

[root@ postgres source]# tar -zxvf postgresql-12.0.tar.gz

Step 4

[root@postgres source]# ll
drwxrwxrwx. 6 1107 1107 4096 Jan 31 14:43 postgresql-12.0
-rw-r--r--. 1 root root 26393892 Jan 31 13:01 postgresql-12.0.tar.gz
[root@postgres source]# cd postgresql-12.0/

Step 5

[root@postgrespostgresql-12.0]# ll

total 1204

drwxrwxrwx. 2 1107 1107 4096 Oct 1 01:39 config

Step 6

./configure --prefix=/var/lib/pg1sql

Step 7

make
make install

Step 8

mkdir /var/lib/data
chmod 700 /data
chown -R postgres. Data

Step 9

su - postgres
cd /var/lib/pg1sql/bin
-bash-4.2$ cd pg1sql/
-bash-4.2$ ll
drwxr-xr-x. 2 root root 4096 Jan 31 14:50 bin
-bash-4.2$ cd bin/
-bash-4.2$ ll
-rwxr-xr-x. 1 root root 142520 Jan 31 14:50 initdb

Step 10

.-bash-4.2$ ./initdb -D /var/lib/data

Step 11

-bash-4.2$ ./pg_ctl --help

pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage

pg_ctl init[db] [-D DATADIR] [-s] [-o OPTIONS]
pg_ctl start [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
[-o OPTIONS] [-p PATH] [-c]
pg_ctl stop [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
pg_ctl restart [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
[-o OPTIONS] [-c]
pg_ctl reload [-D DATADIR] [-s]
pg_ctl status [-D DATADIR]
pg_ctl promote [-D DATADIR] [-W] [-t SECS] [-s]
pg_ctl logrotate [-D DATADIR] [-s]
pg_ctl kill SIGNALNAME PID

Common options

-D, --pgdata=DATADIR location of the database storage area
-s, --silent only print errors, no informational messages
-t, --timeout=SECS seconds to wait when using -w option
-V, --version output version information, then exit
-w, --wait wait until operation completes (default)
-W, --no-wait do not wait until operation completes
-?, --help show this help, then exit

If the -D option is omitted, the environment variable PGDATA is used.

Options for stop or restart

-m, --mode=MODE MODE can be "smart", "fast", or "immediate"

Shutdown modes are,

Smart - quit after all clients have disconnected

Fast - quit directly, with proper shutdown (default)

Immediate - quit without complete shutdown; will lead to recovery on restart

Allowed signal names for kill,

ABRT HUP INT KILL QUIT TERM USR1 USR2

Step 12 - HOW TO STOP SERVICE

-bash-4.2$ pwd
/var/lib/pg1sql/bin
postgres-# \q
-bash-4.2$ ./pg_ctl -D /var/lib/data stop

waiting for the server to shut down.... done.

server stopped.

Step 13 - HOW TO START SERVICE

-bash-4.2$ ./pg_ctl -D /var/lib/data start
waiting for server to start....2020-02-03 17:57:07.810 IST [10281] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-02-03 17:57:07.810 IST [10281] LOG: listening on IPv4 address "10.1.4.88", port 5432
2020-02-03 17:57:07.811 IST [10281] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-02-03 17:57:07.820 IST [10282] LOG: database system was shut down at 2020-02-03 17:54:01 IST
2020-02-03 17:57:07.826 IST [10281] LOG: database system is ready to accept connections

done.

server started.

Step 14 - HOW TO STATUS SERVICE

-bash-4.2$ ./pg_ctl -D /var/lib/data status 
pg_ctl: server is running (PID: 10281)
/var/lib/pg1sql/bin/postgres "-D" "/var/lib/data"

Step 15

postgres 7125 7124 0 14:34 pts/1 00:00:00 -bash OS LEVEL FIND OUT SERVICE STATUS
[root@postgres source]# ps -ef |grep postgres
postgres 10281 1 0 17:57 ? 00:00:00 /var/lib/pg1sql/bin/postgres -D /var/lib/data

Step 16

-bash-4.2$ ./pg_ctl -D /var/lib/data status
pg_ctl: server is running (PID: 10281)
/var/lib/pg1sql/bin/postgres "-D" "/var/lib/data"
-bash-4.2$ ./psql -p 5432
psql (12.0)

Type "help" for help.

postgres=#

Step 17

postgres=# \l

List of database,

postgres=# \dn

List of schemas,

​​​​​Post Installation – Configuration

cd /var/lib/psql/12/datal
vi pg_hba.conf
# IPv4 local connections:
host all all ::1/128 ident
host all all 0.0.0.0/0 trust
vi postgresql.conf


listen_addresses = '10.1.4.78' # what IP address(es) to listen on,

port = 5432 # (change requires restart)
max_connections = 300 # (change requires restart)

​​​​​​​Note
After the configuration file is edited we must restart the service for changes replication.

Remote Access

Postgresql Uninstall

Uninstall 11.7 version

Pre-Request for uninstalling

  1. . Enter the postgres user and we need to stop the postmaster service.
  2. . Remove the list of rpm
  3. . Remove the bin path and data-path
  4. . Remove the Postgres user

Step 1 - stop the service

[postgres@gokul ~]$ /usr/pgsql-11/bin/pg_ctl -D /var/lib/datapg11.7 status
/usr/pgsql-11/bin/postgres "-D" "/var/lib/datapg11.7"pg_ctl: server is running (PID: 2236)
[postgres@gokul ~]$ /usr/pgsql-11/bin/pg_ctl -D /var/lib/datapg11.7 stop

waiting for server to shut down.... done

server stopped

Step 2 - remove rpm from gokul Environment

[root@gokul ~]# rpm -qa | grep postgres
postgresql11-libs-11.7-1PGDG.rhel7.x86_64
postgresql11-11.7-1PGDG.rhel7.x86_64
postgresql11-server-11.7-1PGDG.rhel7.x86_64
postgresql11-contrib-11.7-1PGDG.rhel7.x86_64
[root@gokul ~]#rpm -e postgresql11-11.7-1PGDG.rhel7.x86_64
[root@gokul ~]#rpm -e postgresql11-server-11.7-1PGDG.rhel7.x86_64
[root@gokul ~]#rpm -e postgresql11-libs-11.7-1PGDG.rhel7.x86_64
[root@gokul ~]#rpm -e postgresql11-contrib-11.7-1PGDG.rhel7.x86_64

Step 3 - Enter the “root” user remove the bin path and data path from gokul environment

Bin path removal,

[root@gokul ~]# cd /usr
[root@gokul usr]# rm -rf pgsql-11

Data path removal,

[root@gokul ~]# cd /var/lib
[root@gokul lib]# rm -rf datapg11.7

Step 4 - Removal of postgres user

[root@gokul ~]# userdel -r postgres

Successfully uninstalled 11.7 version