0

I am trying to copy a PostgreSQL schema and it's data from one database to another without impacting the availability of the current schema (old_schema). I would also like to do this for a specific subset of tables within the schema, and want the new schema to have a different name in the other database.

I am using Python for steps 1. and 2. in the following procedure,

  1. Get a list of table names from old_schema that I want to copy.
    select
        distinct
        information_schema.columns.table_name as table_name
    from
        information_schema.columns
    where
        information_schema.columns.table_schema = 'public'
        and
        information_schema.columns.table_name ~ 'lime_.*'
    ;
  1. Loop through the table names, creating the same tables in the new_schema
    create table if not exists {new_schema}.{lime_table} (like {old_schema}.{lime_table} including all);

and also copy the data over from the old_schema to the new_schema for each table

    insert into {new_schema}.{lime_table} (select * from {old_schema}.{lime_table});
  1. Now we have a copy of the tables we want in new_schema.

Here comes the part with unexpected behavior from PostgreSQL. For migrating the new_schema to the other database, we first dump it to a file using

    pg_dump.exe
        --host="<HOST>"
        --port=<PORT>
        --username=<USERNAME>
        --table="lime*"  // redundant because of step 1.
        --format=c
        --schema=new_schema // our `new_schema`
        --exclude-schema="public" // doesn't work, public still being written in dump file
      "<DB_NAME>" > C:\Users\<PATH>\backup.sql

However, even after copying tables from public to new_schema in step 2., specifying to pg_dump to only dump new_schema, and also specifying to exclude the public schema (the schema where the data originates from), we still get public.<table> in the dump file! It's just like what's outlined in this question - pg_dump does not honor -n.

  1. If the dump were to work, the plan is to use the following to copy new_schema to a different database.
    pg_restore.exe
        --host="<HOST>"
        --port=<PORT>
        --username=<USERNAME>
        --verbose -1
        --dbname="<DB_NAME>"  
      C:\Users\<PATH>\backup.sql

The versions of PostgreSQL I am using are outlined in the dump files.

-- Dumped from database version 10.9.17

-- Dumped by pg_dump version 14.1

1 Answer 1

0

If one were to refer to the manual, one would discover that

The -n and -N switches have no effect when -t is used, because tables selected by -t will be dumped regardless of those switches

This seems to be exactly what is happening in your case. Contrary to what your comment says (--table="lime*" // redundant because), that switch actually overrides your schema specification. You should omit --table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.