PostgreSQL – Page 2 – David Ghedini

This post will cover installing PostgreSQL 13 on CentOS 8.

We’ll be using a bash script below, which is commented so you can see the steps.

We’ll install PostgreSQL using the PostgreSQL repository, configure the pg_hba.conf file to secure the instance, and update the postgresql.conf file to allow remote connections and enable SSL.

We will also create a self-signed SSL certificate for the cluster.

#!/bin/bash -e
# This script will install PostgreSQL 13 on CentOS 8
# This can be used for previous versions by adjusting the PG_VER below.

PG_VER='13'

function install_postgresql(){

#1. Install PostgreSQL Repository

PG_V2=$(echo ${PG_VER} | sed 's/\.//')
if [ ! -f /etc/yum.repos.d/pgdg-redhat-all.repo ]; then
rpm -ivh https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
fi

#2. Disable CentOS repo for PostgreSQL

if [ $(grep -m 1 -c 'exclude=postgresql' /etc/yum.repos.d/CentOS-Base.repo) -eq 0 ]; then
sed -i.save '/\[base\]/a\exclude=postgresql*' /etc/yum.repos.d/CentOS-Base.repo
sed -i.save '/\[updates\]/a\exclude=postgresql*' /etc/yum.repos.d/CentOS-Base.repo
fi

#3. Install PostgreSQL

yum install -y postgresql${PG_V2} postgresql${PG_V2}-devel postgresql${PG_V2}-server postgresql${PG_V2}-libs postgresql${PG_V2}-contrib postgresql${PG_V2}-plperl postgresql${PG_V2}-pltcl

#4. The the Environment and Initialize the Cluster

export PGDATA='/var/lib/pgsql/${PG_VER}/data'
export PATH="${PATH}:/usr/pgsql-${PG_VER}/bin/"
if [ $(grep -m 1 -c '/usr/pgsql-${PG_VER}/bin/' /etc/environment) -eq 0 ]; then
echo "export PATH=${PATH}" >> /etc/environment
fi

if [ $(grep -m 1 -c 'PGDATA' /etc/environment) -eq 0 ]; then
echo "export PGDATA=${PGDATA}" >> /etc/environment
fi

if [ ! -f /var/lib/pgsql/${PG_VER}/data/pg_hba.conf ]; then
sudo -u postgres /usr/pgsql-${PG_VER}/bin/initdb -D /var/lib/pgsql/${PG_VER}/data
fi

#5. Start PostgreSQL

systemctl start postgresql-${PG_VER}

#6. Set the postgres User Password

if [ $(grep -m 1 -c 'pg pass' /root/auth.txt) -eq 0 ]; then
PG_PASS=$(< /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c32);
sudo -u postgres psql 2>/dev/null -c "alter user postgres with password '${PG_PASS}'"
echo "pg pass: ${PG_PASS}" > /root/auth.txt
fi

#7. Configure ph_hba.conf

cat > /var/lib/pgsql/${PG_VER}/data/pg_hba.conf <<CMD_EOF
local all all md5
host all all 127.0.0.1 255.255.255.255 md5
host all all 0.0.0.0/0 md5
host all all ::1/128 md5
hostssl all all 127.0.0.1 255.255.255.255 md5
hostssl all all 0.0.0.0/0 md5
hostssl all all ::1/128 md5
CMD_EOF

#8. Update postgresql.conf for Remote Connections and Enable SSL

sed -i.save "s/.*listen_addresses.*/listen_addresses = '*'/" /var/lib/pgsql/${PG_VER}/data/postgresql.conf
sed -i.save "s/.*ssl =.*/ssl = on/" /var/lib/pgsql/${PG_VER}/data/postgresql.conf

#9. Create Symlinks for Backward Compatibility

ln -sf /usr/pgsql-${PG_VER}/bin/pg_config /usr/bin
ln -sf /var/lib/pgsql/${PG_VER}/data /var/lib/pgsql

#10. Create a Self-Singed SSL Certificate

if [ ! -f /var/lib/pgsql/${PG_VER}/data/server.key -o ! -f /var/lib/pgsql/${PG_VER}/data/server.crt ]; then
SSL_PASS=$(< /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c32);
if [ $(grep -m 1 -c 'ssl pass' /root/auth.txt) -eq 0 ]; then
echo "ssl pass: ${SSL_PASS}" >> /root/auth.txt
else
sed -i.save "s/ssl pass:.*/ssl pass: ${SSL_PASS}/" /root/auth.txt
fi
openssl genrsa -des3 -passout pass:${SSL_PASS} -out server.key 2048
openssl rsa -in server.key -passin pass:${SSL_PASS} -out server.key

chmod 400 server.key

openssl req -new -key server.key -days 3650 -out server.crt -passin pass:${SSL_PASS} -x509 -subj '/C=US/ST=Illinois/L=Chicago/O=PostgreSQL/CN=domain.com/emailAddress=info@domain.com'
chown postgres.postgres server.key server.crt
mv server.key server.crt /var/lib/pgsql/${PG_VER}/data
fi

systemctl restart postgresql-${PG_VER}
systemctl enable postgresql-${PG_VER}
}

