使用PostgreSQL

分享人：杨景

PostgreSQL特性

• 表分区
• 逻辑复制
• 并行查询
• 查询JIT编译
• 丰富的数据类型：数组、JSON、XML、Hstore、network address、point、geometric等
• 用户自定义数据类型
• 丰富的数据索引类型
• Common Table Expressions：通用表表达式和递归
• 物化视图：物化视图就像普通视图那样代表一个经常使用的查询结果集，只是结果集像一个普通表那样存储在磁盘上。物化视图也可以添加索引，不像普通视图每次请求时重新生成，物化视图是及时的快照。
• FDW：Forerign Data Wrappers，外部数据包装。可通过PostgreSQL访问其它关系型数据库，如：MySQL、Oracle、MS SQL Server等（也可以访问其它Postgres数据库实例。同时，还能访问NoSQL和文件存储，如：HBase、Cassandra、MongoDB、Redis、CSV/Text、JSON等
• ……

数值类型

字符/二进制类型

日期、时间类型

JSON类型

• JSON 基本类型和相应的PostgreSQL类型

• 示例

select '[1, 2, "foo", null]'::jsonb;
select '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::jsonb;

JSON类型

jsonb与json

• 当一个 json 值被输入并且接着不做任何附加处理就输出时，json会输出和输入完全相同的文本，而jsonb则不会保留语义上没有意义的细节（例如空格）。例如，注意下面的不同：

SELECT '{"bar":    "   baz", "balance":    7.77,     "active":false}'::json;
-------------------------------------------------
{"bar":    "   baz", "balance":    7.77,     "active":false}
(1 row)

SELECT '{"bar":    "   baz", "balance":    7.77,     "active":false}'::jsonb;
--------------------------------------------------
{"bar": "   baz", "active": false, "balance": 7.77}
(1 row)

• 值得一提的一种语义上无意义的细节是，在jsonb中数据会被按照底层 numeric类型的行为来打印。实际上，这意味着用E记号 输入的数字被打印出来时就不会有该记号，例如：

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

XML类型

• 通过xmlparse函数从字符数据中生成一个xml类型

select XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>');
select XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>');
select '<foo>bar</foo>'::xml;

其它类型

PostgreSQL安装

• Docker
• 二进制安装包：Linux、Windows、Mac
• Linux软件仓库：Yum/dnf、Apt
• 第三方软件包：BigSQL（https://www.openscg.com/）
• 源码编译

使用Docker安装PostgreSQL

Dockerfile

• Docker脚本，设置系统默认字符集为zh_CN.UTF-8

FROM postgres:10.5

RUN localedef -i zh_CN -c -f UTF-8 -A /usr/share/locale/locale.alias zh_CN.UTF-8

ENV LANG zh_CN.utf8
ENV TZ Asia/Shanghai

COPY init.sql /docker-entrypoint-initdb.d/

• 使用Docker在开发环境运行Postgres是一个很好的方案，可以统一团队成员的数据库版本及运行环境。

使用Docker安装PostgreSQL

init.sql

• 数据库初始化脚本，docker run时执行些数据库初始化工作。

create user massdata with nosuperuser replication encrypted password 'Massdata.2018';
create database massdata owner = massdata template = template0 encoding = 'UTF-8' 
  lc_ctype = 'zh_CN.UTF-8' lc_collate = 'zh_CN.UTF-8';
\c massdata;
create extension adminpack;
create extension hstore;

使用Docker安装PostgreSQL

Build & Run

$ docker build -t postgres-10-dev .
$ docker run -h postgres-10-dev -p 5432:5432 --name=postgres-10-dev -d postgres-10-dev

登录数据库

$ docker run -it --rm --link=postgres-10-dev postgres-10-dev \
  psql -h postgres-10-dev -d massdata -U massdata
