Postgresql Installation and Uninstallation

1       Introduction

In this article, I have explained about PostgreSQL Database 12.1 installation in 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 the PostgreSQL database

2       The Hardware Requirement

2.1              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  

2.2              System architecture

The system architecture of the OS is 64bit

$ uname -m   

2.3              Disk space Requirement

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

$ df -h /tmp 

3       The Software Requirement

3.1              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  

3.2              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

 

4       Installation

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

(1)  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.

(2)  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*

(3)  SOURCE - Requirement Installation

Using make, make all commands we can create new source based on our requirement

4.1              Rpm Installation

4.1.1   Pre-request (Enter the root user)

1)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/

 

2).bin path creation:

Created bin path automatically in /usr while initdb start

4.1.2   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                           

 

4.1.3   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 = '10.1.4.45'          # what IP address(es) to listen on;

port = 5432        # (change requires restart)

max_connections = 100                    # (change requires restart)

 

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

 

Step:7 Remote Access

             

4.2              Yum Installation

To use the PostgreSQL Yum Repository, follow these steps:

 /usr/pgsql-12/bin/postgresql-12-setup initdb

  systemctl enable postgresql-12.service

  systemctl start postgresql-12.service

4.2.1   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 configuration file edited we must restart the service for changes replication.

Remote Access

4.3              Source Installation

Step:1:

Step:2

 

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@postgres postgresql-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 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

4.3.1   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

5       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

pg_ctl: server is running (PID: 2236)

/usr/pgsql-11/bin/postgres "-D" "/var/lib/datapg11.7"

[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

Database