Postgres Toolkit

Postgres Toolkit is a collection of script and command that can easily perform on the complex DBA works when performing PostgreSQL server operation management, performance tuning and Troubleshooting.

If you have any questions, requests or report bugs about Postgres Toolkit, please email me or use Github issues. email:postgres-toolkit at uptime dot jp Github: http://www.github.com/uptimejp/postgres-toolkit/

Contents:

Install the Toolkit

Supported OS

For a list of Operating System supported.

  • Red Hat Enterprise Linux 6 / CentOS 6
  • Red Hat Enterprise Linux 7 / CentOS 7
  • Ubuntu 14.04 LTS

Make sure you have Python2.6 or Python2.7 is installed.

PostgreSQL Version

For a list of PostgreSQL supported.

  • PostgreSQL 9.0
  • PostgreSQL 9.1
  • PostgreSQL 9.2
  • PostgreSQL 9.3
  • PostgreSQL 9.4

Installation

Use following command, you can install the toolkit 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

When the installs are done, copying related files under /opt/uptime/postgres-toolkit-<VERSION>.

pt-archive-xlog

Summary

Archive a transaction log file in safe way.

This command can be used in the archive_command parameter.

Usage

pt-archive-xlog <XLOGFILEPATH> <DESTDIR>

Options

No option.

Output Items

It returns with the exit code 0 on success, or 1 on failure.

Examples

Following example shows how to configure archive_command in postgresql.conf.

archive_command = '/path/to/pt-archive-xlog %p /path/to/archivedir'

pt-config

Summary

Refer the setting of the PosgreSQL configuration file postgresql.conf, and then modify it.

Usage

pt-config [options...] get [PARAM]
pt-config [options...] set [PARAM] [VALUE]
pt-config [options...] disable [PARAM]

Commands

get [PARAM] Displays the current value. If it is disabled(comment-out). (disabled) is displayed.
set [PARAM] [VALUE] Sets the new value. The value will be enabled if it is disabled (comment-out).
disable [PARAM] Disable the setting value.(comment-out)

Options

-D, --pgdata=PGDATA        Specify a PostgreSQL database cluster.
--apply                    Apply change(s).
--help                     Print this help.

-D, --pgdata Specifies the PostgreSQL database cluster. If not specified, to set the value of PGDATA.

--apply It will actually apply the changes to the postgresql.conf when it runs set / disable command.

Output Items

Reading: Loading the postgresql.conf file displays in the full path.
Dry-run mode: The before and after values is only displayed. It does not provide the actual configuration changes.
Applying: Doing the actual configuration changes.
Old Displays the value of before change.
New Displays the value of after change.
Updating: Modifying the postgresql.conf file displays in the full path.

Examples

Displays the current value of shared_buffers

$ pt-config -D /var/lib/pgsql/9.4/data get shared_buffers
[2015-04-16 17:08:12] INFO: Reading: /var/lib/pgsql/9.4/data/postgresql.conf
256MB
$

Sets the value 512MB of shared_buffers. (Does not do the actual configuration changes)

$ pt-config -D /var/lib/pgsql/9.4/data set shared_buffers 512MB
[2015-04-16 17:08:44] INFO: Reading: /var/lib/pgsql/9.4/data/postgresql.conf
[2015-04-16 17:08:44] INFO: Dry-run mode:
[2015-04-16 17:08:44] INFO:   Old: shared_buffers = 256MB                       # min 128kB
[2015-04-16 17:08:44] INFO:   New: shared_buffers = 512MB                       # min 128kB
$

Sets the value 512MB of shared_buffers. (Doing the actual configuration change)

$ pt-config -D /var/lib/pgsql/9.4/data --apply set shared_buffers 512MB
[2015-04-16 17:09:11] INFO: Reading: /var/lib/pgsql/9.4/data/postgresql.conf
[2015-04-16 17:09:11] INFO: Applying:
[2015-04-16 17:09:11] INFO:   Old: shared_buffers = 256MB                       # min 128kB
[2015-04-16 17:09:11] INFO:   New: shared_buffers = 512MB                       # min 128kB
[2015-04-16 17:09:11] INFO: Updated: /var/lib/pgsql/9.4/data/postgresql.conf
$

Sets the disable(comment-out) of shared_buffers.

$ pt-config -D /var/lib/pgsql/9.4/data --apply disable shared_buffers
[2015-04-16 17:09:52] INFO: Reading: /var/lib/pgsql/9.4/data/postgresql.conf
[2015-04-16 17:09:52] INFO: Applying:
[2015-04-16 17:09:52] INFO:   Old: shared_buffers = 512MB                       # min 128kB
[2015-04-16 17:09:52] INFO:   New: #shared_buffers = 512MB                      # min 128kB
[2015-04-16 17:09:52] INFO: Updated: /var/lib/pgsql/9.4/data/postgresql.conf
$

Displays the value of shared_buffers (Disabled).

$ pt-config -D /var/lib/pgsql/9.4/data get shared_buffers
[2015-04-16 17:10:00] INFO: Reading: /var/lib/pgsql/9.4/data/postgresql.conf
512MB (disabled)
$

pt-index-usage

Summary

Displays the indexes usage.

Usage

pt-index-usage [option...]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
-o, --owner=STRING
-n, --schema=STRING
-t, --table=STRING
-i, --index=STRING
-u, --unused
--help

-h, --host Specifies the connecting the PostgreSQL database server name or its IP address. If not specified, to set the value of PGHOST. localhost will be used if no other value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PostgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used If no other value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. Database name as database user name will be used if no other value has been defined in PGDATABASE.

-o, --owner The index information is only displayed which matches to a string specified where is a name of the owner. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

-n, --schema The index information is only displayed which matches to a string specified where is in the schema. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

-t, --table The index information is only displayed which matches to a string specified where is in the table. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

-i, --index The index information is only displayed which matches to a string specified. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

-u, --unused The index information is displayed which not use the index.

-d (or --dbname ), -o (or --owner ), -n (or --schema ), -t (or --table ), -i (or --index ), -u (or --unused ), if specified the options at the same time, the index is displayed which matches to it.

Output Items

OID Object ID of index
OWNER Owner name of index
SCHEMA Schema name on being indexed
TABLE Table name on being indexed
INDEX Index name
BLKS Block (every 8kb) of index
SCAN Index scan number of executions
T_READ Number of index entries by index scan
T_FTCH Number of tuple on table by index scan
B_READ Number of index blocks read from disk
B_HIT Number of index pages read from shared buffer
STATUS Index status. It reads from pg_index system table.
TABLESPACE The tablespace name that has index

Examples

For the index of table in the public schema, displays the usage situation.

$ pt-index-usage -n public -d postgres
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
|  OID  | OWNER | SCHEMA |      TABLE       |         INDEX         | BLKS | SCAN | T_READ | T_FTCH | B_READ | B_HIT | STATUS | TABLESPACE |
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
| 26793 | snaga | public | pgbench_accounts | pgbench_accounts_pkey |  276 |    1 |      1 |      1 |      4 |     0 |        | spc1       |
| 26789 | snaga | public | pgbench_branches | pgbench_branches_pkey |    2 |    1 |      1 |      0 |      2 |     0 |        | pg_default |
| 26791 | snaga | public | pgbench_tellers  | pgbench_tellers_pkey  |    2 |    0 |      0 |      0 |      0 |     0 |        | pg_default |
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
$

For the index of pgbench_accounts table in the public schema, displays the usage situation.

$ pt-index-usage -n public -d postgres -t pgbench_accounts
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
|  OID  | OWNER | SCHEMA |      TABLE       |         INDEX         | BLKS | SCAN | T_READ | T_FTCH | B_READ | B_HIT | STATUS | TABLESPACE |
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
| 26793 | snaga | public | pgbench_accounts | pgbench_accounts_pkey |  276 |    1 |      1 |      1 |      4 |     0 |        | spc1       |
+-------+-------+--------+------------------+-----------------------+------+------+--------+--------+--------+-------+--------+------------+
$

For the index not used even once, in the public schema, displays the usage situation.

$ pt-index-usage -d postgres -n public -u
+-------+-------+--------+-----------------+----------------------+------+------+--------+--------+--------+-------+--------+------------+
|  OID  | OWNER | SCHEMA |      TABLE      |        INDEX         | BLKS | SCAN | T_READ | T_FTCH | B_READ | B_HIT | STATUS | TABLESPACE |
+-------+-------+--------+-----------------+----------------------+------+------+--------+--------+--------+-------+--------+------------+
| 26791 | snaga | public | pgbench_tellers | pgbench_tellers_pkey |    2 |    0 |      0 |      0 |      0 |     0 |        | pg_default |
+-------+-------+--------+-----------------+----------------------+------+------+--------+--------+--------+-------+--------+------------+
$

pt-kill

Summary

Interrupting the processing of PostgreSQL backend, or to exit it.

Usage

pt-kill [options...] [command] [pid]

Commands

cancel                     Cancel a running query.
terminate                  Terminate a backend with canceling query.

Options

--help                     Print this help.

Output Items

None.

Examples

Cancel the SQL running in the process ID 3289.

$ pt-kill cancel 3289

Exit the backend in the process ID 3219.

$ pt-kill terminate 3291

pt-privilege-autogen

Summary

Generates REVOKE/GRANT statements for giving minimum privileges to users and database objects. It uses PostgreSQL statistics to learn access policy of the application.

Usage

pt-privilege-autogen [option...] [ start | stop ]
pt-privilege-autogen [option...] generate <USERNAME>

