Postgres cluster backup using BART tool

Backup and Recovery Tool(BART)

BART is a backup and recovery tool for EDB Postgres Databases. EDB Backup and Recovery Tool (BART) is a key component of an enterprise-level Postgres-based data management strategy. BART implements retention policies and point-in-time recovery requirements for large-scale Postgres deployments. Use version 1.0 for testing purpose which is free. Still this tool you can use for open source postgresql.

Using BART 2.0 or above, you can take incremental backups also. In postgres open source there is no incremental backup till version 12.

The first version of BART 1.0 was released in October of 2014. It:

• Supports complete, hot, physical backups of multiple Postgres Plus Advanced Server and PostgreSQL database servers
• Provides backup and recovery management of the database servers on local or remote hosts
• Uses a single, centralized catalog for backup data
• Displays backup information in an easy-to-read form
• Verifies backup’s with checksums
• Simplifies the point-in-time recovery process

-- Install BART

Install BART rpm to configure to take backup from Multiple clusters from multiple locations

-- RMP Installation

[root@postgres PostgresSoftware]# rpm -ivh edb-bart-1.1.0-1.rhel6.x86_64.rpm
warning: edb-bart-1.1.0-1.rhel6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 7e30651c: NOKEY
Preparing...                ########################################### [100%]
   1:edb-bart               ########################################### [100%]
[root@postgres PostgresSoftware]#


- for demonstration purpose, let us create two cluster in a single host and enable archive log

Data directories and region defined for BART backup
----------------------------------------------------
1) cluster-1 : /opt/demodata_bart - port: 5434 - Region: Hyderabad
2) Cluster-2 : /opt/PostgreSQL/10/data   - port: 5432 - Region: Bangalore

Let us start the POC:

step#1 - Let us assume, a cluster / instance is running on 5432 from '/opt/PostgreSQL/10/data' data directory. Now enable archive log and configure backup location

root@postgres archive_software]# su - postgres
-bash-4.1$
-bash-4.1$ exit
logout
[root@postgres archive_software]# cd /opt/
[root@postgres opt]# mkdir bart_archive
[root@postgres opt]#
[root@postgres opt]# mkdir bart_backup
[root@postgres opt]#
[root@postgres opt]# chown postgres:postgres bart_* -R

[root@postgres opt]#-bash-4.1$ psql -p 5432
psql.bin (10.12)
Type "help" for help.

postgres=# alter system set archive_mode to on;
ALTER SYSTEM
postgres=#
postgres=# alter system set archive_command to 'cp %p /opt/bart_archive/%f';
ALTER SYSTEM
postgres=#
postgres=# \q
-bash-4.1$
-bash-4.1$ pg_ctl -D $PGDATA restart

step#2 - Let us assume, another cluster / instance is running on 5434 from '/opt/demodata_bart' data directory. Now enable archive log and configure backup location

-bash-4.1$ cd /var/demodata/
-bash-4.1$
-bash-4.1$ vi postgresql.conf


archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'cp %p /opt/demodata_bart/%f'

:wq

[root@postgres opt]# mkdir demodata_bart
[root@postgres opt]#
[root@postgres opt]# chown postgres:postgres demodata_bart/ -R
[root@postgres opt]#


-bash-4.1$ pg_ctl -D /var/demodata/ restart

Step# 3 - Configure each region as per the plan

[root@postgres etc]# cd /usr/edb-bart-1.1/etc/
[root@postgres etc]#
[root@postgres etc]# vi bart.cfg

[BART]
bart-host= postgres@127.0.0.1
backup_path = /opt/PostgreSQL/10/bart_backup
pg_basebackup_path = /opt/PostgreSQL/10/bin/pg_basebackup
logfile = /tmp/bart.log

[hyderabad]
host = 127.0.0.1
port = 5434
user = postgres
description = "Postgres server"
retention_policy= 1 BACKUPS

[bangalore]
host = 127.0.0.1
port = 5432
user = postgres
description = "Postgres server"
retention_policy= 1 BACKUPS

:wq

Step# 3 -setup environment variables and Library path 

[root@postgres etc]# su - postgres
-bash-4.1$
-bash-4.1$ cd /usr/edb-bart-1.1/bin/
-bash-4.1$
-bash-4.1$ source /opt/PostgreSQL/10/pg_env.sh
-bash-4.1$
-bash-4.1$ export LD_LIBRARY_PATH=/opt/PostgreSQL/10/lib/
-bash-4.1$


step# 4 - Take backup using BART and see these backups

-bash-4.1$ ./bart BACKUP -s hyderabad

-bash-4.1$ ./bart BACKUP -s hyderabad

INFO:  creating backup for server 'hyderabad'
INFO:  backup identifier: '1588444892965'
40984/40984 kB (100%), 1/1 tablespace

INFO:  backup completed successfully
INFO:  backup checksum: 195ac082e96df56aeaba4b48310e8bb6 of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1588444892965
BACKUP NAME: none
BACKUP LOCATION: /opt/PostgreSQL/10/bart_backup/hyderabad/1588444892965
BACKUP SIZE: 40.02 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
 ChkSum                             File   
 195ac082e96df56aeaba4b48310e8bb6   base.tar

TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000002
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2020-05-03 00:11:37 IST
STOP TIME: 2020-05-03 00:12:02 IST
TOTAL DURATION: 25 sec(s)

-bash-4.1$

-- List backup

-bash-4.1$ ./bart SHOW-BACKUPs -s hyderabad
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 hyderabad     1588444892965   2020-05-03 00:12:02 IST   40.03 MB      0.00 bytes    0           active
         

-bash-4.1$ ./bart BACKUP -s hyderabad


