1. 首页
  2. 数据库运维
  3. PostgreSQL

探索postgresql数据库(一)

最近在看《postgresql 9 admin cookbook》这本书,博主把从这本书中看到的内容和大家一起探讨下。可能会有理解偏差,欢迎各位指正。

一、查看服务器版本
postgres=# select version() ;
                                                    version
—————————————————————————————————————
 PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
版本格式: Major.Minor.Maintenance,应该就是“主要.次要.维护”这三个意思。
 
二、查看数据库服务器运行时间
postgres=# SELECT date_trunc(‘second’,current_timestamp – pg_postmaster_start_time()) as uptime;
  uptime
———-
 22:27:34
这里可以分成三个部分来理解。
1、查看数据库服务器启动时间
postgres=# SELECT pg_postmaster_start_time();
2、当前时间减去启动时间就是运行时间
postgres=# SELECT current_timestamp – pg_postmaster_start_time();
    ?column?
—————-
 22:30:57.39856
3、对时间做下格式设定
postgres=# SELECT date_trunc(‘second’,current_timestamp – pg_postmaster_start_time()) as uptime;
三、数据库文件位置
Debian和Ubuntu默认安装在/var/lib/postgresql/R.r/main,R.r分别表示Major和Minor版本号,配置文件在/etc/postgresql/R.rNn/main/。main只是数据库的一个名字,其他名字也是正确的。
Red  Hat RHEL,  CentOS, or  Fedora系统data目录默认在/var/lib/pgsql/data/,其中包含配置文件。
这些只是一些默认配置,可能和实际生产环境中有区别。
data下的几个文件目录
子目录 用途
base 主要数据目录,各个数据库在base目录中对应一个文件夹,文件夹包含对应数据库表和索引。
global 在所有数据库中都共享的那些表,比如系统表。
pg_clog 事务状态文件
pg_multixact 行级锁状态文件
pg_subtrans 子事务状态文件
pg_tblspc 链接到外部的表空间
pg_twophase “2-phase commit”, or Prepared transaction status
pg_xlog WAL预写式日志文件
如果不明白这几个文件,最好不到动它。不过在data目录下我们可以修改配置文件*.conf,比如postgres.conf。
四、数据库服务器的日志文件
数据库日志文件Debian或Ubuntu默认在/var/log/postgresql,Red Hat, RHEL, CentOS, 或Fedora默认在/var/lib/pgsql/data/pg_log。
严重性
DEBUG 1
through
DEBUG 5
内部诊断信息
INFO 用户命令的输出
NOTICE 一些有用的信息
WARNING 有可能发生一些问题的警告
ERROR 当前失败或中止的命令
LOG For sysadmins
FATAL Event that disconnects one session only,一个会话断开。
PANIC Event that crashes server,数据库服务器崩溃。

log可以在postgres.conf配置文件中设置,比如可以设置log_min_messages参数调整信息出现数,log_error_verbosity参数调整信息显示的数量。

pg_controldata <data-directory> | grep “system identifier”
这个命令可以显示系统标识符,这里在data-directory之前不需要加-D。pg_controldata是显示系统控制文件的一个系统应用,不要对这些参数进行修改,否则可能会导致意想不到的错误。
五、在数据库服务器中显示数据库
postgres@vm-05:~$ psql -l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
———–+———-+———-+————+————+———————–
 postgres  | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 |
 template0 | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
也可以用psql登陆后用\l查看,或者直接select系统表。
postgres=# select datname from pg_database ;
  datname
———–
 template1
 template0
 postgres
默认数据库中有3个数据库,分别是template1,template0和postgres,主要的用户数据都在postgres中。可以用CREATE DATABASE my_database;自行创建数据库。template1和template0都是一些模板数据库。template0和postgres这两个数据库不要修改。template1可以按照自己需求修改,按照这个模板新建新数据库。template0相当于是template1的备份,如果对template1不满意可以恢复到原始状态。
六、一个数据库中有多少张表
select count(*) from information_schema.tables where table_schema not in (‘information_schema’,  ‘pg_catalog’);
这个语句可以显示当前连接的数据库中有多少张表,有多个数据库就需要分别统计。还有not in (‘information_schema’,  ‘pg_catalog’)这个限定条件是排除系统设置表。数据库表越多,反映出系统越复杂。当然如果表不多也可以在psql中用\d命令来查看。
七、查看数据库占用多少磁盘空间
查看数据库占用多少空间有两个办法,第一是在服务器系统查看data目录,但这有一个缺点,如果数据库目录比较多或者包含很多表空间的话很麻烦。另外一个是用sql语句查看,这个是最简单的。
查看当前数据库占用大小
postgres=# SELECT pg_database_size(current_database());
 pg_database_size
——————
          6362232
查看所有数据库占用大小
postgres=# SELECT sum(pg_database_size(datname)) from pg_database ;
   sum
———-
 18967284

联系我们

0574-55011290

QQ:248687950

邮件:admin@nbhao.org

工作时间:周一至周五,9:00-18:00,节假日休息

QR code