查看postgresql系统信息的常用命令操作

postgres=# select version();
     version     
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)
 

postgres=# select pg_postmaster_start_time();
 pg_postmaster_start_time  
-------------------------------
 2019-08-26 10:53:47.328699+08
(1 row)

postgres=# select pg_conf_load_time();
  pg_conf_load_time  
------------------------------
 2019-08-26 10:53:46.57045+08
(1 row)

postgres=# show timezone;
 TimeZone 
----------
 PRC
(1 row)

postgres=# select now();
    now    
-------------------------------
 2019-08-26 10:58:36.508472+08
(1 row)

postgres=# select user;
 current_user 
--------------
 postgres
(1 row)
 
postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# select session_user;
 session_user 
--------------
 postgres
(1 row)
 
postgres=# set role=aaa;
SET
 
postgres=> select session_user;
 session_user 
--------------
 postgres
(1 row)
 
postgres=> select user;
 current_user 
--------------
 aaa
(1 row)

postgres=# select current_catalog,current_database();
 current_database | current_database 
------------------+------------------
 postgres   | postgres
(1 row)

postgres=# select inet_client_addr(),inet_client_port();
 inet_client_addr | inet_client_port 
------------------+------------------
     |     
(1 row)

postgres=# select inet_server_addr(),inet_server_port();
 inet_server_addr | inet_server_port 
------------------+------------------
 192.168.91.5  | 5866  
(1 row) 

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
   3958
(1 row)

postgres=# show shared_buffers;
 shared_buffers 
----------------
 128MB
(1 row)

postgres=# set maintenance_work_mem to '128MB';
SET
 
postgres=# select set_config('maintenance_work_mem','128MB',false);
 set_config 
------------
 128MB
(1 row)

postgres=# select pg_xlogfile_name(pg_current_xlog_location());
  pg_xlogfile_name  
--------------------------
 00000001000000000000004B
(1 row)

postgres=# select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
 pg_xlog_location_diff 
-----------------------
      0
(1 row)

postgres=# select pg_is_in_backup(),pg_backup_start_time(); pg_is_in_backup | pg_backup_start_time 
-----------------+----------------------
 f    | 
(1 row)

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f
(1 row)

postgres=# select pg_database_size('postgres'),pg_size_pretty(pg_database_size('postgres'));
 pg_database_size | pg_size_pretty 
------------------+----------------
   67922104 | 65 MB
(1 row)

postgres=# select pg_size_pretty(pg_relation_size('test')); pg_size_pretty 
----------------
 0 bytes
(1 row)

postgres=# select pg_size_pretty(pg_total_relation_size('test'));
 pg_size_pretty 
----------------
 0 bytes
(1 row)

postgres=# select pg_size_pretty(pg_indexes_size('test'));
 pg_size_pretty 
----------------
 0 bytes
(1 row)

postgres=# select pg_size_pretty(pg_tablespace_size('pg_global'));
 pg_size_pretty 
----------------
 477 kB
(1 row)

postgres=# select pg_relation_filepath('test');
 pg_relation_filepath 
----------------------
 base/12902/24952
(1 row)

psql [option…] [dbname [username]]

psql -h 127.0.0.1 -p 5432 -d database -U postgres

psql -U postgres -d database -f sqlScript.sql

\l '*post*'

\c 可选参数 dbname [ username ] [ host ] [ port ]

\c postgres
或者
\c postgres username localhost 5432

SHOW search_path;

 search_path  
----------------
 "$user",public

SET search_path TO myschema;
# set search_path to auth;
# SHOW search_path;
 search_path 
-------------
 auth