PostgreSQL的学习

PostgreSQL的一些常用命令

查看pg数据库的版本信息

SELECT version();

数据库连接

默认的用户和数据库是postgres（注意”-U”、”-p”和”-d”的大小写，其中”-p”是指定端口号——默认为5432；在psql中不允许命令行上放密码，可以使用.pgpass密码文件代替）

psql -U user -d dbname -p portname

在Windows上免密码登录的方法设置，参考：
http://stackoverflow.com/questions/6216432/windows-psql-command-line-is-there-a-way-to-allow-for-passwordless-login

列举数据库，相当于mysql的show databases

\l

切换数据库,相当于mysql的use dbname

\c dbname

列举表，相当于mysql的show tables

\dt

查看表结构，相当于desc tblname,show columns from tblname

\d tblname
\d #后面不接tblname的，显示全部表

查看索引

\di

mysql 的 show create table 在 postgresql 里面是什么命令？

最简单的做法：

pg_dump -h db_ip_address -U db_user -t table_name -s db_name

-s, –schema-only 只转储模式, 不包括数据
-t, –table=TABLE 只转储指定名称的表

输入/输出

  copy ...             执行 SQL COPY，将数据流发送到客户端主机
  echo [字符串]       将字符串写到标准输出
  i 文件          从文件中执行命令
  ir FILE               与 i类似, 但是相对于当前脚本的位置
  o [文件]        将全部查询结果写入文件或 |管道
  qecho [字符串]      将字符串写到查询输出串流(参考 o)

连接

  c[onnect] [数据库名称|- 用户名称|- 主机|- 端口|-]
                 连接到新的数据库(目前是 "postgres")
  encoding [编码名称] 显示或设定客户端编码
  password [USERNAME]  安全地为用户改变口令
  conninfo              显示当前连接的相关信息

资讯性

  (选项: S = 显示系统对象, + = 其余的详细信息)
  d[S+]          列出表,视图和序列
  d[S+]  名称      描述表，视图，序列，或索引
  da[S]  [模式]    列出聚合函数
  db[+]  [模式]     列出表空间
  dc[S+] [PATTERN]      列表转换
  dC[+]  [PATTERN]      列出类型强制转换
  ddp     [模式]    列出缺省权限
  dD[S+] [PATTERN]      列出共同值域
  det[+] [PATTERN]      列出引用表
  des[+] [模式]    列出外部服务器
  deu[+] [模式]     列出用户映射
 dew[+] [模式]       列出外部数据封装器
   df[antw][S+] [模式]    列出[只包括 聚合/常规/触发器/窗口]函数
  dF[+]  [模式]   列出文本搜索配置
  dFd[+] [模式]     列出文本搜寻字典
 dFp[+] [模式]     列出文本搜索解析器
  dFt[+] [模式]   列出文本搜索模版
  dg[+]  [PATTERN]      列出角色
 di[S+] [模式]  列出索引
  dl                   列出大对象， 功能与lo_list相同
  dL[S+] [PATTERN]      列出所有过程语言
  dm[S+] [PATTERN]      列出所有物化视图
  dn[S+] [PATTERN]     列出所有模式
  do[S]  [模式]   列出运算符
  dO[S+] [PATTERN]      列出所有校对规则
  dp     [模式]     列出表，视图和序列的访问权限
  drds [模式1 [模式2]] 列出每个数据库的角色设置
  ds[S+] [模式]    列出序列
  dt[S+] [模式]     列出表
  dT[S+] [模式]  列出数据类型
  du[+]  [PATTERN]      列出角色
  dv[S+] [模式]   列出视图
  dE[S+] [PATTERN]      列出引用表
  dx[+]  [PATTERN]      列出扩展
  dy     [PATTERN]      列出所有事件触发器
  l[+]   [PATTERN]      列出所有数据库
  sf[+] FUNCNAME        显示函数定义

计时·查看每个命令的执行时间

How to turn on timing, and checking how much time a query takes to execute ?
{ timing — After this if you execute a query it will show how much time it took for doing it. }

# timing
Timing is on.
# SELECT * from pg_catalog.pg_attribute ;
......
Time: 9.583 ms

添加用户、建库/表的命令：

create user hey with password 'xxx';
create database testdb;
create database testdb with owner hey;
...
drop database testdb;

本机查看帮助的一些方法：

1.将PostgreSQL的安装后的bin目录添加至系统路径
2.命令行运行“psql –help”

进入PostgreSQL之后查看帮助的方法：

postgres=# help
您正在使用psql, 这是一种用于访问PostgreSQL的命令行界面
键入： copyright 显示发行条款
       h 显示 SQL 命令的说明
       ? 显示 pgsql 命令的说明
       g 或者以分号(;)结尾以执行查询
       q 退出

PostgreSQL和MySQL的一些对比：

mysql 里头的 auto_increment 在 postgresql 里头对应怎么弄？

postgresql 里头有个类型，叫 serial，等同于 mysql 里头的 int auto_increment 的定义，也就是说，在mysql里头下面的SQL代码：

create table
(
id int auto_increment
)

等效于 postgresql 里头的：

create table
(
id serial
)

postgresql 里头还有个 bigserial，是8字节的整数，如果需要特别大范围的序列号，可以用bigserial。

mysql 的 show full processlist 在 postgresql 里面怎么弄？

select * from pg_stat_activity;

因为pg_stat_activity是一个系统视图（view），所以你可以对它加条件，甚至是排序什么的，比如：

select client_addr, count(1)
from pg_stat_activity
group by client_addr order by count(1) desc;

看看哪个客户端连接最多。

mysql 的 show global variables 对应的 postgresql 命令是什么？{在psql里面执行}

show all;

查看系统中所有索引并按索引大小排序

select indexrelname, pg_size_pretty(pg_relation_size(relname::text)) from pg_stat_user_tables where schemaname = 'public' order by pg_relation_size(relname::text) desc;

查看系统中所有表并按表大小排序

select relname, pg_size_pretty(pg_total_relation_size(relname::text)) from pg_stat_user_tables where schemaname = 'public' order by pg_total_relation_size(relname::text) desc;

查看主表大小

select relname, pg_size_pretty(pg_total_relation_size(relname::text)) from pg_stat_user_tables where schemaname = 'public' order by pg_total_relation_size(relname::text) desc;

学习PostgreSQL的资源：

PostgreSQL research – 德哥@Digoal
PostgreSQL DBA – francs
PostgreFAQ
以及从上面的blog中引出的链接……

参考链接：