PostgreSQL High Availability Cookbook - Second 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 this 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 with this 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:

        CREATE ...