将Postgresql和Elasticsearch进行数据同步以加速查询_es 和postgre 同步

(本文内容适用于elasticsearch7.x,postgresql11)
受命要求优化一个1500w行的表的查询优化，该表中的数据按天update，所以尽管每天vacuum，但是因为空间并没有实际被释放，查询仍然很慢，这下靓仔怎么办？

一开始想到的方法是进行表分区，按日分区，同时修改数据格式减少update，但是一是附加的代码改动过多，二是实际跑了几个测试数据优化效果只有三分之一。不爽啊，这不是一次合格的优化。

然后想起以前做游戏分发业务的时候，把所有接口数据弄成现成的格式直接从redis进行返回，数据的统计，格式化等等过程在django的signal过程内进行。我现在的项目算是个flask项目，虽然并没使用任何orm，但是这也为我提供了一种思路:把postgresql里的数据用某种方式同步到redis或者类似的玩意儿里。最终经过一系列的调研，选定了elasticsearch。
这段时间里主要靠以下几个文档作为出发点（以下内容适用于elasticsearch2.x）：

https://www.jianshu.com/p/629f698a7c58
https://yq.aliyun.com/articles/56824#modal-login

针对pg11/es7 上述过程已经有些不使用，比如pg-es-fdw已经被归档，而它并不兼容于es7,需要使用其他人fork并迭代的版本:
postgres-elasticsearch-fdw (https://github.com/matthewfranglen/postgres-elasticsearch-fdw)
而它的依赖urllib3在python2(必须大于2.7才能安装)环境里安装的时候如果使用setup.py安装则会因为setuptool版本的问题无法安装，我尝试了这个方法并且提交了issue和pr，最后被拒了,大概是因为我的个例，很蠢，setuptool的迷之配置（https://github.com/urllib3/urllib3/issues/1753），但是我在如此修改安装文件之后成功地安装了相关的包。

正式开始：

0：环境：CentOS Linux release 7.6.1810 (Core)
首先需要安装：
1、PostgreSQL我的版本是11
2、elasticsearch我用的7.4 按照官网的攻略安装就可以
3、安装multicorn:
https://github.com/Kozea/Multicorn
在centos里安装不需要上边链接里花里胡哨的操作，那个是在mac上安装的
4、在pg里新建extension: multicorn，在你想要优化的表所在的schema里创建：
create extension multicorn;
5、安装postgresql插件postgresql-elasticsearch-fdw,上边链接里的pg-es-fw不适用用es7
https://github.com/matthewfranglen/postgres-elasticsearch-fdw

6、在你想要优化的表所在的schema里创建foreign server:

CREATE SERVER multicorn_es FOREIGN DATA WRAPPER multicorn
OPTIONS (
  wrapper 'pg_es_fdw.ElasticsearchFDW'
);
1
2
3
4

7、如果是新表，或者你打算试试，可以先建个测试表：

CREATE TABLE articles (
    id serial PRIMARY KEY,
    title text NOT NULL,
    content text NOT NULL,
    created_at timestamp
);
1
2
3
4
5
6

然后创建对应的外表:

CREATE FOREIGN TABLE articles_es (
    id bigint,
    title text,
    content text
) SERVER multicorn_es OPTIONS (host '127.0.0.1', port '9200', node 'test', index 'articles');
1
2
3
4
5

外表对应的index就是es里你的index。外表的字段应当与你想要缓存的字段相同，涉及触发器内的操作。host，port，node，index分别对应es的连接信息，index应该在es里提前建好。

8、然后在你想要优化的表所在的schema里创建触发器:

```sql
CREATE OR REPLACE FUNCTION schema.index_article() RETURNS trigger AS $def$
    BEGIN
        INSERT INTO schema.articles_es (id, title, content) VALUES
            (NEW.id, NEW.title, NEW.content);
        RETURN NEW;
    END;
$def$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION schema.reindex_article() RETURNS trigger AS $def$
    BEGIN
        UPDATE schema.articles_es SET
            title = NEW.title,
            content = NEW.content
        WHERE id = NEW.id;
        RETURN NEW;
    END;
$def$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION schema.delete_article() RETURNS trigger AS $def$
    BEGIN
        DELETE FROM schema.articles_es a WHERE a.id = OLD.id;
        RETURN OLD;
    END;
$def$ LANGUAGE plpgsql;

CREATE TRIGGER schema.es_insert_article
    AFTER INSERT ON schema.articles
    FOR EACH ROW EXECUTE PROCEDURE schema.index_article();

CREATE TRIGGER schema.es_update_article
    AFTER UPDATE OF title, content ON schema.articles
    FOR EACH ROW
    WHEN (OLD.* IS DISTINCT FROM NEW.*)
    EXECUTE PROCEDURE schema.reindex_article();

CREATE TRIGGER schema.es_delete_article
    BEFORE DELETE ON schema.articles
    FOR EACH ROW EXECUTE PROCEDURE schema.delete_article();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

9、然后在es里创建你的索引：
curl -H "Content-Type:application/json" -X PUT 'http://localhost:9200/articles/' -d '{}'

10、然后就是相关增删改查的测试操作了，以下不再赘述，记得把之前的表数据往es里传一份。以后读写es的表就可以了

我在其中遇到的一些问题：
python报错：’hit‘
就介仨字母。反映在运行的sql语句里。说明没有从es取到数据
咋办呢？安装postgresql-elasticsearch-fdw以适应es7,es最坑的就是版本。

查询es返回404:
es索引建错了 curl一下索引地址看看。

对这个方法的疑问：
触发器机制的可靠性和性能

发现的问题：当es崩掉的时候，所有的查询都会直接报错，

下一个方法：放弃pg-fdw扩展，使用python分别操作postgresql和es