PostgreSQL 菜鸟入门

之前从来没接触过Postgre SQL,这里记录下我的入门第一步，供跟我一样的新手分享，另外里面有说的不对的，欢迎各位大牛指正！

 

1、安装

  在本机，先安装一个centos7的虚拟机(这玩意还是在linux下玩比较靠谱)，然后在官网下载9.3版本的源码包，参考官网手册的15.4节：

  ./configure 

  gmake

  gmake install

  安装完成，启动服务：

  /usr/local/pgsql/bin/pg_ctl start -l /usr/local/pgsql/log/startlogfile -D       /usr/local/pgsql/data

 

    

2、PG中的几个名词：

  ROLE，USER:

  user 和 role ，两个名词是一个概念，都是指用户，唯一的区别在于create user时默认是使用LOGIN选项的，也就是允许用户登录，而create role则不是

  DATABASE：这个概念等于MySQL或者SQLServer数据库的DATABASE概念

  SCHEMA：可以理解为一系列对象的集合，SQLServer用户对此并不陌生，MySQL中没这个概念

一个database中可以有多个schema，默认会有一个名为public的schema,不同的schema下面，可以有同名的对象名称。用户在创建对象时，如果不指定schema(既不手工指定，也不设置set search_path)，则会将对象放到public下，还有一个名为pg_catelog的schema,存放系统内置的数据库类型、函数等一系列元数据，还有一个名为information_schema的schema，目前还没搞懂它和pg_catelog的具体区别，只知道它的默认所有者是db的owner，用户有权限操作它，包括drop；还有一点就是它不在search_path中，要访问其中的对象，必须带上其名称

  TABLESPACE:代表物理文件夹的位置，可以通过它来设置DB或者TABLE在磁盘上的IO存储位置

 

3、建立一个数据库，并允许远程连接进来

  3.1 在数据库服务器上，使用  psql postgres 命令连接进来(本地连接，管理员身份)

  3.2 创建一个用户(testdbrole1)：

     create role testdbrole1 NOCREATEDB NOCREATEROLE LOGIN ENCRYPTED            PASSWORD '1' NOREPLICATION CONNECTION LIMIT 200;

  3.3 创建一个TABLESPACE（testtablespace1）:

     CREATE TABLESPACE testtablespace1  OWNER testdbrole1  LOCATION            '/usr/local/pgsql/data/testtablespace1';

  3.4 创建一个数据库（testdb1）：

     CREATE DATABASE testdb1 OWNER = testdbrole1 TABLESPACE = testtablespace1;

  3.5 切换到testdb1下：\c testdb1

  3.6 创建一个Schema（testdbschema1）：

     CREATE SCHEMA IF NOT EXISTS testdbschema1;

 

4、尝试连接：

  离开虚拟机，到本机环境去telnet 5432端口，却发现断开不通（先保证防火墙是不做5432断开限制的哈），原因是服务器端的TCP listener没有配置为接受外部的连接，解决办法为到虚拟机中修改配置文件：

  /usr/local/pgsql/data/postgresql.conf

  listen_addresses = '*'

 

  重启服务，这时在本机是可以telnet的，下载pgadmin，使用testdbrole1连接，仍然拒绝连接，解决办法为到虚拟机中修改配置文件：

  /usr/local/pgsql/data/pg_hba.conf  添加如下一行：

  host    testdb1testdbrole10.0.0.0/0md5   (意思是接受以TCP方式连接的，来自任何IP地址的testdbrole1用户的连接)

 

  此时可用成功登录进去，截图如下：

    

 

连接进去之后，截图如下：

 

 

 

可以看到标红的地方，和命令行中之前输入的命令，是一一对应的

 

到此，算是一个入门了吧。

PG里，还有一些很独特的概念，比如表可以继承、表可以定义分区表、数据类型还可以自定义。。。。

 

具体的请参考官方手册，答案都在里面。

 

下面是从网上找来的一些常用的SQL：

\l 列出数据库

\c  转到另一个数据库

\dt 列出所有表

\d 查看表结构

\di 查看索引

 

SELECT current_database(); 查看当前数据库

select version();  查看版本

select current_user;  查看当前用户

SELECT pg_database_size(current_database());  查看当前库大小

SELECT sum(pg_database_size(datname)) from pg_database;  查询所有库大小之和

select pg_relation_size('accounts');   ---查询表大小

select pg_total_relation_size('accounts');   ---查询包含表和表索引其他总大小

select pg_tablespace_size('tbs_index')/1024/1024 as "SIZE M";    查看表空间大小

 

查看TOP10大表：

SELECT table_name

,pg_relation_size(table_name) as size

FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema',

'pg_catalog')

ORDER BY size DESC

LIMIT 10;

 

快速评估表的行数：(一般都是大表，如果用count比较耗时间)

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;

 

查询等待会话

SELECT

w.current_query as waiting_query,

w.procpid as w_pid,

w.usename as w_user,

l.current_query as locking_query,

l.procpid as l_pid,

l.usename as l_user,

t.schemaname || '.' || t.relname as tablename

from pg_stat_activity w

join pg_locks l1 on w.procpid = l1.pid and not l1.granted

join pg_locks l2 on l1.relation = l2.relation and l2.granted

join pg_stat_activity l on l2.pid = l.procpid

join pg_stat_user_tables t on l1.relation = t.relid

where w.waiting;

 

SELECT date_trunc('second',

current_timestamp - pg_postmaster_start_time()) as uptime; ----数据库开启多久

 

SELECT pg_postmaster_start_time();     --------什么时候开启的

 

登录时指定用户和数据库：

psql -U hellen -d template1