cbaWorkflow : Postgresql server installation and configuration

Getting Slonik comfortable.

JIRA HTOD-32

Installation
Configure and start
creating schemas
pgadmin4 installation
Installing ODBC Driver
Install development libraries and headers for C
Install development libraries and headers for C++

Installation

for Centos 7 the installation is straightforward with yum:

1, Get the repository:

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

2. install client libraries:

yum install postgresql10

3.Install the server:

yum install postgresql10-server

If the yum install is not possible(in case of RHEL7) then go to

https://yum.postgresql.org/rpmchart.php

Download the following packages:

postgresql10-libs-10.12-1PGDG.rhel7.x86_64.rpm
postgresql10-10.12-1PGDG.rhel7.x86_64.rpm
postgresql10-server-10.12-1PGDG.rhel7.x86_64.rpm

there is a standard way with obtaining the postgres repos, but it does not work right out of the box.

obtain the package:

pgdg-redhat-repo-42.0-9.noarch - this package installs postgreSQL repos

when you start the install

yum install pgadmin4

Error:

https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-%24releasever-x86_64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found

Note the releasever variable did not get substituted with value.

Workaround:

go to /etc/yum.repos.d and update the file pgdg-redhat-all.repo as follows for version 10 of postgreSQL.

#######################################################
# PGDG Red Hat Enterprise Linux / CentOS repositories #
#######################################################

# PGDG Red Hat Enterprise Linux / CentOS stable common repository for all PostgreSQL versions

[pgdg-common]
name=PostgreSQL common for RHEL/CentOS $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-7-x86_64
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

# PGDG Red Hat Enterprise Linux / CentOS stable repositories:
[pgdg10]
name=PostgreSQL 10 for RHEL/CentOS $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

install using yum install or rpm -i

yum install postgresql10-libs-10.12-1PGDG.rhel7.x86_64.rpm
yum install postgresql10-10.12-1PGDG.rhel7.x86_64.rpm
yum install postgresql10-server-10.12-1PGDG.rhel7.x86_64.rpm

Configure and start

////////////////// INITIALIZE database after installation //////////////

Initialize database:
/usr/pgsql-10/bin/postgresql-10-setup initdb

Starting postgresql

Starting postgresql service
systemctl start postgresql-10

make the service enabled during the system restart

systemctl enable postgresql-10

starting postgresql not as service

export PGDATA=/var/lib/pgsql/10/data

pg_ctl start

change the password of the postrgress user:
passwd postgres

connect as postgres
su - postgres

Connect to the template1 database and change admin password:
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'tHePAsswOrD';"

open the psql interface:
psql
create a new user:
create role dataexplorer1 with login password 'sOmePasWD' valid until 'infinity';

create a database for the user:
create database dataexplorerdb1 with encoding='UTF8' owner=dataexplorer1 connection limit=-1;

grant all privileges:
grant all privileges on database dataexplorerdb1 to dataexplorer1;

show the hba_file location:
show hba_file;
/var/lib/pgsql/10/data/pg_hba.conf

close psql:

\q
Edit the pg_hba.conf file changing peer to trust
vi /var/lib/pgsql/10/data/pg_hba.conf

add the line:

host all all 192.168.2.0/24 trust

edit the postgresql.conf
file
listen_addresses = '192.168.2.23' # what IP address(es) to listen on;
port = 5432

if listening on all interfaces is OK then:

listen_addresses = '*'

connect remotely:
psql -U dataexplorer1 -h 192.168.2.23 dataexplorerdb1

List databases:

\l

create table, populate and query:

dataexplorerdb1=> create table mytab1(id int);
CREATE TABLE
dataexplorerdb1=> insert into mytab1 values(23);
INSERT 0 1
dataexplorerdb1=> select * from mytab1;
id
----
23
(1 row)

creating schemas

the same database can be used by the different users. This may bring about the naming conflicts, e.g. several repositories can be created in the same database for different users.

this is addressed by creating a schema for a user and letting them have all the tables in the namespace of their own.

update the postgresql.conf and restart the server

search_path = '"$user",public'

psql:

postgres=# show search_path;
search_path
----------------
"$user",public

\c cbaworkflowdb

create schema dataexplorer1;

grant all on schema dataexplorer1 to dataexplorer1;

alter user dataexplorer1 set search_path="dataexplorer1";

create schema dataexplorer2;

grant all on schema dataexplorer2 to dataexplorer2;

alter user dataexplorer2 set search_path="dataexplorer2";

After the changes above the database cbaworkflowdb has 3 schemas: public,dataexplorer1 and dataexplorer2.

When the user dataexplorer1 creates a table with non fully qualified name it will be created in dataexplorer1 schema.

pgadmin4 installation

Optionally install pgadmin4 on the server, but this will require the workaround above, as the package is hard to find for download

The pgadmin4 can be alternatively installed on a Windows box using installer from postgresql download. Otherwise continue:

