利用PostgreSQL外部表实现数据库sharding

最新推荐文章于 2024-08-01 18:47:20 发布

weixin_34192816

最新推荐文章于 2024-08-01 18:47:20 发布

阅读量391

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/tianbing/blog/1031105

版权

本文介绍了如何利用PostgreSQL的postgres_fdw功能和外部表继承特性实现跨数据库的分片（sharding）。通过创建外部表、定义继承关系和规则，将数据分散到多个物理节点，从而实现数据库的水平扩展。然而，这种方法在查询性能上存在不足，全表扫描可能导致性能下降，需要进一步的查询优化。

摘要由CSDN通过智能技术生成

2019独角兽企业重金招聘Python工程师标准>>>

前言

PostgreSQL9.3开始推出postgres_fdw功能，9.5又实现了外部表可以继承的特性。那么我们就可以像实现分区表一样，利用"外部表继承+约束+规则"来实现数据库的sharding。

原理

传统的普通表之间的继承特性可以实现数据库级别的表分区，而外部表可以继承普通表的特性，则可以实现跨数据库，甚至跨物理节点的表分区。下图是实现数据库sharding的一个逻辑图。

应用

测试场景有4台机器，一台作为本地机用于管理访问外部节点分区，另外3台作为存储表分区的机器。

1. 先在3台外部节点机器上，创建3个表结构相同的普通表，并添加相关约束。

postgres=# create table test_partition_127(id int check (id >= 1 AND id < 100), tt text, tm timestamp);
CREATE TABLE

postgres=# create table test_partition_136(id int check (id >= 100 AND id < 200), tt text, tm timestamp);
CREATE TABLE

postgres=# create table test_partition_144(id int check (id >= 200 AND id < 300), tt text, tm timestamp);
CREATE TABLE

表名后面的数字127,136,144表示节点IP的后三位。

2. 为了让本地机器可以访问外部节点，还需要对这3个外部节点的配置文件postgresql.conf，pg_hba.conf配置一下。

下面操作都是在本地机上进行。

3. 先创建postgres_fdw扩展。

postgres=# create extension postgres_fdw;
CREATE EXTENSION

4. 创建可以映射到外部节点普通表的外部表。

postgres=# CREATE SERVER server_remote_127 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host '192.168.100.127', port '5432', dbname 'postgres');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR postgres SERVER server_remote_127 OPTIONS(user 'highgo');
CREATE USER MAPPING
postgres=# CREATE FOREIGN TABLE test_partition_foreign_127(id int, tt text, tm timestamp) SERVER server_remote_127 OPTIONS(schema_name 'public', table_name 'test_partition_127');
CREATE FOREIGN TABLE
postgres=# 
postgres=# CREATE SERVER server_remote_136 FOREIGN DA