Functions In Postgresql

Server Signaling Functions :-

Use of these functions is usually restricted to superusers

pg_cancel_backend():- cancels the running query

pg_terminate_backend():- terminates the entire process and thus the database connection.Terminate a backend A connection which is idle or idle in transaction does not have a current query to cancel, but it has a backend process which can be terminated.

pg_reload_conf():-  Cause server processes to reload their configuration files

pg_rotate_logfile():- Rotate server's log file

Backup Control Functions:-

These functions cannot be executed during recovery (except pg_is_in_backup, pg_backup_start_time and pg_xlog_location_diff).

pg_create_restore_point(name text):- Create a named point for performing restore (restricted to superusers)

pg_current_xlog_insert_location():- Get current transaction log insert location

pg_current_xlog_location():- Get current transaction log write location

pg_start_backup(label text [, fast boolean ]):- Prepare for performing on-line backup (restricted to superusers or replication roles)

pg_stop_backup():- Finish performing on-line backup (restricted to superusers or replication roles)

pg_is_in_backup():- True if an on-line exclusive backup is still in progress.

pg_backup_start_time():- Get start time of an on-line exclusive backup in progress.

pg_switch_xlog():- Force switch to a new transaction log file (restricted to superusers)

pg_xlogfile_name(location text):- Convert transaction log location string to file name

pg_xlogfile_name_offset(location text):- Convert transaction log location string to file name and decimal byte offset within file

pg_xlog_location_diff(location text, location text):- Calculate the difference between two transaction log locations

Recovery Control Functions:-

The functions shows provide information about the current status of the standby. These functions may be executed both during recovery and in normal running.

pg_is_xlog_replay_paused():- True if recovery is paused.

pg_xlog_replay_pause():- Pauses recovery immediately.

pg_xlog_replay_resume():- Restarts recovery if it was paused.

Database Object Size Functions:-

The functions shows to calculate the disk space usage of database objects.

pg_column_size(any):- Number of bytes used to store a particular value (possibly compressed)

pg_database_size(oid):- Disk space used by the database with the specified OID

pg_database_size(name):- Disk space used by the database with the specified name

pg_indexes_size(regclass):- Total disk space used by indexes attached to the specified table

pg_relation_size(relation regclass, fork text):- Disk space used by the specified fork ('main', 'fsm', 'vm', or 'init') of the specified table or index

pg_relation_size(relation regclass):- Shorthand for pg_relation_size(..., 'main')

pg_size_pretty(bigint):- Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units

pg_size_pretty(numeric):- Converts a size in bytes expressed as a numeric value into a human-readable format with size units

pg_table_size(regclass):- Disk space used by the specified table, excluding indexes (but including TOAST, free space map, and visibility map)

pg_tablespace_size(oid):- Disk space used by the tablespace with the specified OID

pg_tablespace_size(name):- Disk space used by the tablespace with the specified name

pg_total_relation_size(regclass):- Total disk space used by the specified table, including all indexes and TOAST data

Database Object Location Functions

pg_relation_filenode(relation regclass):- Filenode number of the specified relation

pg_relation_filepath(relation regclass):- File path name of the specified relation