Tuesday, May 12, 2015

Postgres Toolkit 0.2 released

Today, we pleased to announce the release of Postgres Toolkit 0.2.

■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.)

CommandDescription
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 Toolkit

Postgres Toolkit 0.2 can be downloaded and installed via the Internet. Run
curl -L http://dl.uptimeforce.com/postgres-toolkit/install.sh | sh
or
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.

■Conclusion

In 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.

Enjoy PostgreSQL!

11 comments:

Unknown said...

For the wget installation you really should use https.

The names collide with Percona Toolkit (e.g. pt-kill), which makes it very hard to install both on the same box (did you know pt-query-digest from percona support Postgres)

Satoshi Nagayasu said...

Hi! Thanks for the comment!

I will setup https asap.

Indeed. The prefix is a bit confusing for some people.
But I think it could be OK to install in the different directories if you want to install both on one box.

AFAIK, pt-query-digest does support only server log files for PostgreSQL. Right?

pt-session-profiler can capture and analyze the PostgreSQL wire protocol with tcpdump.

And I guess Postres DBA does not install Percona Toolkit on their box...

Anyway, thanks for your feedback again!
I want to learn more about how to improve productivity and quality of our DBA work!

Ross Reedstrom said...

As to the name collision - it's likely that a DBA who has to support all the things that have been installed over the years in any legacy environment will have to deal with both PostgreSQL and ... other databases. If I could specialize to just PostgreSQL, that would be ... amazing. The default PostgreSQL prefix for tools is pg_. How about moving to pgt_ for postgresql toolkit?

Anonymous said...

These are useful tools - however these would be much nicer in a package.
also...there is no 'uninstall' currently that I see.


being a redhat world, why did you pick /opt ?

default path'ing...etc could be taken care of. also dep solving could be taken care of with packages.

Anonymous said...

These are useful tools - however these would be much nicer in a package.
also...there is no 'uninstall' currently that I see.


being a redhat world, why did you pick /opt ?

default path'ing...etc could be taken care of. also dep solving could be taken care of with packages.

Satoshi Nagayasu said...

Ross,

Thanks for the comment.

> The default PostgreSQL prefix for tools is pg_. How about moving to pgt_ for postgresql toolkit?

It's considerable, but I'm afraid that such 3-letter prefix is a bit bothersome.

Would like to look for more ideas...

Satoshi Nagayasu said...

> These are useful tools - however these would be much nicer in a package.
> also...there is no 'uninstall' currently that I see.

> being a redhat world, why did you pick /opt ?

I just started considering building a RPM package and following the Red Hat's path'ing.

The toolkit may be also provided as a RPM package in the future release.

Ross Reedstrom said...

Since you're doing packaging anyway, perhaps a build option for prefix? gnu tools used to have to do this to avoid collision w/ (usually broken) system binaries, hence gawk and gmake.

Eloi Ribeiro said...

Hi Satoshi, cool tools and thanks for making them available.
In my machine (Ubuntu 14.04) I have Python 2.7, I see that the Python version used has been 2.6.
Every time I try to run one of the tools I get '/usr/bin/env: python2.6: No such file or directory'. Most likely this code can run on Python 2.7 (correct me if I'm wrong). So my question is: is there a way to point to other path whether Python is installed? Thanks!

Satoshi Nagayasu said...

Hi Eloi,

Thanks for your interest.
Now, I'm working on that to support Ubuntu.
Please visit the issue (and the branch) on Github.

https://github.com/uptimejp/postgres-toolkit/issues/7
https://github.com/uptimejp/postgres-toolkit/tree/fix/ubuntu

As of today, I just confirmed that python2.7 on Ubuntu 14.04
can run with PostgreSQL 9.3, but I've not yet confirmed other
version.

I will release next version which support Ubuntu (or python2.7)
asap after passing the regression tests with other major versions.

If you want a quick try with python2.7, you can replace "python2.6"
with "python2.7" in every scripts by yourself. And it would work.

Eloi Ribeiro said...

Thanks Satoshi,

Following the instructions from the link you provide everything is working fine.
So in Ubuntu 14.04 the installation should look like:

wget http://dl.uptimeforce.com/postgres-toolkit/install.sh
sudo sh install.sh
export PATH=$PATH:/opt/uptime/postgres-toolkit-0.2/bin
sudo ln -s /usr/bin/python2.7 /usr/bin/python2.6

Cheers,