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?

Javier salvañá
  • 191
  • 2
  • 16
  • 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? – jjanes Aug 04 '21 at 22:44

2 Answers2

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • 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. – Javier salvañá Aug 05 '21 at 10:20
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.

Alex Yu
  • 3,412
  • 1
  • 25
  • 38
  • 1
    I thought the `CREATE SCHEMA cliente2` was included in the generated script but it wasn´t. Running that command manually solved the problem. – Javier salvañá Aug 05 '21 at 10:25