Database Reference
In-Depth Information
Although
\watch
is primarily designed for monitoring query output, you can also use
it to run statements at fixed intervals. In
Example 3-4
, we log activity every five seconds.
Example 3-4. Log traffic every five seconds
SELECT * INTO log_activity FROM pg_stat_activity;
INSERT INTO log_activity SELECT * FROM pg_stat_activity; \watch 5
Create table and perform first insert.
Insert every five seconds.
If you want to kill a watch, use
CTRL-X CTRL-C
. Needless to say, watches are meant for
interactive psql only.
You can find more examples of using watch at
Michael Paquier: Watch in psql
.
Lists
Various psql commands can give you lists of objects along with details.
Example 3-5
demonstrates how to list all tables in the
pg_catalog
schema that start with
pg_t
, along
with their sizes.
Example 3-5. List tables with \dt+
\dt+ pg_catalog.pg_t*
Schema | Name | Type | Owner | Size | Description
-----------+------------------+-------+----------+--------+-------------
pg_catalog | pg_tablespace | table | postgres | 40 kB |
pg_catalog | pg_trigger | table | postgres | 16 kB |
pg_catalog | pg_ts_config | table | postgres | 40 kB |
pg_catalog | pg_ts_config_map | table | postgres | 48 kB |
pg_catalog | pg_ts_dict | table | postgres | 40 kB |
pg_catalog | pg_ts_parser | table | postgres | 40 kB |
pg_catalog | pg_ts_template | table | postgres | 40 kB |
pg_catalog | pg_type | table | postgres | 112 kB |
If we wanted details about a particular object, such as the
pg_ts_config
table, we would
use the
\d+
command, as shown in
Example 3-6
.
Example 3-6. Describe object with \d+
\d+ pg_ts_dict
Table "pg_catalog.pg_ts_dict"
Column | Type | Modifiers | Storage | Stats target | Description
---------------+------+-----------+----------+--------------+-------------
dictname | name | not null | plain | |
dictnamespace | oid | not null | plain | |
dictowner | oid | not null | plain | |
dicttemplate | oid | not null | plain | |
dictinitoption | text | | extended | |