Commands

start Starts collecting table access statistics of the database.
generate Generates REVOKE/GRANT statements from collected access statistics of the tables. Requires user name to be granted.
stop Stops collecting table access statistics of the database.

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
--help

-h, --host Specifies the connecting PostgreSQL database server name or IP address. If not specified, to set the value of PGHOST. localhost will be used if no value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PosgtgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used if no value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. It connect to the database same as the database username if no value has been defined in PGDATABASE.

Output Items

This command shows GRANT/REVOKE statements that can be applied to the PostgreSQL database.

Examples

This example shows how to generate REVOKE/GRANT statements for the user snaga to be allowed to run pgbench transactions with minimum privileges.

Following procedure needs to be done by the user who already has the several permissions, like super-user. This example uses postgres super-user.

First, prepare a database for pgbench with superuser, and make sure that a regular user can’t access to the tables.

$ createdb -U postgres mydb
$ pgbench -i -U postgres mydb
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.70 s, remaining 0.00 s).
vacuum...
set primary keys...
done.
$ pgbench -c 1 -t 1 -U snaga -n mydb
ERROR:  permission denied for relation pgbench_branches
$

Second, start collecting table access statistics, and run pgbench transactions.

$ pt-privilege-autogen -U postgres -d mydb start
[2015-08-04 04:40:45] INFO: Collecting access statistics started.
$ pgbench -c 1 -t 1 -U postgres -n mydb
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1
number of transactions actually processed: 1/1
latency average: 0.000 ms
tps = 14.402581 (including connections establishing)
tps = 20.464964 (excluding connections establishing)
$

Then, generate an access policy file, and apply it to the database. A regular user snaga is granted least privileges for four tables here.

$ pt-privilege-autogen -U postgres -d mydb generate snaga

-- Database
REVOKE ALL ON DATABASE "mydb" FROM "public";
GRANT CONNECT,TEMP ON DATABASE "mydb" TO "snaga";

-- Schema
REVOKE ALL ON SCHEMA "public" FROM "public";
GRANT USAGE ON SCHEMA "public" TO "snaga";

-- Table
REVOKE ALL ON TABLE "public"."pgbench_accounts" FROM "snaga";
REVOKE ALL ON TABLE "public"."pgbench_branches" FROM "snaga";
REVOKE ALL ON TABLE "public"."pgbench_history" FROM "snaga";
REVOKE ALL ON TABLE "public"."pgbench_tellers" FROM "snaga";
GRANT SELECT,UPDATE ON TABLE "public"."pgbench_accounts" TO "snaga";
GRANT SELECT,UPDATE ON TABLE "public"."pgbench_branches" TO "snaga";
GRANT INSERT ON TABLE "public"."pgbench_history" TO "snaga";
GRANT SELECT,UPDATE ON TABLE "public"."pgbench_tellers" TO "snaga";


$ pt-privilege-autogen -U postgres -d mydb generate snaga > grant.sql
$ psql -f grant.sql -U postgres mydb
REVOKE
GRANT
REVOKE
GRANT
REVOKE
REVOKE
REVOKE
REVOKE
GRANT
GRANT
GRANT
GRANT
$

Finally, stop collecting access statistics, and make sure that the regular user snaga can now run pgbench transaction on the database with the least privileges.

$ pt-privilege-autogen -U postgres -d mydb stop
[2015-08-04 04:44:21] INFO: Collecting access statistics stopped.
$ pgbench -c 1 -t 1 -U snaga -n mydb
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1
number of transactions actually processed: 1/1
latency average: 0.000 ms
tps = 33.598764 (including connections establishing)
tps = 82.182774 (excluding connections establishing)
$

pt-proc-stat

Summary

Displays statistics I/O for each process.

Usage

pt-proc-stat [option...] [delay [count]]

pt-proc-stat Referring to proc file system. Make sure you run by running PostgreSQL user or root user.

Options

-D, --pgdata=DATADIR
-P, --pid=PID
--help

-D, --pgdata Specifies the directory of the database cluster.

-P, --pid Specifies the process ID of the postmaster process.

Output Items

PROCESS NAME Process name
PID Process ID
STAT Status of the process
USR User CPU time (Difference)
SYS System CPU time (Difference)
VSZ Virtual memory size (MB)
RSS Usage of a physical memory (MB)
READ Reading I/O volume is displayed differently between both of the data(kB).
WRITE Writing I/O volume is displayed differently between both of the data(kB).
READ2 Load I/O except the READ (KB / Difference)
WRITE2 Load I/O except the WRITE (KB / Difference)

Examples

It looks for the postmaster process automatically. Statistics I/O of the postmaster and the child process is displayed every five seconds twice.

$ sudo ./pt-proc-stat 5 2
Fri May  1 22:23:39 JST 2015
        PROCESS NAME[  PID] STAT  USR  SYS  VSZ  RSS   READ  WRITE  READ2 WRITE2
          postmaster[24026]    S    4   13  100    9  23752 1290092 1090800 155357
              logger[24028]    S    0    1   85    1      4     76     30    -45
        checkpointer[24030]    S    4  117  100    8    176  56768   -176 -46965
              writer[24031]    S  104   66  100    9      0 291080      0 130560
          wal writer[24032]    S    8   19  100    1      0   2928      0      0
 autovacuum launcher[24033]    S    3    3  101    2      8      8    288      0
     stats collector[24034]    S   13   32   85    1      0   2140     34   -649
snaga postgres 127.0[25473]    R   32    9  101    7    296   1472   1264      0
snaga postgres 127.0[25474]    R   33    9  101    7    424   1384   1120      0
snaga postgres 127.0[25475]    R   33    9  101    7    424   1448   1016      0
snaga postgres 127.0[25476]    S   32    9  101    7    580   1400    780      0
snaga postgres 127.0[25477]    R   32    9  101    7    908   1368    492      0

Fri May  1 22:23:44 JST 2015
        PROCESS NAME[  PID] STAT  USR  SYS  VSZ  RSS   READ  WRITE  READ2 WRITE2
          postmaster[24026]    S    0    0  100    9      0      0      0      0
              logger[24028]    S    0    0   85    1      0      0      0      0
        checkpointer[24030]    S    0    0  100    8      0      0      0      0
              writer[24031]    S    4    1  100    9      0  11928      0    392
          wal writer[24032]    S    0    0  100    1      0      0      0      0
 autovacuum launcher[24033]    S    0    0  101    2      0      0      0      0
     stats collector[24034]    S    0    0   85    1      0      0      0      0
snaga postgres 127.0[25473]    R   72   18  101   10   1772   3608   1740      0
snaga postgres 127.0[25474]    R   68   20  101   10   1436   3920   2020      0
snaga postgres 127.0[25475]    D   70   18  101   10   1304   4216   2368      0
snaga postgres 127.0[25476]    R   70   20  101   10   1252   3384   2212      0
snaga postgres 127.0[25477]    R   73   16  101   10   1464   3224   2080      0

$

pt-replication-stat

Summary

The replication status is displayed by referring the master node statistical information of the replication. It is possible to display continuously on every specified interval.

PostgreSQL 9.1 or higher. PostgreSQL 9.0 doesn’t work because it doesn’t have no “pg_stat_replication” system view.

Usage

pt-replication-stat [option...] [delay [count]]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME

Output Items

PID The process ID of WAL sender processes on the master node
NAME The slave node name of the registered in the replication
HOST The host name of slave node or IP address
PORT The port number of the master node that connected to the slave node
STATE The slave node status. It acquires the value of following startup or backup or catchup or streaming
SENT Position on the WAL which sent to slave
WRITTTEN Position on the WAL which wrote to the WAL buffer on the slave
FLUSHED Position on the WAL which synchronously wrote to the WAL file on the slave
REPLAYED Position on WAL which applied to the data file on the slave
PRI If the slave node is synchronously replication that the node priority is displayed.
MODE The operation mode is displayed. sync is synchronous mode. async is asynchronous mode. potential is running in asynchronous mode. But there is a possibility to upgrade to the synchronous mode.

Examples

Connect to the port 5433 of host 127.0.0.1 by postgres user, the statistical information is displayed every five seconds twice.

$ pt-replication-stat -h 127.0.0.1 -p 5433 -U postgres 5 2
Sat Mar 28 21:45:23 JST 2015
+------+----------+-----------+-------+-----------+-----------+-----------+-----------+-----------+-----+--------+
| PID  |   NAME   |    HOST   |  PORT |   STATE   |    SENT   |  WRITTTEN |  FLUSHED  |  REPLAYED | PRI |  MODE  |
+------+----------+-----------+-------+-----------+-----------+-----------+-----------+-----------+-----+--------+
|      |          |           |       | local     | 0/5F30398 | 0/5F300B0 |           |           |     | master |
| 3323 | replica1 | 127.0.0.1 | 55580 | streaming | 0/5F300B0 | 0/5F300B0 | 0/5F300B0 | 0/5F2FE48 |   0 | async  |
| 3367 | replica2 | 127.0.0.1 | 55589 | streaming | 0/5F300B0 | 0/5F300B0 | 0/5F2FE48 | 0/5F2FE48 |   0 | async  |
+------+----------+-----------+-------+-----------+-----------+-----------+-----------+-----------+-----+--------+