-bash-4.1$ ./bart SHOW-BACKUPs -s hyderabad
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 hyderabad     1588445017589   2020-05-03 00:13:43 IST   40.03 MB      0.00 bytes    0           active
 hyderabad     1588444892965   2020-05-03 00:12:02 IST   40.03 MB      0.00 bytes    0           active
                                                                                                       
-bash-4.1$

-bash-4.1$ ./bart SHOW-BACKUPs -s bangalore
 SERVER NAME   BACKUP ID   BACKUP TIME   BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS

-bash-4.1$ ./bart BACKUP -s bangalore

ERROR: backup failed for server 'bangalore'
connection to the server failed: fe_sendauth: no password supplied

The error because of permissions not given to bangalore region where 5432 cluster is configured.

let us fix:

-bash-4.1$ cd cd /opt/PostgreSQL/10/data
-bash-4.1$
-bash-4.1$ vi pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 md5

:wq

-bash-4.1$ pg_ctl -D $PGDATA reload
server signaled

-bash-4.1$ cd /usr/edb-bart-1.1/bin/
-bash-4.1$
-bash-4.1$ ./bart BACKUP -s bangalore

-bash-4.1$ ./bart BACKUP -s bangalore

-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 bangalore     1588445851470   2020-05-03 00:27:36 IST   117.46 MB     0.00 bytes    0           active
 bangalore     1588445680120   2020-05-03 00:24:56 IST   117.46 MB     0.00 bytes    0           active


If you will go to physical location, you will find folder names with region where you have configured in BART configuration file.

e.g.,

-bash-4.1$ cd /opt/PostgreSQL/10/bart_backup
-bash-4.1$ ll
total 8
drwx------. 5 postgres postgres 4096 May  3 00:27 bangalore
drwxr-xr-x. 5 postgres postgres 4096 May  3 00:13 hyderabad
-bash-4.1$

let us see what inside these folders.

-bash-4.1$ pwd
/opt/PostgreSQL/10/bart_backup/bangalore
-bash-4.1$ ls
1588445680120  1588445851470  archived_wals
-bash-4.1$

Now see here, backup IDs are matching to these directories what we saw in "bart SHOW-BACKUPS -s bangalore" outputs. i.e., for each backup a folder is created.
and actually .tar file created what we are usually doing in pg_basebackup

e.g.,

-bash-4.1$ cd 1588445680120
-bash-4.1$ pwd
/opt/PostgreSQL/10/bart_backup/bangalore/1588445680120
-bash-4.1$ ls
16430.tar  16434.tar  backupinfo  base.tar
-bash-4.1$

But, you may notice one thing, we took backup two times for each region and in BART configuration we mentioned retention as one. So here, why it is showing 'ACTIVE' for each backup.

let us see:

-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 bangalore     1588445851470   2020-05-03 00:27:36 IST   117.46 MB     0.00 bytes    0           active
 bangalore     1588445680120   2020-05-03 00:24:56 IST   117.46 MB     0.00 bytes    0           active
       
-- Manage backups

So here we need to manage backup using 'MANAGE' command from BART.

e.g.,

-bash-4.1$ ./bart MANAGE -s bangalore
INFO:  processing server 'bangalore', backup '1588445851470'
INFO:  processing server 'bangalore', backup '1588445680120'
INFO:  marking backup '1588445680120' as obsolete
INFO:  0 WAL file(s) marked obsolete
-bash-4.1$

Now list the backups again:

-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS 
                                                                                                         
 bangalore     1588445851470   2020-05-03 00:27:36 IST   117.46 MB     0.00 bytes    0           active 
 bangalore     1588445680120   2020-05-03 00:24:56 IST   117.46 MB     0.00 bytes    0           obsolete
                                                                                                         
-bash-4.1$

-- Delete backup piece from BART

-bash-4.1$ ./bart DELETE -s bangalore -i 1588445680120
INFO:  deleting backup '1588445680120' of server 'bangalore'
INFO:  deleting backup '1588445680120'
INFO:  0 WAL file(s) will be removed
INFO:  backup(s) deleted
-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 bangalore     1588445851470   2020-05-03 00:27:36 IST   117.46 MB     0.00 bytes    0           active
                                                                                                       
-bash-4.1$

what are _fsm and _vm files in postgres?

_fsm and _vm files will be not calculated while checking the table size.

_fsm :
                free space map file :how face free space pages available for usage.

_vm :   
               visibility map: what are the pages already occupied. excluding dead tuples.


e.g.,

$ cd <pg_base_dir/database_id>
-rw-------. 1 postgres postgres   8192 Apr 19 18:59 2608_vm
-rw-------. 1 postgres postgres  24576 Apr 19 18:59 2608_fsm
-rw-------. 1 postgres postgres 442368 Apr 19 18:59 2608
-rw-------. 1 postgres postgres   8192 Apr 19 18:59 1259_vm
-rw-------. 1 postgres postgres   8192 Apr 19 18:59 2619_vm
-rw-------. 1 postgres postgres  24576 Apr 19 18:59 2619_fsm

......

Query to find parameters which requires restart in postgresql

Ans:

select name,setting from pg_settings where context='postmaster';

e.g.,

postgres=#  select name,setting from pg_settings where context='postmaster';
                name                 |                 setting                 
-------------------------------------+-----------------------------------------
 allow_system_table_mods             | off
 archive_mode                        | off
 autovacuum_freeze_max_age           | 200000000
 autovacuum_max_workers              | 3
 autovacuum_multixact_freeze_max_age | 400000000
 bonjour                             | off

.....

Fixing psql version issue in postgres

