PostgreSQL的实践一：初识

简介和认知

发音

post-gres-q-l

服务（server）

一个操作系统中可以启动多个postgres服务。
每个服务由多个进程组成，为首的进程名为postmaster。
每个服务要占用一个端口，多个服务不能共享端口。
每个服务都有一个data目录用于存放数据，目录不允许修改，否则会破坏数据库，并且无法修复。
服务使用4字节长的内部事务标识符，即时发生重叠后仍然继续使用，这会导致问题，所以需要定期进行VACUUM操作。

数据库（database）

一个服务中可以拥有多个数据库。
数据库默认是任何用户可连接的，创建好后需要修改相应的权限。
数据库之间的数据是隔离的，不能进行联表。
数据库默认的数据块大小为8192。

模式（schema）

一个数据库中可以有多个模式，模式相当于表的命名空间，类似于mysql中的database，可以使用带模式的完整名称来访问或者创建对象。
不同模式之间的表是可以联表查询的。
可以通过对用户设置search_path参数来指定默认搜索的模式。

表（table）

一个模式中可以有多张表。
表是由多个关系元素组成的，大字段数据放在另一个名为TOAST的表中，每张表都有一个TOAST表和TOAST索引。
用双引号括起来的表和没用双引号括起来的表是不一样的，即使名字一样。
双引号括起来的表区分大小写，没用双引号括起来的表不区分大小写。

列（column）

每张表都由许多列组成，每一列有一个列名、类型、默认值等属性，用来存储每一条记录中的各种值。
文本类型统一由一种数据类型存储，支持长度从1B到1G，经过优化，存储少的时候很高效，存储多的时候会自动管理和压缩。
自增类型serial本质上就是整数，通过创建并关联到一个SEQUENCE类型的对象来记录自增值。

表空间（tablespace）

默认情况下，所有的数据都会放在postgres指定的data目录下，通过定义表空间，可以让postgres将数据存放在不同的设备上。
表空间是通过软链接来实现的。
建议为每个数据库设立一个单独的表空间，尤其是不同数据库中有同名的模式或者表的时候。
postgres=# CREATE TABLESPACE tbs LOCATION '/usr/local/tbs';

视图（view）

视图本质上是预定义好的一个sql查询，以一张表的形式给出，在每次调用时都会执行相应的sql查询。
postgres=# CREATE VIEW view AS SELECT * FROM tb;

当视图足够简单的时候，postgres是支持视图更新的，相应的更新会传递到相应的表中。
还可以使用INSTEAD OF触发器或者规则来实现视图更新，请参考具体的操作手册。
物化视图可以预先将数据查询出来，这样调用的时候就不必反复查询了，更新需要手动更新。
postgres=# CREATE MATERIALIZED VIEW view AS SELECT * FROM tb;
postgres=# REFRESH MATERIALIZED VIEW view;

行（row）

行即表中的一条数据。
postgres中每个行都有一个行版本，而且还有两个系统列xmin和xmax，分别标示这个行被创建和删除的事务。
删除时，设置xmax为删除事务号，不会实际执行删除。
UPDATE操作被认为是紧跟INSERT操作后的DELETE操作。
索引（index）
索引可以用来给表添加约束或者提高查询速度。
在涉及高比例插入删除的表中，会造成索引膨胀，这时候可以重建索引。

reindexdb

创建CONCURRENTLY索引时不会持有全表锁，这条指令分成两个步骤，第一部分创建索引并标记为不可用，这时候INSERT、UPDATE、DELETE操作已经开始维护索引了，但是查询不能使用索引。建立完毕后才会被标记为可用。
postgres=# CREATE CONCURRENTLY INDEX index ON tb(id);

可以手工设置索引的可用性。

UPDATE pg_index SET indisvalid = false WHERE indexrelid = index::regclass;

pgsql 中表空间／数据库／模式 的关系

表空间是物理结构，同一表空间下可以有多个数据库
数据库是逻辑结构，是表／索引／视图／存储过程的集合，一个数据库下可以有多个schema
模式是逻辑结构，是对数据库的逻辑划分

安装

# vist https://www.postgresql.org/download/
# Interactive installer by EnterpriseDB -> 选择10.5版本的Windows x86-64下载
1. 一路的next安装，当然你可以自己选择安装的目录
2. 提示输入postgres帐号的密码，你可以根据自己的喜好，设置一个，比如这里我设置了：123456
2. 提示安装插件扩展，取消即可，暂时不需要安装

psql客户端简单实用

连接

# $MY_POSTGRES_PATH = D:\PostgreSQL; 这个环境参数代表我安装的Postgresql服务器所在的目录
# $MY_POSTGRES_PATH/bin/psql -U postgres
$MY_POSTGRES_PATH/bin/scripts/runpsql
# 依次默认回车，如果有需要调整参数，你可以自定义
# 输入123456


#output
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
用户 postgres 的口令：
psql (10.5)
输入 "help" 来获取帮助信息.
postgres=#

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

# \l 查看已存在的数据库
postgres-# \l
                                                                             数据库列表
   名称    |  拥有者  | 字元编码 |                      校对规则                       |                        Ctype                        |       存取权限