Sat Mar 28 21:45:28 JST 2015
+------+----------+-----------+-------+-----------+-----------+-----------+-----------+-----------+-----+--------+
| PID  |   NAME   |    HOST   |  PORT |   STATE   |    SENT   |  WRITTTEN |  FLUSHED  |  REPLAYED | PRI |  MODE  |
+------+----------+-----------+-------+-----------+-----------+-----------+-----------+-----------+-----+--------+
|      |          |           |       | local     | 0/608CD68 | 0/608CAC0 |           |           |     | master |
| 3323 | replica1 | 127.0.0.1 | 55580 | streaming | 0/608CAC0 | 0/608CAC0 | 0/608C7D8 | 0/608C7D8 |   0 | async  |
| 3367 | replica2 | 127.0.0.1 | 55589 | streaming | 0/608CAC0 | 0/608CAC0 | 0/608C7D8 | 0/608C7D8 |   0 | async  |
+------+----------+-----------+-------+-----------+-----------+-----------+-----------+-----------+-----+--------+

$

Connect to the default port (5432) of localhost, the statistical information is displayed every five seconds continuously. CTRL-C to end this.

$ pt-replication-stat -h localhost 5
Sat Mar 28 21:45:23 JST 2015
+------+----------+-----------+-------+-----------+-----------+-----------+-----------+-----------+-----+--------+
| PID  |   NAME   |    HOST   |  PORT |   STATE   |    SENT   |  WRITTTEN |  FLUSHED  |  REPLAYED | PRI |  MODE  |
+------+----------+-----------+-------+-----------+-----------+-----------+-----------+-----------+-----+--------+
|      |          |           |       | local     | 0/5F30398 | 0/5F300B0 |           |           |     | master |
| 3323 | replica1 | 127.0.0.1 | 55580 | streaming | 0/5F300B0 | 0/5F300B0 | 0/5F300B0 | 0/5F2FE48 |   0 | async  |
| 3367 | replica2 | 127.0.0.1 | 55589 | streaming | 0/5F300B0 | 0/5F300B0 | 0/5F2FE48 | 0/5F2FE48 |   0 | async  |
+------+----------+-----------+-------+-----------+-----------+-----------+-----------+-----------+-----+--------+

^C[2015-03-28 21:45:25] INFO: Terminated.
$

pt-session-profiler

Summary

Displays the query and performance information by detecting the PostgreSQL session from capturing the network traffic.

For executing tcpdump requires root privileges.

Usage

pt-session-profiler [option...]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-i [INTERFACE]
-T, --threshold=MILLISECONDS
--help

-h, --host Specifies the IP address or PostgreSQL server name of the PostgreSQL session to parse. If not specified, to set the value of PGHOST. Every server name or IP address packet will be analyzed if no other value has been defined in PGHOST.

-p, --port Specifies the port number of the PostgreSQL session to parse. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-i Specifies the network interface to capture. All network interfaces (any) will be used if it is omitted.

-T, --threshold Specifies the threshold of execution time in the displayed query. The unit is milliseconds. The default value is 1000.

Output Items

sess Unique session strings. (Different values for each session. The source IP address and the port number and the destination IP address and the port number and use the 12 characters of the hash string MD5.)
time Query execution time
query Run the query string

Examples

It monitors the ones destined to port 5432 of the TCP packet that passes through all of the network interface. CTRL-C to end this.

$ sudo pt-session-profiler -T 500
[2015-03-29 15:07:22] INFO: Threshold: 500 ms
[2015-03-29 15:07:22] INFO: tcpdump -l -i any -s 0 -X -p tcp port 5432
[2015-03-29 15:07:36] INFO: sess:e27f20dae08f, time:0:00:00.557728, query:UPDATE pgbench_tellers SET tbalance = tbalance + 2084 WHERE tid = 23;
[2015-03-29 15:07:36] INFO: sess:b3674d7bbea0, time:0:00:00.980950, query:INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (32, 5, 255511, 2695, CURRENT_TIMESTAMP);
[2015-03-29 15:07:45] INFO: sess:1c32286cab7a, time:0:00:01.115904, query:SELECT abalance FROM pgbench_accounts WHERE aid = 161999;
[2015-03-29 15:07:45] INFO: sess:33f8c268624c, time:0:00:00.526850, query:UPDATE pgbench_accounts SET abalance = abalance + 3877 WHERE aid = 326415;
[2015-03-29 15:07:46] INFO: sess:b370afd07dcf, time:0:00:00.719780, query:SELECT abalance FROM pgbench_accounts WHERE aid = 852680;
[2015-03-29 15:07:46] INFO: sess:0f04724051ad, time:0:00:00.543609, query:BEGIN;
^C[2015-03-29 15:07:51] INFO: Terminated.
$

pt-set-tablespace

Summary

Change at once the tablesapace of the index associated with the specified table.

Usage

pt-set-tablespace [option...] [tablespace]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
-o, --owner=STRING
-n, --schema=STRING
-t, --table=STRING
-l, --list
--apply
--help

-h, --host Specifies the connecting PostgreSQL database server name or its IP address. If not specified, to set the value of PGHOST. localhost will be used if no value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PosgtgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used if no value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. It connects to the database same as the database username if no value has been defined in PGDATABASE.

-o, --owner It applies only table that is owning user that matches the specified name. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

-n, --schema It applies only table that is a schema that matches the specified name. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

-t, --table It applies only table that matches the specified name. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

--apply The change of tablespace, it is actually reflected in the database.

-l, --list Displays the tablespace information.

-o (or --owner ), -n (or --schema ), -t (or --table ), if specified the options at the same time, the only table that matches the conditions.

If it fails to move one or more tables, returned an exit code of 1. If it’s successful the movement of all of the files, returned a “0”.

Output Items

-l, --list Items that are displayed in the options.

OID Object ID of tablespace
OWNER Owner name of tablespace
TABLESPACE Tablespace name
LOCATION Path of the directory for tablespace
USE% Disk usage of the partition for tablespace
AVAIL Free space of the partition for tablespace

Other output items are shown below.

Dry-run mode Free space of the partition for tablespace.  
Applying ALTER TABLE/INDEX Acutually running the ALTER TABLE/INDEX statement it will change tablespace of the index.
X tables/indexes moved. Y failed. X of tables/indexes is moved successfully Y failed to move.

Examples

Displays list of tablespace that exist in the PostgreSQL instance. Gets the used area of the each partition, and displays as a list together.

$ pt-set-tablespace --list
+--------+----------+------------+---------------------------+------+-------+
|  OID   |  OWNER   | TABLESPACE |          LOCATION         | USE% | AVAIL |
+--------+----------+------------+---------------------------+------+-------+
|   1663 | postgres | pg_default |                           |      |       |
|   1664 | postgres | pg_global  |                           |      |       |
| 121263 | postgres | hddspc2    | /disk/disk2/pgsql         | 85%  | 80G   |
|  16818 | postgres | ssdspc1    | /disk/disk1/tblspc1       | 67%  | 127G  |
| 305242 | postgres | ssdspc2    | /disk/disk3/pgsql/ssdspc2 | 98%  | 13G   |
+--------+----------+------------+---------------------------+------+-------+
$

All orders tables and the indexes that was created on orders table in dbt3 database, it displays ALTER TABLE and ALTER INDEX statement for to move the tablesapace. (Does not actually move)

$ pt-set-tablespace -d dbt3 --table orders ssdspc1
[2015-04-29 17:35:24] INFO: Dry-run mode:
[2015-04-29 17:35:24] INFO: ALTER TABLE "public"."orders" SET TABLESPACE "ssdspc1";
[2015-04-29 17:35:24] INFO: ALTER INDEX "public"."pk_orders" SET TABLESPACE "ssdspc1";
[2015-04-29 17:35:24] INFO: ALTER INDEX "public"."i_o_orderdate" SET TABLESPACE "ssdspc1";
[2015-04-29 17:35:24] INFO: ALTER INDEX "public"."i_o_custkey" SET TABLESPACE "ssdspc1";
$

All orders tables and the indexes that were created on orders table in dbt3 database, to move to the ssdspc1 tablespace.

$ pt-set-tablespace -d dbt3 --table orders --apply ssdspc1
[2015-04-29 17:37:06] INFO: Applying ALTER TABLE/INDEX...
[2015-04-29 17:37:08] INFO: 4 tables/indexes moved. 0 failed.
$

All tables in the dbt3 schema and all indexes, to move to the ssdspc1 tablespace.

$ pt-set-tablespace --schema dbt3 --apply ssdspc1
[2015-04-29 17:38:39] INFO: Applying ALTER TABLE/INDEX...
[2015-04-29 17:38:57] INFO: 31 tables/indexes moved. 0 failed.
$

pt-show-locks

Summary

Shows waiting and blocking lock information and those dependencies with the associated queries.

Usage

pt-show-locks [option...]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
--help

-h, --host Specifies the connecting the PostgreSQL database server name or its IP address. If not specified, to set the value of PGHOST. localhost will be used if no other value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PostgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used If no other value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. Database name as database user name will be used if no other value has been defined in PGDATABASE.

Output Items

BLOCKER Blocking process id when the lock is waiting.  
PID Backend process id.  
LOCKTYPE Lock type.  
MODE Lock mode.  
GRANTED t on granted f on waiting.
QUERY Query string being executed.  

Examples

Following example shows the lock status with connecting mydb database with postgres user.