psql (10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.

massdata=>

DDL

• 创建表

create table t_book(
  id serial primary key,
  isbn varchar(64) not null,
  title varchar(255) not null,
  authors text[] not null,
  created_at timestamptz
);

• 创建索引

create unique index t_book_isbn_idx on t_book(title);

• 添加列

alter table t_book add column description text null;

• 查看表定义：\d t_book;

massdata=> \d t_book;
                                       Table "public.t_book"
   Column    |           Type           | Collation | Nullable |              Default               
-------------+--------------------------+-----------+----------+------------------------------------
 id          | integer                  |           | not null | nextval('t_book_id_seq'::regclass)
 isbn        | character varying(64)    |           | not null | 
 title       | character varying(255)   |           | not null | 
 authors     | text[]                   |           | not null | 
 created_at  | timestamp with time zone |           |          | 
 description | text                     |           |          | 
Indexes:
    "t_book_pkey" PRIMARY KEY, btree (id)
    "t_book_isbn_idx" UNIQUE, btree (title)

查询

massdata=> select id, isbn, title, authors from t_book;
 id |       isbn        |             title              |                    authors                    
----+-------------------+--------------------------------+-----------------------------------------------
  1 | 978-7-115-48356-0 | Scala实用指南                  | {文卡特·苏帕拉马尼亚姆}
  2 | 9787111370048     | Java并发编程                   | {"Brian Goetz","Time Peierls","Joshua Bloch"}
  3 | 9787111255833     | Effective Java中文版（第二版） | {"joshua Bloch"}
(3 rows)

数组字段的查询

• 查找作者 Joshua Bloch 写的所有书籍。

massdata=> select id, isbn, title, authors from t_book
massdata-> where 'Joshua Bloch' like any(authors);
 id |     isbn      |             title              |     authors      
----+---------------+--------------------------------+------------------
  3 | 9787111255833 | Effective Java中文版（第二版） | {"joshua Bloch"}
(1 row)

• 通过any函数来匹配数组内的任一个元素。
• 因为大小写问题，作者 Joshua Bloch 的书只找到一本。

数组字段的查询

• 通过 unnest 函数将数组字段值转换成表数据记录

massdata=> select p.id, p.isbn, p.title, p.authors from
massdata-> (select *, unnest(authors) as author from t_book) p
massdata-> where p.author like '%Bloch';
 id |     isbn      |             title              |                    authors                    
----+---------------+--------------------------------+-----------------------------------------------
  2 | 9787111370048 | Java并发编程                   | {"Brian Goetz","Time Peierls","Joshua Bloch"}
  3 | 9787111255833 | Effective Java中文版（第二版） | {"joshua Bloch"}
(2 rows)

• 通过 ilike 操作符来进行忽略大小写的字符比较

massdata=> select id, isbn, title, authors from t_book
massdata-> where 'joshua Bloch' ilike any(authors);
 id |     isbn      |             title              |                    authors                    
----+---------------+--------------------------------+-----------------------------------------------
  2 | 9787111370048 | Java并发编程                   | {"Brian Goetz","Time Peierls","Joshua Bloch"}
  3 | 9787111255833 | Effective Java中文版（第二版） | {"joshua Bloch"}
(2 rows)

JSON

• 创建 t_user 用户表

create table t_user(
  id bigserial primary key,
  username varchar(255) not null,
  attrs jsonb not null default '{}',
  created_at timestamptz not null default now()
);
create unique index t_user_username_uidx on t_user(username);

• JSON字段的插入

insert into t_user(username, attrs) values
('yangbajing', '{"nickname":"羊八井","age":32,"email":"yangbajing@gmail.com", "contacts":[{"city":"重庆","address":"渝北区金开大道西段106号10栋移动新媒体产业大厦11楼"}]}'),
('yangjing', '{"nickname":"杨景","age":32}'),
('yangjiajiang', '{"nickname":"杨家将","age":32, "contacts":[{"city":"江津","address":"重庆市江津区南门路"}]}');

• 查询

massdata=> select * from t_user;
 id |  username  |                                                                               attrs                                                                                |          created_at

----+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------
-------
  2 | yangjing   | {"age": 32, "nickname": "杨景"}                                                                                                                                    | 2018-09-03 12:00:42.274
754+00
  1 | yangbajing | {"age": 33, "email": "yangbajing@gmail.com", "contacts": [{"city": "重庆", "address": "渝北区金开大道西段106号10栋移动新媒体产业大厦11楼"}], "nickname": "羊八井"} | 2018-09-03 12:00:42.274
754+00
  3 | yangjiajiang | {"age": 32, "contacts": [{"city": "江津", "address": "重庆市江津区南门路"}], "nickname": "杨家将"}                                                                 | 2018-09-04 08:53:35.6
29468+00
(2 rows)

JSON

• 动态schema
• 不用再定义 col1，col2，col3 这样无意义的字段

通过 ->> 操作符根据 username 查询

massdata=> select id, username, attrs from t_user where attrs->>'nickname' = '杨景';
 id | username |              attrs              
----+----------+---------------------------------
  2 | yangjing | {"age": 32, "nickname": "杨景"}
(1 row)

根据jsonb字段进行检索

massdata=> select id, username, attrs from t_user where attrs @> '{"contacts":[{"city":"重庆"}]}'::jsonb;
 id |  username  |                                                                               attrs
  1 | yangbajing | {"age": 32, "email": "yangbajing@gmail.com", "contacts": [{"city": "重庆", "address": "渝北区金开大道西段106号10栋移动新媒体产业大厦11楼"}], "nickname": "羊八井"}
(1 row)

massdata=> select id, username, attrs from t_user where attrs @> '{"contacts":[{"city":"江津"}]}'::jsonb;
 id |   username   |                                               attrs
  3 | yangjiajiang | {"age": 32, "contacts": [{"city": "江津", "address": "重庆市江津区南门路"}], "nickname": "杨家将"}
(1 row)

massdata=> select id, username, attrs from t_user where attrs->'age' = '32'::jsonb;
 id |   username   |                                               attrs
  2 | yangjing     | {"age": 32, "nickname": "杨景"}
  3 | yangjiajiang | {"age": 32, "contacts": [{"city": "江津", "address": "重庆市江津区南门路"}], "nickname": "杨家将"}

JSON

jsonb索引

• GIN 索引可以被用来有效地搜索在大量jsonb文档（数据）中出现 的键或者键值对。提供了两种 GIN “操作符类”，它们在性能和灵活 性方面做出了不同的平衡。

create index t_user_attrs_idx ON t_user using gin(attrs);

• jsonb操作符

JSON

更多jsonb操作符

hstore

• hstore是Postgres的一个扩展，数据类型用来在一个单一PostgreSQL值中存储键值对。这在很多情景下都有用，例如带有很多很少被检查的属性的行或者半结构化数据。键和值都是简单的文本字符串。
• hstore的文本表示由零个或多个英文逗号来分隔key=>value对：

massdata=> select 'a=>1,a=>2,b=>null'::hstore;
       hstore
---------------------
 "a"=>"1", "b"=>NULL
(1 row)

• hstore里key是唯一的，键值对之间或者=>号周围的空白将被忽略。一个值可以是SQL NULL（不区分大小写）。

jsonb和hstore？

• 更推荐使用jsonb类型，JSON可表达的数据类型更丰富，可嵌套。

WITH查询（CTE，公共表表达式）

• WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH查询（CTE，公共表表达式）

RECURSIVE 递归

• 计算1到100的整数和

massdata=> WITH RECURSIVE t(n) AS (
    VALUES (1)
    UNION ALL
    SELECT n+1 FROM t WHERE n < 100
    )
massdata-> SELECT sum(n) FROM t;
 sum
------
 5050
(1 row)

WITH查询（CTE，公共表表达式）

找到某个组织的所有父组织

massdata=> select * from t_org;
 id |      name      | parent
----+----------------+--------
  1 | 机级组织       |
  2 | 市工商         |      1
  3 | 市公安         |      1
  4 | 市工商小微企部 |      2
(4 rows)

massdata=> with recursive tree as (
        select id, name, parent from t_org where id = 4
            union all
        select t_org.id, t_org.name, t_org.parent from t_org, tree where t_org.id = tree.parent
        ) select id, name from tree offset 1;
 id |   name
----+----------
  2 | 市工商
  1 | 机级组织
(2 rows)

WITH查询（CTE，公共表表达式）

将父组织转换成数组

create or replace function array_reverse(anyarray) returns anyarray as $$
select array(select $1 [ i ] from generate_subscripts($1, 1) as s(i) order by i desc);
$$ language 'sql' strict immutable;

massdata=> select array_reverse(array(with recursive tree as (
        select id, name, parent from t_org where id = 4
            union all
            select t_org.id, t_org.name, t_org.parent from t_org, tree where t_org.id = tree.parent
        ) select id from tree offset 1)) as parents;
 parents
---------
 {1,2}
(1 row)

从修改的行中返回数据

• INSERT、UPDATE和DELETE命令都有支持可选的RETURNING子句，使用RETURNING可以避免执行额外的数据查询来收集数据。
• 所允许的RETURNING子句的内容与SELECT命令的输出列表相同。它可以包含命令的目标表的列名，或者包含使用这些列的值表达式。一个常见的简写是RETURNING *，它按顺序选择目标表的所有列。
• 以下语句允许我们获得插入的自增ID值，获取更新后的数据和获取已删除的数据：

insert into t_org (name, parent) values ('市公安', 1) returning id;
update t_org set name = '市公安局' where id = 3 returing *;
delete from t_org where id = 3 returing *;

ON CONFLICT

• 使用on conflict可以在发生主键冲突时控制接下来的动作。我们可以选择更新已有数据，或者忽略本次操作。

massdata=> insert into t_org (id, name, parent) values (3, '市公安局', 1);
ERROR:  duplicate key value violates unique constraint "t_org_pkey"
DETAIL:  Key (id)=(3) already exists.

insertOrUpdate

massdata=> insert into t_org (id, name, parent)
        values (3, '市公安局', 1)
        on conflict (id) do update set name = EXCLUDED.name, parent = EXCLUDED.parent;
INSERT 0 1

• 使用on conflict(xxx) do特性，必需指定（复合）主键。
• 可以使用on conflict(xxx) do nothing在主键冲突时进行忽略（不返回错误也不更新数据）。
• 批量插入语句也可以使用on conflict(xxx) do特性。

insert into t_org (id, name, parent)
values (3, '市公安局', 1), (4, '市工商局小微企部', 2), (5, '市体育局', 1)
on conflict (id) do update set name = EXCLUDED.name, parent = EXCLUDED.parent;

FDW

访问其它PostgreSQL数据库

1. 使用CREATE EXTENSION来安装postgres_fdw扩展。
2. 使用CREATE SERVER创建一个外部服务器对象，它用来表示你想连接的每一个远程数据库。指定除了user和password之外的连接信息作为该服务器对象的选项。
3. 使用CREATE USER MAPPING创建一个用户映射，每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的user和password选项。
4. 为每一个你想访问的远程表使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA创建一个外部表。外部表的列必须匹配被引用的远程表。但是，如果你在外部表对象的选项中指定了正确的远程名称，你可以使用不同于远程表的表名和/或列名。

启动第二个PostgreSQL数据库

$ docker run -h postgres-10-dev2 --link=postgres-10-dev --name=postgres-10-dev2 -d postgres-10-dev
$ docker run -it --rm --link=postgres-10-dev2 postgres-10-dev psql -h postgres-10-dev2 -U postgres -d massdata

FDW

访问其它PostgreSQL数据库

massdata=# create extension postgres_fdw ;
CREATE EXTENSION
massdata=# create server foreign_server foreign data wrapper postgres_fdw options (host 'postgres-10-dev', port '5432', dbname 'massdata');
CREATE SERVER
massdata=#  create user MAPPING FOR postgres server foreign_server options (user 'postgres', password 'postgres');
CREATE USER MAPPING
massdata=# create foreign table ft_org(
          id     int          not null,
          name   varchar(128) not null,
          parent int          null
        ) server foreign_server options(schema_name 'public', table_name 't_org');
