如何横向扩展 PostgreSQL?

$ psql -h /tmp -p 6001 postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.6001" failed: FATAL:  the database system is not accepting connections
DETAIL:  Hot standby mode is disabled.

hot_standby = on

$ psql -h /tmp -p 6001 postgres psql (15.1 (Debian 15.1-1.pgdg110+1)) Type "help" for help. postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row)

# add this to your standby to make it lag behind the primary by 4 hours
recovery_min_apply_delay = '4h'

postgres=# create table tbl1 (a int primary key);
CREATE TABLE

postgres=# insert into tbl1 (a) select generate_series(1, 100);
INSERT 0 100

postgres=# create publication pub1 for table tbl1;
CREATE PUBLICATION

postgres=# create table tbl1 (a int primary key);
CREATE TABLE

postgres=# create subscription sub1 connection 'host=/tmp port=6000 dbname=postgres' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

postgres=# select count(*) from tbl1;
 count
-------
   100
(1 row)

-- include all tables present at time of creation of publication
create publication pub2 for all tables;

-- include only specified list of tables
create publication pub3
    for table tbl3, tbl4, tbl5;

-- Postgres v15+ only
create publication pub4
    for tables in schema schema1, schema2;

postgres=# create table tbl3 (a int primary key, b text, c text);
CREATE TABLE

postgres=# create publication pub3 for table tbl3 (a, c);
CREATE PUBLICATION

postgres=# insert into tbl3 (a, b, c) values (1, 'hello', 'world');
INSERT 0 1

postgres=# create table tbl3 (a int primary key, c text);
CREATE TABLE

postgres=# create subscription sub3 connection 'host=/tmp port=6000 dbname=postgres' publication pub3;
NOTICE:  created replication slot "sub3" on publisher
CREATE SUBSCRIPTION

postgres=# select * from tbl3;
 a |   c
---+-------
 1 | world
(1 row)

postgres=# create table tbl4 (a int primary key, b text);
CREATE TABLE

postgres=# create publication pub4 for table tbl4 with (publish = 'insert, update');
CREATE PUBLICATION

postgres=# select * from tbl4;
 a | b
---+---
(0 rows)

postgres=# create table tbl4 (a int primary key, b text);
CREATE TABLE

postgres=# create subscription sub4 connection 'host=/tmp port=6000 dbname=postgres' publication pub4;
NOTICE:  created replication slot "sub4" on publisher
CREATE SUBSCRIPTION
然后让我们在发布者上插入、更新和删除一些行：

postgres=# select * from tbl4;
 a | b
---+---
(0 rows)

postgres=# insert into tbl4 (a, b) values (1, 'jello');
INSERT 0 1
postgres=# insert into tbl4 (a, b) values (2, 'world');
INSERT 0 1
postgres=# update tbl4 set b='hello' where a=1;
UPDATE 1
postgres=# delete from tbl4 where a=2;
DELETE 1
postgres=# select * from tbl4;
 a |   b
---+-------
 1 | hello
(1 row)

postgres=# select * from tbl4;
 a |   b
---+-------
 2 | world
 1 | hello
(2 rows)

synchronous_commit = remote_write
synchronous_standby_names = mystandby1

synchronous_commit = remote_apply
synchronous_standby_names = mystandby1

synchronous_commit = remote_write
synchronous_standby_names = ANY 2 (mystandby1, mystandby2, mystandby3)

BEGIN;
SET synchronous_commit = 'remote_write';
-- use the global setting for synchronous_standby_names
-- update data here
COMMIT;

srcdb=# create table srct (a int primary key);
CREATE TABLE

srcdb=# insert into srct (a) select generate_series(1, 100);
INSERT 0 100

destdb=# create extension postgres_fdw;
CREATE EXTENSION

destdb=# create server src foreign data wrapper postgres_fdw options (host '/tmp', port '6000', dbname 'srcdb');
CREATE SERVER

destdb=# create user mapping for current_user server src;
CREATE USER MAPPING

destdb=# import foreign schema public limit to (srct) from server src into public;
IMPORT FOREIGN SCHEMA

destdb=# select count(*) from srct;
 count
-------
   100
(1 row)

destdb=# create table destt (b int primary key, c text);
CREATE TABLE

destdb=# insert into destt (b,c) values (10,'foo'), (20,'bar');
INSERT 0 2

destdb=# select a,b,c from srct join destt on srct.a = destt.b;
 a  | b  |  c
----+----+-----
 10 | 10 | foo
 20 | 20 | bar
(2 rows)

destdb=# create materialized view destmv as select a,b,c from srct join destt on srct.a = destt.b;
SELECT 2

destdb=# select * from destmv;
 a  | b  |  c
----+----+-----
 10 | 10 | foo
 20 | 20 | bar
(2 rows)

destdb=# CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE

destdb=# CREATE TABLE measurement_y2023
PARTITION OF measurement
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE

destdb=# CREATE FOREIGN TABLE measurement_y2022
PARTITION OF measurement
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01')
SERVER src;
CREATE FOREIGN TABLE

srcdb=# CREATE TABLE measurement_y2022 (
    city_id         int not null,
    logdate         date not null
        CHECK (logdate >= '2022-01-01' and logdate <= '2023-01-01'),
    peaktemp        int,
    unitsales       int
);
CREATE TABLE

destdb=# insert into measurement (city_id, logdate, peaktemp, unitsales)
values (1, '2022-01-03', 66, 100), (1, '2023-01-03', 67, 300);
INSERT 0 2

destdb=# select * from measurement;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2022-01-03 |       66 |       100
       1 | 2023-01-03 |       67 |       300
(2 rows)

destdb=# explain select * from measurement;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Append  (cost=100.00..219.43 rows=3898 width=16)
   ->  Foreign Scan on measurement_y2022 measurement_1  (cost=100.00..171.44 rows=2048 width=16)
   ->  Seq Scan on measurement_y2023 measurement_2  (cost=0.00..28.50 rows=1850 width=16)
(3 rows)