比较

1、 why favor postgresql over mariadb mysql （中文翻译）

基础概念

角色与用户

对于 PostgreSQL 来说，这是完全相同的两个对象。唯一的区别是在创建的时候，USER 除了默认具有 LOGIN 权限之外，其他与 ROLE 是完全相同的。

数据库与模式

• 模式（schema）是对数据库（database）逻辑分割。一个数据库至少有一个模式。
• 在数据库创建的同时，就已经默认为数据库创建了一个模式 public，这也是该数据库的默认模式。
• 所有为此数据库创建的对象（表、函数、试图、索引、序列等）都是在这个模式中的。

创建模式

-- 创建一个新模式 kanon，对应于登录用户 kanon
CREATE SCHEMA kanon OWNER kanon;
-- 再次创建一张 test 表，指明使用模式
CREATE TABLE kanon.test (id integer not null);
-- 查看所有模式
\dn;
-- 查看表所对应的模式
\d;

用户登录系统，连接到一个数据库后，是通过该数据库的 search_path 来寻找 schema 的搜索顺序

-- 查看模式的顺序
SHOW search_path;
-- 修改顺序
SET search_path TO 'schema_name'

官方建议是这样的：在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后，将 search_path 设置为 “$user”，这样，任何当某个用户连接上来后，会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

数据库与表空间

默认的数据库所有者是当前创建数据库的角色，默认的系统表空间是 pg_default。

在 PostgreSQL 中，数据库的创建是通过克隆数据库模板来实现的，这与 SQL SERVER 是同样的机制。

由于 CREATE DATABASE dbname 并没有指明数据库模板，所以系统将默认克隆 template1 数据库，得到新的数据库 dbname。而 template1 数据库的默认表空间是 pg_default，这个表空间是在数据库初始化时创建的，所以所有 template1 中的对象将被同步克隆到新的数据库中。

-- 相对完整的语法。
CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;
-- 创建表空间，应确保目录 /tmp/data/tskanon 存在，并且目录为空。
CREATE TABLESPACE tskanon OWNER kanon LOCATION '/tmp/data/tskanon';
-- 创建一个数据库，指明该数据库的表空间。
CREATE DATABASE dbkanon TEMPLATE template1 OWNERE kanon TABLESPACE tskanon;

总结

总结一下这张关系网，表空间是一个存储区域，在一个表空间中可以存储多个数据库，尽管 PostgreSQL 不建议这么做，但我们这么做完全可行。

一个数据库并不知道直接存储表结构等对象的，而是在数据库中逻辑创建了至少一个模式，在模式中创建了表等对象，将不同的模式指派该不同的角色，可以实现权限分离，又可以通过授权，实现模式间对象的共享，并且，还有一个特点就是：public 模式可以存储大家都需要访问的对象。

这样，我们的网就形成了。可是，既然一个表在创建的时候可以指定表空间，那么，是否可以给一个表指定它所在的数据库表空间之外的表空间呢？

答案是肯定的！这么做完全可以：那这不是违背了表属于模式，而模式属于数据库，数据库最终存在于指定表空间这个网的模型了吗？！

是的，看上去这确实是不合常理的，但这么做又是有它的道理的，而且现实中，我们往往需要这么做：将表的数据存在一个较慢的磁盘上的表空间，而将表的索引存在于一个快速的磁盘上的表空间。

但我们再查看表所属的模式还是没变的，它依然属于指定的模式。所以这并不违反常理。实际上，PostgreSQL 并没有限制一张表必须属于某个特定的表空间，我们之所以会这么认为，是因为在关系递进时，偷换了一个概念：模式是逻辑存在的，它不受表空间的限制。

使用

初始化

初始化（用 brew 安装，已经初始化好了默认库）

initdb /usr/local/var/postgres

创建用户，这一步必须做，否则会报：role "postgres" does not exist

/usr/local/opt/postgres/bin/createuser -s postgres

login/sign out from command line tool

psql -U postgres -d postgres -h 127.0.0.1
\q (or ctrl + D)

create user, database and grant all privileges

create user username with password 'password';
create database dbname owner username;
grant all privileges on database dbname to username;

Export & Import

# To dump a database called projection into a SQL-script file:
$ pg_dump -h localhost -d projection -U hiway > projection.sql
# To load a SQL-script file into a database called projection:
$ pg_dump -h localhost -d projection -U hiway < projection.sql

# To dump a database with gzip compression:
$ pg_dump -h localhost -d projection -U hiway | gzip > /projection.gz
# To load a gzip file into a database:
$ gunzip -c /projection.gz | psql -U hiway -d projection

# 分片备份
pg_dump -h localhost -d projection -U hiway | split -b 100k - /vend/vend
cat /vend/vend* | psql -U hiway -d projection

# To reload such a script into a (freshly created) database named newdb:
$ psql -d newdb -f db.sql

# To dump a database into a custom-format archive file:
$ pg_dump -Fc mydb > db.dump

# To dump a database into a directory-format archive:
$ pg_dump -Fd mydb -f dumpdir

# To reload an archive file into a (freshly created) database named newdb:
$ pg_restore -d newdb db.dump

From the pg_restore documentation:

Examples

Assume we have dumped a database called mydb into a custom-format dump file:

$ pg_dump -Fc mydb > db.dump
To drop the database and recreate it from the dump:

$ dropdb mydb
$ pg_restore -C -d postgres db.dump

数据库配置

listen remote connections

# update file postgresql.conf
listen_addresses = '*'

add connection method

# add a remote user information in pg_hba.conf
# for example:
host	all		postgres	192.168.1.58/32		trust