CREATE FOREIGN TABLE
massdata=# select * from ft_org ;
 id |      name      | parent
----+----------------+--------
  1 | 市级组织        |
  2 | 市工商          |      1
  3 | 市公安          |      1
  4 | 市工商小微企部   |      2
(4 rows)

FDW

访问其它PostgreSQL数据库

massdata=# \d ft_org
                         Foreign table "public.ft_org"
 Column |          Type          | Collation | Nullable | Default | FDW options
--------+------------------------+-----------+----------+---------+-------------
 id     | integer                |           | not null |         |
 name   | character varying(128) |           | not null |         |
 parent | integer                |           |          |         |
Server: foreign_server
FDW options: (schema_name 'public', table_name 't_org')

• postgres_fdw覆盖了较老的dblink模块，提供了更透明且兼容标准的语法来访问远程表，并拥有更好的性能。
• 可以使用select、insert、update和delete操作远程表。
• insert语句支持on conflict do nothing，但不支持on conflict do update。
• 建议外部表的列被声明为与被无用的远程表列完全相同的数据类型和排序规则（如果需要）。
• 与远程表的列匹配是通过名字而不是位置进行的。
• 更多内容：http://www.postgres.cn/docs/10/postgres-fdw.html

FDW

访问MySQL数据库

• PostgreSQL foregin data wrapper for MySQL