$ pg-show-locks -d mydb -U postgres
+---------+------+----------+---------------------+---------+--------------------------------------------+
| BLOCKER | PID  | LOCKTYPE |         MODE        | GRANTED |                   QUERY                    |
+---------+------+----------+---------------------+---------+--------------------------------------------+
|         | 2076 | relation | AccessExclusiveLock | t       | lock TABLE pgbench_accounts;               |
|    2076 | 2049 | relation | RowShareLock        | f       | select * from pgbench_accounts for update; |
|    2076 | 2436 | relation | RowExclusiveLock    | f       | delete from pgbench_accounts;              |
+---------+------+----------+---------------------+---------+--------------------------------------------+
$

pt-snap-statements

Summary

Displays by calculating the difference between the statistics of two difference time of SQL statements.

If it has specified options value, it can be displayed to sort of each specific items.

Make sure you have pg_stat_statements of contrib module.

And also make sure to enable track_io_timing option.

Usage

pt-snap-statements [option...] [interval]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
-s, --sort=KEY
-l
-t, --top=NUMBER
-R, --reset
--help

-h, --host Specifies the connecting the PostgreSQL database server name or its IP address. If not specified, to set the value of PGHOST.``localhost`` will be used if no other value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting the PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PostgreSQL database. If not specified, to set the value of PGUSER. Vars USER will be used if no other value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. Database name as database user name will be used if no other value has been defined in PGDATABASE.

-s (not implemented) Specifies the sort item. KEY can take one of following values: CALLS, T_TIME, ROWS, B_HIT, B_READ, B_DIRT, B_WRTN, R_TIME, W_TIME

-l (not implemented) Displays with detailed every block classification (Shared buffer, Local buffer, Temporary buffer). If not specified, it is displayed the total number of shared buffer, local buffer and temporary buffer.

-t, --top Specifies the number of displayed queries. If not specified, it is displayed the all queries.

-R, --reset It initializes the statistical information of``pg_stat_statements`` view.

Output Items

USER Username of query run
DBNAME Database name of query run
QUERYID Query ID of query run (Hexadecimal)
QUERY Query performed (Display up to 30 characters)
CALLS Number of times of query run
T_TIME Total number of times of query run (Millisecond)
ROWS Total number of rows that has received the obtain or influence
B_HIT Total number of blocks read from the buffer at the time blocks read
B_READ Total number of blocks read from the disk at the time blocks read
B_DIRT Total number of pages that page has been updated by the query
B_WRTN Total number of blocks that are written to disk by the query
R_TIME Total time of block read from the disk (Millisecond) ( Make sure to enable track_io_timing parameter )
W_TIME Total time of block write to the disk (Millisecond) ( Make sure to enable track_io_timing parameter )

Examples

Connects to the postgres database, and the SQL statements executed in 5 seconds is sorted in descending order of total execution time (T_TIME), and displays all.

