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 

 

 

 

Send Email using PowerShell script

Windows PowerShell is a Windows command-line shell designed for system administrators. We can use Send-MailMessage cmdlet to send an email from PowerShell using SMTP protocol.

Copy below script in notepad, change required parameter and save it as SendMail.ps1

[System.Net.ServicePointManager]::SecurityProtocol = 'Tls,TLS11,TLS12'

$From = "Sender email address"

$To = "recipient’s email address"

$Cc = "cc recipient’s email address"

$Subject = "Your Subject"

$Body = "Message Body"

$SMTPServer = "SMTP Server address"

$SMTPPort = "SMTP Server port"

Send-MailMessage -From $From -to $To -Cc $Cc -Subject $Subject -Body $Body -SmtpServer $SMTPServer -port $SMTPPort -UseSsl -Credential (Get-Credential)

Post saving file, Open PowerShell and run command .\SendMail.ps1

It will prompt you for the login credentials.

Note : 

- This script is 100% working with gmail. You must enable less secure app or need to create app password to use this script.

How to enable less secure App in Gmail. - Click Here.

How to create App Password in Gmail. - Click Here.

With Help of Microsoft Documentation.

How to install CentOS 7

CentOS media is available on centos.org. Please watch the below video to install CentOS 7.