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:

exclude=postgresql*

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

# 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:

listen_address = '*'
port = 5432

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

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

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

host    all             all             127.0.0.1/32            ident

to:

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:

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

Change to the postgres user and set a password:

# 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:

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

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

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