Many of you might be thinking after reading the title – Why to write about such a simple or rudimentary task, what is so tricky about installing EDB PostgreSQL software ? I know it’s quite easy and straight forward, but only if you are able to add the EDB repository to your server or register it, if you fail to add or register it, then it will be very difficult and becomes a tedious and a time consuming activity to install all of the software’s and their long list of dependencies. This post is all about how to deal with such situation, how to download the source RPMs and install them on the server if you are not able to add the EDB repository.
First step is to download the complete EDB’s tarball, I am downloading the complete tarball here as I don’t want to miss any dependent packages which are needed by the core components. This tarball is close to 1.8 GBs in size, you can download the file using below wget command, here you need to use your EDB credentials.
Now, once the tarball is installed, we can go and create the YUM local repository, though to create YUM repository is optional as you can also install RPMs directly, but will make your work lot easier otherwise you have to look out for dependencies manually. So, I have deceided to create the local repository here.
Once the above file is downloaded, unzip it. You will see list of all core and dependent packages/rpm, just like below.
Next I will create a directory which will be used as a repository container. [root@canttowin edb]# mkdir -p /home/user/repo
move all unzipped files/rpms to this new directory. [root@canttowin edb]# mv * /home/user/repo
change permissions of the directory. [root@canttowin edb]# chown -R root.root /home/user/repo [root@canttowin edb]# chmod -R o-w+r /home/user/repo
Now we can go and create the repository, for that we will use ‘createrepo‘ command. [root@canttowin edb]# createrepo /home/user/repo Spawning worker 0 with 1151 pkgs Workers Finished Saving Primary metadata Saving file lists metadata Saving other metadata Generating sqlite DBs Sqlite DBs complete
Now let’s create the YUM repository entry under /etc/yum.repos.d [root@canttowin edb]# more /etc/yum.repos.d/myrepo.repo [local] name=Prashant Local EDB Repo baseurl=file:///home/user/repo enabled=1 gpgcheck=0 [root@canttowin edb]#
All set! let’s try to look for any EDB’s package using this new local repository
[root@canttowin ~]# yum search edb-as12-server Loaded plugins: langpacks, ulninfo =============================================================== N/S matched: edb-as12-server ================================================================ edb-as12-server.x86_64 : EnterpriseDB Advanced Server Client and Server Components edb-as12-server-client.x86_64 : The client software required to access EDBAS server. edb-as12-server-cloneschema.x86_64 : cloneschema is a module for EnterpriseDB Advanced Server edb-as12-server-contrib.x86_64 : Contributed source and binaries distributed with EDBAS edb-as12-server-core.x86_64 : The core programs needed to create and run a EnterpriseDB Advanced Server edb-as12-server-devel.x86_64 : EDBAS development header files and libraries edb-as12-server-docs.x86_64 : Extra documentation for EDBAS edb-as12-server-edb-modules.x86_64 : EDB-Modules for EnterpriseDB Advanced Server edb-as12-server-indexadvisor.x86_64 : Index Advisor for EnterpriseDB Advanced Server edb-as12-server-libs.x86_64 : The shared libraries required for any EDBAS clients edb-as12-server-llvmjit.x86_64 : Just-In-Time compilation support for EDBAS edb-as12-server-parallel-clone.x86_64 : parallel_clone is a module for EnterpriseDB Advanced Server edb-as12-server-pldebugger.x86_64 : PL/pgSQL debugger server-side code edb-as12-server-plperl.x86_64 : The Perl procedural language for EDBAS edb-as12-server-plpython.x86_64 : The Python procedural language for EDBAS edb-as12-server-plpython3.x86_64 : The Python3 procedural language for EDBAS edb-as12-server-pltcl.x86_64 : The Tcl procedural language for EDBAS edb-as12-server-sqlprofiler.x86_64 : SQL profiler for EnterpriseDB Advanced Server edb-as12-server-sqlprotect.x86_64 : SQL Protect for EnterpriseDB Advanced Server
Great, so we are now able to look and install all our EDB packages through YUM, it’s lot easier than manually fixing dependencies and install core packages.
EnterpriseDB dapat di-install untuk running as containerized, pada artikel ini sebelumnya saya telah setup openshift versi 3.11. Untuk men-deploy enterprisedb pada openshift yang pertama dilakukan adalah membuat project baru
oc new-namespace edb; oc project edb;
Prerequisite
Sebelum mendeploy EDB ada beberapa hal yang harus dipersiapkan terlebih dulu:
Presistent volume untuk volume edb-data, edb-backup, edb-archive dan edb-pem misalnya menggunakan hostpath volume. Pertama buat directory di semua worker node atau worker node yg ingin jadi nodeselector
Setelah semua prerequisite siap, langkah selanjutnya adalah modifikasi script deployment dan deployment config edb. PEM server harus sudah terinstall sebelum melanjutkan untuk deploy edb, bart dan pgpool.
Dalam folder sample deployment ini, ada yang harus di sesuaikan di script deploy credential dari oc login dan di file yaml bagian image juga presistent volume.
cd 05-pem-server/ sh deploy.sh
pod PEM server sudah running dan siap menerima traffic
Expose service PEM server dengan cara buka webconsole, pilih Application > click Service > pilih service PEM > create route, ganti target port ke 8080
Install EDB Postgres Enterprise Manager (PEM) on RHEL 8
Below we will document the steps to install EDB’s flagship tool to manage Postgres Databases, Postgres Enterprise Manager (PEM) on RHEL 8, this will require a FREE EDB account to generate a repository key.
PEM is a licensed product and the purpose of below is for educational purposes only.
Steps are performed as root unless specified otherwise, you could also use a user with sudo privilege as well for root commands.
Step 1: Create a Unix Service to stop and start a new PEM db we create later
vi /usr/lib/systemd/system/pemdata.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. It is recommended to use systemd
# "dropin" feature; i.e. create file with suffix .conf under
# /etc/systemd/system/postgresql-16.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit postgresql-16"
# Look at systemd.unit(5) manual page for more info.
# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.
# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-16-setup.
[Unit]
Description=PostgreSQL 16 database server
Documentation=https://www.postgresql.org/docs/16/static/
After=syslog.target
After=network-online.target
[Service]
Type=notify
User=postgres
Group=postgres
# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.
# Location of database directory
#Environment=PGDATA=/var/lib/pgsql/16/data/
Environment=PGDATA=/var/lib/pgsql/16/pemdata/
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on postgres main process
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-16/bin/postgres -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
# Do not set any timeout value, so that systemd will not kill postgres
# main process during crash recovery.
TimeoutSec=0
# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0
TimeoutStopSec=1h
[Install]
WantedBy=multi-user.target
Insert above into /usr/lib/systemd/system/pemdata.service file and save.
Step 2: Create a new postgres database cluster called pemdata to contain PEM data
Output:
Updating Subscription Management repositories.
Last metadata expiration check: 0:00:43 ago on Sat 06 Jan 2024 16:26:40 GMT.
epel-release-latest-8.noarch.rpm 52 kB/s | 25 kB 00:00
Dependencies resolved.
===============================================================================================================================================================================
Package Architecture Version Repository Size
===============================================================================================================================================================================
Installing:
epel-release noarch 8-19.el8 @commandline 25 k
Transaction Summary
===============================================================================================================================================================================
Install 1 Package
Total size: 25 k
Installed size: 35 k
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : epel-release-8-19.el8.noarch 1/1
Running scriptlet: epel-release-8-19.el8.noarch 1/1
Many EPEL packages require the CodeReady Builder (CRB) repository.
It is recommended that you run /usr/bin/crb enable to enable the CRB repository.
Verifying : epel-release-8-19.el8.noarch 1/1
Installed products updated.
Installed:
epel-release-8-19.el8.noarch
Complete!
/usr/bin/crb enable
Output: Took 10 minute to run and turn back to prompt for me!
Enabling CRB repo
Repository 'codeready-builder-for-rhel-8-x86_64-rpms' is enabled for this system.
CRB repo is enabled and named: codeready-builder-for-rhel-8-x86_64-rpms
Run PEM configuration file provide the details below:
Output:
-----------------------------------------------------
EDB Postgres Enterprise Manager
-----------------------------------------------------
Install type: 1:Web Services and Database, 2:Web Services 3: Database [ ] :1
Enter local database server installation path (i.e. /usr/edb/as12 , or /usr/pgsql-12, etc.) [ ] :/usr/pgsql-16
Enter database super user name [ ] :postgres
Enter database server port number [ ] :5401
Enter database super user password [ ] :
Please enter CIDR formatted network address range that agents will connect to the server from, to be added to the server's pg_hba.conf file. For example, 192.168.1.0/24 [ 0.0.0.0/0 ] :
Enter database systemd unit file or init script name (i.e. edb-as-12 or postgresql-12, etc.) [ ] :pemdata
Please specify agent certificate path (Script will attempt to create this directory, if it does not exists) [ /root/.pem/ ] :
< ALL INSTALL OUTPUT>
--> [Info] --> [Info] Generating certificates.
--> [Info] --> [Info] Executing systemctl stop pemdata
--> [Info] --> [Info] Writing configurations in /var/lib/pgsql/16/pemdata/pg_hba.conf file
--> [Info] --> [Info] Writing configurations in /var/lib/pgsql/16/pemdata/postgresql.conf file
--> [Info] --> [Info] Executing systemctl start pemdata
Postgres Enterprise Manager Agent registered successfully!
--> [Info] --> [Info] Registering database server with PEM server.
--> [Info] --> [Info] Enable pemagent service.
--> [Info] --> [Info] Executing systemctl enable pemagent
Created symlink /etc/systemd/system/multi-user.target.wants/pemagent.service → /usr/lib/systemd/system/pemagent.service.
--> [Info] --> [Info] Stop pemagent service
--> [Info] --> [Info] Executing systemctl stop pemagent
--> [Info] --> [Info] Start pemagent service.
--> [Info] --> [Info] Executing systemctl start pemagent
--> [Info] --> [Info] Configuring httpd server
--> [Info] --> [Info] Executing systemctl stop httpd
--> [Info] --> [Info] Creating /usr/edb/pem/web/pem.wsgi
--> [Info] --> [Info] Generating PEM Cookie Name.
--> [Info] --> [Info] Creating /usr/edb/pem/web/config_setup.py
--> [Info] --> [Info] Creating /etc/httpd/conf.d/edb-pem.conf
--> [Info] --> [Info] Configuring httpd server sslconf
--> [Info] --> [Info] Taking backup of /etc/httpd/conf.d/edb-ssl-pem.conf
--> [Info] --> [Info] Executing /usr/edb/pem/web/setup.py
Postgres Enterprise Manager - Application Initialisation
========================================================
--> [Info] --> [Info] Check and Configure SELinux security policy for PEM
getenforce found, now executing 'getenforce' command
Configure the httpd to work with the SELinux
Allow the httpd to connect the database (httpd_can_network_connect_db = on)
Allow the httpd to connect the network (httpd_can_network_connect = on)
Allow the httpd to work with cgi (httpd_enable_cgi = on)
Allow to read & write permission on the 'pem' user home directory
SELinux policy is configured for PEM
--> [Info] --> [Info] Executing systemctl start httpd
--> [Info] --> [Info] Configured the webservice for EDB Postgres Enterprise Manager (PEM) Server on port '8443'.
--> [Info] --> [Info] PEM server can be accessed at https://127.0.0.1:8443/pem at your browser
STEP 11: Enable HTTPD for auto restart
systemctl enable httpd
Output:
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
Step 12: Login to EDB Postgres Enterprise Manager (PEM)
