How to rename a schema and remove dependencies on initial schema (PostgreSQL) during data migration?

Question

PostgreSQL 11.1

I am moving my development "database" into its first "real" beta testing environment. The initial schema, the one to which the currently running applications rely on, is NOVA. After several years of development, the new schema--the one which the new applications runs on-- is PHOENIX.

During development (over two years), the NOVA schema was modified with tables, constraints, triggers, etc.., being modified, dropped, and added. However, the schema name remained as NOVA.

Here is what I did:

Installed the newest version of PostgreSQL, 11.1.
Used Pg_Dump to get the current data of the working NOVA database and then Pg_restore to bring that schema and data into PostgreSQL 11.1.
RENAMED THE NOVA SCHEMA IN THE DEVELOPMENT DATABASE TO PHOENIX. This to not conflict with the initial (working) nova schema.
Used Pg_dump followed by pg_restore to bring the new Phoenix schema (with the development data) into PostgreSQL 11.1.

Now, my general plan, (suggestions are very welcome!), is to first update the tables in PHOENIX WITH NO FOREIGN KEYS from the data in the currently used NOVA schema. And, in general, to proceed from the tables with the fewest foreign keys to the tables with the most foreign keys.

So, to answer the basic question of number of foreign keys, I am starting with this (executed in Pg_Admin 4 v4.1) (Get Table Foreign Keys):

SET search_path TO Phoenix;

SELECT

    current_schema() AS "schema",
    current_catalog AS "database",
    "pg_constraint".conrelid::regclass::text AS "primary_table_name",
    "pg_constraint".confrelid::regclass::text AS "foreign_table_name",

    (
        string_to_array(
            (
                string_to_array(
                    pg_get_constraintdef("pg_constraint".oid),
                    '('
                )
            )[2],
            ')'
        )
    )[1] AS "foreign_column_name",

    "pg_constraint".conindid::regclass::text AS "constraint_name",

    TRIM((
        string_to_array(
            pg_get_constraintdef("pg_constraint".oid),
            '('
        )
    )[1]) AS "constraint_type",

    pg_get_constraintdef("pg_constraint".oid) AS "constraint_definition"

FROM pg_constraint AS "pg_constraint"

JOIN pg_namespace AS "pg_namespace" ON "pg_namespace".oid = "pg_constraint".connamespace

WHERE
    --fkey and pkey constraints
    "pg_constraint".contype IN ( 'f', 'p' )
    AND
    "pg_namespace".nspname = current_schema()
    -- AND
    -- "pg_constraint".conrelid::regclass::text IN ('whatever_table_name')

The resulting table looks like (how to format this as a table in SO?):

Schema database primary_table_name foreign_table_name foreign_column_name contraint_name contraint_type constraint_definition name name text text text text text text

"phoenix" "chaos" "nova.referral_details" "nova.orders" "orderno" "nova.orderno_unique" "FOREIGN KEY" "FOREIGN KEY (orderno) REFERENCES nova.orders(orderno) ON UPDATE CASCADE ON DELETE RESTRICT"

The important point being that although the schema was renamed from NOVA to PHOENIX, it seems that the contraints, triggers, etc... are still referencing the development NOVA tables!

So, how do I fix the PHOENIX schema to show NO dependencies on NOVA? (or, is there a better way to move new data on an old schema to a new schema without altering the old schema?)

Any and all help with this is most appreciated.

Note: The overall goal here is to allow the currently running front-end applications to run undisturbed with the NOVA schema while the newer applications can be beta tested with the new PHOENIX schema.

How to rename a schema and remove dependencies on initial schema (PostgreSQL) during data migration?

0 Answers0