1

I need to clone a Postgres schema programatically in nodejs.

I have tried this approach

How to duplicate schemas in PostgreSQL

but it is quite old and could not make it work.

I have tried using pg_dump with -s flag to copy the schema structure then replacing in that file all the ocurrencies of the original schema for the new schema name.

Then i run psql instead of pg_restore because the file is not compatible with that command.

psql lims -U lims -h localhost -f new_schema.dump

psql:new_schema.dump:1127: ERROR: no existe el esquema «cliente2»

How could i create the schema programatically?

Improve this question
1
  • I think you will need to do the heavy lifting here yourself. Was "cliente2" the destination schema you were trying to use? Was there not a CREATE SCHEMA command for it in the post-replacement file? Was there not a CREATE SCHEMA in the original dump file? Commented Aug 4, 2021 at 22:44

2 Answers 2

Reset to default
3

I think the most robust and sanity-preserving option is to use the database software itself to rename the schema. So something like this:

pg_dump -s -n old_schema > dmp_old.sql
createdb scratch_db_465484
psql scratch_db_465484 -1 -f dmp_old.sql
psql scratch_db_465484 -c 'alter schema old_schema rename to new_schema'
pg_dump scratch_db_465484 -s -n new_schema > dmp_new.sql
psql -1 -f dmp_new.sql
dropdb scratch_db_465484

I think the main problem here would be any extensions which installed their objects into old_schema would not get dumped but might be depended upon.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Ahhh. You create a new database (temporal) to rename the schema and then insert it back to the original database. I will try this approach.
1

I suppose that:

  • you created dump of schema
  • than search-replace old schema name to new. I suppose it's cliente2 in your case
  • then you tried to run it on postgresql

Problem: "file is not compatible with that command"

I suppose you forgot to run

CREATE SCHEMA cliente2;

before running your script.

That's why you have an error message that schema cliente2 not exists.

Other (possible) problems

A. Most probably auto-generated script will need manual edit to be correct. Especially if you copied schema public into new schema.

B. Usually you need to add GRANTs for users to use schema and objects inside it.

Improve this answer

1 Comment

I thought the CREATE SCHEMA cliente2 was included in the generated script but it wasn´t. Running that command manually solved the problem.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.