yum install pgadmin4

Configure pgadmin:

cd /etc/httpd/conf.d

cp pgadmin4.conf.sample pgadmin4.conf

vi pgadmin.conf

LoadModule wsgi_module modules/pgadmin4-python3-mod_wsgi.so
WSGIDaemonProcess pgadmin processes=1 threads=25
WSGIScriptAlias /pgadmin4 /usr/lib/python3.6/site-packages/pgadmin4-web/pgAdmin4.wsgi

<Directory /usr/lib/python3.6/site-packages/pgadmin4-web/>
WSGIProcessGroup pgadmin
WSGIApplicationGroup %{GLOBAL}
<IfModule mod_authz_core.c>
# Apache 2.4
Require all granted
</IfModule>
<IfModule !mod_authz_core.c>
# Apache 2.2
Order Deny,Allow
Deny from All
Allow from 127.0.0.1
Allow from ::1
</IfModule>
</Directory>

Note the usage of Python 3.6

Create the directories:

mkdir /var/lib/pgadmin4/

mkdir - /var/lib/pgadmin4/sessions

mkdir -p /var/lib/pgadmin4/storage

chown -R apache:apache /var/lib/pgadmin4

mkdir -p /var/log/pgadmin4/

chown -R apache:apache /var/log/pgadmin4

Update the config_distro.py:

vi /usr/lib/python3.6/site-packages/pgadmin4-web/config_distro.py
HELP_PATH = '/usr/share/doc/pgadmin4-docs/en_US/html'
UPGRADE_CHECK_ENABLED = False
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'

Create the user account:

python3 /usr/lib/python3.6/site-packages/pgadmin4-web/setup.py
Enter email and password, record it on a piece of paper

Start the Apache server:
systemctl restart httpd

in the browser go to http://datanode1.custom-built-apps.com/pgadmin4



Enter the email and password you generated with setup.py

Add a server:



Add connection information:


After the connection is established you will see something like this:

Installing ODBC Driver

yum install postgresql10-odbc
rpm -qi postgresql10-odbc.x86_64
Name : postgresql10-odbc
Version : 12.01.0000
Release : 1PGDG.rhel7
Architecture: x86_64
Install Date: Fri 01 May 2020 01:04:48 AM EDT
Group : Unspecified
Size : 1060881
License : LGPLv2
Signature : DSA/SHA1, Mon 27 Jan 2020 03:15:31 AM EST, Key ID 1f16d2e1442df0f8
Source RPM : postgresql10-odbc-12.01.0000-1PGDG.rhel7.src.rpm
Build Date : Mon 27 Jan 2020 03:14:22 AM EST
Build Host : koji-centos7-x86-64-pgbuild
Relocations : (not relocatable)
Vendor : PostgreSQL Global Development Group
URL : https://odbc.postgresql.org/
Summary : PostgreSQL ODBC driver
Description :
This package includes the driver needed for applications to access a
PostgreSQL system via ODBC (Open Database Connectivity).

rpm -ql postgresql10-odbc.x86_64
/usr/pgsql-10/lib/psqlodbc.so
/usr/pgsql-10/lib/psqlodbca.so
/usr/pgsql-10/lib/psqlodbcw.so
/usr/share/doc/postgresql10-odbc-12.01.0000
/usr/share/doc/postgresql10-odbc-12.01.0000/readme.txt
/usr/share/licenses/postgresql10-odbc-12.01.0000
/usr/share/licenses/postgresql10-odbc-12.01.0000/license.txt
[root@r01edge etc]# ldd -r /usr/pgsql-10/lib/psqlodbc.so

vi /etc/odbc.ini

[ODBC Data Sources]
Test = PostgreSQL Test
[Test]
Driver=/usr/pgsql-10/lib/psqlodbc.so
Description=Sample PostgreSQL DSN
DSN=Test
Servername=192.168.2.23
Username=dataexplorer1
Database=dataexplorerdb1
ReadOnly=No
Servertype=postgres
Port=5432
FetchBufferSize=99
ServerOptions=
ConnectOptions=
Options=
ReadOnly=no
Trace=1
TraceFile=/etc/odbc.trace
Debug=1
DebugFile=/etc/odbc.debug
CommLog=1

[Default]
Driver=/usr/pgsql-10/lib/psqlodbc.so

[ODBC]
InstallDir = /usr/lib64/libodbc.so

vi my.sql

select * from mytab

isql Test dataexplorer1 myPaSsWoRd -v <my.sql

Install development libraries and headers for C

yum install postgresql10-devel

yum install postgresql-devel

the latest one for libpq-fe.h

Install development libraries and headers for C++

wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz

tar xvzf libpqxx-4.0.tar.gz

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

export LD_LIBRARY_PATH=/usr/pgsql-10/lib:{LD_LIBRARY_PATH}

cd libpqxx

./configure

make

make install