-- Fixing psql version issue
-- Higher postgres version commands will not work even postgres cluster is higher version and your psql version is lower version

Example: \ds commands will not work psql 8

case : \ds command not worked even after using postgres 10.12

example:

postgres=# \db
ERROR:  column "spclocation" does not exist
LINE 3:   spclocation AS "Location"

-- Find postgres cluster version
          
postgres=# select version();
                               version                                                  
--------------------------------------------------------------------
 PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)

postgres=# select spcname from pg_tablespace;
  spcname   
------------
 pg_default
 pg_global
 pg_demo_ts
(3 rows)

postgres=# \db
ERROR:  column "spclocation" does not exist
LINE 3:   spclocation AS "Location"
          ^
postgres=# \q

-- See your psql version

bash-4.1$ psql postgres
Password: 
psql (8.4.18, server 10.12)
WARNING: psql version 8.4, server version 10.12.
         Some psql features might not work.
Type "help" for help.

postgres-# \q
bash-4.1$ 

Let us fix: ( Note: you use your postgres binary location)
Find pg_env.sh file

bash-4.1$ cd /opt/PostgreSQL/10

bash-4.1$ ls pg_env.sh
pg_env.sh  

Run below shell script:

bash-4.1$ source pg_env.sh

bash-4.1$ psql postgres
Password: 
psql.bin (10.12)
Type "help" for help.

Now run the command, it will work.

postgres=# \db
             List of tablespaces
    Name    |  Owner   |       Location       
------------+----------+----------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 pg_demo_ts | postgres | /home/demo_base
(3 rows)

postgres=# 

How to use pgbadger in open source postgres and generate pgbadger report

Sometimes logs are the last things checked when things are going wrong, but they are usually the first things screaming for help when something happens. Manually looking through the logs for problems helps, but why not use log analyzers to automatically generate reports to provide insight on the database before something goes wrong?

The PostgreSQL log analyzer “pg-badger” is an open source “fast PostgreSQL log analysis report” program written in Perl that takes the log output from a running PostgreSQL instance and processes it into an HTML file. The report it generates shows all information found in a nice and easy to read report format. These reports can help shed light on errors happening in the system, checkpoint behavior, vacuum behavior, trends, and other basic but crucial information for a PostgreSQL system.

Demonstration: How to generate pg-badger report:

Note: Here we are not analyze the report.

-- Let us create a new PostgreSQL cluster and then we ll create new databases, users and add some load

-- Create a base directory

# cd /opt/PostgreSQL/10/

# mkdir pgdata

[root@postgres 10]# chown postgres:postgres pgdata -R

-- Initialize the base directory 

[root@postgres 10]# su - postgres
$ pwd
/opt/PostgreSQL/10

$ initdb -D pgdata
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
....
....
Success. You can now start the database server using:

    pg_ctl -D pgdata -l logfile start
$

$ 

-- Now change the port number to access the new cluster change given below log settings

$ cd pgdata/
$ ls postgresql.conf 
postgresql.conf


$ vi postgresql.conf

port=5005

log_destination = 'stderr'
logging_collector = on
log_rotation_age = 0
client_min_messages = notice
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_error_verbosity = verbose
log_hostname = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_lock_waits = on
log_statement = 'all'
log_temp_files = 0

:wq

-- Now start the cluster

pg_ctl -D /opt/PostgreSQL/10/pgdata start

$ pg_ctl -D /opt/PostgreSQL/10/pgdata start
waiting for server to start....2020-04-19 22:19:33 IST [17280]: [1-1] user=,db= LOG:  00000: listening on IPv6 address "::1", port 5005
2020-04-19 22:19:33 IST [17280]: [2-1] user=,db= LOCATION:  StreamServerPort, pqcomm.c:593
2020-04-19 22:19:33 IST [17280]: [3-1] user=,db= LOG:  00000: listening on IPv4 address "127.0.0.1", port 5005
2020-04-19 22:19:33 IST [17280]: [4-1] user=,db= LOCATION:  StreamServerPort, pqcomm.c:593
2020-04-19 22:19:33 IST [17280]: [5-1] user=,db= LOG:  00000: listening on Unix socket "/tmp/.s.PGSQL.5005"
2020-04-19 22:19:33 IST [17280]: [6-1] user=,db= LOCATION:  StreamServerPort, pqcomm.c:587
2020-04-19 22:19:33 IST [17280]: [7-1] user=,db= LOG:  00000: redirecting log output to logging collector process
2020-04-19 22:19:33 IST [17280]: [8-1] user=,db= HINT:  Future log output will appear in directory "log".
2020-04-19 22:19:33 IST [17280]: [9-1] user=,db= LOCATION:  SysLogger_Start, syslogger.c:666
 done
server started
$ 


-- Connect to the newly created cluster

$ psql -p 5005 postgres
psql.bin (10.9)
Type "help" for help.

-- From here now create some users, create databases and cone=nect to these databases and create some load
Example:

postgres=# create user user1 password 'Pas$w0rd';
CREATE ROLE
postgres=# create user hr password 'Pas$w0rd';
CREATE ROLE
postgres=# create user admin password 'Pas$w0rd';
CREATE ROLE
postgres=# create database db1;
CREATE DATABASE
postgres=# create database db2;
CREATE DATABASE
postgres=# create database db3;
CREATE DATABASE
postgres=# \c db1 user1
You are now connected to database "db1" as user "user1".
db1=> create table tbl_test as select * from pg_class;
SELECT 341
db1=> select count(*) from tbl_test;
 count 
-------
   341
(1 row)

