8.15. System Administration Functions

Table 8-34 shows the functions available to query and alter run-time configuration parameters.

Table 8-34. Configuration Settings Functions

NameReturn TypeDescription
current_setting(setting_name) TEXTcurrent value of setting
set_config(setting_name, new_value, is_local) TEXTset parameter and return new value

The function current_setting yields the current value of the setting setting_name. It corresponds to the SQL command SHOW. An example:

SELECT current_setting('datestyle');

 current_setting
-----------------
 ISO, MDY
(1 row)

set_config sets the parameter setting_name to new_value. If is_local is true, the new value will only apply to the current transaction. If you want the new value to apply for the current session, use false instead. The function corresponds to the SQL command SET. An example:

SELECT set_config('log_statement_stats', 'off', false);

 set_config
------------
 off
(1 row)

The function shown in Table 8-35 sends control signals to other server processes. Use of this function is restricted to superusers.

Table 8-35. Backend Signalling Functions

NameReturn TypeDescription
pg_cancel_backend(pid) INTCancel a backend's current query

This function returns 1 if successful, 0 if not successful. The process ID (pid) of an active backend can be found from the procpid column in the pg_stat_activity view, or by listing the edb-postgres processes on the server with ps.

The functions shown in Table 8-36 assist in making on-line backups. Use of these functions is restricted to superusers.

Table 8-36. Backup Control Functions

NameReturn TypeDescription
pg_start_backup(label_text) TEXTSet up for performing on-line backup
pg_stop_backup() TEXTFinish performing on-line backup

pg_start_backup accepts a single parameter which is an arbitrary user-defined label for the backup. (Typically this would be the name under which the backup dump file will be stored.) The function writes a backup label file into the database cluster's data directory, and then returns the backup's starting WAL offset as text. (The user need not pay any attention to this result value, but it is provided in case it is of use.)

pg_stop_backup removes the label file created by pg_start_backup, and instead creates a backup history file in the WAL archive area. The history file includes the label given to pg_start_backup, the starting and ending WAL offsets for the backup, and the starting and ending times of the backup. The return value is the backup's ending WAL offset (which again may be of little interest).

For details about proper usage of these functions, see Section 27.3.