Monitoring PostgreSQL database performance using Pganalyze

Pganalyze is used to tracking logs and slow queries. Pganalyze has feature of performance monitoring, user and roles privileges setup, logs and insights.

1. Installation and configuration of Postgres server 9.x:

a. Install the postgresql server with contributions setup using below commands in root user,

$ sudo apt-get install postgresql postgresql-contrib

b. Add this extension code at end of the file /etc/postgresql/postgresql.conf as below,

shared_preload_libraries = 'pg_stat_statements'

# Increase the max size of the query strings Postgres records

track_activity_query_size = 2048

# Track statements generated by stored procedures as well

pg_stat_statements.track = all

c. Restart the postgresql service by below commands,

$ sudo service postgresql restart

2. Installation and configuration of pganalyze-collector :

a. First, curl command the website of pganalyze-collector keys as below,

curl -L https://packages.pganalyze.com/pganalyze_signing_key.asc | apt-key add -

b. And save the debian file in repository list,

echo "deb [arch=amd64] https://packages.pganalyze.com/ubuntu/trusty/ stable main" > /etc/apt/sources.list.d/pganalyze_collector.list

c. Update the system repository and install pganalyze-collector as below commands,

apt-get update

apt-get install pganalyze-collector

d. Edit /etc/pganalyze-collector.conf and enter API key from the site https://app.pganalyze.com/users/sign_in (sign up and sign in before proceeding with this step)

[pganalyze]

api_key: NMQD47CHU6V3HGKA

[server1]

db_name: postgres

db_username: postgres

db_password: password

db_host: localhost

db_port: 5432

e. Test the collector first and then you’ll now need to reload the running pganalyze collector process, so it picks up the new configuration,

$ pganalyze-collector –test

f. I [default] Test submission successful (1010 KB received) – proceed running the collector as a daemon

$ systemctl reload pganalyze-collector

Wait for 20 Minutes, postgresql server will integrated in pganalyze through pganalyze-collector as shown above.

3. Configuring Query Performance Monitoring and Log insights:

a. First, upgrade your system and pganalyze-collector if requires,

$ sudo apt-get upgrade pganalyze-collector

b. Edit /etc/pganalyze-collector.conf for query entry in server and log file for log insights,

[server1]

db_name: postgres, *

db_username: postgres

db_password: password

db_host: localhost

db_port: 5432

db_log_location: /var/log/postgresql/postgresql-9.3-main.log

c. We provide a helper for discovering the log directory, which you can run like this as root,

$ pganalyze-collector –discover-log-location

2018/12/05 11:51:30 I [server1] Found log location, add this to your pganalyze-collector.conf in the [server1] section:

db_log_location = /var/log/postgresql/postgresql-9.3-main.log

d. Now we can use the –test option of the collector to verify that log collection and parsing works,

$ pganalyze-collector –test

2018/12/05 11:40:06 I [server1] Testing statistics collection...
2018/12/05 11:40:07 I [server1] Test submission successful (15.8 KB received)
2018/12/05 11:40:07 I [server1] Testing local log tailing...
2018/12/05 11:40:13 I [server1] Log test successful
2018/12/05 11:40:13 I Re-running log test with reduced privileges of "pganalyze" user (uid = 107, gid = 113)
2018/12/05 11:40:13 I [server1] Testing local log tailing...
2018/12/05 11:40:19 I [server1] Log test successful