db1=> \c db2 hr
You are now connected to database "db2" as user "hr".
db2=> create table tbl_test1 (id int);
CREATE TABLE
db2=> insert into tbl_test1 select * from generate_Series(1,100000) order by random();
INSERT 0 100000
db2=> update tbl_test set id=1 where id between 1 and 100;
UPDATE 100
db2=> 
db2=> \c db3 admin
You are now connected to database "db3" as user "admin".
db3=> 
db3=> 
db3=> create table tbl_test2 (id int,name varchar);
CREATE TABLE
db3=> 
db3=> 
db3=> insert into tbl_test2 values(generate_series(1,100000),'data '||generate_series(1,100000));
INSERT 0 100000

db3=> 
db3=> 
db3=> delete from tbl_test2 where id between 1 and 6000;
DELETE 6000
db3=> 
db3=> 
db3=> select count(*) from tbl_test2;
 count 
-------
 94000
(1 row)

db3=> \q

-- Now download the pg-badger and copy to some location

https://sourceforge.net/projects/pgbadger/

Example:
#
[root@postgres Postgres Software]# cp pgbadger-master.zip /opt
[root@postgres Postgres Software]# cd /opt


[root@postgres opt]# unzip pgbadger-master.zip 

[root@postgres opt]# cd /opt/pgbadger-master
[root@postgres pgbadger-master]# pwd
/opt/pgbadger-master
[root@postgres log]# /opt/PostgreSQL/10/pgdata/log
[root@postgres log]# ls
postgresql-2020-04-19_221933.log
[root@postgres log]# /opt/pgbadger-master
[root@postgres pgbadger-master]# 
[root@postgres pgbadger-master]# ./pgbadger -f stderr -o report.html /opt/PostgreSQL/10/pgdata/log/postgresql-2020-04-19_221933.log 

e.g.,

[root@postgres pgbadger-master]# ./pgbadger -f stderr -o report.html /opt/PostgreSQL/10/pgdata/log/postgresql-2020-04-19_221933.log
[========================>] Parsed 15468 bytes of 15468 (100.00%), queries: 28, events: 3
LOG: Ok, generating html report...
[root@postgres pgbadger-master]# 

[root@postgres pgbadger-master]# ls -ltr
total 2148
drwxr-xr-x. 2 root root    4096 Apr 18  2016 tools
-rw-r--r--. 1 root root   30433 Apr 18  2016 README
-rwxr-xr-x. 1 root root 1219877 Apr 18  2016 pgbadger
-rw-r--r--. 1 root root     334 Apr 18  2016 META.yml
-rw-r--r--. 1 root root      81 Apr 18  2016 MANIFEST
-rw-r--r--. 1 root root    1400 Apr 18  2016 Makefile.PL
-rw-r--r--. 1 root root     903 Apr 18  2016 LICENSE
drwxr-xr-x. 2 root root    4096 Apr 18  2016 doc
-rw-r--r--. 1 root root     347 Apr 18  2016 CONTRIBUTING.md
-rw-r--r--. 1 root root   81420 Apr 18  2016 ChangeLog
-rw-r--r--. 1 root root  832859 Apr 19 22:41 report.html

Now download report.html to your windows machine and analyze it.


Here is one sample screen-shot:

create custom template database and create new database using custom template database in postgres

Here you can see one demo on below requirement:

create custom template database and create new database using custom template database in postgres

-- connect to to your database

$ psql postgres

postgres# \c template1

template1=# select datname from pg_database where datistemplate=true;
  datname  
-----------
 template1
 template0
(2 rows)

-- Note: you can't use template0 database
e.g.,

template1=# \c template0 postgres
FATAL:  database "template0" is not currently accepting connections
Previous connection kept
template1=# 

-- create new database e.g., template2

template1=# create database template2;
CREATE DATABASE
template1=# 
template1=# \c template2 postgres
You are now connected to database "template2" as user "postgres".
template2=# 

-- Create required objects in this new database
e.g.,
template2=# create table tblx as select * from pg_class;
SELECT 341
template2=# 
template2=# create table tblz as select * from pg_description;
SELECT 4560
template2=# 
template2=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tblx | table | postgres
 public | tblz | table | postgres
(2 rows)

-- Now convert this database as a template database

template2=# update pg_database set datistemplate=true where datname='template2';
UPDATE 1
template2=# 
template2=# select datname from pg_database where datistemplate=true;
  datname  
-----------
 template1
 template0
 template2
(3 rows)

-- Now you can create new custom database using custom template database e.g., template2

template2=# create database testdb template template2;
CREATE DATABASE
template2=# 
template2=# \c testdb postgres
You are now connected to database "testdb" as user "postgres".
testdb=# 
testdb=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tblx | table | postgres
 public | tblz | table | postgres
(2 rows)

testdb=# \dt+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size  | Description 
--------+------+-------+----------+--------+-------------
 public | tblx | table | postgres | 80 kB  | 
 public | tblz | table | postgres | 320 kB | 
(2 rows)

Source code PostgreSQL software installation on Linux system

About PostgreSQL:

PostgreSQL is a powerful, open source object-relational database system. It has more than 17 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, macOS, Solaris, etc.), and Windows. It is fully ACID compliant

PostgreSQL database limits:

Limit Value
------- ------------------
Maximum Database Size Unlimited
Maximum Table Size 32 TB
Maximum Row Size 1.6 TB
Maximum Field Size 1 GB
Maximum Rows per Table Unlimited
Maximum Columns /Table 250 - 1600 depending on column types
Maximum Indexes /Table Unlimited

To install progresql on Linux platform, you can follow below steps :

Step 1: Download postgreSQL source code:
Download the software from :

https://www.postgresql.org/ftp/source/ or Click Here

