PostgreSQL 12 High Availability Cookbook

How to do it…

Assuming we have a PostgreSQL 10 provider node that has pglogical installed, follow these steps:

1. Produce an unlogged table of all the schemas that should be transferred. Use a query like this to generate an initial list:

CREATE UNLOGGED TABLE schema_list ASSELECT nspname  FROM pg_namespace WHERE nspname NOT LIKE 'pg\_%'   AND nspname NOT IN ('information_schema', 'pglogical');

2. Audit the resulting table of schemas and remove any subsequent schemas that should not be copied.
3. Produce a list of tables that do not have a primary key using the following query:

CREATE UNLOGGED TABLE table_list ASSELECT n.nspname AS schema_name,       t.relname as table_name,       c.conname IS NOT NULL AS has_primary_key  FROM pg_class t JOIN pg_namespace n ON ...