#11. Install Prerequisites and Create auth.txt File

touch /root/auth.txt
yum -y install wget unzip tar

#12. Specific CentOS fixes

dnf module disable -y postgresql
sed -i.save 's/enabled=0/enabled=1/' /etc/yum.repos.d/CentOS-PowerTools.repo

#13. Run install_postgresql()

install_postgresql;

#14. Cat postgres and SSL Passwords from auth.txt to Display to User

echo "Passwords saved in /root/auth.txt"
cat /root/auth.txt

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

#!/bin/bash -e

# This script will install PostgreSQL 13 on CentOS 8

# This can be used for previous versions by adjusting the PG_VER below.

PG_VER='13'

function install_postgresql(){

#1. Install PostgreSQL Repository

PG_V2=$(echo ${PG_VER} | sed 's/\.//')

if [ ! -f /etc/yum.repos.d/pgdg-redhat-all.repo ]; then

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

#2. Disable CentOS repo for PostgreSQL

if [ $(grep -m 1 -c 'exclude=postgresql' /etc/yum.repos.d/CentOS-Base.repo) -eq 0 ]; then

sed -i.save '/\[base\]/a\exclude=postgresql*' /etc/yum.repos.d/CentOS-Base.repo

sed -i.save '/\[updates\]/a\exclude=postgresql*' /etc/yum.repos.d/CentOS-Base.repo

#3. Install PostgreSQL

yum install -y postgresql${PG_V2} postgresql${PG_V2}-devel postgresql${PG_V2}-server postgresql${PG_V2}-libs postgresql${PG_V2}-contrib postgresql${PG_V2}-plperl postgresql${PG_V2}-pltcl

#4. The the Environment and Initialize the Cluster

export PGDATA='/var/lib/pgsql/${PG_VER}/data'

export PATH="${PATH}:/usr/pgsql-${PG_VER}/bin/"

if [ $(grep -m 1 -c '/usr/pgsql-${PG_VER}/bin/' /etc/environment) -eq 0 ]; then

echo "export PATH=${PATH}" >> /etc/environment

if [ $(grep -m 1 -c 'PGDATA' /etc/environment) -eq 0 ]; then

echo "export PGDATA=${PGDATA}" >> /etc/environment

if [ ! -f /var/lib/pgsql/${PG_VER}/data/pg_hba.conf ]; then

sudo -u postgres /usr/pgsql-${PG_VER}/bin/initdb -D /var/lib/pgsql/${PG_VER}/data

#5. Start PostgreSQL

systemctl start postgresql-${PG_VER}

#6. Set the postgres User Password

if [ $(grep -m 1 -c 'pg pass' /root/auth.txt) -eq 0 ]; then

PG_PASS=$(< /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c32);

sudo -u postgres psql 2>/dev/null -c "alter user postgres with password '${PG_PASS}'"

echo "pg pass: ${PG_PASS}" > /root/auth.txt

#7. Configure ph_hba.conf

cat > /var/lib/pgsql/${PG_VER}/data/pg_hba.conf <<CMD_EOF

local all all md5

host all all 127.0.0.1 255.255.255.255 md5

host all all 0.0.0.0/0 md5

host all all ::1/128 md5

hostssl all all 127.0.0.1 255.255.255.255 md5

hostssl all all 0.0.0.0/0 md5

hostssl all all ::1/128 md5

CMD_EOF

#8. Update postgresql.conf for Remote Connections and Enable SSL

sed -i.save "s/.*listen_addresses.*/listen_addresses = '*'/" /var/lib/pgsql/${PG_VER}/data/postgresql.conf

sed -i.save "s/.*ssl =.*/ssl = on/" /var/lib/pgsql/${PG_VER}/data/postgresql.conf

#9. Create Symlinks for Backward Compatibility

ln -sf /usr/pgsql-${PG_VER}/bin/pg_config /usr/bin

ln -sf /var/lib/pgsql/${PG_VER}/data /var/lib/pgsql

#10. Create a Self-Singed SSL Certificate

if [ ! -f /var/lib/pgsql/${PG_VER}/data/server.key -o ! -f /var/lib/pgsql/${PG_VER}/data/server.crt ]; then

SSL_PASS=$(< /dev/urandom tr -dc _A-Z-a-z-0-9 | head -c32);

if [ $(grep -m 1 -c 'ssl pass' /root/auth.txt) -eq 0 ]; then

echo "ssl pass: ${SSL_PASS}" >> /root/auth.txt

else

sed -i.save "s/ssl pass:.*/ssl pass: ${SSL_PASS}/" /root/auth.txt

openssl genrsa -des3 -passout pass:${SSL_PASS} -out server.key 2048

openssl rsa -in server.key -passin pass:${SSL_PASS} -out server.key

chmod 400 server.key

openssl req -new -key server.key -days 3650 -out server.crt -passin pass:${SSL_PASS} -x509 -subj '/C=US/ST=Illinois/L=Chicago/O=PostgreSQL/CN=domain.com/emailAddress=info@domain.com'

chown postgres.postgres server.key server.crt

mv server.key server.crt /var/lib/pgsql/${PG_VER}/data

systemctl restart postgresql-${PG_VER}

systemctl enable postgresql-${PG_VER}

}

