pgAdmin在Ubuntu上的数据库监控

Installing pgAdmin on Ubuntu
To monitor a PostgreSQL database using pgAdmin on Ubuntu, you first need to install pgAdmin. Run the following commands in the terminal to update your package list, install pgAdmin, and set a password for the default postgres user:

sudo apt update
sudo apt install pgadmin4
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'your_password';"

During installation, follow the on-screen prompts to complete the setup. You can access pgAdmin via a web browser (typically at http://localhost:5050) or launch it from the application menu.

Connecting pgAdmin to PostgreSQL
After installation, connect pgAdmin to your PostgreSQL server:

1. In pgAdmin’s left-hand Object Browser, right-click Servers and select Create > Server….
2. In the General tab, enter a server name (e.g., “Local PostgreSQL”).
3. Go to the Connection tab and fill in the details:
   • Host name/address: localhost (or your server’s IP if remote).
   • Port: 5432 (default PostgreSQL port).
   • Username: postgres (default superuser).
   • Password: The password you set for the postgres user.
4. Click Save to establish the connection.

Using pgAdmin’s Built-in Monitoring Tools
pgAdmin offers several GUI tools to monitor database performance and activity:

• Activity Monitor: After connecting to a server, click the Dashboard icon (or go to Tools > Monitor) to view real-time metrics like CPU usage, memory consumption, disk I/O, and active connections. This provides a high-level overview of server health.
• Server Statistics: Right-click a server in the Object Browser and select Statistics. Here, you can track metrics such as total queries executed, tuples returned/inserted/updated/deleted, and cache hit ratios—useful for identifying trends in database workload.
• Table Statistics: Expand a database, right-click a table, and choose Statistics. This shows table-specific data like row count, index size, toast table usage, and sequential vs. index scans—helping you optimize table performance.

Monitoring Queries with pgAdmin
pgAdmin helps you analyze query performance to pinpoint bottlenecks:

• Query Tool: Open the Query Tool (Tools > Query Tool) to execute SQL queries. After running a query, click the Explain/Explain Analyze button (or press F7) to view the execution plan. Look for operations with high costs (e.g., sequential scans) or long durations—these are areas for optimization.
• Active Sessions: Use the pg_stat_activity view to monitor current connections and queries. Run the following query in the Query Tool to see active sessions (filter by state = 'active' to focus on running queries):

  SELECT pid, usename, application_name, client_addr, query_start, state, query 
  FROM pg_stat_activity 
  WHERE state = 'active';
  

  This helps identify long-running or stuck queries that may impact performance.

Advanced Monitoring with System Tools and Logs
For comprehensive monitoring, combine pgAdmin with system tools and log analysis:

• System Monitoring Commands: Use top or htop to view real-time CPU and memory usage, vmstat 1 to monitor virtual memory and disk I/O (refreshes every second), and iostat to check disk performance (e.g., await time for storage latency). Install these tools with sudo apt install htop vmstat iostat.
• Slow Query Logs: Enable slow query logging in PostgreSQL by editing /etc/postgresql/<version>/main/postgresql.conf. Set log_min_duration_statement = 500 (logs queries taking longer than 500ms) and logging_collector = on. Restart PostgreSQL (sudo systemctl restart postgresql) to apply changes. Use pgBadger to parse logs and generate visual reports:

  sudo apt install pgbadger
  sudo pgbadger /var/log/postgresql/postgresql-*.log -o /var/log/pgbadger/report.html
  

  Open the HTML report to analyze slow queries and trends.

Key Performance Metrics to Track
When monitoring with pgAdmin, focus on these critical metrics to ensure optimal database performance:

• CPU Usage: High CPU utilization may indicate inefficient queries (e.g., missing indexes) or excessive connection counts.
• Memory Usage: Check shared_buffers usage (via pg_stat_database) to ensure PostgreSQL has enough memory for caching.
• Disk I/O: High await times (from iostat) or frequent disk reads/writes (from vmstat) suggest slow storage or poorly optimized queries.
• Active Connections: A high number of idle connections can consume resources; use pg_stat_activity to identify and terminate unnecessary connections.