CODEWITHSUNDEEP
postgresqldatabase-migrationpgcopydb
Nikhil VJ
Nikhil VJ

Reputation: 6122

pgcopydb change schema name when copying

In a pgcopydb copy table-data command, the source data is under "public" schema whereas in the destination db I have to put it under a different schema, say, "schema1". I'm not able to find a way to change this schema name during the copy table-data process. How to do this?

Ref: https://pgcopydb.readthedocs.io/
https://github.com/dimitri/pgcopydb?tab=readme-ov-file

Edit : I found one section but wasn't able to understand this at all: https://pgcopydb.readthedocs.io/en/latest/tutorial.html#how-to-edit-the-schema-when-copying-a-database -> Thanks for the explanation @adrian-klaver ; this one doesn't apply then.

Upvotes: 1

Views: 44

Answers (1)

Zegarek
Zegarek

Reputation: 26347

The schema edit section in the doc suggests to make your changes into a .sql script and run it before/after you kick off pgcopydb copy table-data.

If schema public on destination db is empty, has no traffic on it or you can afford a maintenance window, you could use a temporary name swap:

alter schema public rename to old_public_temporarily_renamed;
create schema public;
--run your `pgcopydb` from `public` on your source to this `public`, then
alter schema public rename to schema1; 
alter schema old_public_temporarily_renamed rename to public;

If you implemented a secure schema usage pattern with private-only and/or enabling access to public only when explicitly, fully qualified, it shouldn't be that much of a problem.

You could also redirect the data through views

create view public.table1 as table schema1.table1;

for each of your target objects. This type of view is updatable/insertable, redirecting the inserted records. Let the job write to them, drop them after you're done. This approach might be better if you're only moving parts of the contents, for only some of the objects in that schema.

If the name wasn't as short, generic and happen to also be a keyword, you could

pg_dump ... | sed 's/src_schema_name/new_schema_name/g' | psql ...

The rename-on-the-fly option is probably missing because it requires a (large and difficult to add) SQL parser to safely and reliably find and replace all instances of the identifier in a dump without accidentally hitting table, type, column and other names - and in case of public, also mess up dumped create publication statements.

Upvotes: 0

Related Questions