#11. Install Prerequisites and Create auth.txt File

touch /root/auth.txt

yum -y install wget unzip tar

#12. Specific CentOS fixes

dnf module disable -y postgresql

sed -i.save 's/enabled=0/enabled=1/' /etc/yum.repos.d/CentOS-PowerTools.repo

#13. Run install_postgresql()

install_postgresql;

#14. Cat postgres and SSL Passwords from auth.txt to Display to User

echo "Passwords saved in /root/auth.txt"

cat /root/auth.txt

As root, you can save the above script as postgresql-13-centos-8.sh or, simpler still, use wget to grab scipt from Github:

wget https://raw.githubusercontent.com/DavidGhedini/postgresql-13-centos-8/main/postgresql-13-centos-8.sh

1	wget https://raw.githubusercontent.com/DavidGhedini/postgresql-13-centos-8/main/postgresql-13-centos-8.sh

As root, make the script executable:

[root@demo ~]# chmod +x postgresql-13-centos-8.sh

1	[root@demo ~]# chmod +x postgresql-13-centos-8.sh

Run the script:

[root@demo ~]# ./postgresql-13-centos-8.sh

1	[root@demo ~]# ./postgresql-13-centos-8.sh

Upon completion, the postgres and SSL password will be displayed as below:

Passwords saved in /root/auth.txt
pg pass: 4nOfDD7_LeddBqjG3VDCV45hyA-bzhQs
ssl pass: Wx9SJdBjaV9wXL2o401sjgP4ARdbeoLT

Passwords saved in /root/auth.txt

pg pass: 4nOfDD7_LeddBqjG3VDCV45hyA-bzhQs

ssl pass: Wx9SJdBjaV9wXL2o401sjgP4ARdbeoLT

The random passwords will also be saved as auth.txt in the root directory.

[root@xe-demo ~]# ls -l
total 24
-rw-------. 1 root root 5589 Jan 13 21:54 anaconda-ks.cfg
-rw-r--r--. 1 root root   85 Mar 16 22:18 auth.txt
-rw-------. 1 root root 5355 Jan 13 21:54 original-ks.cfg
-rwxr-xr-x. 1 root root 3583 Mar 16 22:17 postgresql-13-centos-8.sh
[root@xe-demo ~]#

[root@xe-demo ~]# ls -l

total 24

-rw-------. 1 root root 5589 Jan 13 21:54 anaconda-ks.cfg

-rw-r--r--. 1 root root 85 Mar 16 22:18 auth.txt

-rw-------. 1 root root 5355 Jan 13 21:54 original-ks.cfg

-rwxr-xr-x. 1 root root 3583 Mar 16 22:17 postgresql-13-centos-8.sh

[root@xe-demo ~]#

Tes your installation with su – postgres and then enter the password to start psql:

[root@demo ~]# su - postgres
[postgres@demo ~]$ psql
Password for user postgres:
psql (13.0)
Type "help" for help.

postgres=# create database demo;
CREATE DATABASE
postgres=#

[root@demo ~]# su - postgres

[postgres@demo ~]$ psql

Password for user postgres:

psql (13.0)

Type "help" for help.

postgres=# create database demo;

CREATE DATABASE

postgres=#

Looking at the files under /var/lib/pgsql/data, our pg_hba.conf looks like below:

local	all all 							md5
host	all all 127.0.0.1	255.255.255.255	md5
host	all all 0.0.0.0/0					md5
host	all all ::1/128						md5
hostssl all all 127.0.0.1	255.255.255.255	md5
hostssl all all 0.0.0.0/0					md5
hostssl all all ::1/128						md5

local all all md5

host all all 127.0.0.1 255.255.255.255 md5

host all all 0.0.0.0/0 md5

host all all ::1/128 md5

hostssl all all 127.0.0.1 255.255.255.255 md5

hostssl all all 0.0.0.0/0 md5

hostssl all all ::1/128 md5

Our postgresql.conf has also been updated both to allow connections as well as enable SSL:

# - Connection Settings -
listen_addresses = '*'
        
# - SSL -
ssl = on

# - Connection Settings -