-----------+----------+----------+-----------------------------------------------------+-----------------------------------------------------+-----------------------
 postgres  | postgres | UTF8     | Chinese (Simplified)_People's Republic of China.936 | Chinese (Simplified)_People's Republic of China.936 |
 template0 | postgres | UTF8     | Chinese (Simplified)_People's Republic of China.936 | Chinese (Simplified)_People's Republic of China.936 | =c/postgres          +
           |          |          |                                                     |                                                     | postgres=CTc/postgres
 template1 | postgres | UTF8     | Chinese (Simplified)_People's Republic of China.936 | Chinese (Simplified)_People's Republic of China.936 | =c/postgres          +
           |          |          |                                                     |                                                     | postgres=CTc/postgres
(3 行记录)

# 我们可以发现默认存在postgres、template0和template1数据库，template`X`是模板数据库
# template1为可修改模版库，template0为不可修改模版库

创建数据库

postgres=# create database testdb;
# CREATE DATABASE

# \c = \connect
postgres=# \c testdb;
# 您现在已经连接到数据库 "testdb",用户 "postgres".

创建表

# 查看表
testdb=# \d
# Did not find any relations.

# 创建表
testdb=# create table test1(id int primary key, name varchar(50));
# CREATE TABLE

testdb=# \d
               关联列表
 架构模式 | 名称  |  类型  |  拥有者
----------+-------+--------+----------
 public   | test1 | 数据表 | postgres
(1 行记录)
# 架构模式(schema)我们后续会讲，暂时你可以先理解为一个数据库逻辑分类的概念，默认创建数据库都会有一个public的schema

常规显示设置

# 设置显示查询时间
\timing on
# 设置border的边框内外都有
\pset border 2
# 查看编码
\encoding
# 设置编码
\encoding UTF8
# 开启扩展显示，纵向打印每列数据
\x
# 例子：
testdb=# select * from test1;
+-[ RECORD 1 ]-+
| id   | 1   |
| name | qkl |
+------+-----+

# 设置命令执行的真正sql：on打开 off关闭
\set ECHO_HIDDEN on
\set ECHO_HIDDEN off
# 案例：
testdb=# \set ECHO_HIDDEN on
testdb=# \d
********* 查询 **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' T
HEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

关联列表
+-[ RECORD 1 ]--------+
| 架构模式 | public   |
| 名称     | test1    |
| 类型     | 数据表   |
| 拥有者   | postgres |
+----------+----------+

常用命令

testdb-# \?
一般性
  \copyright            显示PostgreSQL的使用和发行许可条款
  \crosstabview [COLUMNS] 执行查询并且以交叉表显示结果
  \errverbose            以最冗长的形式显示最近的错误消息
  \g [文件] or;     执行查询 (并把结果写入文件或 |管道)
  \gexec                 执行策略，然后执行其结果中的每个值
  \gset [PREFIX]     执行查询并把结果存到psql变量中
  \gx [FILE]             as \g, but forces expanded output mode
  \q             退出 psql
  \watch [SEC]          每隔SEC秒执行一次查询

帮助
  \? [commands]          显示反斜线命令的帮助
  \? options             显示 psql 命令行选项的帮助
  \? variables           显示特殊变量的帮助
  \h [名称]          SQL命令语法上的说明，用*显示全部命令的语法说明

查询缓存区
  \e [FILE] [LINE]        使用外部编辑器编辑查询缓存区(或文件)
  \ef [FUNCNAME [LINE]]   使用外部编辑器编辑函数定义
  \ev [VIEWNAME [LINE]]  用外部编辑器编辑视图定义
  \p                    显示查询缓存区的内容
  \r                    重置(清除)查询缓存区
  \w 文件          将查询缓存区的内容写入文件

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

Conditional
  \if EXPR               begin conditional block
  \elif EXPR             alternative within current conditional block
  \else                  final alternative within current conditional block
  \endif                 end conditional block

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

格式化
  \a                  在非对齐模式和对齐模式之间切换
  \C [字符串]        设置表的标题，或如果没有的标题就取消
  \f [字符串]         显示或设定非对齐模式查询输出的字段分隔符
  \H                    切换HTML输出模式 (目前是 关闭)
  \pset [NAME [VALUE]]   set table output option
                         (NAME := {border|columns|expanded|fieldsep|fieldsep_zero|
                         footer|format|linestyle|null|numericlocale|pager|
                         pager_min_lines|recordsep|recordsep_zero|tableattr|title|
                         tuples_only|unicode_border_linestyle|
                         unicode_column_linestyle|unicode_header_linestyle})
  \t [开|关]       只显示记录 (目前是 关闭)
  \T [字符串]         设置HTML <表格>标签属性, 或者如果没有的话取消设置
  \x [on|off|auto]       切换扩展输出模式(目前是 关闭)

连接
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         连接到新数据库（当前是"testdb"）
  \conninfo              显示当前连接的相关信息
  \encoding [编码名称] 显示或设定客户端编码
  \password [USERNAME]  安全地为用户更改口令

操作系统
  \cd [目录]     更改目前的工作目录
  \setenv NAME [VALUE]   设置或清空环境变量
 \timing [开|关]       切换命令计时开关 (目前是 开启)
  \! [命令]      在 shell中执行命令或启动一个交互式shell

变量
  \prompt [文本] 名称 提示用户设定内部变量
  \set [名称 [值数]] 设定内部变量，若无参数则列出全部变量
  \unset 名称    清空(删除)内部变量

大对象
  \lo_export LOBOID 文件
  \lo_import 文件 [注释]
  \lo_list
  \lo_unlink LOBOID   大对象运算