PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

All SQL statements in this recipe should be executed by the postgres database user. Follow these steps to build a table to map logical shards to their physical locations:

1. Execute the following SQL statement to create the shard-mapping table:

CREATE TABLE shard.shard_map 
( 
  map_id         SERIAL   PRIMARY KEY, 
  shard_id       INT      NOT NULL, 
  source_schema  VARCHAR  NOT NULL, 
  shard_schema   VARCHAR  NOT NULL, 
  server_name    VARCHAR  NOT NULL, 
  UNIQUE (shard_id, source_schema) 
); 

2. Create a shard and register it with the shard map using the following SQL:

CREATE SCHEMA myapp1;
INSERT INTO shard.shard_map
  (shard_id, source_schema, shard_schema, server_name)
VALUES (1, 'myapp', 'myapp1', 'pg-primary');

3. Repeat the previous step to create a second shard: ...