Monitoring PostgreSQL in real time is one of the most important tasks for database administrators and developers. Whether you want to observe running queries, detect blocking transactions, or analyze system performance, having the right tool can make a huge difference.
One of the best tools for this purpose is pg_activity, a terminal-based PostgreSQL activity monitor inspired by htop. It provides a live view of query execution, system usage, and backend performance—all from the command line.
In this blog, we’ll explore:
- What pg_activity is
- How to install it (via APT and source code)
- How to connect and use it effectively
- Some useful query examples for monitoring performance
- And why this tool is a must-have for PostgreSQL administrators
What is pg_activity?
pg_activity is an open-source monitoring tool developed by the Dalibo community. It offers a top-like, real-time interface for PostgreSQL server activity. It is lightweight, easy to use, and doesn’t require complex setup like GUI monitoring tools.
When you launch it, it shows:
- Currently running queries
- CPU and memory usage per process
- Read/write operations
- Query duration and transaction times
- Blocked or waiting queries
- Database and user activity
With a simple command-line interface, you can monitor database health in real time—making it ideal for both production and development environments.
Installing pg_activity Using APT
If you’re using Ubuntu or Debian, the easiest way to install pg_activity is via the package manager.
sudo apt update
sudo apt install pg-activity
Once the installation is complete, you can verify it using:
pg_activity --version
Example output:
pg_activity 3.0.3
Now, connect to your PostgreSQL database and launch the tool:
sudo -u postgres pg_activity -U postgres
This command connects to your PostgreSQL server as the postgres user and displays a live, color-coded interface showing all running activities.
Installing pg_activity from Source Code
If you want to install the latest version or explore the tool’s source code, you can build it manually from GitHub.
Step 1: Clone the Repository
git clone https://github.com/dalibo/pg_activity.git
cd pg_activity
Step 2: Create a Virtual Environment
It’s always recommended to work inside a Python virtual environment to avoid dependency issues.
python3 -m venv .venv
source .venv/bin/activate
Step 3: Install Dependencies and the Package
Run the following command inside the virtual environment:
pip install ".[psycopg]"
This installs pg_activity along with all required dependencies such as psutil, blessed, and psycopg.
Step 4: Verify Installation
pg_activity --version
Example output:
pg_activity 3.6.1
Step 5: Run the Tool
If you’re logged in as a normal Linux user, you might see a connection error like:
could not connect to PostgreSQL: connection failed: FATAL: role "username" does not exist
This happens because PostgreSQL by default connects using the Linux username. To fix this, switch to the postgres user:
sudo su postgres
pg_activity
Now, you’ll see the real-time PostgreSQL activity dashboard running inside your terminal.
Command-Line Usage and Options
You can view all available options using:
pg_activity --help
Some commonly used options are:
- --rds : Enables AWS RDS support (hides system databases like rdsadmin)
- --output : Save running queries to a CSV file
- --no-db-size : Skip total database size calculation for faster refresh
- --wrap-query : Wrap query text instead of truncating
- --duration-mode : Choose to monitor by query, transaction, or backend duration
- --filter FIELD:REGEX : Filter by field (like dbname) using regex
- --refresh : Set custom refresh interval (default is 2 seconds)
Connecting to PostgreSQL Using Different Methods
Default Connection (as postgres user)
sudo -u postgres pg_activity -U postgres
1. Specify Database and Host
- pg_activity -U postgres -d mydatabase -h localhost -p 5432
- If it shows error, then create a file named pgpass and add the configuration for the Postgres user
- Create a password file so pg_activity and psql can connect without asking for a password.
nano ~/.pgpass
Add this line:
localhost:5432:postgres:postgres:your_password_here
Then set secure permissions:
chmod 600 ~/.pgpass
Now, run:
pg_activity -U postgres -d postgres -h localhost -p 5432
2. Connection String Format
pg_activity "host=localhost port=5432 dbname=mydatabase user=postgres"
3. Filter Queries by Database
Sudo su postgres
pg_activity --filter dbname:mydatabase
These methods help in connecting to the desired database or filtering monitoring output based on your needs.
Practical SQL Query Examples for Monitoring
Although pg_activity gives you a live overview, it’s useful to combine it with some SQL queries for deeper insights.
Here are some examples you can run in psql before using pg_activity to understand what’s happening behind the scenes.
1. Check Active Queries
SELECT pid, datname, usename, state, query
FROM pg_stat_activity
WHERE state = 'active';
2. Find Long-Running Queries
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;
3. Identify Blocked Queries
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
4. View I/O Statistics
SELECT relname, heap_blks_read, heap_blks_hit,
heap_blks_hit * 100.0 / NULLIF(heap_blks_hit + heap_blks_read, 0) AS cache_hit_ratio
FROM pg_statio_user_tables
ORDER BY cache_hit_ratio DESC;
Running these queries helps you understand what data pg_activity visualizes in its terminal interface.
Why pg_activity is Essential for DBAs
- Real-Time Query Monitoring: Instantly see what queries are running and for how long, without running multiple SQL queries manually.
- Performance Debugging: Detect slow or blocking queries directly from the terminal.
- Resource Analysis: Monitor CPU, memory, and disk I/O usage per backend process.
- Transaction Insights: Switch duration modes to view long-running transactions or idle-in-transaction sessions.
- Cloud Support: Works seamlessly with PostgreSQL on AWS RDS using the --rds flag.
Lightweight and Fast: Unlike GUI tools, pg_activity doesn’t consume heavy system resources and can be used over SSH for remote monitoring.
Best Practices
- Always use the postgres user or a user with sufficient privileges to access pg_stat_activity.
- Use the --output option to log query performance data for offline analysis.
- Combine pg_activity with PostgreSQL configuration parameters like log_min_duration_statement for deeper query insights.
- Adjust the refresh interval (--refresh) to balance real-time accuracy with performance.
- Use --filter to monitor a specific database in multi-database instances.
Conclusion
pg_activity is one of the most efficient and user-friendly command-line tools for monitoring PostgreSQL in real time. It’s lightweight, fast, and gives you complete visibility into what’s happening in your database at any moment.
Whether you install it via APT or from source, pg_activity helps you quickly identify performance bottlenecks, long-running queries, and resource usage — all from your terminal.
For database administrators, it’s not just a tool — it’s an everyday companion that simplifies database monitoring and performance tuning.
So next time you face a PostgreSQL performance issue, simply run:
sudo -u postgres pg_activity
And within seconds, you’ll have a live dashboard of your database activity right before your eyes.