$ pt-snap-statements -d postgres 5
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
|  USER |  DBNAME  | QUERYID  |             QUERY              | CALLS | T_TIME | ROWS | B_HIT | B_READ | B_DIRT | B_WRTN | R_TIME | W_TIME |
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
| snaga | postgres | 80053daf | UPDATE pgbench_branches SET bb |   677 |  12007 |  677 |  9160 |      1 |      1 |      0 | 0.0    | 0.0    |
| snaga | postgres | 1675159e | UPDATE pgbench_tellers SET tba |   681 |   7648 |  681 |  3403 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | ec088219 | UPDATE pgbench_accounts SET ab |   684 |    530 |  684 |  2289 |    585 |    568 |      0 | 125.9  | 0.0    |
| snaga | postgres | 198383d  | SELECT abalance FROM pgbench_a |   682 |     73 |  682 |  2080 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | da8cc6f  | INSERT INTO pgbench_history (t |   676 |     34 |  676 |   704 |     12 |     10 |      0 | 0.0    | 0.0    |
| snaga | postgres | d4e6bf94 | BEGIN;                         |   684 |      4 |    0 |     0 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | a81672e  | END;                           |   671 |      3 |    0 |     0 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | 8caa574  | select count(*) from pgbench_b |     1 |      0 |    1 |     4 |      0 |      0 |      0 | 0.0    | 0.0    |
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
$

User snaga connects to the postgres database of the PostgreSQL server running on the port 5433 of host 192.168.1.101, and the SQL statements executed in 5 seconds is sorted in descending order of total execution time (T_TIME), and the top 5 are displayed.

$ pt-snap-statements --host 192.168.1.101 -p 5433 -U snaga -d postgres -t 5 5
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
|  USER |  DBNAME  | QUERYID  |             QUERY              | CALLS | T_TIME | ROWS | B_HIT | B_READ | B_DIRT | B_WRTN | R_TIME | W_TIME |
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
| snaga | postgres | 80053daf | UPDATE pgbench_branches SET bb |   503 |   9953 |  503 |  8430 |     14 |      7 |      0 | 0.6    | 0.0    |
| snaga | postgres | 1675159e | UPDATE pgbench_tellers SET tba |   508 |   6483 |  508 |  2551 |     10 |      9 |      0 | 0.3    | 0.0    |
| snaga | postgres | ec088219 | UPDATE pgbench_accounts SET ab |   511 |    560 |  511 |  1424 |    698 |    477 |      7 | 91.0   | 12.1   |
| snaga | postgres | 198383d  | SELECT abalance FROM pgbench_a |   511 |     93 |  511 |  1550 |      0 |      0 |      0 | 0.0    | 0.0    |
| snaga | postgres | da8cc6f  | INSERT INTO pgbench_history (t |   503 |     20 |  503 |   530 |     13 |     11 |      0 | 0.1    | 0.0    |
+-------+----------+----------+--------------------------------+-------+--------+------+-------+--------+--------+--------+--------+--------+
$

pt-stat-snapshot

Summary

Get a snapshot of statistical information, save, and manage.

Usage

pt-stat-snapshot [option...] install
pt-stat-snapshot [option...] uninstall
pt-stat-snapshot [option...] create [level]
pt-stat-snapshot [option...] list
pt-stat-snapshot [option...] delete [sid]
pt-stat-snapshot [option...] export [file]
pt-stat-snapshot [option...] import [file]

Commands

install Creates a schema and tables and functions of the package.
uninstall Drops a schema and tables and functions of the package.
create [level] Takes a snapshot of the database statistics. [level] can be 1 or 2 or 4.
list Shows a list of stored snapshots.
delete [sid] Deletes a snapshot specified by snapshot id. Multiple snapshot ids can be specified by range as M:N.
export [file] Exports all snapshot data into the specified file.
import [file] Imports snapshot data from the specified file.

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
--help

-h, --host Specifies the connecting the PostgreSQL database server name or its IP address. or its IP address. If not specified, to set the value of PGHOST. localhost will be used if no other value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the username of the PostgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used If no other value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. Database name as database username will be used if no other value has been defined in PGDATABASE.

Output Items

list command shows following items.

SID Snapshot ID (Assigned to every snapshot. Monotonic increase.)
TIMESTAMP Timestamp of the snapshot taken.
LEVEL Snapshot level.

Examples

It installs a schema, tables and functions of the package to testdb database.

$ pt-stat-snapshot -h 127.0.0.1 -U postgres -d testdb install
[2015-03-31 17:21:37] INFO: Succeeded to install pgperf snapshot.
$

It takes a snapshot with the snapshot level 4.

$ pt-stat-snapshot -h 127.0.0.1 -U postgres -d testdb create 4
[2015-03-31 17:21:47] INFO: Succeeded to take a snapshot.
$

It shows a list of the snapshots.

$ pt-stat-snapshot -h 127.0.0.1 -U postgres -d testdb list
+-----+---------------------+-------+
| SID |      TIMESTAMP      | LEVEL |
+-----+---------------------+-------+
|   0 | 2015-03-31 17:21:47 |     1 |
+-----+---------------------+-------+
$

It uninstalls the schema, tables and functions of the package from testdb database.

$ pt-stat-snapshot -h 127.0.0.1 -U postgres -d testdb uninstall
[2015-03-31 17:21:59] INFO: Succeeded to uninstall pgperf snapshot.
$

About pgperf-snapshot Module

pt-stat-snapshot command depends on another module internally, previously known as the pgperf-snapshot.

For more details about the pgperf-snapshot module, please refer to the following documents.

PgPerf Package Overview

What is PgPerf package?

The PgPerf package is a collection of SQL functions and tables to take snapshots of the performance statistics in PostgreSQL, and store them into dedicated snapshot tables.

“Performance statistics” mentioned here is following statistics available in PostgreSQL:

  • Database object access statistics in system views. (c.f. pg_stat_database)
  • Optimizer statistics in pg_statistic system table.
  • Other system statistics which could by taken by system functions. (c.f. pg_current_xlog_location())
  • Fragmentation statistics which could be taken by system functions. (c.f. pgstattuple())

By taking those performance statistics and storing them, DBA can understand what’s actually going on inside the PostgreSQL databases, and also it would help DBA predict the future trend in terms of the performance.

Advantages of PgPerf package

The PgPerf package has following advantages:

  • It does not depend the platform where PostgreSQL is running. Provided scripts (SQL, PL/pgSQL) should work whereever you want.
  • It’s easy to take and store several performance metrics, and it’s also easy to analyze them later whatever you want.
  • It’s easy to deploy (and undeploy) without changing any configuration in the production environment.
Supported Platform

The PgPerf package would work with following platforms.

  • PostgreSQL 9.0, 9,1, 9.2, 9.3, 9.4

It does not depend on the operating system.

PgPerf Components

The PgPerf package consists of following components:

  • Dedicated schema to be used by the package.
  • SQL functions to take performance snapshots.
  • Snapshot tables to store performace snapshots.
  • Some shell scripts for convenience.

Introduction to PgPerf package

This chapter is intended to give a brief introduction to the PgPerf snapshot package.

Installing PgPerf package

To install the PgPerf package, pgperf_snapshot_install.sql script needs to be executed on a database, where the DBA wants to take performance snapshots, to create pgperf schema, snapshot functions, and snapshot tables.

psql -f pgperf_snapshot_install<VERSION>.sql <DBNAME>
Uninstalling PgPerf package

To uninstall the PgPerf package, pgperf_snapshot_uninstall.sql script needs to be executed on the database where the PgPerf package has been installed.

psql -f pgperf_snapshot_uninstall.sql <DBNAME>

Once pgperf_snapshot_uninstall.sql is executed on a database, it would drops``pgperf`` schema, snapshot functions and snapshot tables installed in the database.

Taking a performance snapshot with using PgPerf package

In order to take a performance snapshot, a SQL function pgperf.create_snapshot() needs to be called.

Once pgperf.create_snapshot() function is called with specifying a snapshot level, a snapshot of the several performance statistics would be taken, and a snapshot id associated with the performance snaphsot would be returned.

In the following example, a snapshot level is specified as 4, and then, 1005 is returned as the snapshot id.

postgres=# SELECT pgperf.create_snapshot(4);
 create_snapshot
-----------------
            1005
(1 row)

postgres=#
Showing a list for snapshots

pgperf.snapshot table would hold a list of the snapshots.

By querying the table, snapshot ids and those timestamps can be obtained as following.

postgres=# SELECT * FROM pgperf.snapshot;
 sid |             ts             | level
-----+----------------------------+-------
   0 | 2015-04-11 19:11:24.04428  |     1
   1 | 2015-04-11 19:11:24.060965 |     2
   2 | 2015-04-11 19:11:24.110034 |     4
(3 rows)

postgres=#
Deleting a snapshot

To delete a snapshot, pgperf.delete_snapshot() function with specifying a snapshot id can be used.

In the following example, a snapshot associated with snaphsot id 2 is going to be dropped.

postgres=# SELECT pgperf.delete_snapshot(2);
 delete_snapshot
-----------------
 t
(1 row)

postgres=# SELECT * FROM pgperf.snapshot;
 sid |             ts             | level
-----+----------------------------+-------
   0 | 2015-04-11 19:11:24.04428  |     1
   1 | 2015-04-11 19:11:24.060965 |     2
(2 rows)

postgres=#
Taking snapshots as a routine

To take a snapshot as a routine, pgperf.create_snapshot() function needs to be called with using a cron-like tool.

A shell script,``get_snapshot.sh``, which is contained in the package, executes pgperf.create_snapshot() function on the specified database or all databases, which can be connected by the script (and not a template database).

In the following example, the script is taking a snapshot for postgres database.

$ ./get_snapshot.sh postgres

On the other hand, the script is taking a snapshot for every database in the following example.

$ ./get_snapshot.sh

By setting a crontab, get_snapshot.sh can be called to take snapshots periodically.

A crontab shown in below is intended to take performance snapshots of all the databases with calling get_snapshot.sh script in every 10 minutes.

0-59/10 * * * *       /path/to/get_snapshot.sh > /dev/null 2>&1
Purging snapshots

pgperf.purge_snapshots() function can be used to purge older snapshots at once.

In the following example, snapshots, which are older than 1 week or more, are going to be purged.

postgres=# SELECT sid,ts FROM pgperf.snapshot ORDER BY ts LIMIT 1;
 sid |             ts
-----+----------------------------
   2 | 2012-10-21 18:20:01.238885
(1 row)

postgres=# SELECT now(),pgperf.purge_snapshots('1 weeks');
              now              | purge_snapshots
-------------------------------+-----------------
 2012-10-29 14:57:04.092243+09 |             121
(1 row)

postgres=# SELECT sid,ts FROM pgperf.snapshot ORDER BY ts LIMIT 1;
 sid |            ts
-----+--------------------------
 123 | 2012-10-22 15:00:01.8397
(1 row)

postgres=#

Snapshot Functions

This chapter is intended to give detailed information about several snapshot functions, provided in the PgPerf package, to deal with performance snaphsots.

Snapshot Function List
Function Name Description
pgperf.create_snapshot(level) Takes a new snapshot.
pgperf.delete_snapshot(snapid) Drops a snapshot specified by the snapshot id.
pgperf.purge_snapshots(interval) Drops snapshots older than the specified period.
pgperf.get_interval(snapid1, snapid2) Gets an interval (in second) between two snapshots.
pgperf.create_snapshot() Function
Description

pgperf.create_snapshot() function takes a snapshot of the performance statistics, which can be obtained in the PostgreSQL database, and stores them in the snapshot tables.

Declaration
integer pgperf.create_snapshot(integer level)
Parameters
Name Type Description
level integer Snapshot level to be taken.

Unfortunately, heavy performance impact could sometimes be generated while taking a snapshot for all the performance statistics available in the database.

To avoid that, pgperf.create_snapshot() function allows DBA to obtain a snapshot with less performance impact by specifying the snapshot level. Then, DBA can avoid taking a snapshot which generates heavy performance impact so frequently.

Snapshot Level Snapshot Contents
1

Obtains a snapshot of the basic access statistics and the session statistics.

pg_stat_database, pg_database_size()

pg_stat_user_tables, pg_statio_user_tables

pg_stat_user_indexes, pg_statio_user_indexes

pg_relation_size(), pg_total_relation_size()

pg_current_xlog_location(), pg_current_xlog_insert_location()

pg_stat_bgwriter

pg_stat_activity, pg_locks, pg_stat_statements

2

In addition to the level 1, obtains a snapshot of the optimizer statistics.

pg_statistic

3 Not used.
4

In addition to the level 2, obtains a snapshot of the table/index fragmentation statistics.

pgstattuple(), pgstatindex()

5 Not used.
pgperf.delete_snapshot() Function
Description

pgperf.delete_snapshot() function deletes a snapshot of the performance statistics specified by the snapshot id.

Declaration
integer pgperf.delete_snapshot(integer snapid);
Parameters
Name Type Description
snapid integer A snapshot id accociated with the snapshot to be deleted.
pgperf.purge_snapshots() Function
Description

pgperf.purge_snapshots() function purges older performance snapshots at once.

Declaration
integer pgperf.purge_snapshots(interval period);
Parameters
Name Type Description
period interval A period of the snapshots to be kept in the snapshot tables.

This function drops snapshots older than the period (interval) specified by the parameter period .

See the PostgreSQL manual for more details about how to express an interval value.

pgperf.get_interval() Function
Description

pgperf.get_interval() function obtains an interval between two snapshots.

This function is intended to give some convenience to convert an obtained value into a ‘per-second’ value, particularly in SQL scripts.

Declaration
integer pgperf.get_interval(integer snapid1, integer snapid2)
Parameters
Name Type Description
snapid1 integer A snapshot id at the start point.
snapid2 integer A snapshot id at the end point.

Snapshot Tables

This chapter is intended to give detailed information about several snapshot tables, where the performance snapshot to be stored.

Snapshot Table List

Performance snapshot is going to be stored in the following tables associated with each performance statistics available in PostgreSQL.

Table Name Description Note
pgperf.snapshot Stores snapshot id and timestamp of the performance snapshot.  
pgperf.snapshot_pg_stat_database Stores a snapshot of the pg_stat_database system view.  
pgperf.snapshot_pg_database_size Stores a snapshot of the database size.  
pgperf.snapshot_pg_stat_user_tables Stores a snapshot of the pg_stat_user_tables system view.  
pgperf.snapshot_pg_statio_user_tables Stores a snapshot of the pg_statio_user_tables system view.  
pgperf.snapshot_pg_stat_user_indexes Stores a snapshot of the pg_stat_user_indexes system view.  
pgperf.snapshot_pg_statio_user_indexes Stores a snapshot of the pg_statio_user_indexes system view.  
pgperf.snapshot_pg_statio_user_sequences Stores a snapshot of the pg_statio_user_sequences system view.  
pgperf.snapshot_pg_stat_user_functions Stores a snapshot of the pg_stat_user_functions system view.  
pgperf.snapshot_pg_relation_size Stores a snaphsot of the table and index size.  
pgperf.snapshot_pg_current_xlog Stores a snapshot of the current xlog location and the current insert location.  
pgperf.snapshot_pg_stat_bgwriter Stores a snapshot of the pg_stat_bgwriter system view.  
pgperf.snapshot_pg_stat_activity Stores a snapshot of the pg_stat_activity system view.  
pgperf.snapshot_pg_locks Stores a snapshot of the pg_locks system view.  
pgperf.snapshot_pg_statistic Stores a snapshot of the pg_statistics system table.  
pgperf.snapshot_pg_stat_statements Stores a snapshot of the pg_stat_statements view. 8.4 or later
pgperf.snapshot_pgstattuple Stores a snapshot of the result of pgstattuple function.  
pgperf.snapshot_pgstatindex Stores a snapshot of the result of pgstatindex function.  
pgperf.snapshot Table

This table stores snapshot id and timestamp of each snapshot taken by the snapshot function.

Column Name Type Source Note
sid integer Snapshot ID Monotone increasing
ts timestamp Timestamp of the snapshot  
level integer Snapshot level  
pgperf.snapshot_pg_stat_database Table

This table stores snapshots of the pg_stat_database system view which contains the database access statistics.

Column Name Type Source Note
sid integer Snapshot ID  
datid oid pg_stat_database.datid  
datname name pg_stat_database.datname  
numbackends integer pg_stat_database.numbackends  
xact_commit bigint pg_stat_database.xact_commit  
xact_rollback bigint pg_stat_database.xact_rollback  
blks_read bigint pg_stat_database.blks_read  
blks_hit bigint pg_stat_database.blks_hit  
tup_returned bigint pg_stat_database.tup_returned  
tup_fetched bigint pg_stat_database.tup_fetched  
tup_inserted bigint pg_stat_database.tup_inserted  
tup_updated bigint pg_stat_database.tup_updated  
tup_deleted bigint pg_stat_database.tup_deleted  
conflicts bigint pg_stat_database.conflicts 9.1 or later
stats_reset timestampz pg_stat_database.stats_reset 9.1 or later
pgperf.snapshot_pg_database_size Table

This table stores snapshots of result of the pg_database_size() function which gets database size.

Column Name Type Source Note
sid integer Snapshot ID  
datname name pg_database.datname  
pg_database_size bigint pg_database_size()  
pgperf.snapshot_pg_stat_user_tables Table

This table stores snapshots of the pg_stat_user_tables system view which contains the table access statistics.

Column Name Type Source Note
sid integer Snapshot ID  
relid oid pg_stat_user_tables.relid  
schemaname name pg_stat_user_tables.schemaname  
relname name pg_stat_user_tables.relname  
seq_scan bigint pg_stat_user_tables.seq_scan  
seq_tup_read bigint pg_stat_user_tables.seq_tup_read  
idx_scan bigint pg_stat_user_tables.idx_scan  
idx_tup_fetch bigint pg_stat_user_tables.idx_tup_fetch  
n_tup_ins bigint pg_stat_user_tables.n_tup_ins  
n_tup_upd bigint pg_stat_user_tables.n_tup_upd  
n_tup_del bigint pg_stat_user_tables.n_tup_del  
n_tup_hot_upd bigint pg_stat_user_tables.n_tup_hot_upd  
n_live_tup bigint pg_stat_user_tables.n_live_tup  
n_dead_tup bigint pg_stat_user_tables.n_dead_tup  
last_vacuum timestampz pg_stat_user_tables.last_vacuum  
last_autovacuum timestampz pg_stat_user_tables.last_autovacuum  
last_analyze timestampz pg_stat_user_tables.last_analyze  
last_autoanalyze timestampz pg_stat_user_tables.last_autoanalyze  
vacuum_count bigint pg_stat_user_tables.vacuum_count 9.1 or later
autovacuum_count bigint pg_stat_user_tables.autovacuum_count 9.1 or later
analyze_count bigint pg_stat_user_tables.analyze_count 9.1 or later
autoanalyze_count bigint pg_stat_user_tables.autoanalyze_count 9.1 or later
pgperf.snapshot_pg_statio_user_tables Table

This table stores snapshots of the pg_statio_user_tables system view which contains the table access statistics.

Column Name Type Source Note
sid integer Snapshot ID  
relid oid pg_statio_user_tables.relid  
schemaname name pg_statio_user_tables.schemaname  
relname name pg_statio_user_tables.relname  
heap_blks_read bigint pg_statio_user_tables.heap_blks_read  
heap_blks_hit bigint pg_statio_user_tables.heap_blks_hit  
idx_blks_read bigint pg_statio_user_tables.idx_blks_read  
idx_blks_hit bigint pg_statio_user_tables.idx_blks_hit  
toast_blks_read bigint pg_statio_user_tables.toast_blks_read  
toast_blks_hit bigint pg_statio_user_tables.toast_blks_hit  
tidx_blks_read bigint pg_statio_user_tables.tidx_blks_read  
tidx_blks_hit bigint pg_statio_user_tables.tidx_blks_hit  
pgperf.snapshot_pg_stat_user_indexes Table

This table stores snapshots of the pg_stat_user_indexes system view which contains the index access statistics.

Column Name Type Source Note
sid integer Snapshot ID  
relid oid pg_stat_user_indexes.relid  
indexrelid oid pg_stat_user_indexes.indexrelid  
schemaname name pg_stat_user_indexes.schemaname  
relname name pg_stat_user_indexes.relname  
indexrelname name pg_stat_user_indexes.indexrelname  
idx_scan bigint pg_stat_user_indexes.idx_scan  
idx_tup_read bigint pg_stat_user_indexes.idx_tup_read  
idx_tup_fetch bigint pg_stat_user_indexes.idx_tup_fetch  
pgperf.snapshot_pg_statio_user_indexes Table

This table stores snapshots of the pg_statio_user_indexes system view which contains the index access statistics.

Column Name Type Source Note
sid integer Snapshot ID  
relid oid pg_statio_user_indexes.relid  
indexrelid oid pg_statio_user_indexes.indexrelid  
schemaname name pg_statio_user_indexes.schemaname  
relname name pg_statio_user_indexes.relname  
indexrelname name pg_statio_user_indexes.indexrelname  
idx_blks_read bigint pg_statio_user_indexes.idx_blks_read  
idx_blks_hit bigint pg_statio_user_indexes.idx_blks_hit  
pgperf.snapshot_pg_statio_user_sequences Table

This table stores snapshots of the pg_statio_user_sequences system view which contains the sequence access statistics.

Column Name Type Source Note
sid integer Snapshot ID  
relid oid pg_statio_user_sequences.relid  
schemaname name pg_statio_user_sequences.schemaname  
relname name pg_statio_user_sequences.relname  
blks_read int8 pg_statio_user_sequences.blks_read  
blks_hit int8 pg_statio_user_sequences.blks_hit  
pgperf.snapshot_pg_stat_user_functions Table

This table stores snapshots of the pg_stat_user_functions system view which contains the function access statistics.

Column Name Type Source Note
sid integer Snapshot ID  
funcid oid pg_stat_user_functions.funcid  
schemaname name pg_stat_user_functions.schemaname  
funcname name pg_stat_user_functions.funcname  
calls int8 pg_stat_user_functions.calls  
total_time int8 pg_stat_user_functions.total_time  
self_time int8 pg_stat_user_functions.self_time  
pgperf.snapshot_pg_relation_size Table

This table stores snapshots of the result of pg_relation_size() and pg_total_relation_size() function which gets table and/or index size.

Column Name Type Source Note
sid integer Snapshot ID  
schemaname name pg_stat_user_tables.schemaname, pg_stat_user_indexes.schemaname  
relid oid pg_stat_user_tables.relid, pg_stat_user_indexes.indexrelid  
relname name pg_class.relname  
pg_relation_size bigint pg_relaion_size()  
pg_total_relation_size bigint pg_total_relaion_size() Available only for tables
pgperf.snapshot_pg_current_xlog Table

This table stores snapshots of the result of pg_current_xlog_location() and pg_current_xlog_insert_location() function which gets current WAL locations.

Column Name Type Source Note
sid integer Snaphsot ID  
location text pg_current_xlog_location()  
insert_location text pg_current_xlog_insert_location()  
pgperf.snapshot_pg_stat_bgwriter Table

This table stores snapshots of the pg_stat_bgwriter system view which contains the background writer statistics.

Column Name Type Source Note
sid integer Snapshot ID  
checkpoints_timed bigint pg_stat_bgwriter.checkpoints_timed  
checkpoints_req bigint pg_stat_bgwriter.checkpoints_req  
checkpoint_write_time double precision pg_stat_bgwriter.checkpoint_write_time 9.2 or later
checkpoint_sync_time double precision pg_stat_bgwriter.checkpoint_sync_time 9.2 or later
buffers_checkpoint bigint pg_stat_bgwriter.buffers_checkpoint  
buffers_clean bigint pg_stat_bgwriter.buffers_clean  
maxwritten_clean bigint pg_stat_bgwriter.maxwritten_clean  
buffers_backend bigint pg_stat_bgwriter.buffers_backend  
buffers_backend_fsync bigint pg_stat_bgwriter.buffers_backend_fsync 9.1 or later
buffers_alloc bigint pg_stat_bgwriter.buffers_alloc  
stats_reset timestampz pg_stat_bgwriter.stats_reset 9.1 or later
pgperf.snapshot_pg_stat_activity Table

This table stores snapshots of the pg_stat_activity system view which contains the session information.

Column Name Type Source Note
sid integer Snapshot ID  
datid oid pg_stat_activity.datid  
datname name pg_stat_activity.datname  
procpid int4 pg_stat_activity.procpid 9.1 or before
pid int4 pg_stat_activity.pid 9.2 or later
usesysid oid pg_stat_activity.usesysid  
usename name pg_stat_activity.usename  
application_name text pg_stat_activity.application_name 9.0 or later
client_addr inet pg_stat_activity.client_addr  
client_hostname text pg_stat_activity.client_hostname 9.1 or later
client_port int4 pg_stat_activity.client_port  
backend_start timestamptz pg_stat_activity.backend_start  
xact_start timestamptz pg_stat_activity.xact_start  
query_start timestamptz pg_stat_activity.query_start  
state_change timestamptz pg_stat_activity.state_change 9.2 or later
waiting bool pg_stat_activity.waiting  
state text pg_stat_activity.state 9.2 or later
current_query text pg_stat_activity.current_query 9.1 or before
query text pg_stat_activity.query 9.2 or later
pgperf.snapshot_pg_locks Table

This table stores snapshots of the pg_locks system view which contains the lock information.

Column Name Type Source Note
sid integer Snapshot ID  
locktype text pg_locks.locktype  
database oid pg_locks.database  
relation oid pg_locks.relation  
page int4 pg_locks.page  
tuple int2 pg_locks.tuple  
virtualxid text pg_locks.virtualxid  
transactionid xid pg_locks.transactionid  
classid oid pg_locks.classid  
objid oid pg_locks.objid  
objsubid int2 pg_locks.objsubid  
virtualtransaction text pg_locks.virtualtransaction  
pid int4 pg_locks.pid  
mode text pg_locks.mode  
granted bool pg_locks.granted  
fastpath bool pg_locks.fastpath 9.2 or later
pgperf.snapshot_pg_statistic Table

This table stores snapshots of the pg_statistic system table which contains the optimizer statistics.

Column Name Type Source Note
sid integer Snapshot ID  
starelid oid pg_statistic.starelid  
starelname name pg_class.relname  
staattnum smallint pg_statistic.staattnum  
staattname name pg_attribute.attname  
stainherit boolean pg_statistic.stainherit 9.0 or later
stanullfrac real pg_statistic.stanullfrac  
stawidth integer pg_statistic.stawidth  
stadistinct real pg_statistic.stadistinct  
stakind1 smallint pg_statistic.stakind1  
stakind2 smallint pg_statistic.stakind2  
stakind3 smallint pg_statistic.stakind3  
stakind4 smallint pg_statistic.stakind4  
stakind5 smallint pg_statistic.stakind5 9.2 or later
staop1 oid pg_statistic.staop1  
staop2 oid pg_statistic.staop2  
staop3 oid pg_statistic.staop3  
staop4 oid pg_statistic.staop4  
staop5 oid pg_statistic.staop5 9.2 or later
stanumbers1 real[] pg_statistic.stanumbers1  
stanumbers2 real[] pg_statistic.stanumbers2  
stanumbers3 real[] pg_statistic.stanumbers3  
stanumbers4 real[] pg_statistic.stanumbers4  
stanumbers5 real[] pg_statistic.stanumbers5 9.2 or later
stavalues1 text pg_statistic.stavalues1  
stavalues2 text pg_statistic.stavalues2  
stavalues3 text pg_statistic.stavalues3  
stavalues4 text pg_statistic.stavalues4  
stavalues5 text pg_statistic.stavalues5 9.2 or later
pgperf.snapshot_pg_stat_statements Table

This table stores snapshots of the pg_stat_statements view which contains the session statistics. This table is available only when the pg_stat_statements module has been installed and enabled.

Column Name Type Source Note
sid integer Snapshot ID  
userid oid pg_stat_statements.userid  
dbid oid pg_stat_statements.dbid  
query text pg_stat_statements.query  
calls bigint pg_stat_statements.calls  
total_time double precision pg_stat_statements.total_time  
rows bigint pg_stat_statements.rows  
shared_blks_hit bigint pg_stat_statements.shared_blks_hit  
shared_blks_read bigint pg_stat_statements.shared_blks_read  
shared_blks_dirtied bigint pg_stat_statements.shared_blks_dirtied 9.2 or later
shared_blks_written bigint pg_stat_statements.shared_blks_written  
local_blks_hit bigint pg_stat_statements.local_blks_hit  
local_blks_read bigint pg_stat_statements.local_blks_read  
local_blks_dirtied bigint pg_stat_statements.local_blks_dirtied 9.2 or later
local_blks_written bigint pg_stat_statements.local_blks_written  
temp_blks_read bigint pg_stat_statements.temp_blks_read  
temp_blks_written bigint pg_stat_statements.temp_blks_written  
blk_read_time double precision pg_stat_statements.blk_read_time 9.2 or later
blk_write_time double precision pg_stat_statements.blk_write_time 9.2 or later
pgperf.snapshot_pgstattuple Table

This table stores snapshots of the result of the pgstattuple() function which gets the table fragmentation statistics. This table is available only when the pgstattuple module has been installed.

Column Name Type Source Note
sid integer Snapshot ID  
schemaname name pg_stat_user_tables.schemaname  
relname name pg_stat_user_tables.relname  
table_len int8 pgstattuple().table_len  
tuple_count int8 pgstattuple().tuple_count  
tuple_len int8 pgstattuple().tuple_len  
tuple_percent float8 pgstattuple().tuple_percent  
dead_tuple_count int8 pgstattuple().dead_tuple_count  
dead_tuple_len int8 pgstattuple().dead_tuple_len  
dead_tuple_percent float8 pgstattuple().dead_tuple_percent  
free_space int8 pgstattuple().free_space  
free_percent float8 pgstattuple().free_percent  
pgperf.snapshot_pgstatindex Table

This table stores snapshots of the result of the pgstatindex() function which gets the index fragmentation statistics. This table is available only when the pgstattuple module has been installed.

Column Name Type Source Note
sid integer Snapshot ID  
schemaname name pg_stat_user_indexes.schemaname  
relname name pg_stat_user_indexes.relname  
indexrelname name pg_stat_user_indexes.indexrelname  
version int4 pgstatindex().version  
tree_level int4 pgstatindex().tree_level  
index_size int8 pgstatindex().index_size  
root_block_no int8 pgstatindex().root_block_no  
internal_pages int8 pgstatindex().internal_pages  
leaf_pages int8 pgstatindex().leaf_pages  
empty_pages int8 pgstatindex().empty_pages  
deleted_pages int8 pgstatindex().deleted_pages  
avg_leaf_density float8 pgstatindex().avg_leaf_density  
leaf_fragmentation float8 pgstatindex().leaf_fragmentation  

pt-table-usage

Summary

Displays status information about the table

Usage

pt-table-usage [option...]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
-o, --owner=STRING
-n, --schema=STRING
-t, --table=STRING
--help

-h, --host Specifies the connecting PostgreSQL database server name or IP address. If not specified, to set the value of PGHOST. localhost will be used if no value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PosgtgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used if no value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. It connect to the database same as the database username if no value has been defined in PGDATABASE.

-o, --owner Displays only table that is the owner user that matches the specified name. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

-n, --schema Displays only table that is schema that matches the specified name. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

-t, --table Displays only table that matches the specified name. Also accepts regular expression when starting and ending with slashes. (cf. /REGEXP/ )

-d (or --dbname ), -o (or --owner ), -n (or --schema ), -t (or --table ), if specified the options at the same time, only table that matches the conditions.

Output Items

OID Table object ID
OWNER Owner name of table
SCHEMA Schema name that exists in the table
TABLE Table name
BLKS Number of the table block (every 8kb)
SCAN Sequential scan number of executions
T_READ Number of tuple by sequential scan
T_INS Number of inserted tuple
T_UPD Number of updated tuple(include HOT UPDATE)
T_DEL Number of delated tuple
B_READ Number of read table block from disk
B_HIT Number of read table page by shared buffer
VACUUMED Date and time that run VACUUM on the last (VACUUM command or automatic VACUUM)
ANALYZED Date and time that run ANALYZE on the last (ANALYZE command or automatic ANALYZE)
TABLESPACE The tablespace name where the table is placed.

Examples

It connects to the PostgreSQL instance running on the port 5432 of localhost, and to display all of the table usage of dbt3 database.

$ pt-table-usage -d dbt3
+---------+-------+--------+----------+--------+------+----------+---------+-------+-------+--------+---------+----------+---------------------+------------+
|   OID   | OWNER | SCHEMA |  TABLE   |  BLKS  | SCAN |  T_READ  |  T_INS  | T_UPD | T_DEL | B_READ |  B_HIT  | VACUUMED |       ANALYZED      | TABLESPACE |
+---------+-------+--------+----------+--------+------+----------+---------+-------+-------+--------+---------+----------+---------------------+------------+
| 1273410 | snaga | public | customer |   3531 |    5 |   750000 |  150000 |     0 |     0 |   6499 |   29943 |          | 2015-03-08 18:31:41 | ssdspc1    |
| 1273416 | snaga | public | lineitem | 106583 |   12 | 66656465 | 6001215 |     0 |     0 | 240547 | 1340871 |          | 2015-03-08 18:31:42 | ssdspc1    |
| 1273419 | snaga | public | nation   |      1 |    4 |      100 |      25 |     0 |     0 |      1 |       5 |          | 2015-03-08 18:31:42 | ssdspc1    |
| 1273413 | snaga | public | orders   |  25326 |    5 |  7500000 | 1500000 |     0 |     0 |  48612 |  208386 |          | 2015-03-08 18:31:41 | ssdspc1    |
| 1273404 | snaga | public | part     |   4064 |    3 |   600000 |  200000 |     0 |     0 |   6082 |   26558 |          | 2015-03-08 18:31:40 | ssdspc1    |
| 1273407 | snaga | public | partsupp |  17087 |    5 |  4000000 |  800000 |     0 |     0 |  32200 |  148518 |          | 2015-03-08 18:31:41 | ssdspc1    |
| 1273422 | snaga | public | region   |      1 |    3 |       15 |       5 |     0 |     0 |      1 |       4 |          | 2015-03-08 18:31:42 | ssdspc1    |
| 1273401 | snaga | public | supplier |    218 |    4 |    40000 |   10000 |     0 |     0 |    220 |    1802 |          | 2015-03-08 18:31:40 | ssdspc1    |
+---------+-------+--------+----------+--------+------+----------+---------+-------+-------+--------+---------+----------+---------------------+------------+
$

It connects to the PostgreSQL instance running on the port 5432 of localhost, and to display the table usage of the tables which start with pa in the dbt3 database.

$ pt-table-usage -d dbt3 -t /^pa/
+---------+-------+--------+----------+-------+------+---------+--------+-------+-------+--------+--------+----------+---------------------+------------+
|   OID   | OWNER | SCHEMA |  TABLE   |  BLKS | SCAN |  T_READ | T_INS  | T_UPD | T_DEL | B_READ | B_HIT  | VACUUMED |       ANALYZED      | TABLESPACE |
+---------+-------+--------+----------+-------+------+---------+--------+-------+-------+--------+--------+----------+---------------------+------------+
| 1273404 | snaga | public | part     |  4064 |    3 |  600000 | 200000 |     0 |     0 |   6082 |  26558 |          | 2015-03-08 18:31:40 | ssdspc1    |
| 1273407 | snaga | public | partsupp | 17087 |    5 | 4000000 | 800000 |     0 |     0 |  32200 | 148518 |          | 2015-03-08 18:31:41 | ssdspc1    |
+---------+-------+--------+----------+-------+------+---------+--------+-------+-------+--------+--------+----------+---------------------+------------+
$

pt-tablespace-usage

Summary

Displays the usage of table space for earch databases.

For each table space, it will be able to check which database is using how much the capacity.

Usage

pt-tablespace-usage [option...]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-U, --username=USERNAME
-d, --dbname=DBNAME
--help

-h, --host Specifies the connecting PostgreSQL database server name or its IP address. If not specified, to set the value of PGHOST. localhost will be used if no value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-U, --username Specifies the user name of the PosgtgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used if no value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. It connect to the database same as the database username if no value has been defined in PGDATABASE.

Output Items

TABLESPACE Tablespace name
DBNAME Database name (The blank in case of pg_global table space)
SIZE (MB) The capacity that is using the database object on the table space.(MB)

Examples

It connects to the postgres database of PostgreSQL instance running on the port 5432 of localhost, and to display the capacity that they are using for each database in each tablespace.

$ pt-tablespace-usage -d postgres
+------------+-----------+-----------+
| TABLESPACE |   DBNAME  | SIZE (MB) |
+------------+-----------+-----------+
| pg_default | postgres  |         8 |
| pg_default | template1 |         6 |
| pg_default | testdb    |         8 |
| pg_global  |           |         1 |
| spc1       | postgres  |        16 |
+------------+-----------+-----------+

pt-verify-checksum

Summary

Verifies the checksum of the specified PostgreSQL file

Make sure to have created a database cluster that enable the checksum. (-k option of initdb command )

Version PosgreSQL 9.3 or higher

The script invoke verifychecksum command inside. If it run on other OS than Hat Enterprise Linux 6/CentOS 6, you must separately build to src/verifychecksum.c to be located in the following bin directory.

Usage

pt-verify-checksum [option...] [segment file | directory]

If specified the file name as an argument, verify the checksum.

If specified a directory name as an argument, of the files in that directory, to verify the checksum as the subject of the following files.

  • The files found in the global and base directories, and
  • The file name is composed of a number (ex. 1234,``1234.1``) , and it contained that _vm or _fsm are appended at the end of files.

If errors were found in one or more files in the checksum verification, return exit code 1. If any other error occured, return exit code 2. If there are no problems in any files, return 0.

Options

-r, --recursive
-v, --verbose
--help

-r`, --recursive If it options specified directory, recursively scan the sub-directory below, to verify the checksum of the applicable file.

-v, --verbose During the checksum verification, it will show output the more messages.

Output Items

blkno Block number that found the checksum error
expected Checksum calculated from the data of the block
found Checksum which had been recorded in the page header
Verified N files Number of files that the checksum verification
N files corrupted Number of files that found the checksum error

Examples

Verifies the checksum of single file.

$ pt-verify-checksum /var/lib/pgsql/9.4/data/base/16386/16399
[2015-03-28 15:50:03] INFO: Verified 1 files. 0 files corrupted.
$

Verifies the checksum of all of files in the database.

$ pt-verify-checksum /var/lib/pgsql/9.4/data/base/16386
[2015-03-28 15:51:00] INFO: Verified 311 files. 0 files corrupted.
$

It recursively searched in the database cluster, and to verify the checksum of all of files.

$ pt-verify-checksum -r /var/lib/pgsql/9.4/data
[2015-03-28 15:55:16] INFO: /var/lib/pgsql/9.4/data/base/12144/11905: blkno 7, expected 2cf, found da97
[2015-03-28 15:55:16] INFO: 1 blocks corrupted in /var/lib/pgsql/9.4/data/base/12144/11905.
[2015-03-28 15:55:16] INFO: Verified 1046 files. 1 files corrupted.
$

pt-xact-stat

Summary

Displays transaction statistical information of multiple node. It is possible to display each specified interval continuously.

Usage

pt-xact-stat [option...] [delay [count]]

Options

-h, --host=HOSTNAME
-p, --port=PORT
-H, --host-list=HOSTLIST
-U, --username=USERNAME
-d, --dbname=DBNAME
-H, --host-list=HOSTNAME:PORT,HOSTNAME:PORT[,...]

-h, --host Specifies the connecting PostgreSQL database server name or its IP address. If not specified, to set the value of PGHOST. localhost will be used if no value has been defined in PGHOST.

-p, --port Specifies the port number of the connecting PostgreSQL database. If not specified, to set the value of PGPORT. 5432 will be used if no value has been defined in PGPORT.

-H, --host-list If it is connected to multiple database servers, it specifies database server name or several combination IP address or port number. The form is 192.168.1.101:5432,192.168.1.102:5433 . A set of server and port number is linked with a colon. A set of several server is linked with a comma. This port number is optional and will be set for the default port number.

-U, --username Specifies the user name of the PosgtgreSQL database. If not specified, to set the value of PGUSER. The value of USER will be used if no value has been defined in PGUSER.

-d, --dbname Specifies the connecting database name. If not specified, to set the value of PGDATABASE. It connect to the database same as the database username if no value has been defined in PGDATABASE.

Output Items

HOST Host name of PostgreSQL server
PORT Port number of PstgreSQL server
DBNAME Database name
CONN Number of sessions that are connected to the database
COMMITS Total number of transactions that are committed
ROLLBACKS Total number of transactions that are rollback
B_READ Number of blocks in the table that read from disk
B_HIT Number of pages that read from shared buffer

Examples

It connects to the two PostgreSQL instance running on the port 5432 and port 5433 of the localhost, and to display the statistical information of transaction for each instance every 5 seconds twice.

$ pt-xact-stat --host-list 127.0.0.1:5432,127.0.0.1:5433,127.0.0.1:5434 -d postgres 5 2
Sat Mar 28 20:47:50 JST 2015
+-----------+------+----------+------+---------+-----------+--------+-------+
|    HOST   | PORT |  DBNAME  | CONN | COMMITS | ROLLBACKS | B_READ | B_HIT |
+-----------+------+----------+------+---------+-----------+--------+-------+
| 127.0.0.1 | 5432 | postgres |    1 |     137 |         1 |    104 | 10273 |
| 127.0.0.1 | 5433 | postgres |    1 |       8 |         0 |    104 |  1350 |
| 127.0.0.1 | 5434 | postgres |    1 |      76 |         0 |    104 |  7708 |
+-----------+------+----------+------+---------+-----------+--------+-------+

Sat Mar 28 20:47:55 JST 2015
+-----------+------+----------+------+---------+-----------+--------+-------+
|    HOST   | PORT |  DBNAME  | CONN | COMMITS | ROLLBACKS | B_READ | B_HIT |
+-----------+------+----------+------+---------+-----------+--------+-------+
| 127.0.0.1 | 5432 | postgres |    1 |     139 |         1 |    104 | 10460 |
| 127.0.0.1 | 5433 | postgres |    1 |      10 |         0 |    104 |  1537 |
| 127.0.0.1 | 5434 | postgres |    1 |      78 |         0 |    104 |  7895 |
+-----------+------+----------+------+---------+-----------+--------+-------+

$

Connects to the three PostgreSQL instance running on the port 5432, port 5433 and port 5434 of the localhost, and to display once the statistical information of transaction for each instance, and exit.

$ pt-xact-stat --host-list 127.0.0.1:5432,127.0.0.1:5433,127.0.0.1:5434 -d postgres
Sat Mar 28 21:05:48 JST 2015
+-----------+------+----------+------+---------+-----------+--------+-------+
|    HOST   | PORT |  DBNAME  | CONN | COMMITS | ROLLBACKS | B_READ | B_HIT |
+-----------+------+----------+------+---------+-----------+--------+-------+
| 127.0.0.1 | 5432 | postgres |    1 |      12 |         0 |    104 |  1400 |
| 127.0.0.1 | 5433 | postgres |    1 |       4 |         0 |    104 |   976 |
| 127.0.0.1 | 5434 | postgres |    1 |       4 |         0 |    104 |   976 |
+-----------+------+----------+------+---------+-----------+--------+-------+

$

Release Note

Version 0.2.1

  • Add support for Red Hat Enterprise Linux 7, CentOS 7 and Ubuntu 14.04 LTS with Python 2.7.
  • Fix to correct help messages. (pt-index-usage, pt-set-tablespace, pt-table-usage)

Version 0.2.0

  • First Release Version

Indices and tables