MySQL Dockerfile

FROM mysql:5.7

ENV LANG zh_CN.utf8
ENV TZ Asia/Shanghai

COPY init.sql /docker-entrypoint-initdb.d/

$ docker build -t mysql-5.7-dev .
$ docker run -p 3306:3306 --name=mysql-5.7-dev -h mysql-5.7-dev -e MYSQL_ROOT_PASSWORD=Mysql.2018 -d mysql-5.7-dev
$ docker run --rm -it --link=mysql-5.7-dev mysql-5.7-dev mysql -h mysql-5.7-dev -D massdata -u massdata -pMassdata.2018

mysql> insert into test(name, created_at) values ('MySQL', now()), ('PostgreSQL', now());
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

FDW

访问MySQL数据库

• 启动一个支持mysql_fdw扩展的PostgreSQL数据库

$ docker run --name=pgsql-fdq-mysql -h pgsql-fdq-mysql --link=mysql-5.7-dev -d geographica/postgis-fdw-mysql
$ docker run -it --rm --link=pgsql-fdq-mysql geographica/postgis-fdw-mysql psql -h pgsql-fdq-mysql -U postgres  # 密码：postgres

postgres@pgsql-fdq-mysql ~> create extension mysql_fdw;
postgres@pgsql-fdq-mysql ~> create server mysql_server foreign data wrapper mysql_fdw options(host 'mysql-5.7-dev', port '3306');
postgres@pgsql-fdq-mysql ~> create user mapping for postgres server mysql_server options(username 'massdata', password 'Massdata.2018');
postgres@pgsql-fdq-mysql ~> create foreign table ft_test(
          id bigint not null,
          name       varchar(255),
          created_at timestamp
        ) server mysql_server options(dbname 'massdata', table_name 'test');
