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,
- Get a list of table names from
old_schemathat 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_.*'
;
- 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});
- 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.
- If the dump were to work, the plan is to use the following to copy
new_schemato 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