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

Scripts

s_clu_filenames.sql

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

s_db_filenames.sql

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

s_db_filenames_only.sql

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

s_file_size.sql

Go to Top

Purpose: Displays size in kbytes of database objects.
Caution: This script 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
...