Configuring a Centos 7 Postgres Server for a Fun Database Class
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:
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
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]