listen_addresses = '*'

# - SSL -

ssl = on

This post will cover install PostGIS 3.x on CentOS 8.

If you do not already have PostgreSQL 12 installed, do so here

PostGIS 3.x requires additional packages not available in the PostgreSQL repository.

Install the Epel repository for these dependencies:

[root@demo ~]# yum install epel-release
Last metadata expiration check: 0:14:47 ago on Mon 16 Mar 2020 10:21:19 PM UTC.
Dependencies resolved.
================================================================================
 Package               Architecture    Version            Repository       Size
================================================================================
Installing:
 epel-release          noarch          8-5.el8            extras           22 k

Transaction Summary
================================================================================
Install  1 Package

Total download size: 22 k
Installed size: 30 k
Is this ok [y/N]: y

[root@demo ~]# yum install epel-release

Last metadata expiration check: 0:14:47 ago on Mon 16 Mar 2020 10:21:19 PM UTC.

Dependencies resolved.

================================================================================

Package Architecture Version Repository Size

================================================================================

Installing:

epel-release noarch 8-5.el8 extras 22 k

Transaction Summary

================================================================================

Install 1 Package

Total download size: 22 k

Installed size: 30 k

Is this ok [y/N]: y

With Epel installed, we can now use Yum to install the PostGIS 3.x packages from the PostgreSQL Repository:

[root@demo ~]# yum -y install postgis30_12-client postgis30_12-devel postgis30_12-docs postgis30_12-utils

1	[root@demo ~]# yum -y install postgis30_12-client postgis30_12-devel postgis30_12-docs postgis30_12-utils

Once completed, su to postgres and connect to the target database where you wish to enable PostGIS.

Below, we are using database ‘demo’:

[root@demo ~]# su - postgres
Last login: Mon Mar 16 22:19:39 UTC 2020 on pts/0
[postgres@demo ~]$ psql
Password for user postgres:
psql (12.2)
Type "help" for help.

postgres=# \c demo
You are now connected to database "demo" as user "postgres".

[root@demo ~]# su - postgres

Last login: Mon Mar 16 22:19:39 UTC 2020 on pts/0

[postgres@demo ~]$ psql

Password for user postgres:

psql (12.2)

Type "help" for help.

postgres=# \c demo

You are now connected to database "demo" as user "postgres".

Install the PostGIS extension using CREATE EXTENSION:

demo=# create extension postgis;
CREATE EXTENSION
demo=#

demo=# create extension postgis;

CREATE EXTENSION

demo=#

Check the PostGIS version installed:

demo=# SELECT PostGIS_full_version();
                                                                        postgis_
full_version

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----
 POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ=
"6.3.1" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.4.3 (Intern
al)"
(1 row)

demo=# SELECT PostGIS_full_version();

postgis_

full_version

--------------------------------------------------------------------------------

-----

POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " PROJ=

"6.3.1" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0" WAGYU="0.4.3 (Intern

al)"

(1 row)

Lets test out our extension using a basic example from https://postgis.net/docs/using_postgis_dbmanagement.html

Create a table with a GEOGRAPHY column:

CREATE TABLE global_points (
id SERIAL PRIMARY KEY,
name VARCHAR(64),
location GEOGRAPHY(POINT,4326);

CREATE TABLE global_points (

id SERIAL PRIMARY KEY,

name VARCHAR(64),

location GEOGRAPHY(POINT,4326);

Insert some data:

INSERT INTO global_points (name, location) VALUES ('Town', 'SRID=4326;POINT(-110 30)');
INSERT INTO global_points (name, location) VALUES ('Forest', 'SRID=4326;POINT(-109 29)');
INSERT INTO global_points (name, location) VALUES ('London', 'SRID=4326;POINT(0 49)');

INSERT INTO global_points (name, location) VALUES ('Town', 'SRID=4326;POINT(-110 30)');

INSERT INTO global_points (name, location) VALUES ('Forest', 'SRID=4326;POINT(-109 29)');

INSERT INTO global_points (name, location) VALUES ('London', 'SRID=4326;POINT(0 49)');

Select the name column values:

demo=# select name from global_points;
  name
--------
 Town
 Forest
 London
(3 rows)

demo=# select name from global_points;

name

--------

Town

Forest

London

(3 rows)

And run a Spatial Query:

demo=# SELECT name FROM global_points WHERE ST_DWithin(location, 'SRID=4326;POINT(-110 29)'::geography, 1000000);
  name
--------
 Town
 Forest
(2 rows)

demo=# SELECT name FROM global_points WHERE ST_DWithin(location, 'SRID=4326;POINT(-110 29)'::geography, 1000000);

name

--------

Town

Forest

(2 rows)

Learn more at https://postgis.net/

Category: PostgreSQL

Install PostgreSQL 13 on CentOS 8

Install PostGIS 3.x on CentOS 8