PostgreSQL Enterprise Manager - PEM Monitoring Tools
Database administration and management is required for any database on which there are continuous business transactions. If databases across enterprises are not monitored and maintained, then such databases are vulnerable to disasters, which eventually brings downtime to missions-critical application and, of course, it will incur losses in the business. Data is now one of the key assets for running a business, globally. Hence, it is one of the major responsibilities of enterprises to invest in resources for database monitoring and management. There are many database management and monitoring tools available for proactive database monitoring. In this article, we will look at PEM – “PostgreSQL Enterprise Manager”.Enterprise Manager, or PEM, is a graphical database management/monitoring tool that can help database administrators, system administrators, database developers, database managers, system architects, and performance analysts manage databases efficiently and proactively. PEM has a rich user interface for performing many database administration activities from a single console.
“Why do we need additional software for database management?” Many customers/managers have asked me this and I would like to answer in simple words – “PEM provides a single console for managing and administering single/multiple databases at a single site or across multiple locations . PEM allows full control of databases remotely for better and more efficient database management.
Architecture of Postgres Enterprise Manager:
The diagram below illustrates the Postgres Enterprise Manager Deployment architecture. Postgres Enterprise Manager has three different layers in its deployment.
- Postgres Enterprise Manager Server
- Postgres Enterprise Manager Agent
- SQL Profiler
- Client
Postgres Enterprise Manager Architecture
Postgres Enterprise Manager Server is the main software, which can be installed on an existing Postgres database server or on a fresh server. PEM Server needs its own database, used for maintaining the EM repository and for storing information on all its target databases. Currently PEM Server is supported on the Windows and Linux platforms.
Postgres Enterprise Manager Agent is lightweight software that is installed on database server hosts. This software is responsible for connecting to the database and uploading collected statistics to the Postgres Enterprise Manager Server. The information is transported between management agent and management server using SSL certificates.
Client is the end layer, from which all added target databases are monitored. Client has access to the Postgres Enterprise Manager Server secure URL using the “https” protocol and internally Postgres Enterprise Manager Server connects to the database targets as the single management console for one or more local and/or remote databases.
SQL Profiler EnterpriseDB provides the SQL Profiler component in EDB Postgres Advanced Serverand in EDB Postgres Enterprise Manager (for PostgreSQL users) to help assist you in locating and optimizing poorly-running SQL code.
The SQL Profiler Plugin allows you to profile a server’s workload. You must install the plugin on each server on which you wish to use SQL Profiler (since it is a plugin to the database server, you must install a copy of the plugin for each Postgres instance you wish to profile on each host you wish to profile). For example, if you have a host running PostgreSQL 9.2 and PostgreSQL 9.5, you must install two versions of the plugin, one for each server.
SQL Profiler for Postgres Enterprise Manager™ is officially supported only on the EnterpriseDB distributions of PostgreSQL version 9.1 (and above) and Advanced Server version 9.1 (and above). The plugin is distributed via StackBuilder, or is available from the EnterpriseDB website at:
The plugin is also distributed and installed with the server component of the Advanced Server 9.1 (and above) installer.
The SQL Profiler plugin may be installed on servers with or without a PEM agent, however traces can only be run in ad-hoc mode on unmanaged servers, and may only be scheduled on managed servers.
Follow the installation steps listed below to install the plugin for PostgreSQL before continuing to the Configuration section. If you are using Advanced Server 9.1 or above, skip ahead to the Configuration section.
- PostgreSQL, SQL Profiler default maintenance database is postgres.
- Advanced Server, SQL Profiler default maintenance database is edb
Benefits of Using Postgres Enterprise Manager:
Postgres Enterprise Manager has many benefits, including the following:
1- Robust Management Design: Postgres Enterprise Manager has a robust design for enterprise database administration and management. It was developed for managing multiple local or distributed small/medium/large-scale databases from a single console.
2- Multi-Platform Support: Postgres Enterprise Manager can collect information from PostgreSQL databases running on different OS platforms using remote connection with the Postgres Enterprise Manager Server. It can collect information on overall database activity and publish it on the PEM Server dashboard.
3- GUI-based Administration: Postgres Enterprise Manager supports complete GUI-based administration for all databases that are managed by the PEM Server. You can perform administration activities such as server startup/shutdown, configuration of database parameters, creation of objects, tablespace/datafiles storage management, database backup and recovery and much more.
4- Database Performance Monitoring: PEM provides excellent performance monitoring and performance tuning capabilities for all Postgres databases managed by the PEM Server. It cangather performance statistics from multiple databases and display them on its single dashboard, enabling efficient performance management.
5- Database Alerts: Postgres Enterprise Manager can alerts for configured thresholds. It can send alerts for configured items such as Memory, Disks, CPU etc. Each of these items has a default threshold set, but we can change the threshold values to meet SLAs. Any item exceeding the threshold value will cause an alert on the dashboard and from there an administrator can take corrective action.
6- Capacity Planning: PEM Server keeps its collected database performance metrics for a specified period. The capacity manager utility can help us select a specific time (such as peak load/overloaded/crash) and run an analysis on it, and based on the results it will guide us in corrective capacity planning for resources such as disks, CPU, and memory.
7- Audit Manager: The audit manager works only on Postgres Plus Advanced Server database instances. PEM audit manager can monitor database connections/disconnections from a database server. It can also log SQL statements executed on a database.
8- Postgres Replication: Postgres Enterprise Manager supports streaming replication. The streaming replication wizard is user friendly, enabling you to add new database servers for streaming replication or use an existing replication setup.
9- Log Manager: Can collect server logs with optional requirements and record them in a centralized table. We can use this wizard to customize the log rotation, log destination etc. PEM has separate a tab for the server log dashboard.
10- Expert Database Analysis: Postgres Enterprise Manager has an optional Expert utility that can analyze a database and score it for security best practices, database configuration and other keys areas and generate a report against best practices. We can use the report to configure our database to meet industry best practices.
Those are the top 10 features offered by Postgres Enterprise Manager in my opinion; there are many more features and option available in PEM Server. In the future, I am going to start a series of blog posts that will cover how we can utilize these features for effective and proactive database management of Postgres databases.
Server Information
S#
|
Hostname
|
IP
|
Type
|
1
|
2ndquadrant.in-1
|
10.11.128.81
|
PEM-AGENT ( production
db-1)
|
2
|
2ndquadrant.in-2
|
10.11.128.82
|
PEM-AGENT ( production
db-2)
|
3
|
2ndquadrant.in-3
|
10.11.133.193
|
Pem-server
( web/app server – httpd + pem database)
|
PEM Server Installation PEM Installation Steps:
- PostgreSQL
- pem-httpd
- Language Pack
- PEM Agent Installation
- SQL ProfilerInstallation
EnterpriseDB has released the Postgres Enterprise Manager 7.5. When installation is as easy as for the current version that should just be a matter of clicking next, lets see.
1. PEM Server Installation
Because the installer will create the systemd services installation should be done as root so need execute permission:
[root@2ndquadrant.in-3]# ls -l
total 310764
-rw-rw-r-- 1 465632 465632 317906671 Nov 15 16:54 pem-server-7.5.0-3-linux-x64 (1).run
[root@2ndquadrant.in-3]# chmod 766 pem-server-7.5.0-3-linux-x64\ \(1\).run
[root@2ndquadrant.in-3]# chown root:root pem-server-7.5.0-3-linux-x64\ \(1\).run
[root@2ndquadrant.in-3]# ls -l
total 310764
-rwxrw-rw- 1 root root 317906671 Nov 15 16:54 pem-server-7.5.0-3-linux-x64 (1).run
From now on all is Interactive Installer and straight forward:
[root@2ndquadrant.in-3]#./pem-server-7.5.0-3-linux-x64\ \(1\).run
----------------------------------------------------------------------------
Welcome to the Postgres Enterprise Manager (PEM) Server Setup Wizard.
----------------------------------------------------------------------------
Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.
Press [Enter] to continue:
Limited Use Software License Agreement
Version 2.9
Then accept The Licence Aggrement :
Press [Enter] to continue:
Do you accept this license? [y/n]:
Do you accept this license? [y/n]: y
----------------------------------------------------------------------------
Installation Directory
Please select a directory for the PEM server installation.
Installation Directory [/opt/edb/pem]:
Enter y for Displaying advance option :
Show &advanced options [y/N]: y
----------------------------------------------------------------------------
Advanced options
Select installation type
[1] Web Services and Database
[2] Web Services
[3] Database
Please choose an option [1] :
Warning: PostgreSQL 10, PEM-HTTPD and LanguagePack for Postgres Enterprise
Manager were not found.
Please extract them using '--extract-dependents' options and install them
manually before installing the 'Postgres Enterprise Manager Server 7.5.0'.
Press [Enter] to continue:
SOLUTION :
You can perform an unattended PEM server installation by providing installation preferences on the command line when invoking the installer. Please note that the system on which you are installing the PEM server must have internet access.
Step 1. Before invoking the installer, you must disable SE Linux:
[root@2ndquadrant.in-3] # sestatus
SELinux status: disabled
For Selinux Disabling:
[root@2ndquadrant.in-3] # vi /etc/sysconfig/selinux
Then change the directive SELinux=enforcing to SELinux=disabled
The PEM server installer has three dependencies:
• PostgreSQL
• pem-httpd
• Language Pack
• pem-httpd
• Language Pack
Step 2. Create a directory for extracting dependencies:
[root@2ndquadrant.in-3] # mkdir ext
[root@2ndquadrant.in-3] # pwd
/home/465632/2ndQ/ext
Step 3 .You can use the PEM server installer to satisfy the dependencies of the PEM server; use the following command to extract the dependencies:
[root@2ndquadrPEM serverant.in-3] # ./pem-server-7.5.0-3-linux-x64\ \(1\).run --extract-dependents /home/465632/2ndQ/ext
Step 4. In our example, the files are extracted to the /home/465632/2ndQ/ extdirectory. After extracting the files, you must install each program. Navigate into the directory that contains the files (in our example, /home/465632/2ndQ/ext), and enter:
[root@2ndquadrant.in-3 ext]# ls -l
total 223944
-rwxr-xr-x 1 root root 77268218 Oct 23 03:32 edb-languagepack-10-5-linux-x64.run
-rwxr-xr-x 1 root root 17827979 Oct 23 04:14 pem-httpd-2.4.33-1-linux-x64.run
-rwxr-xr-x 1 root root 133975489 Oct 23 00:56 postgresql-10.5-2-linux-x64.run
# Execute three dependencies as a root user :
I . Installing PostgreSQL 10.5 :
[root@2ndquadrant.in-3 ext]# ./postgresql-10.5-2-linux-x64.run --mode text
----------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.
----------------------------------------------------------------------------
Please specify the directory where PostgreSQL will be installed.
Installation Directory [/opt/PostgreSQL/10]:
----------------------------------------------------------------------------
Select the components you want to install; clear the components you do not want
to install. Click Next when you are ready to continue.
PostgreSQL Server [Y/n] :y
pgAdmin 4 [Y/n] :n
Stack Builder [Y/n] :n
Command Line Tools [Y/n] :y
Is the selection above correct? [Y/n]: y
----------------------------------------------------------------------------
Please select a directory under which to store your data.
Data Directory [/opt/PostgreSQL/10/data]:
----------------------------------------------------------------------------
Please provide a password for the database superuser (postgres). A locked Unix
user account (postgres) will be created if not present.
Password :
Retype password :
----------------------------------------------------------------------------
Please select the port number the server should listen on.
Port [5432]:
[769] zh_TW.euctw
[770] zh_TW.utf8
[771] zu_ZA
[772] zu_ZA.iso88591
[773] zu_ZA.utf8
Please choose an option [1] :
----------------------------------------------------------------------------
Pre Installation Summary
The following settings will be used for the installation::
Installation Directory: /opt/PostgreSQL/10
Server Installation Directory: /opt/PostgreSQL/10
Data Directory: /opt/PostgreSQL/10/data
Database Port: 5432
Database Superuser: postgres
Operating System Account: postgres
Database Service: postgresql-10
Command Line Tools Installation Directory: /opt/PostgreSQL/10
Press [Enter] to continue:
----------------------------------------------------------------------------
Setup is now ready to begin installing PostgreSQL on your computer.
Do you want to continue? [Y/n]: y
----------------------------------------------------------------------------
Please wait while Setup installs PostgreSQL on your computer.
Installing
0% ______________ 50% ______________ 100%
#########################################
----------------------------------------------------------------------------
Setup has finished installing PostgreSQL on your computer.
II . Installing languagepack
[root@2ndquadrant.in-3 ext] # ./edb-languagepack-10-5-linux-x64.run --mode text
Language Selection
Please select the installation language
[1] English - English
Please choose an option [1] :
----------------------------------------------------------------------------
Welcome to the Language Pack Setup Wizard.
----------------------------------------------------------------------------
Existing Installation
An existing Language Pack installation has been found at
/opt/edb/languagepack-10. This installation will be upgraded.
Press [Enter] to continue:
----------------------------------------------------------------------------
Setup is now ready to begin installing on your computer.
/opt/edb/languagepack-10
Do you want to continue? [Y/n]: y
----------------------------------------------------------------------------
Please wait while Setup installs Language Pack on your computer.
Installing Language Pack
0% ______________ 50% ______________ 100%
#########################################
----------------------------------------------------------------------------
EnterpriseDB is the leading provider of value-added products and services for
the Postgres community.
Please visit our website at www.enterprisedb.com
Starting the PostgreSQL services:
[root@2ndquadrant.in-3 ext]# sudo sed -e 's@PERL_INSTALL_PATH@/opt/edb/languagepack-10/Perl-5.24/@g' \
-e 's@PYTHON_INSTALL_PATH@/opt/edb/languagepack-10/Python-3.4/@g' \
-e 's@TCL_INSTALL_PATH@/opt/edb/languagepack-10/Tcl-8.6/@g' \
-i /opt/PostgreSQL/10/etc/sysconfig/plLanguages.config
[root@2ndquadrant.in-3 ext]#
[root@2ndquadrant.in-3 ext]# service postgresql-10 start
Redirecting to /bin/systemctl start postgresql-10.service
[root@2ndquadrant.in-3 ext]# ps -ef|grep postgres
root 7045 27601 0 14:47 pts/0 00:00:00 su postgres
postgres 7047 7045 0 14:47 pts/0 00:00:00 bash
postgres 18864 1 0 16:42 ? 00:00:00 /opt/PostgreSQL/10/bin/postgres -D /opt/PostgreSQL/10/data
postgres 18865 18864 0 16:42 ? 00:00:00 postgres: logger process
postgres 18867 18864 0 16:42 ? 00:00:00 postgres: checkpointer process
postgres 18868 18864 0 16:42 ? 00:00:00 postgres: writer process
postgres 18869 18864 0 16:42 ? 00:00:00 postgres: wal writer process
postgres 18870 18864 0 16:42 ? 00:00:00 postgres: autovacuum launcher process
postgres 18871 18864 0 16:42 ? 00:00:00 postgres: stats collector process
postgres 18872 18864 0 16:42 ? 00:00:00 postgres: bgworker: logical replication launcher
root 18885 30442 0 16:42 pts/2 00:00:00 grep --color=auto postgres
III. Installing pem-httpd (apache web server) :
[root@2ndquadrant.in-3 ext]# ./pem-httpd-2.4.33-1-linux-x64.run --mode text
----------------------------------------------------------------------------
Welcome to the PEM-HTTPD Setup Wizard.
----------------------------------------------------------------------------
Please specify the directory where PEM-HTTPD will be installed.
Installation Directory [/opt/edb/pem/httpd]:
----------------------------------------------------------------------------
Apache Server Details
Please specify a port on which Apache will run
Apache Port [8080]:
----------------------------------------------------------------------------
Setup is now ready to begin installing PEM-HTTPD on your computer.
Do you want to continue? [Y/n]:
----------------------------------------------------------------------------
Please wait while Setup installs PEM-HTTPD on your computer.
Installing PEM-HTTPD
0% ______________ 50% ______________ 100%
#########################################
----------------------------------------------------------------------------
EnterpriseDB is the leading provider of value-added products and services for
the Postgres community.
Please visit our website at www.enterprisedb.com
Step 5. Then, you can install the PEM server installer:
[root@2ndquadrant.in-3 2ndQ]# ./pem-server-7.5.0-3-linux-x64\ \(1\).run --mode text
The example that follows demonstrates performing a text-mode installation on a Linux system; the installation process on a Windows system is similar.
When you invoke the installer, the installer welcomes you:
----------------------------------------------------------------
Welcome to the Postgres Enterprise Manager (PEM) Server Setup Wizard.
----------------------------------------------------------------
Before installing the PEM server, you must review and accept the terms of the PEM license agreement:
Please read the following License Agreement. You must accept the terms of this agreement before continuing with the installation.
Review the license agreement; when prompted, accept the agreement, and press Return.
Do you accept this license? [y/n]: y
----------------------------------------------------------------
After accepting the license agreement, you will be prompted for the name and address of a registered EnterpriseDB user; provide the information when prompted:
EnterpriseDB User Account Information
Please enter the email address and password for your enterprisedb.com user account.
Email address []:
Password :
----------------------------------------------------------------
Next, you will be prompted for an installation directory; you can use the default installation directory, or specify an alternate location.
Enter a Y after Show advanced options to access menu options that allow you to use a pre-existing Postgres server installation or Apache installation with the PEM server; for more information
Installation Directory
Please select a directory for the PEM server installation.
Installation Directory [/opt/edb/pem]:
Show &advanced options [y/N]: y
----------------------------------------------------------------------------
Advanced options
Select installation type
[1] Web Services and Database
[2] Web Services
[3] Database
Please choose an option [1] :
----------------------------------------------------------------------------
Database Server Selection
Select the database server.
The database servers below may be used by Postgres Enterprise Manager for its data store.
[1] PostgreSQL 10 on Port 5432
[2] Other Database Server
Please choose an option [1] :
----------------------------------------------------------------------------
Use the fields that follow the Database Server Installation Details heading to provide the name and password of the PostgreSQL database superuser for the PEM backing database:
Database Server Installation Details
Please specify user and password for the local server - 'PostgreSQL 10'
installation running on port 5432.
User [postgres]:
Password :
----------------------------------------------------------------
When prompted, provide the network address or range that will be added to the pg_hba.conf file of the PEM server host; this will allow PEM agents on monitored hosts to contact the server:
Network Details
Please enter the 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.
Network address [127.0.0.1/32]: 10.11.128.0/24
----------------------------------------------------------------------------
Use the Agent Details fields to specify a descriptive name for the agent and the location of the agent certificate:
Agent Details.
Please specify a description for the agent.
Description. [Postgres Enterprise Manager Host]:
Agent certificate path [/root/.pem]:
----------------------------------------------------------------------------
The installer will prompt you before it proceeds with the installation; press Return to start the installation:
Setup is now ready to begin installing the PEM server on your computer.
Do you want to continue? [Y/n]:
Please wait while Setup installs the PEM server on your computer.
Installing
0% ______________ 50% ______________ 100%
###########################
The installer will notify you when the installation is complete:
Info: Configured the webservice for Postgres Enterprise Manager (PEM) Server on
port '8443'.
Created and configured the 'pem' database.
Press [Enter] to continue:
----------------------------------------------------------------------------
EnterpriseDB is the leading provider of value-added products and services for
the Postgres community.
Please visit our website at www.enterprisedb.com
Note : Whie installing PEM Server – cidr_address_range specifies the address range that will be added to the pg_hba.conf file of the PEM server’s backing database to allow connections from the agents that will be monitored by the server.
You may wish to specify a network range (for example, 0.0.0.0/24) to provide server access to agents that reside on the all network.
PostgreSQL PEM to accept all incoming connections
host all all 0.0.0.0/0 md5
Step 6. confirm the installation of PEM as well as postgres :
[root@2ndquadrant.in-3 2ndQ]# ps -ef|grep postgres
root 7045 27601 0 14:47 pts/0 00:00:00 su postgres
postgres 7047 7045 0 14:47 pts/0 00:00:00 bash
postgres 21050 1 0 17:07 ? 00:00:00 /opt/PostgreSQL/10/bin/postgres -D /opt/PostgreSQL/10/data
postgres 21051 21050 0 17:07 ? 00:00:00 postgres: logger process
postgres 21053 21050 0 17:07 ? 00:00:00 postgres: checkpointer process
postgres 21054 21050 0 17:07 ? 00:00:00 postgres: writer process
postgres 21055 21050 0 17:07 ? 00:00:00 postgres: wal writer process
postgres 21056 21050 0 17:07 ? 00:00:00 postgres: autovacuum launcher process
postgres 21057 21050 0 17:07 ? 00:00:00 postgres: stats collector process
postgres 21058 21050 0 17:07 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 21226 21050 1 17:07 ? 00:00:06 postgres: agent1 pem 127.0.0.1(41256) idle
postgres 21227 21050 0 17:07 ? 00:00:00 postgres: agent1 pem 127.0.0.1(41258) idle
postgres 21229 21050 0 17:07 ? 00:00:00 postgres: agent1 pem 127.0.0.1(41260) idle
postgres 21230 21050 0 17:07 ? 00:00:01 postgres: agent1 pem 127.0.0.1(41262) idle
postgres 21231 21050 0 17:07 ? 00:00:00 postgres: agent1 pem 127.0.0.1(41264) idle
postgres 21239 21050 0 17:07 ? 00:00:00 postgres: postgres postgres 127.0.0.1(41272) idle
root 22240 30442 0 17:14 pts/2 00:00:00 grep --color=auto postgres
[root@2ndquadrant.in-3 2ndQ]# ps -ef|grep pem
root 21212 1 0 17:07 ? 00:00:00 /opt/edb/pem/agent/bin/pemagent -c /opt/edb/pem/agent/etc/agent.cfg
root 21213 21212 0 17:07 ? 00:00:01 /opt/edb/pem/agent/bin/pemworker -c /opt/edb/pem/agent/etc/agent.cfg --pid 21212
postgres 21226 21050 1 17:07 ? 00:00:06 postgres: agent1 pem 127.0.0.1(41256) idle
postgres 21227 21050 0 17:07 ? 00:00:00 postgres: agent1 pem 127.0.0.1(41258) idle
postgres 21229 21050 0 17:07 ? 00:00:00 postgres: agent1 pem 127.0.0.1(41260) idle
postgres 21230 21050 0 17:07 ? 00:00:01 postgres: agent1 pem 127.0.0.1(41262) idle
postgres 21231 21050 0 17:07 ? 00:00:00 postgres: agent1 pem 127.0.0.1(41264) idle
root 21276 1 0 17:07 ? 00:00:00 /opt/edb/pem/httpd/apache/bin/httpd -k start -f /opt/edb/pem/httpd/apache/conf/httpd.conf
pem 21277 21276 0 17:07 ? 00:00:00 EDBPEM -k start -f /opt/edb/pem/httpd/apache/conf/httpd.conf
daemon 21278 21276 0 17:07 ? 00:00:00 /opt/edb/pem/httpd/apache/bin/httpd -k start -f /opt/edb/pem/httpd/apache/conf/httpd.conf
daemon 21279 21276 0 17:07 ? 00:00:00 /opt/edb/pem/httpd/apache/bin/httpd -k start -f /opt/edb/pem/httpd/apache/conf/httpd.conf
daemon 21280 21276 0 17:07 ? 00:00:00 /opt/edb/pem/httpd/apache/bin/httpd -k start -f /opt/edb/pem/httpd/apache/conf/httpd.conf
root 22246 30442 0 17:14 pts/2 00:00:00 grep --color=auto pem
[root@2ndquadrant.in-3 2ndQ]# netstat -plunt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:6010 0.0.0.0:* LISTEN 3730/sshd: 465632@p
tcp 0 0 0.0.0.0:13724 0.0.0.0:* LISTEN 1072/vnetd
tcp 0 0 0.0.0.0:10050 0.0.0.0:* LISTEN 1061/zabbix_agentd
tcp 0 0 127.0.0.1:37230 0.0.0.0:* LISTEN 1017/pbx_exchange
tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN 21276/httpd
tcp 0 0 0.0.0.0:1556 0.0.0.0:* LISTEN 1017/pbx_exchange
tcp 0 0 127.0.0.1:1557 0.0.0.0:* LISTEN 1017/pbx_exchange
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 30015/sshd
tcp 0 0 0.0.0.0:13782 0.0.0.0:* LISTEN 1092/bpcd
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 21050/postgres
tcp6 0 0 :::8443 :::* LISTEN 21276/httpd
tcp6 0 0 :::10050 :::* LISTEN 1061/zabbix_agentd
tcp6 0 0 :::1556 :::* LISTEN 1017/pbx_exchange
tcp6 0 0 :::5432 :::* LISTEN 21050/postgres
udp 0 0 0.0.0.0:33208 0.0.0.0:* 973/rsyslogd
udp 0 0 0.0.0.0:58655 0.0.0.0:* 973/rsyslogd
udp 0 0 0.0.0.0:38783 0.0.0.0:* 973/rsyslogd
udp 0 0 10.11.133.193:123 0.0.0.0:* 605/ntpd
udp 0 0 127.0.0.1:123 0.0.0.0:* 605/ntpd
udp 0 0 0.0.0.0:123 0.0.0.0:* 605/ntpd
udp6 0 0 fe80::250:56ff:fea9:123 :::* 605/ntpd
udp6 0 0 ::1:123 :::* 605/ntpd
udp6 0 0 :::123 :::* 605/ntpd
[root@2ndquadrant.in-3 2ndQ]# sudo netstat -nap | grep postgres
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 21050/postgres
tcp 0 0 127.0.0.1:5432 127.0.0.1:41262 ESTABLISHED 21230/postgres: age
tcp 0 0 127.0.0.1:5432 127.0.0.1:41272 ESTABLISHED 21239/postgres: pos
tcp 0 0 127.0.0.1:5432 127.0.0.1:41256 ESTABLISHED 21226/postgres: age
tcp 0 0 127.0.0.1:5432 127.0.0.1:41264 ESTABLISHED 21231/postgres: age
tcp 0 0 127.0.0.1:5432 127.0.0.1:41260 ESTABLISHED 21229/postgres: age
tcp 0 0 127.0.0.1:5432 127.0.0.1:42064 ESTABLISHED 22383/postgres: pos
tcp 0 0 127.0.0.1:5432 127.0.0.1:41258 ESTABLISHED 21227/postgres: age
tcp6 0 0 :::5432 :::* LISTEN 21050/postgres
udp6 0 0 ::1:50084 ::1:50084 ESTABLISHED 21050/postgres
unix 2 [ ACC ] STREAM LISTENING 4369711 21050/postgres /tmp/.s.PGSQL.5432
[root@2ndquadrant.in-3 2ndQ]# sudo netstat -nap | grep pem
tcp 0 0 127.0.0.1:41256 127.0.0.1:5432 ESTABLISHED 21213/pemworker
tcp 0 0 127.0.0.1:41272 127.0.0.1:5432 ESTABLISHED 21213/pemworker
tcp 0 0 127.0.0.1:41258 127.0.0.1:5432 ESTABLISHED 21213/pemworker
tcp 0 0 127.0.0.1:41260 127.0.0.1:5432 ESTABLISHED 21213/pemworker
tcp 0 0 127.0.0.1:41262 127.0.0.1:5432 ESTABLISHED 21213/pemworker
tcp 0 0 127.0.0.1:41264 127.0.0.1:5432 ESTABLISHED 21213/pemworker
unix 2 [ ACC ] STREAM LISTENING 4370598 21276/httpd /opt/edb/pem/httpd/apache/logs/wsgi.21276.0.1.sock
[root@2ndquadrant.in-3 2ndQ]#
Step 7. .After configuring the PEM server, you can access the PEM web interface in your browser. Navigate to:
https://ip_address_of_PEM_server:8443
Lets connect to PEM: https://10.11.133.193:8443/pem
Error:
Network Error (tcp_error)
Network Error (tcp_error)
A communication error occurred: “Connection refused”
The Web Server may be down, too busy, or experiencing other problems preventing it from responding to requests. You may wish to try again at a later time.
At 10.11.133.193 server :
[root@2ndquadrant.in-3 2ndQ]# netstat -plunt|grep 8443
tcp6 0 0 :::8443 :::* LISTEN 6717/httpd
[root@2ndquadrant.in-3 2ndQ]# netstat -plunt|grep 8080
tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN 6717/httpd
[root@2ndquadrant.in-3 2ndQ]# netstat -plunt|grep httpd
tcp 0 0 0.0.0.0:8080 0.0.0.0:* LISTEN 6717/httpd
tcp6 0 0 :::8443 :::* LISTEN 6717/httpd
[root@2ndquadrant.in-3 2ndQ]# lsof -i:8080
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
httpd 6717 root 3u IPv4 4521392 0t0 TCP *:webcache (LISTEN)
httpd 6719 daemon 3u IPv4 4521392 0t0 TCP *:webcache (LISTEN)
httpd 6720 daemon 3u IPv4 4521392 0t0 TCP *:webcache (LISTEN)
httpd 6721 daemon 3u IPv4 4521392 0t0 TCP *:webcache (LISTEN)
[root@2ndquadrant.in-3 2ndQ]# lsof -i:8443
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
httpd 6717 root 5u IPv6 4521400 0t0 TCP *:pcsync-https (LISTEN)
httpd 6719 daemon 5u IPv6 4521400 0t0 TCP *:pcsync-https (LISTEN)
httpd 6720 daemon 5u IPv6 4521400 0t0 TCP *:pcsync-https (LISTEN)
httpd 6721 daemon 5u IPv6 4521400 0t0 TCP *:pcsync-https (LISTEN)
### Solution :
- 8443 is SSL based so you have to tell to network team that 8443 is a SSL port you have to open that ports with SSL
- Check the VPN connection , some time VPN should be ON if server is already configured .
- Disable the Proxy server if it is already for Disabling proxy GO to chrome ->> setting ->> Type in search box as ‘xy’ then CLICK “ Open proxysettings “ ->> click LAN SETTING ->> untick “ USE A PROXY SERVER FOR YOUR LAN “ then Give ->> OK ->> OK .
then you can connect the PEM server
If you have an EDB subscription now it is the time to enter the product key:
What you immediately can see is that the look and feel changed to that of pgadmin4 :
Adding Pem Agent By using PostgreSQL PEM :
You have to give Name and Group
We added pem agent by using pem server web console but not configured at db side so we have install and configure the pem server at master as well as slave because right now we have master and slave only.
See I tried to configure the log file but unable to do that because still not installed pem agent at DB side.
In a next we’ll install a PEM Agent and start to monitor it.
2. PEM Agent Installation
The PEM agent executes tasks scheduled via the PEM server, and reports statistics from the agent’s operating system and one or more Postgres servers running on the agent’s host. A PEM agent must reside on each monitored virtual or physical machine. A copy of the PEM server must be installed and running on the network on which the PEM agent’s host resides.
You can perform an unattended PEM server installation by providing installation preferences on the command line when invoking the installer. Please note that the system on which you are installing the PEM server must have internet access.
On Linux
Before Installing the PEM agent installer, you must:
- Install the PEM server; the pg_hba.conf file of the PEM server must allow connections from the host of the PEM agent.
- Disable SE Linux on the host of the PEM agent; for more information,
- Ensure that the monitored Postgres database has SSL enabled and is accepting connections.
Pem Agent Need Some Dependency packages they are :
You must be a superuser to install the PEM agent. Use the following command to install the PEM agent installer :
[root@2ndquadrant.in-1]# ./pem-agent-7.5.0-3-linux-x64.run --mode text
----------------------------------------------------------------------------
Welcome to the Postgres Enterprise Manager (PEM) Agent Setup Wizard.
----------------------------------------------------------------------------
Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.
Press [Enter] to continue:
Limited Use Software License Agreement
Version 2.9
The example that follows demonstrates performing a text-mode installation on a Linux system; the installation process on a Windows system is similar.
When you invoke the PEM agent installer, the installer welcomes you:
-----------------------------------------------------------------
Welcome to the Postgres Enterprise Manager (PEM) Agent Setup Wizard.
-----------------------------------------------------------------
Before installing the PEM server, you must review and accept the terms of the PEM license agreement:
Please read the following License Agreement. You must accept the terms of this agreement before continuing with the installation.
Press [Enter] to continue:
Do you accept this license? [y/n]:
-----------------------------------------------------------------
Next, you will be prompted for an installation directory; you can use the default installation directory, or specify an alternate location.
By default, the PEM agent installer places a certificate in~root/.pem (on Linux) or in the Administrator’s %APPDATA%\pem directory (on Windows). Enter a Y after Show advanced options to access menu options that allow you to specify an alternate path for the certificate file.
Installation Directory
Please select a directory for PEM agent installation.
Installation Directory [/opt/edb/pem]:
Show advanced options [y/N]:
-----------------------------------------------------------------
When prompted, provide information about the PEM server installation:
PEM server installation details
Please verify the PEM server installation details
Host [localhost]: 10.11.133.193
User Name [postgres]: postgres
Password :
Port [5432]:
-----------------------------------------------------------------
You can provide a descriptive name for the agent, or press return to accept the default:
Agent Details
Please provide the agent description
Description [localhost]:
-----------------------------------------------------------------
The installer will prompt you before it proceeds with the installation; press Return to start the installation:
Setup is now ready to begin installing the PEM agent on your computer.
Do you want to continue? [Y/n]:
-----------------------------------------------------------------
Please wait while Setup installs the PEM agent on your computer.
Installing
0% ______________ 50% ______________ 100%
#########################################
The installer will notify you when the installation is complete:
/opt/edb/pem/agent/bin/pemworker −−register-agent −−pem-server 10.11.133.193 −−pem-user postgres −−pem-port 5432
3. SQL Profiler Installation
The SQL Profiler Plugin allows you to profile a server’s workload. You must install the plugin on each server on which you wish to use SQL Profiler.
Plugin is available for download at the following link:
http://www.enterprisedb.com/download-postgres-enterprise-manager
http://www.enterprisedb.com/download-postgres-enterprise-manager
Note:-The SQL Profiler is installed as part of Postgres Plus Advanced Server and only requires configuration.
For PostgreSQL, download the relevant version of SQL Profiler from the link mentioned above, below is an example for PostgreSQL 9.0:
./sqlprofiler-pg-9.0-2.1.0-1-linux-x64.run
Note: while installation provide the details accordingly.
Text mode installation:-
./sqlprofiler-pg-9.0-2.1.0-1-linux-x64.run --mode text
----------------------------------------------------------------------------
Welcome to the Postgres Enterprise Manager (PEM) SQL Profiler Plugin Setup
Wizard.
Do you accept this license? [y/n]: y
Please enter the installation path of your PostgreSQL server.
[/opt/PostgreSQL/9.0]:
----------------------------------------------------------------------------
Setup is now ready to begin installing SQL-Profiler on your computer.
Do you want to continue? [Y/n]: y
----------------------------------------------------------------------------
Please wait while Setup installs SQL-Profiler on your computer.
Installing the PEM SQL profiler plugin
0% ______________ 50% ______________ 100%
#########################################
Configuring SQL Profiler
To use the profiler, you must:
1) Load the profiler plugin
2) Run the sql-profiler.sql against postgres/edb.
2) Run the sql-profiler.sql against postgres/edb.
LOADING THE PROFILER:
You should preload the profiler into all sessions by adding the following line to your postgresql.conf configuration file:
shared_preload_libraries = '$libdir/sql-profiler'
There is a tiny (probably immeasurable) amount of extra overhead once you’ve loaded the profiler.
START/STOP/LOAD TRACES:
Use Postgres Enterprise Manager (client) to
– Define and start a trace for any user (or, all users) against any
database (or, all the databases).
– Stop the running trace
– Load an existing trace created earlier
– Remove an existing trace(s).
– Schedule a trace to start it later
(NOTE: This requires – the database to be registered on a Postgres
Enterprise Manager and the PEM Agent should be running and bound
to that server.)
– Define and start a trace for any user (or, all users) against any
database (or, all the databases).
– Stop the running trace
– Load an existing trace created earlier
– Remove an existing trace(s).
– Schedule a trace to start it later
(NOTE: This requires – the database to be registered on a Postgres
Enterprise Manager and the PEM Agent should be running and bound
to that server.)
PEM Server – START/STOP/RESTART :
Stop / start Postgres 10:
/opt/PostgreSQL/10/bin/pg_ctl -D /opt/PostgreSQL/10/data restart
Stop the PEMHTTPD and the pemagent service:
systemctl restart pemagent
systemctl restart PEMHTTPD
Error logs For Apache server :
tail -f /opt/edb/pem/httpd/apache/logs/error_log
Comments
Post a Comment