Download the required version. Here I downloaded v.11.0. Then follow below steps:

Step 2: Create postgreSQL user account
# adduser postgres
# passwd postgres
Changing password for user postgres.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

verify:
[root@example03 home]# cat /etc/passwd|grep /bin/bash
postgres:x:1006:1007::/home/postgres:/bin/bash
[root@example03 home]#

[root@example03 home]# cat /etc/group|grep postgres
postgres:x:1007:

Step 3: Install postgreSQL

source code installation
------------------------------------------------------------
you can make that user as sudo user.

[root@example03 home]# vi /etc/sudoers
postgres ALL=(ALL)      ALL


[root@example03 home]# whoami
root
[root@example03 home]# su - postgres
Last failed login: Sat Aug 10 23:09:45 PDT 2019 on pts/0
There were 4 failed login attempts since the last successful login.
[postgres@example03 ~]$ whoami
postgres


[postgres@example03 ~]$ pwd
/home/postgres


before going to install source code, we have to check /usr/local/ folder, under this folder there should not be pgsql folder. if pgsql folder is there already some one installed. make sure if pgsql folder not there, if it is there try to rename that folder or else try to install your s/w with different directory.


download the software.
click on source code.
select version
select 1st or 4th file and click on copy link address


[root@example03 pgsql]# wget https://ftp.postgresql.org/pub/source/v11.0/postgresql-11.0.tar.bz2
--2019-08-17 18:31:37--  https://ftp.postgresql.org/pub/source/v11.0/postgresql-11.0.tar.bz2
Resolving ftp.postgresql.org (ftp.postgresql.org)... 204.145.124.244, 217.196.149.55, 72.32.157.246, ...
Connecting to ftp.postgresql.org (ftp.postgresql.org)|204.145.124.244|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 21206820 (20M) [application/x-bzip-compressed-tar]
Saving to: ‘postgresql-11.0.tar.bz2’

100%[===================================================================================================================>] 21,206,820  3.05MB/s   in 12s

2019-08-17 18:31:50 (1.72 MB/s) - ‘postgresql-11.0.tar.bz2’ saved [21206820/21206820]

OR
You can download the file and do SCP to linux host;

[root@example03 pgsql]# tar -xvf postgresql-11.0.tar.bz2


[root@example03 pgsql]# cd postgresql-11.0
[root@example03 postgresql-11.0]# ls -ltr
total 740
-rw-r--r--  1 1107 1107   1212 Oct 15  2018 README
-rw-r--r--  1 1107 1107   1682 Oct 15  2018 Makefile
-rw-r--r--  1 1107 1107    284 Oct 15  2018 HISTORY
-rw-r--r--  1 1107 1107   3664 Oct 15  2018 GNUmakefile.in
-rw-r--r--  1 1107 1107   1192 Oct 15  2018 COPYRIGHT
-rw-r--r--  1 1107 1107  83596 Oct 15  2018 configure.in
-rwxr-xr-x  1 1107 1107 558874 Oct 15  2018 configure
-rw-r--r--  1 1107 1107    486 Oct 15  2018 aclocal.m4
drwxrwxrwx 56 1107 1107   4096 Oct 15  2018 contrib
drwxrwxrwx  2 1107 1107   4096 Oct 15  2018 config
drwxrwxrwx  3 1107 1107    101 Oct 15  2018 doc
-rw-r--r--  1 1107 1107  72717 Oct 15  2018 INSTALL
drwxrwxrwx 16 1107 1107   4096 Oct 15  2018 src
[root@example03 postgresql-11.0]#


[root@example03 postgresql-11.0]# ./configure --without-readline --without-zlib (this is in demo redhat)

[root@example03 postgresql-11.0]# ./configure (in realtime)


[root@example03 postgresql-11.0]# make
make[1]: Leaving directory `/var/lib/pgsql/postgresql-11.0/config'
All of PostgreSQL successfully made. Ready to install.
[root@example03 postgresql-11.0]#



[root@example03 postgresql-11.0]# make install
make[1]: Leaving directory `/var/lib/pgsql/postgresql-11.0/config'
PostgreSQL installation complete.

in postgreql in addition to actual they have given some more utilities with contrib module, so we have to install contrib module as well.

[root@example03 postgresql-11.0]# cd contrib
[root@example03 contrib]# make

[root@example03 contrib]# make install

