Jelkner's
Weblog


About me

Blog Posts Blog Tags

2021 2020 2019 2018 2017

Log in

Copyright © 2020
Jeffrey Elkner

Configuring a Centos 7 Postgres Server for a Fun Database Class


Curious Moon book cover

As Shankar Kambhampathy's July 23, 2018 article in Forbes online magazine is titled, It's All About Data, or as Catherine Devlin quotes in her keynote address at Pycon 2018, "data is the new oil". So it is with great interest and enthusiasm that I approach the coming school year, where I will have four students enrolled in dual-enrolled courses titled Advanced Database Management and Data Modeling and Design.

Unfortunately, the dry, tedious, textbooks mandated by the community college with which I am associated seem designed to drive all but the most hardy and most desparate running away from studying database management. I know there must be a better way, so I will be exploring what looks to be an informative, engaging and novel (pun intended) approach to learning PostgreSQL, A Curious Moon: A data science mystery featuring PostgreSQL, Cassini and Enceladus.

Before we can dive into the book, I need to setup a Postgres server that the four students can use, and that is what this post will describe.

In Ubuntu 18.04 KVM Dev Server Setup II I described how to setup PostgreSQL on an Ubuntu 18.04 KVM machine. Here I'll see if I can do it on a Centos 7 vm.

Using Install PostgreSQL 10.5 on Fedora 28/27, CentOS/RHEL/SL 7.5/6.10 to guide me, I edited the /etc/yum.repos.d/CentOS-Base.repo file, adding:

System Message: ERROR/3 (<string>, line 37)

Unknown directive type "highlight".

.. highlight:: none

exclude=postgresql*

at the end of both the [base] and [updates] sections. Then I ran:

System Message: ERROR/3 (<string>, line 45)

Unknown directive type "highlight".

.. highlight:: none

# yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
# yum update
# yum install postgresql10 postgresql10-server
# /usr/pgsql-10/bin/postgresql-10-setup initdb

Then I edited /var/lib/pgsql/10/data/postgresql.conf, uncommenting:

System Message: ERROR/3 (<string>, line 56)

Unknown directive type "highlight".

.. highlight:: none

listen_address = '*'
port = 5432

and changing the listen_address from localhost to *. Next I did:

System Message: ERROR/3 (<string>, line 65)

Unknown directive type "highlight".

.. highlight:: none

# vi /var/lib/pgsql/10/data/pg_hba.conf

and changed line 82 (after # IPv4 local connections: from:

System Message: ERROR/3 (<string>, line 73)

Unknown directive type "highlight".

.. highlight:: none

host    all             all             127.0.0.1/32            ident

to:

System Message: ERROR/3 (<string>, line 81)

Unknown directive type "highlight".

.. highlight:: none

host    all             all             0.0.0.0/0               ident

so connections from other machines are allowed. Then start the server and enable autostart on boot:

System Message: ERROR/3 (<string>, line 90)

Unknown directive type "highlight".

.. highlight:: none

# systemctl start postgresql-10.service
# systemctl enable postgresql-10.service

Change to the postgres user and set a password:

System Message: ERROR/3 (<string>, line 99)

Unknown directive type "highlight".

.. highlight:: none

# su - postgres
Last login: Sun Sep  9 19:07:32 EDT 2018 on pts/0
-bash-4.2$ psql
psql (10.5)
Type "help" for help.

postgres=# \password postgres
Enter new password: [password]
Entner it again: [password]
postgres=# \q
-bash-4.2$ createdb enceladus
-bash-4.2$ psql enceladus
psql (10.5)
Type "help" for help.

enceladus=#

Open port 5432 on the iptables firewall:

System Message: ERROR/3 (<string>, line 122)

Unknown directive type "highlight".

.. highlight:: none

# firewall-cmd --permanent --zone=public --add-port=5432/tcp
# systemctl restart firewalld.service

Finally login from the host machine to confirm all is working:

System Message: ERROR/3 (<string>, line 132)

Unknown directive type "highlight".

.. highlight:: none

$ psql -h pgsqlserv.local -p 5432 -U postgres
Password for user postgres: [password]