postgres@pgsql-fdq-mysql ~> select * from ft_test;
 id |    name    |     created_at
----+------------+---------------------
  1 | MySQL      | 2018-09-05 02:07:17
  2 | PostgreSQL | 2018-09-05 02:07:17
(2 rows)

FDW

访问MySQL数据库

• mysql_fdw支持PostgreSQL 9.3, 9.4, 9.5, 9.6, 10。
• 支持连接到MySQL、MariaDB
• 支持读写
• 连接池：使用连接池优化与MySQL数据库的连接。
• where子句下推：支持将where子句下推到外部服务器上执行，只传递需要的数据行到PostgreSQL。
• column下推：支持在外部服务器筛选需要的列后再将数据传递到PostgreSQL。
• 预编译语句

表分区

• 表分区是将逻辑上的一个大表分成一些小的物理上的片

优势：

1. 在某些情况下可显著提升查询性能。
2. 当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。
3. 如果需求计划使用划分设计，可以通过增加或移除分区来完成批量载入和删除。执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE删除一个单独的分区都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。
4. 很少使用的数据可以被迁移到便宜且较慢的存储介质上。

Note:

• 当一个表非常大时，划分所带来的好处是非常值得的。一个表何种情况下会从划分获益取决于应用，一个经验法则是当表的尺寸超过了数据库服务器物理内存时，划分会为表带来好处。
• 分区可以有自己的索引、约束和默认值，与其他分区不同。 索引必须为每个分区单独创建。
• 分区的分区键范围不能重叠。

分区形式：

1. 范围分区：该表被分区到由键列或列集定义的“范围”中，分配给不同分区的值范围之间没有重叠。例如，可以按日期范围进行分区，也可以按特定业务对象的标识符范围进行分区。
2. 列表分区：表通过明确列出每个分区中出现的键值进行分区。

表分区

范围分区

创建分区表和子表：

create table t_log(
  id uuid not null,
  content text not null,
  created_at timestamp not null
) partition by range(created_at);

create table t_log_his    partition of t_log for values from ('1970-01-01') to ('2018-01-01');
create table t_log_201801 partition of t_log for values from ('2018-01-01') to ('2018-02-01');
create table t_log_201802 partition of t_log for values from ('2018-02-01') to ('2018-03-01');
create table t_log_201803 partition of t_log for values from ('2018-03-01') to ('2018-04-01');

插入数据：

insert into t_log(id, content, created_at) values
(uuid_generate_v4(), 'TRACE', '2017-12-11'),
(uuid_generate_v4(), 'DEBUG', '2018-01-11'),
(uuid_generate_v4(), 'INFO', '2018-02-11'),
(uuid_generate_v4(), 'WARN', '2018-03-11');

表分区

范围分区

插入分区范围之外的数据会报错：

massdata=> insert into t_log(id, content, created_at) values (uuid_generate_v4(), 'ERROR', '2018-04-11');
ERROR:  no partition of relation "t_log" found for row
DETAIL:  Partition key of the failing row contains (created_at) = (2018-04-11 00:00:00).

通过explain显示执行计划，可以看到分别从4张子表内获取的数据：

massdata=> explain select * from t_log;
                              QUERY PLAN