------------------
validation:
-------------------
[root@example03 local]# cd pgsql/
[root@example03 pgsql]# ls -ltr
total 16
drwxr-xr-x 6 root root 4096 Aug 17 19:18 include
drwxr-xr-x 7 root root 4096 Aug 17 19:22 share
drwxr-xr-x 4 root root 4096 Aug 17 19:22 lib
drwxr-xr-x 2 root root 4096 Aug 17 19:22 bin
[root@example03 pgsql]# cd bin/
[root@example03 bin]# ls -ltr
total 11580
-rwxr-xr-x 1 root root 7905413 Aug 17 19:18 postgres
lrwxrwxrwx 1 root root       8 Aug 17 19:18 postmaster -> postgres
-rwxr-xr-x 1 root root  962424 Aug 17 19:18 ecpg
-rwxr-xr-x 1 root root  132667 Aug 17 19:18 initdb
-rwxr-xr-x 1 root root   30328 Aug 17 19:18 pg_archivecleanup
-rwxr-xr-x 1 root root  111843 Aug 17 19:18 pg_basebackup
-rwxr-xr-x 1 root root   74534 Aug 17 19:18 pg_receivewal
-rwxr-xr-x 1 root root   75333 Aug 17 19:18 pg_recvlogical
-rwxr-xr-x 1 root root   33840 Aug 17 19:18 pg_config
-rwxr-xr-x 1 root root   42812 Aug 17 19:18 pg_controldata
-rwxr-xr-x 1 root root   58294 Aug 17 19:18 pg_ctl
-rwxr-xr-x 1 root root  403980 Aug 17 19:18 pg_dump
-rwxr-xr-x 1 root root  168941 Aug 17 19:18 pg_restore
-rwxr-xr-x 1 root root   94715 Aug 17 19:18 pg_dumpall
-rwxr-xr-x 1 root root   53054 Aug 17 19:18 pg_resetwal
-rwxr-xr-x 1 root root   91500 Aug 17 19:18 pg_rewind
-rwxr-xr-x 1 root root   35221 Aug 17 19:18 pg_test_fsync
-rwxr-xr-x 1 root root   30008 Aug 17 19:18 pg_test_timing
-rwxr-xr-x 1 root root  134659 Aug 17 19:18 pg_upgrade
-rwxr-xr-x 1 root root   43526 Aug 17 19:18 pg_verify_checksums
-rwxr-xr-x 1 root root   90182 Aug 17 19:18 pg_waldump
-rwxr-xr-x 1 root root  158854 Aug 17 19:18 pgbench
-rwxr-xr-x 1 root root  461604 Aug 17 19:18 psql
-rwxr-xr-x 1 root root   63466 Aug 17 19:18 createdb
-rwxr-xr-x 1 root root   58814 Aug 17 19:18 dropdb
-rwxr-xr-x 1 root root   64143 Aug 17 19:18 createuser
-rwxr-xr-x 1 root root   58784 Aug 17 19:18 dropuser
-rwxr-xr-x 1 root root   63646 Aug 17 19:18 clusterdb
-rwxr-xr-x 1 root root   68462 Aug 17 19:18 vacuumdb
-rwxr-xr-x 1 root root   63742 Aug 17 19:18 reindexdb
-rwxr-xr-x 1 root root   58799 Aug 17 19:18 pg_isready
-rwxr-xr-x 1 root root   29914 Aug 17 19:22 oid2name
-rwxr-xr-x 1 root root   30494 Aug 17 19:22 pg_standby
-rwxr-xr-x 1 root root   29821 Aug 17 19:22 vacuumlo
[root@example03 bin]#


in source code by default you will not get any defualt configuration files or data file. To generate all the default files we have to perform database initialization by using initdb utility.

To generate default files we need specific diretory, we call that directory as DATA directory.

[root@example03 bin]# mkdir /usr/pgsql

to work with postgresql, all the folders related postgresql should be owned by postgres user.

[root@example03 pgsql]# chown -R postgres:postgres /usr/local/pgsql/
[root@example03 pgsql]# chown -R postgres:postgres /usr/pgsql/


if you want to work with any postgres utility you should run the command with postgres user only.

[root@example03 pgsql]# su - postgres
Last login: Sat Aug 17 18:23:06 PDT 2019 on pts/0
[postgres@example03 ~]$ whoami
postgres
[postgres@example03 ~]$ /usr/local/pgsql/bin/initdb -D /usr/pgsql/
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.

fixing permissions on existing directory /usr/pgsql ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ -l logfile start



[postgres@example03 ~]$ cd /usr/pgsql/
[postgres@example03 pgsql]$ ls -ltr
total 48
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_twophase
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_tblspc
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_stat
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_snapshots
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_serial
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_replslot
drwx------ 4 postgres postgres    34 Aug 17 19:31 pg_multixact
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_dynshmem
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_commit_ts
-rw------- 1 postgres postgres     3 Aug 17 19:31 PG_VERSION
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_stat_tmp
-rw------- 1 postgres postgres 23799 Aug 17 19:31 postgresql.conf
-rw------- 1 postgres postgres    88 Aug 17 19:31 postgresql.auto.conf
-rw------- 1 postgres postgres  1636 Aug 17 19:31 pg_ident.conf
-rw------- 1 postgres postgres  4513 Aug 17 19:31 pg_hba.conf
drwx------ 2 postgres postgres    17 Aug 17 19:31 pg_xact
drwx------ 3 postgres postgres    58 Aug 17 19:31 pg_wal
drwx------ 2 postgres postgres    17 Aug 17 19:31 pg_subtrans
drwx------ 2 postgres postgres    17 Aug 17 19:31 pg_notify
drwx------ 2 postgres postgres  4096 Aug 17 19:31 global
drwx------ 5 postgres postgres    38 Aug 17 19:31 base
drwx------ 4 postgres postgres    65 Aug 17 19:31 pg_logical
[postgres@example03 pgsql]$


start and stop services:
--------------------------------
To stop and start postgresql services we have an utility called pg_ctl.

