一些查询基本命令
1.版本信息
postgres # SELECT version();
或者
bash # psql ––version (客户端)
bash # cat $PGDATADIRECTORY/PG_VERSION
2.启动时间
SELECT pg_postmaster_start_time();
持续时间
postgres=# SELECT date_trunc('second',current_timestamp - pg_postmaster_start_time()) as uptime;
3.默认数据文件位置 PGDATADIR
Redhat/CentOS
/var/lib/pgsql/data/
Windows
C:\Program Files\PostgreSQL\R.r\data
可以通过initdb命令添加额外的文件目录
PGDATADIR目录下子目录和子文件说明
base | Main data directory. Beneath this directory each database has its own directory within which are the fles for each database table or index. |
global | Database server catalog tables that are shared across all databases. |
pg_clog | Transaction status fles. |
pg_multixact | Row-level lock status fles |
pg_subtrans | Subtransaction status fles |
pg_tblspc | Links to external tablespaces |
pg_twophase | "2-phase commit", or Prepared transaction status |
pg_xlog | Transaction log (or Write Ahead Log - WAL) |
postgresql.conf 配置文件
pg_hba.conf /pg_ident.conf 连接配置文件
postmaster.pid 进程文件
控制文件
bash #
pg_controldata <data-directory>
4.列出数据库
bash $ psql -l
或者
postgres=# select datname from pg_database;
5.数据库中的表
SELECT count(*) FROM information_schema.tablesWHERE table_schema NOT IN ('information_schema','pg_catalog');
6.磁盘空间使用情况
当前数据库
SELECT pg_database_size(current_database());
所有数据库
SELECT sum(pg_database_size(datname)) from pg_database;
指定表
select pg_relation_size('tablename‘);
指定表(包含索引及其他)
select pg_total_relation_size('tablename');
命令行
postgres=# \dt+ tablename
特色功能,快速估算表行数(根据数据块计算,
Row Estimate = Number of Data Blocks * Rows per Block)
SELECT (CASE WHEN reltuples > 0
THEN pg_relation_size('mytable')/(8192*relpages/reltuples)
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable'::regclass;
WHERE oid = 'mytable'::regclass;
其他参照后续视图介绍