■What is "Postgres Toolkit"?Postgres Toolkit is a collection of scripts and utilities which is intended to help PostgreSQL DBA to improve quality and productivity of their daily jobs and operations.
With having Postgres Toolkit, DBA will be able to avoid from writing complicated queries and maintaining their own scripts for their daily operations and do daily DBA jobs. So, the concept of the toolkit is like "A Victorinox for PostgreSQL DBA."
I found this concept when I was sitting with our clients to help them solve PostgreSQL performance issues. At that time, I had no such tool which could help me. I had to write my own several scripts, and it meant someone needed to maintain and support them.
So, I have decided to create "a single solution" which everyone can use anytime everywhere. It is similar to Percona Toolkit for MySQL users, produced by Percona.
Postgres Toolkit 0.2 now supports PostgreSQL 9.0, 9.1, 9.2, 9.3 and 9.4, and can run on Red Hat Enterprise Linux 6 and CentOS 6. Also, it requires Python 2.6 (Python 2.6 is instaled on RHEL6/CentOS6 by default.)
■What does Postgres Toolkit contain?Postgres Toolkit 0.2 contains following 13 commands as of today. (The command manual is available here.)
|pt-config||Show and set parameters in postgresql.conf, the PostgreSQL configuration file, without using editor (like emacs or vi.)|
|pt-index-usage||Show index usage, including index size, tuple/block access statistics, timestamp of vacuum/analyze, and tablespace name at once.|
|pt-kill||Send signal to a backend to cancel a running query or to terminate the backend safely.|
|pt-proc-stat||Show status and process statistics, including disk I/O and network I/O, of each process in the PostgreSQL instance.|
|pt-replication-stat||Show status and statistics of master and slave node(s) at once, which a replication cluster consists of.|
|pt-session-profiler||Show queries, which exceeded specified elapsed time, by capturing network traffice and analyze it.|
|pt-set-tablespace||Change tablespace for multiple tables and releted indexes at once.|
|pt-snap-statements||Show statistics of queries which ran in the specified period of time.|
|pt-stat-snapshot||Take snapshots of several statistics which can be obtained in PostgreSQL, and manage them.|
|pt-table-usage||Show table usage, including table size, tuple/block access statistics, timestamp of vacuum/analyze, and tablespace name at once.|
|pt-tablespace-usage||Summarize tablespace usage per database, and show them with available space size of the partition.|
|pt-verify-checksum||Verify checksums of table and index files in a database cluster at once. (Supported 9.3 or later)|
|pt-xact-stat||Show several statistics of multiple PostgreSQL instances at once, including number of sessions and number of transactions (commit and rollbacks)|
■How to install Postgres ToolkitPostgres Toolkit 0.2 can be downloaded and installed via the Internet. Run
curl -L http://dl.uptimeforce.com/postgres-toolkit/install.sh | shor
wget http://dl.uptimeforce.com/postgres-toolkit/install.sh sh install.sh
If you want to try the latest code under development, please obtain from the github repository.
■ConclusionIn the following posts, I'm supposed to introduce how to use each command in the Toolkit. If you are using and managing PostgreSQL, please try this toolkit, and send me your feedback. I think this toolkit can help you in some cases of your daily operations. I welcome your feedback via e-mail, postgres-toolkit at uptime dot jp, or on our Github Issue, or comments on this blog.
I know Postgres Toolkit can be (and needs to be) improved and enhanced in the near future, but it also needs any kind of your feedback. So, please try it and please send me your questions, thoughts and ideas.