----------------------------------------------------------------------
 Append  (cost=0.00..78.80 rows=3880 width=56)
   ->  Seq Scan on t_log_201801  (cost=0.00..19.70 rows=970 width=56)
   ->  Seq Scan on t_log_201802  (cost=0.00..19.70 rows=970 width=56)
   ->  Seq Scan on t_log_201803  (cost=0.00..19.70 rows=970 width=56)
   ->  Seq Scan on t_log_his  (cost=0.00..19.70 rows=970 width=56)
(5 rows)

表分区

添加分区

• 增加一个分区

create table t_log_201804 partition of t_log for values from ('2018-04-01') to ('2018-05-01');

• 附加分区

alter table t_log attach partition t_log_201804 for values from ('2018-04-01') to ('2018-05-01');

删除分区

• 直接删除分区

drop table t_log_201804;

• 分离分区

alter table t_log detach partition t_log_201804;

表分区

massdata=> \d+ t_log;
                                              Table "public.t_log"
   Column   |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 id         | uuid                     |           | not null |         | plain    |              |
 content    | text                     |           | not null |         | extended |              |
 created_at | timestamp with time zone |           | not null |         | plain    |              |
Partition key: RANGE (created_at)
Partitions: t_log_201801 FOR VALUES FROM ('2018-01-01 00:00:00+00') TO ('2018-02-01 00:00:00+00'),
            t_log_201802 FOR VALUES FROM ('2018-02-01 00:00:00+00') TO ('2018-03-01 00:00:00+00'),
            t_log_201803 FOR VALUES FROM ('2018-03-01 00:00:00+00') TO ('2018-04-01 00:00:00+00'),
            t_log_his FOR VALUES FROM ('1970-01-01 00:00:00+00') TO ('2018-01-01 00:00:00+00')

虽然在PG里存储日志不是一个好的设计，但使用分区表来进行日志存储也不失为一个可行的方案。

Backup

任务系统都有崩溃的可能，数据库备份工作的重要性毋庸置疑。

支持的备份方式

• 增量备份：WAL归档日志。
• SQL转储：pg_dump、pg_dumpall。
• 文件系统备份：停机冷备，直接将数据目录打包进行备份。

Backup

pg_dump

• 文本格式备份

pg_dump -Fp -c --insert --column-inserts massdata > massdata.sql

当使用文本格式并导出为insert插入语句时，应同时指定--column-inserts参数来明确指定列的名称，不然当字段顺序变化或有新的列时可能不能正确恢复数据

• 自定义格式备份

pg_dump -Fc -c massdata > massdata.dat

Recovery

文本格式恢复

psql -h localhost -U massdata -d massdata < massdata.sql

自定义格式恢复

pg_restore -h localhost -U massdata -d massdata massdata.dat

JDBC

PostgreSQL支持JDBC 4协议，JDBC URL连接示例如下：

jdbc:postgresql://host:port/database

• 通过Properties获取连接

String url = "jdbc:postgresql://localhost/massdata";
Properties props = new Properties();
props.setProperty("user","massdata");
props.setProperty("password","Massdata.2018");
props.setProperty("ssl","true");
Connection conn = DriverManager.getConnection(url, props);

• 通过JDBC URL获取连接

String url = "jdbc:postgresql://localhost/massdata?user=massdata&password=Massdata.2018&ssl=true";
Connection conn = DriverManager.getConnection(url);

• Spring Boot中配置PostgreSQL数据源

spring.datasource.name=main
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.jdbc-url=jdbc:postgresql://localhost:5432/massdata
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=massdata
spring.datasource.password=Massdata.2018

JDBC

数据类型

JDBC

使用Java 8日期和时间类型

需要使用JDBC 4.2才支持

读

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
while (rs.next())
{
    System.out.print("Column 1 returned ");
    LocalDate localDate = rs.getObject(1, LocalDate.class));
    System.out.println(localDate);
}
rs.close();
st.close();

写

LocalDate localDate = LocalDate.now();
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, localDate);
st.executeUpdate();
st.close();

JDBC

数组

PostgresQL提供了两种使用数组类型的方式。

• 通过java.sql.Connection.createArrayOf(String, Object[]) 创建对象数组，使用PreparedStatement.setArray(int, Array)设置值。
• 通过PreparedStatement.setObject方法直接设置数组，支持以下Java数组的自动映射：

Answer

羊八井

www.yangbajing.me

weibo.com/yangbajing

yangbajing at gmail com