If you’re navigating the seas of PostgreSQL server management, you know how essential it is to monitor its activity effectively. Enter pg_activity, a command-line tool designed to assist in observing PostgreSQL server activity in real-time. This guide will walk you through the installation, usage, and troubleshooting of pg_activity. Let’s dive in!

Installation

Installing pg_activity can be accomplished in various ways depending on your system preferences.

From Distribution Packages

The easiest approach is to utilize the package manager of your Linux distribution. For example, on Debian-based distributions such as Debian, Ubuntu, or Mint, you would execute:

$ sudo apt install pg-activity

Note: Ensure to check if the package is up-to-date with the latest release. If you’re on a Debian bullseye, a backport is also available with:

$ apt install pg-activity/bullseye-backports

From PyPI

You can also install pg_activity using pip if you have Python 3.8 or later:

$ python3 -m pip install pg_activity[psycopg]

Alternatively, deploy it in an isolated environment with pipx:

$ pipx install pg_activity[psycopg]

From Source, Using Git

If you’re looking to test development versions, clone the repository:

$ git clone https://github.com/dalibo/pg_activity.git

Then follow up with:

$ cd pg_activity

Create a virtual environment and install pg_activity:

$ python3 -m venv .venv

$ . .venv/bin/activate

(.venv) $ pip install .[psycopg]

Finally, you can run:

(.venv) $ pg_activity

To quit and remove the environment, execute:

$ deactivate

$ rm -r .venv

Using pg_activity

Once you’ve successfully installed pg_activity, it’s time to put it to work! You have the flexibility to run pg_activity either locally or on a remote server. However, remember that to access detailed system information, you need to execute it as the PostgreSQL user.

Here’s how you can start:

sudo -u postgres pg_activity -U postgres

Configuration and Command-Line Options

pg_activity provides a variety of options for configurations. Think of it as customizing your cockpit for optimal control on your flight!

• -P PROFILE: Define a configuration profile.
• –db-size: Enable or disable displaying total database size.
• –refresh: Set the refresh rate between 0.5 and 5 seconds.
• –filter FIELD:REGEX: Filter activities based on a defined regex.

Troubleshooting

Even the best tools can have hiccups. Here are some troubleshooting tips:

• Issue: I can’t see my queries; only TPS is displayed.

Solution: pg_activity relies on pg_stat_activity. If queries are executed between scans, they won’t appear. Adjust the refresh time with the ‘+’ and ‘-‘ keys.

• Issue: Query text seems truncated.

Solution: You may need to increase the track_activity_query_size parameter in PostgreSQL settings.

• Issue: Trouble copying queries from focused processes.

Solution: Use the ‘y’ shortcut, but ensure your terminal emulator supports OSC 52 escape sequences.

For more insights, updates, or to collaborate on AI development projects, stay connected with fxis.ai.

Further Considerations

As you personalize pg_activity, remember that the color configurations allowed are intriguing and can make monitoring more visually appealing. You can define custom colors for various elements in your configuration file to categorize information better.

In Closing

By monitoring your PostgreSQL server activity with pg_activity, you’re not just keeping an eye on performance; you’re actively participating in optimizing your system. At fxis.ai, we believe that such advancements are crucial for the future of AI, as they enable more comprehensive and effective solutions. Our team is continually exploring new methodologies to push the envelope in artificial intelligence, ensuring that our clients benefit from the latest technological innovations.