PostgreSQL Database Common Commands

--Get the database software version
Select version ();
--Get Database startup time
Select Pg_postmaster_start_time ();
--Get configuration file last load time
Select Pg_conf_load_time ();
--Get the current database time zone
Show TimeZone;
--Get all the databases in the current instance
Psql-l
--Get current database user
Select User;
Select Current_User;
--Get current session user
Select Session_user;
--Get the session client address and port
Select Inet_client_addr (), Inet_client_port ();
--Get the current database server address and port
Select Inet_server_addr (), Inet_server_port ();
--Get the current session service process
Select Pg_backend_pid ();
--Get the current parameter configuration
Show Shared_buffer;
Select Current_setting (' shared_buffers ');
--Modify Session parameter configuration
Set Maintenance_work_mem to ' 32m ';
Select Set_confit (' Maintenance_work_mem ', ' 32m ', false);
--Get the current Wal file
Select Pg_xlogfile_name (Pg_current_xlog_location ());
--Get the Wal buffer that was not written to disk
Select Pg_xlog_location_diff (Pg_current_xlog_insert_location (), pg_current_xlog_location ());
--Get the current backup in the instance
Select Pg_is_in_backup (), Pg_backup_start_time ();
--Get the role status of the current instance (primary or standby)
Select Pg_is_in_recovery ();
--Get database size
Select Pg_database_size (' MyDB '), Pg_size_pretty (pg_database_size (' mydb '));
--Get table size
--Table
Select Pg_size_pretty (pg_relation_size (' Mytab '));
--Tables and indexes
Select Pg_size_pretty (pg_total_relation_size (' Mytab '));
--Get index size
Select Pg_size_pretty (pg_indexes_size (' Mytab '));
--Get Table space size

Select Pg_size_pretty (pg_tablespace_size (' My_tbs '));

--Get free space for each block of data in the table

Select Pg_freespace (' Mytab ');

--Get the free space ratio in each block of data in the table

SELECT (upper-lower)/pagesize::float8):: Integer as Free_pct
From Page_header (get_raw_page (' Mytab ', 11));

--Get the data file corresponding to the table
Select Pg_relation_filepath (' Mytab ');
--Reload the configuration file
Pg_ctl Reload
Select pg_reload_conf ();
--Toggle Log file
Select Pg_rotate_logfile ();
--Toggle Xlog File
Select Pg_rotate_xlog ();
--Manually generate checkpoints.
Checkpoint
--Cancel the SQL being executed
Select Pg_cancel_backend (PID);
--Terminate background service process
Select Pg_terminate_backend (PID);
--Get the SQL being executed

Select Pid,username,query_start,query from Pg_stat_activity;

--Get Combo Command Ids

SELECT T_xmin as Xmin,
T_xmax::text::int8 as Xmax,
T_field3::text::int8 as Cmin_cmax,
(T_infomask::integer & X ' 0020 ':: integer):: bool as Is_combocid
From Heap_page_items (get_raw_page (' Mytab ', 0))
ORDER by 2 DESC, 3;

--Create a prototype view to get a chunk tuple

CREATE VIEW T1_page0 as
SELECT ' (0, ' | | | lp | | ') ' as Ctid,
Case Lp_flags
When 0 Then ' Unused '
When 1 Then ' Normal '
When 2 Then ' Redirect to ' | | Lp_off
When 3 Then ' Dead '
END,
T_xmin::text::int8 as Xmin,
T_xmax::text::int8 as Xmax,
T_ctid
From Heap_page_items (get_raw_page (' T1 ', 0))
ORDER by LP;

SELECT * from T1_PAGE0;

PostgreSQL Database Common Commands