‍‍

Introduction In today’s digital landscape, efficient and reliable database management is crucial for businesses of all sizes. PostgreSQL, with its robust features and scalability, has become a popular choice among organizations. To ensure optimal performance and track vital metrics, it’s essential to have a comprehensive monitoring solution in place. In this tutorial, we will guide you through the process of setting up Prometheus and Grafana Monitoring Platforms to monitor your PostgreSQL database server effectively on Ubuntu 22.04.

Prerequisites

Before diving into the monitoring setup, make sure you have the following prerequisites in place:

• A server with Prometheus and Grafana installed, such as a Linux SSD VPS from Shape.host.
• A target server with PostgreSQL database server already installed.
• A non-root user with sudo/root administrator privileges.

Once you have fulfilled the prerequisites, you can proceed with the PostgreSQL server configuration.

PostgreSQL Server Configuration

To optimize the security and functionality of your PostgreSQL server, some configuration steps are necessary. In this section, we will focus on setting up the ‘scram-sha-256’ password encryption method and adjusting the authentication methods.

1. Start by navigating to the PostgreSQL configuration directory using the following command:

cd /etc/postgresql/14/main

2. Open the ‘postgresql.conf’ configuration file using the nano editor:

sudo nano postgresql.conf

3. Locate the ‘password_encryption’ option and uncomment it. Change the value to ‘scram-sha-256’:

password_encryption = scram-sha-256

4. Save the changes and exit the editor.
5. Next, open the ‘pg_hba.conf’ configuration file using the nano editor:

sudo nano pg_hba.conf

6. Modify the authentication methods for the host ‘127.0.0.1/32’ and ‘::1/128’ to use ‘scram-sha-256’:

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

7. Save the changes and exit the editor.
8. Restart the PostgreSQL service to apply the configuration changes:

sudo systemctl restart postgresql

With the PostgreSQL server configuration complete, we can move on to downloading and configuring the ‘postgres_exporter’.

Downloading postgres_exporter

The ‘postgresexporter’ is an essential component for gathering PostgreSQL server metrics and exposing them to the Prometheus server. Let’s go through the steps to download and set up ‘postgresexporter’ on your PostgreSQL server:

1. Create a new system user named ‘postgres_exporter’ using the following command:

sudo useradd -M -r -s /sbin/nologin postgres_exporter

2. Download the ‘postgresexporter’ binary file. Ensure you are on the latest version by visiting the ‘postgresexporter’ GitHub page:

wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.12.0-rc.0/postgres_exporter-0.12.0-rc.0.linux-amd64.tar.gz

3. Extract the ‘postgres_exporter’ package using the following command:

tar xvf postgres_exporter*.tar.gz

4. Move the extracted directory to ‘/opt/postgres_exporter’:

mv postgres_exporter*/ /opt/postgres_exporter

5. Verify that the ‘postgresexporter’ binary is available in the ‘/opt/postgresexporter’ directory:

ls /opt/postgres_exporter

Now that we have the ‘postgres_exporter’ downloaded, let’s proceed with its configuration.

Configuring postgres_exporter

The configuration of ‘postgres_exporter’ involves defining the PostgreSQL user, password, and adjusting the desired monitoring settings. Additionally, we will set up a systemd service for easy management. Follow the steps below:

1. Move to the ‘/opt/postgres_exporter’ directory:

cd /opt/postgres_exporter

2. Create a new file named ‘.env’ using the nano editor:

nano .env

3. Add the following lines to the file, ensuring to replace the placeholder values with your PostgreSQL user, password, and host details:

DATA_SOURCE_NAME="postgresql://postgres:strongpostgrespassword@localhost:5432/?sslmode=disable"

4. Save the file and exit the editor.
5. Change the ownership of the ‘/opt/postgresexporter’ directory to the ‘postgresexporter’ user:

sudo chown -R postgres_exporter: /opt/postgres_exporter

6. Create a new systemd service file ‘/etc/systemd/system/postgres_exporter.service’ using the following command:

sudo tee /etc/systemd/system/postgres_exporter.service<<EOF
[Unit]
Description=Prometheus exporter for Postgresql
Wants=network-online.target
After=network-online.target

[Service]
User=postgres_exporter
Group=postgres_exporter
WorkingDirectory=/opt/postgres_exporter
EnvironmentFile=/opt/postgres_exporter/.env
ExecStart=/opt/postgres_exporter/postgres_exporter --web.listen-address=:9187 --web.telemetry-path=/metrics
Restart=always

[Install]
WantedBy=multi-user.target
EOF