[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ status
pg_ctl: no server running
[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ start
waiting for server to start....2019-08-17 19:33:36.642 PDT [14521] LOG:  listening on IPv6 address "::1", port 5432
2019-08-17 19:33:36.642 PDT [14521] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:33:36.642 PDT [14521] LOG:  could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:33:36.642 PDT [14521] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:33:36.645 PDT [14521] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:33:36.655 PDT [14522] LOG:  database system was shut down at 2019-08-17 19:31:15 PDT
2019-08-17 19:33:36.657 PDT [14521] LOG:  database system is ready to accept connections
 done
server started

[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ status
pg_ctl: server is running (PID: 14521)
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
[postgres@example03 pgsql]$ ps -ef|grep postgres
root      14457   2204  0 19:30 pts/0    00:00:00 su - postgres
postgres  14458  14457  0 19:30 pts/0    00:00:00 -bash
postgres  14521      1  0 19:33 pts/0    00:00:00 /usr/local/pgsql/bin/postgres -D /usr/pgsql
postgres  14523  14521  0 19:33 ?        00:00:00 postgres: checkpointer
postgres  14524  14521  0 19:33 ?        00:00:00 postgres: background writer
postgres  14525  14521  0 19:33 ?        00:00:00 postgres: walwriter
postgres  14526  14521  0 19:33 ?        00:00:00 postgres: autovacuum launcher
postgres  14527  14521  0 19:33 ?        00:00:00 postgres: stats collector
postgres  14528  14521  0 19:33 ?        00:00:00 postgres: logical replication launcher
postgres  14530  14458  0 19:33 pts/0    00:00:00 ps -ef
postgres  14531  14458  0 19:33 pts/0    00:00:00 grep --color=auto postgres

[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ stop
waiting for server to shut down....2019-08-17 19:34:12.429 PDT [14521] LOG:  received fast shutdown request
2019-08-17 19:34:12.433 PDT [14521] LOG:  aborting any active transactions
2019-08-17 19:34:12.436 PDT [14521] LOG:  background worker "logical replication launcher" (PID 14528) exited with exit code 1
2019-08-17 19:34:12.436 PDT [14523] LOG:  shutting down
2019-08-17 19:34:12.446 PDT [14521] LOG:  database system is shut down
 done
server stopped

[postgres@example03 pgsql]$ ps -ef|grep postgres
root      14457   2204  0 19:30 pts/0    00:00:00 su - postgres
postgres  14458  14457  0 19:30 pts/0    00:00:00 -bash
postgres  14533  14458  0 19:34 pts/0    00:00:00 ps -ef
postgres  14534  14458  0 19:34 pts/0    00:00:00 grep --color=auto postgres

[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ start
waiting for server to start....2019-08-17 19:34:21.739 PDT [14537] LOG:  listening on IPv6 address "::1", port 5432
2019-08-17 19:34:21.739 PDT [14537] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:34:21.739 PDT [14537] LOG:  could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:34:21.739 PDT [14537] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:34:21.742 PDT [14537] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:34:21.754 PDT [14538] LOG:  database system was shut down at 2019-08-17 19:34:12 PDT
2019-08-17 19:34:21.757 PDT [14537] LOG:  database system is ready to accept connections
 done
server started

[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ reload
server signaled
2019-08-17 19:34:29.474 PDT [14537] LOG:  received SIGHUP, reloading configuration files
[postgres@example03 pgsql]$


[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ restart
waiting for server to shut down....2019-08-17 19:34:58.743 PDT [14537] LOG:  received fast shutdown request
2019-08-17 19:34:58.745 PDT [14537] LOG:  aborting any active transactions
2019-08-17 19:34:58.747 PDT [14537] LOG:  background worker "logical replication launcher" (PID 14544) exited with exit code 1
2019-08-17 19:34:58.747 PDT [14539] LOG:  shutting down
2019-08-17 19:34:58.756 PDT [14537] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2019-08-17 19:34:58.851 PDT [14547] LOG:  listening on IPv6 address "::1", port 5432
2019-08-17 19:34:58.851 PDT [14547] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:34:58.851 PDT [14547] LOG:  could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:34:58.851 PDT [14547] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:34:58.854 PDT [14547] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:34:58.865 PDT [14548] LOG:  database system was shut down at 2019-08-17 19:34:58 PDT
2019-08-17 19:34:58.869 PDT [14547] LOG:  database system is ready to accept connections
 done
server started
[postgres@example03 pgsql]$


Note : default mode is fast shut down.

[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ stop -m smart
waiting for server to shut down....2019-08-17 19:37:31.428 PDT [14547] LOG:  received smart shutdown request
2019-08-17 19:37:31.432 PDT [14547] LOG:  background worker "logical replication launcher" (PID 14554) exited with exit code 1
2019-08-17 19:37:31.432 PDT [14549] LOG:  shutting down
2019-08-17 19:37:31.441 PDT [14547] LOG:  database system is shut down
 done
server stopped

[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ start
waiting for server to start....2019-08-17 19:37:45.214 PDT [14562] LOG:  listening on IPv6 address "::1", port 5432
2019-08-17 19:37:45.214 PDT [14562] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:37:45.214 PDT [14562] LOG:  could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:37:45.214 PDT [14562] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:37:45.216 PDT [14562] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:37:45.226 PDT [14563] LOG:  database system was shut down at 2019-08-17 19:37:31 PDT
2019-08-17 19:37:45.229 PDT [14562] LOG:  database system is ready to accept connections
 done
server started

[postgres@example03 pgsql]$ /usr/local/pgsql/bin/pg_ctl -D /usr/pgsql/ stop -m immediate
waiting for server to shut down....2019-08-17 19:37:55.603 PDT [14562] LOG:  received immediate shutdown request
2019-08-17 19:37:55.605 PDT [14567] WARNING:  terminating connection because of crash of another server process
2019-08-17 19:37:55.605 PDT [14567] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2019-08-17 19:37:55.605 PDT [14567] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2019-08-17 19:37:55.607 PDT [14562] LOG:  database system is shut down
 done
server stopped
[postgres@example03 pgsql]$


how to enable services auto start:
----------------------------------------------
[root@example03 lib]# cd /var/lib/pgsql/postgresql-11.0/contrib/start-scripts/
[root@example03 start-scripts]# cp linux /etc/init.d/postgresql10


[root@example03 start-scripts]# chmod 775 /etc/init.d/postgresql10
[root@example03 start-scripts]#
[root@example03 start-scripts]#
[root@example03 start-scripts]# /etc/init.d/postgresql10 status
pg_ctl: server is running (PID: 14573)
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
[root@example03 start-scripts]#
[root@example03 start-scripts]#
[root@example03 start-scripts]# /etc/init.d/postgresql10 stop
Stopping PostgreSQL: 2019-08-17 19:43:12.244 PDT [14573] LOG:  received fast shutdown request
2019-08-17 19:43:12.249 PDT [14573] LOG:  aborting any active transactions
2019-08-17 19:43:12.253 PDT [14573] LOG:  background worker "logical replication launcher" (PID 14580) exited with exit code 1
2019-08-17 19:43:12.253 PDT [14575] LOG:  shutting down
2019-08-17 19:43:12.267 PDT [14573] LOG:  database system is shut down
ok
[root@example03 start-scripts]# /etc/init.d/postgresql10 status
pg_ctl: no server running
[root@example03 start-scripts]# /etc/init.d/postgresql10 start
Starting PostgreSQL: ok
[root@example03 start-scripts]# /etc/init.d/postgresql10 status
pg_ctl: server is running (PID: 14845)
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"
[root@example03 start-scripts]#


in the file fill proper installation location and data directory location.

[postgres@example03 ~]$ cd /usr/pgsql/
[postgres@example03 pgsql]$ ls -ltr
total 60
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_tblspc:all the user created tablespaces information will be recorded here.
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_snapshots: if you create any snapshots, that information will be recorded here, just awr report.
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_replslot: during the replication setup we will disucss
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_dynshmem: shared memory dynamic allocation information will be recorded here.
drwx------ 2 postgres postgres     6 Aug 17 19:31 pg_commit_ts: all the commited transaction infromation will be recorded here
-rw------- 1 postgres postgres     3 Aug 17 19:31 PG_VERSION: it will displays the version
-rw------- 1 postgres postgres 23799 Aug 17 19:31 postgresql.conf --complete parameter managed by this file
-rw------- 1 postgres postgres    88 Aug 17 19:31 postgresql.auto.conf: if you are setting parameter by using alter system set command, those entries will be recorded here.
-rw------- 1 postgres postgres  1636 Aug 17 19:31 pg_ident.conf: some times people will try to conenct db with ldap account, in that case that user details we have to mention here.
-rw------- 1 postgres postgres  4513 Aug 17 19:31 pg_hba.conf:to prevent/manage remote connections
drwx------ 2 postgres postgres    17 Aug 17 19:3pg_Wal : all the wal files will be recorded here.
drwx------ 2 postgres postgres    17 Aug 17 19:31 pg_subtrans: as part of joins, temp tables creations more subtransactions will happens, that data will be managed here.
drwx------ 2 postgres postgres  4096 Aug 17 19:31 global:only global obejcts infromation will be stored here (users, user access rigths, tablesapces)
drwx------ 5 postgres postgres    38 Aug 17 19:31 base: our objects actual data (tables,materilized views,database)
drwx------ 4 postgres postgres    65 Aug 17 19:43 pg_logical: as part of logical replication data will be stored here.
-rw------- 1 postgres postgres    48 Aug 17 19:43 postmaster.opts

[postgres@example03 pgsql]$ cat postmaster.opts
/usr/local/pgsql/bin/postgres "-D" "/usr/pgsql"


drwx------ 2 postgres postgres    17 Aug 17 19:43 pg_notify: during server start up some times we will get error messages with hint, that notification data will be stored here.
-rw-rw-r-- 1 postgres postgres   705 Aug 17 19:43 serverlog : all the activites will be recorded here.

[postgres@example03 pgsql]$ cat serverlog
2019-08-17 19:43:19.082 PDT [14845] LOG:  listening on IPv6 address "::1", port 5432
2019-08-17 19:43:19.082 PDT [14845] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-08-17 19:43:19.082 PDT [14845] LOG:  could not bind IPv4 address "192.168.38.129": Cannot assign requested address
2019-08-17 19:43:19.082 PDT [14845] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2019-08-17 19:43:19.084 PDT [14845] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-08-17 19:43:19.099 PDT [14846] LOG:  database system was shut down at 2019-08-17 19:43:12 PDT
2019-08-17 19:43:19.109 PDT [14845] LOG:  database system is ready to accept connections
[postgres@example03 pgsql]$


-rw------- 1 postgres postgres    77 Aug 17 19:43 postmaster.pid

[postgres@example03 pgsql]$ cat postmaster.pid
14845
/usr/pgsql
1566096199
5432
/tmp
localhost
  5432001    294912
ready
[postgres@example03 pgsql]$ ps -ef|grep 14845
postgres  14845      1  0 19:43 ?        00:00:00 /usr/local/pgsql/bin/postmaster -D /usr/pgsql
postgres  14847  14845  0 19:43 ?        00:00:00 postgres: checkpointer
postgres  14848  14845  0 19:43 ?        00:00:00 postgres: background writer
postgres  14849  14845  0 19:43 ?        00:00:00 postgres: walwriter
postgres  14850  14845  0 19:43 ?        00:00:00 postgres: autovacuum launcher
postgres  14851  14845  0 19:43 ?        00:00:00 postgres: stats collector
postgres  14852  14845  0 19:43 ?        00:00:00 postgres: logical replication launcher
postgres  14957  14875  0 19:59 pts/0    00:00:00 grep --color=auto 14845
[postgres@example03 pgsql]$


drwx------ 2 postgres postgres     6 Aug 17 19:43 pg_stat: all the statistics will be stored here.
drwx------ 2 postgres postgres    24 Aug 17 19:44 pg_stat_tmp:temp statistics, for which database how much size of temp files created, how many temp files created.

Click here to proceed connect database with user and manager user in database