Group many postgresql databases into separate schemas into same database

Question

We have many postgresql databases with the same structure using only public shcema on each one.

How can I group all of them in a single database using separate schemas?

Answer 1

I forgot to post the answer afer all klin comment was the answer, this step was the solution,

Inside customer_x database:

alter schema public rename to customer_x;

And then take pg_dump customer_x:

pg_dump "customer_x" --schema "customer_x" -f customer_x.sql

Inside new conglomerated database:

DROP schema customer_x CASCADE;
create schema customer_x;

Then load the dump of customer_x:

psql "conglomerated_database" -f customer_x.sql

Answer 2

You can dump the database definition and data out, edit the output by putting the default schema as whatever you choose and run the scripts back into database.

Remember to make the dump in SQL format, pg_dump with default custom format won't work. The schema change will only need a change on a row like

SET search_path TO *whateverschema*

If you don't want to edit the dumps (maybe they're very large), you can of course also restore them one by one to the public schema, alter the tables into the desired schema and then repeat for the next one.

There is no special way to convert an existing database into a schema in another database unfortunately.