query postgresql variables

show shared_buffers;
show all;

客户端字符编码

-- 查看服务端字符编码
show server_encoding;
-- 查看客户端字符编码
show client_encoding;
-- 设置临时客户端字符编码，推出后会还原
set client_encoding to 'SQL_ASCII';

时区

-- 查看时间
postgres=# select now();
-- 查看时区
postgres=# show time zone;
-- 查看数据库可供选择的时区
postgres=#  select * from pg_timezone_names;
-- 设置时区（通过这种方式设置时区在你退出 psql 终端后，即恢复到之前的时区）
postgres=# set time zone "Asia/Shanghai";

永久修改时区：

$ sudo cat /var/lib/pgsql/9.6/data/postgresql.conf|grep timezone
log_timezone = 'PRC'
timezone = 'PRC'

将配置文件的这两个变量的值设置成自己想要的时区（PRC指：People’s Republic of China）

然后重新加载即可：

$ pg_ctl reload

基础操作

提示符

mydb=>		-- 普通用户提示符
mydb=#		-- 超级用户提示符

一些常用函数和属性

SELECT version();		-- 查看数据库版本
SELECT current_date;	-- 查看当前日期
SELECT current_time;	-- 查看当前时间

一些内部命令

\q		-- 退出数据库
\?		-- 查询所有内部命令
\h		-- 查询 SQL 命令的帮助语法
\password  -- 修改密码

单步模式

$ psql -s mydb
...
mydb=> \i basics.sql

psql 的 -s 选项把你置于单步模式，它在向服务器发送每个语句之前暂停。 \i 命令从指定的文件中读取命令。

特殊

在 Pg 中，::是指将前面的内容强制转换为:: 后面所指定的数据类型，以满足一些查询或是函数对数据类型的要求。

SQL 语言

COPY

COPY weather FROM '/home/user/weather.txt';

使用 COPY 从文本文件中装载大量数据。这种方式通常更快，因为 COPY 命令就是为这类应用优化的， 只是比 INSERT 少一些灵活性。

DISTINCT

在一些数据库系统里，包括老版本的 PostgreSQL，DISTINCT 的实现自动对行进行排序，因此 ORDER BY 是多余的。但是这一点并不是 SQL 标准的要求，并且目前的 PostgreSQL 并不保证 DISTINCT 会导致行被排序。

数据类型

时间类型

几个特殊的日期和时间

字符类型

数值类型

JSON 类型

PostgreSQL 支持两种 json 数据类型：json 和 jsonb，而两者唯一的区别在于效率。json 是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而 jsonb 是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。

两者对重复键的处理都是保留最后一个键值对。效率的差别：json 类型存储快，使用慢，jsonb 类型存储稍慢，使用较快。

Extension

Install extension

-- 查询扩展
\dx
-- 安装扩展
CREATE EXTENSION pgcrypto;
-- 生成 UUID
SELECT gen_random_uuid();

pg_stat_statements - 资源统计分析插件

为能够从各个维度层面查找最消耗数据库资源的 SQL，使用 pg_stat_statements 插件统计数据库的资源开销和分析 Top SQL。

类似的工具还有：pgstatsinfo, pgsnap, pgtop, pgfouine 等。

本文将通过示例介绍如何创建 pg_stat_statements 插件、如何分析 Top SQL 以及如何重置统计信息。

# 安装 pg_stat_statements 插件
CREATE EXTENSION pg_stat_statements;

输出内容介绍

通过查询 pg_stat_statements 视图，您可以得到数据库资源开销的统计信息。SQL 语句中的一些过滤条件在 pg_stat_statements 中会被替换成变量，可以减少重复显示的问题。

pg_stat_statements 视图包含了一些重要信息，例如：

• SQL 的调用次数，总耗时，最快执行时间，最慢执行时间，平均执行时间，执行时间的方差（看出抖动），总共扫描、返回或处理了多少行。
• shared buffer 的使用情况：命中、未命中、产生脏块、驱逐脏块。
• local buffer 的使用情况：命中、未命中、产生脏块、驱逐脏块。
• temp buffer 的使用情况：读了多少脏块、驱逐脏块。
• 数据块的读写时间。

下表列出了 pg_stat_statements 输出内容中各参数的含义。

分析 TOP SQL

• 最耗 IO SQL

  • 执行如下命令，查询单次调用最耗 IO SQL TOP 5。

    SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time)/calls DESC LIMIT 5;
    

  • 执行如下命令，查询总最耗 IO SQL TOP 5。

    SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time) DESC LIMIT 5;
    

• 最耗时 SQL

  • 执行如下命令，查询单次调用最耗时 SQL TOP 5。

    SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5;
    

  • 执行如下命令，查询总最耗时 SQL TOP 5。

    SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
    

• 响应时间抖动最严重 SQL

  执行如下命令，查询响应时间抖动最严重 SQL。

  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY stddev_time DESC LIMIT 5;
  

• 最耗共享内存 SQL

  执行如下命令，查询最耗共享内存 SQL。

  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (shared_blks_hit+shared_blks_dirtied) DESC LIMIT 5;
  

• 最耗临时空间 SQL

  执行如下命令，查询最耗临时空间 SQL。

  SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY temp_blks_written DESC LIMIT 5;
  

重置统计信息

pg_stat_statements 是累积的统计，如果要查看某个时间段的统计，需要查询快照的信息，详情请参见《PostgreSQL AWR报告（for 阿里云ApsaraDB PgSQL）》。

可以通过执行如下命令，来定期清理历史统计信息。

SELECT pg_stat_statements_reset();

##