7. Reload the systemd manager to apply the changes:

sudo systemctl daemon-reload

8. Start and enable the ‘postgres_exporter’ service:

sudo systemctl start postgres_exporter
sudo systemctl enable postgres_exporter

9. Verify the status of the ‘postgres_exporter’ service:

sudo systemctl status postgres_exporter

The ‘postgres_exporter’ service should be running and enabled, ensuring the collection of PostgreSQL metrics. With the configuration complete, we can now move on to setting up the firewall.

Setting up Firewall

Securing your PostgreSQL server is crucial, and configuring the firewall is an essential part of that process. In this step, we will open the default port of ‘postgres_exporter’ and verify the accessibility of metrics via a web browser.

Configuring UFW (Uncomplicated Firewall)

If your Ubuntu system uses UFW as the firewall, follow these steps:

1. Allow port 9187 for ‘postgres_exporter’:

sudo ufw allow 9187/tcp

2. Reload the firewall to apply the changes:

sudo ufw reload

3. Verify the list of open ports on UFW:

sudo ufw status

Configuring firewalld

If you are using a RHEL-based distribution with firewalld, perform the following steps:

1. Add port 9187 to the firewalld:

sudo firewall-cmd --add-port=9187/tcp --permanent

2. Reload the firewalld to apply the changes:

sudo firewall-cmd --reload

3. Verify that port 9187 is added to the firewall:

sudo firewall-cmd --list-ports

4. Open a web browser and access your PostgreSQL server IP address followed by port 9187 (e.g., http://192.168.5.21:9187/metrics).

You should now see the PostgreSQL server metrics gathered by ‘postgresexporter’. With the firewall configured, we can proceed to add ‘postgresexporter’ to the Prometheus ‘scrape_configs’ target.

Adding postgres_exporter to Prometheus

The next step involves integrating ‘postgres_exporter’ into Prometheus to collect and store PostgreSQL metrics. Follow the instructions below:

1. Open the Prometheus configuration file using the nano editor:

sudo nano /etc/prometheus/prometheus.yml

2. Under the ‘scrape_configs’ section, add the following lines to create a new job for gathering PostgreSQL server metrics:

- job_name: 'postgres_exporter'
scrape_interval: 5s
static_configs:
- targets: ['192.168.5.21:9187']

3. Save the file and exit the editor.
4. Restart the Prometheus service to apply the changes:

sudo systemctl restart prometheus

5. Access the Prometheus web dashboard by entering your Prometheus server IP address followed by the default port 9090 (e.g.,https://192.168.5.100:9090).
6. Log in to the Prometheus dashboard using your credentials if basic authentication is enabled.
7. Click on the ‘browser’ icon to display the list of Prometheus queries.
8. Filter the queries starting with ‘pg’ to view PostgreSQL-related metrics.
9. Navigate to the Status menu and select Targets. Verify that the job name ‘postgres_exporter’ is up and running on the PostgreSQL server with IP address ‘192.168.5.21:9187’.

With ‘postgres_exporter’ successfully added to Prometheus, we can now proceed to set up the Grafana dashboard for monitoring the PostgreSQL server effectively.

Setting up Dashboard Monitoring via Grafana

Grafana provides a user-friendly interface for visualizing metrics collected by Prometheus. Let’s configure Grafana and import the PostgreSQL monitoring dashboard:

1. Open your Grafana domain or server IP address in a web browser and log in using your credentials.
2. Once logged in, click on the Dashboard menu and select Import.
3. Enter the PostgreSQL dashboard ID ‘9628’ and click Load. This ID corresponds to the PostgreSQL dashboard available on the Grafana Dashboard repository.
4. Select the Prometheus data source and click Import to apply the changes.
5. Provide a name for your new dashboard, such as ‘PostgreSQL Monitoring’.
6. After the dashboard is imported, you can explore the various visualizations and metrics related to PostgreSQL server monitoring.

Congratulations! You have successfully completed the setup of PostgreSQL server monitoring with Prometheus and Grafana using ‘postgres_exporter’. With this monitoring solution in place, you can gain valuable insights into the performance and health of your PostgreSQL database.

Prometheus and Grafana, along with ‘postgres_exporter’, provide a powerful stack for monitoring and visualizing your applications. Make sure to regularly check the Prometheus Community GitHub page for updates and additional exporters to enhance your monitoring capabilities.

For reliable and scalable cloud hosting solutions, consider Linux SSD VPS from Shape.host. Their services provide the perfect environment for deploying and managing your PostgreSQL databases. Ensure optimal performance and security with Shape.host’s trusted hosting solutions.