Skripts for PostgreSQL rdbms

Index

Skript name
Comment
s_clu_filenames.sql
Displays files belonging to a PostgreSQL database cluster.
s_db_filenames.sql
Displays files belonging to a PostgreSQL database.
s_db_filenames_only.sql
Displays only file names of a PostgreSQL database.
s_file_size.sql
Displays size in kbytes of database objects.

Skripts
Go to Top
Purpose:
Displays files names, object names, object types, etc. of a PostgreSQL database cluster.
Usage:
psql -d template1 -f s_clu_fienames.sql
Output:
  db_name  |       schema       | object_type  |           object_name           |           file_name
-----------+--------------------+--------------+---------------------------------+--------------------------------
 CLUSTER   | pg_catalog         | INDEX        | pg_shadow_usename_index         | ${PG_ADMIN}/global/16645
 CLUSTER   | pg_catalog         | SYSTEM TABLE | pg_xactlock                     |
 CLUSTER   | pg_toast           | INDEX        | pg_toast_1260_index             | ${PG_ADMIN}/global/16679
...
 t1        | public             | INDEX        | i_account_parent                | ${T1}/base/580657/580715
 t1        | public             | VIEW         | whoami                          | ${T1}/base/580657/580668
 t740c     | information_schema | BASE TABLE   | sql_features                    | ${PG_ADMIN}/base/17145/17070
...

Go to Top
Purpose:
Displays files names, object names, object types, etc. of a PostgreSQL database.
Usage:
psql -d <database> -f s_db_filenames.sql
Output:
 db_name |       schema       | object_type  |           object_name           |           file_name
---------+--------------------+--------------+---------------------------------+-------------------------------
 dbkoch  | information_schema | BASE TABLE   | sql_implementation_info         | ${PG_ADMIN}/base/17143/17075
...
 dbkoch  | information_schema | VIEW         | view_table_usage                | ${PG_ADMIN}/base/17143/17126
 dbkoch  | pg_catalog         | BASE TABLE   | pg_aggregate                    | ${PG_ADMIN}/base/17143/16406
...
 dbkoch  | pg_catalog         | BASE TABLE   | pg_type                         | ${PG_ADMIN}/base/17143/1247
 dbkoch  | pg_catalog         | INDEX        | pg_aggregate_fnoid_index        | ${PG_ADMIN}/base/17143/16601
...
 dbkoch  | pg_catalog         | INDEX        | pg_type_typname_nsp_index       | ${PG_ADMIN}/base/17143/16653
 dbkoch  | pg_catalog         | SYSTEM TABLE | pg_xactlock                     |
 dbkoch  | pg_catalog         | VIEW         | pg_indexes                      | ${PG_ADMIN}/base/17143/16696
...
 dbkoch  | public             | INDEX        | zutaten_rid                     | ${PG_ADMIN}/base/17143/17315
 dbkoch  | public             | SQUENCE      | __rekallobjects_seq             | ${PG_ADMIN}/base/17143/901807

Go to Top
Purpose:
Displays only file names of a PostgreSQL database, e.g. for cold/or hot backup.
Usage:
psql -d <database> -f s_db_filenames_only.sql
Output:
...
 ${PG_ADMIN}/base/17143/1259
 ${PG_ADMIN}/base/17143/16384
 ${PG_ADMIN}/base/17143/16386
...

Go to Top
Purpose:
Displays size in kbytes of database objects. Caution: This skript does only display correct values after VACUUM!
Usage:
psql -d <database> -f s_file_size.sql
Output:
        tablename        |            indexname            | size_kb | filename
-------------------------+---------------------------------+---------+----------
 anweisung               |                                 |      80 |    17149
 anweisung               | anweisung_mid                   |       8 |    17306
 einheiten               |                                 |       8 |    17155
 einheiten               | einheiten_pkey                  |      16 |    17319
 einheiten               | einheiten_abbrev                |      16 |    17307
...