Install PostgreSQL-12, pgAgent and PostGIS in CentOS 7

PostgreSQL is a free and open-source relational database management system and pgAgnet used for scheduling the job. PostgreSQL is also is known as Postgre. 

Please find the below steps to install PostgreSQL12, PostGIS, and pgAgnet in CentOS 7.

• Update Cent OS 7 and install EPEL repo

yum update

yum install epel-release

• Add the PostgreSQL12 Repository

You may find the repo of PostgreSQL 12 using link https://www.postgresql.org/download/linux/redhat/

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm  

• Install PostgresSQL12, pgAgnet and PostGIS

yum install postgresql12

yum install postgresql12-server

yum install pgagent_12

yum install postgis30_12

• Create a PostgreSQL data directory, set permission and set a password for postgres user

By default, Postgres data directory will be initializes at location /var/lib/pgsql/12/data. In this case, we use custom data directory.

mkdir /data/PG_DATA12
chown -R postgres:postgres /data/PG_DATA12
echo "postgres" | passwd --stdin postgres

• Initialize postgres database in a custom directory "/data/PG_DATA12"

su postgres -c "cd /usr/pgsql-12/bin/;./initdb -D /data/PG_DATA12" 

• Enable the service

systemctl enable postgresql-12 

• Change data directory path in postgresql-12.service

Open file postgresql-12.service which is located at /usr/lib/systemd/system/postgresql-12.service and replace "PGDATA=/var/lib/pgsql/12/data/" with "PGDATA=/data/PG_DATA12/". You may use below command also.

sed -i -e 's#Environment=PGDATA=/var/lib/pgsql/12/data/#Environment=PGDATA=/data/PG_DATA12/#g' /usr/lib/systemd/system/postgresql-12.service 

• Reload systemd 

systemctl daemon-reload

• Start and enable the service

systemctl start postgresql-12

systemctl enable pgagent_12

systemctl start pgagent_12 

• Change listen address in  postgresql.conf

 sed -i -e "s/^#listen_addresses = '127.0.0.1'/listen_addresses = '*' /g" /data/PG_DATA12/postgresql.conf

 sed -i -e "s/^#listen_addresses = 'localhost'/listen_addresses = '*' /g" /data/PG_DATA12/postgresql.conf

• Change authentication method from ident to trust in pg_hba.conf

sed -i -e "s#host    all all 127.0.0.1/32 ident#host    all all 127.0.0.1/32 trust#g" /data/PG_DATA12/pg_hba.conf

• Restart postgresql-12 and pgagent_12 service

    systemctl restart postgresql-12
    systemctl restart pgagent_12

• Create an extension for pgAgnet and PostGIS

psql -h localhost -p 5432 -U postgres -d template1 -c "CREATE EXTENSION postgis;"

psql -h localhost -p 5432 -U postgres -c "CREATE EXTENSION pgagent;"

• Start service

    systemctl start pgagent_12
    systemctl start postgresql-12