1查看当前数据库实例的版本

postgres=# select version();

version

---------------------------------------------------------------------------------------------------------------

PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit

(1 row)

2查看书库启动的时间

postgres=# select pg_postmaster_start_time();

pg_postmaster_start_time

-------------------------------

2015-06-29 23:23:19.679754-04

(1 row)

3查看最后load配置文件的时间

postgres=# select pg_conf_load_time();

pg_conf_load_time

-------------------------------

2015-06-29 23:23:19.596817-04

(1 row)

-bash-4.1$ cat .bashrc

export PATH=/usr/pgsql-9.4/bin:/opt/pgsql/bin:$PATH:$HOME/bin

export PGDATA=/var/lib/pgsql/9.4/data

export PGUSER=postgres

export PGPORT=5432

export LD_LIBRARY_PATH=/var/lib/pgsql/9.4:$LD_LIBRARY_PATH

export BACKUP_PATH=/backup

-bash-4.1$ source .bashrc

是用pg_ctl reload改变配置的装载时间

-bash-4.1$ pg_ctl reload

server signaled

-bash-4.1$ psql

psql (9.4.3)

Type "help" for help.

postgres=# select pg_conf_load_time();

pg_conf_load_time

------------------------------

2015-07-14 21:05:22.81119-04

(1 row)

显示当前数据库的时区

postgres=# show timezone;

TimeZone

------------

US/Eastern

(1 row)

postgres=# \q

-bash-4.1$ date

Tue Jul 14 21:06:42 EDT 2015

-bash-4.1$ psql

psql (9.4.3)

Type "help" for help.

postgres=# select now();

now

-------------------------------

2015-07-14 21:07:12.529845-04

(1 row)

有时数据库的时间不是当前操作系统的时间，这是在数据库看到的时间就和操作系统的时间不一样

查看当前用户名

postgres=# select user;

current_user

--------------

postgres

(1 row)

postgres=# select current_user;

current_user

--------------

postgres

(1 row)

查看session用户名

postgres=# select session_user;

session_user

--------------

postgres

(1 row)

postgres=# select user;

current_user

--------------

postgres

(1 row)

通常情况下session_user and user是相同的，但是当用 set role改变用户的角色时两者就不相同了 session user是原始用户 user是当前的角色用户

postgres=# set role u01;

ERROR: role "u01" does not exist

查看当前连接的数据库名称

postgres=# select current_catalog,current_database();

current_database | current_database

------------------+------------------

postgres | postgres

(1 row)

查看当前session所在客户端的ip地址和端口号

postgres=# select inet_client_addr(),inet_client_port();

inet_client_addr | inet_client_port

------------------+------------------

|

(1 row)

查看当前数据库服务器的ip地址和端口号

postgres=# select inrtserver_addr(),inet_server_port();

inet_server_addr | inet_server_port

------------------+------------------

|

(1 row)

查看当前session的后台服务器进程的pid

postgres=# select pg_backend_pid();

pg_backend_pid

----------------

7647

(1 row)

通过操作系统可以插看次后台服务器

postgres=# \q

-bash-4.1$ ps -ef|grep 7647 |grep -v grep

-bash-4.1$ ps -ef|grep 7647 |grep -v greppostgres 7798 7464 0 21:14 pts/2 00:00:00 grep 7647

-bash-4.1$ psql

psql (9.4.3)

Type "help" for help.

查看当前的一些参数配置情况

postgres=# show shared_buffers;

shared_buffers

----------------

128MB

(1 row)

查看当前的一些参数配置

postgres=#selectcurrent_setting(shared_buffers'shared_buffers current_setting

-----------------

128MB

(1 row)

修改当前参数的配置

postgres=# set menaintenance_worjkmem to '128MB';

SET

postgres=# select set_config('maintenance_work_mem','120MB',false);

set_config

------------

120MB

(1 row)

查看当前正在写的wal日志

postgres=# select pg_xlogfile_name(pg_current_xlog_location());

pg_xlogfile_name

--------------------------

0000000A0000000700000079

(1 row)

插看当前wal的buffer中还有多少字节的数据没有写到磁盘中

postgres=#selectpg_xlog_location_diff(pg_current_xlog_insert_location(),pg_courrent_xlog_location());

pg_xlog_location_diff

-----------------------

0

(1 row)

postgres=# select pg_is_in_backup(),pg_bacckup_syatart_time();

pg_is_in_backup | pg_backup_start_time

-----------------+----------------------

f |

(1 row)

postgres=# select pg_is_icn_recovery();

pg_is_in_recovery

-------------------

f

(1 row)

postgres=#selectpg_database_sizeList of databases

--------------+------------------+----------+-------------+-------------+---------------------------------------

template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8

ITPub博客

postgresql常用命令

最新文章