7

I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema.

The dump type is "custom".

So effectively I want something like:

pg_dump -U postgres --schema "source_schema" --format "c" --create --file "source_schema.bak" my_db
pg_restore -U postgres --exit-on-error --dbname "my_db" "source_schema.bak" --destination-schema "destination_schema"

If that is possible it would be fine if I could copy (and rename) source schema before backup.

4 Answers 4

5

If the data is smallish, I would just do the pg_dump, restore it to a temporary just-for-this-purpose database server, rename the schema within that temporary server (ALTER SCHEMA...RENAME...), and dump it out of that temporary server to do the final restore.

1

the easiest thing is: dump the stuff. open the dump file in vi. fix, what has to be fixed and replay the stuff.

4
  • Interesting. That might be the easiest thing to do (thought I would not be able to use binary format for dump).
    – Nux
    Oct 29, 2014 at 13:26
  • 3
    well, you can: pg_restore transforms a custom format dump into textual format. you can add a pipe and use set to adapt the search_path. it would lead to the desired outcome if i have understood your request correctly. pg_restore ... | sed ... | psql ... Oct 29, 2014 at 13:31
  • 1
    On second thought that is not that easy... I would risk changing something in user data or even column names (if one of them has the same name as the schema).
    – Nux
    Oct 29, 2014 at 13:46
  • This a dangerous path, great risk of changing data this way as Nux points. Beyond that, the schema must be really small to result in a workable text file. Sep 20, 2021 at 9:01
1

I'm moving other databases (from the public schema) into a new schemas in a single database (thus a single connection can access them all). My approach was to load into the public schema then rename that.

First, if you have anything in your public schema, rename to save. I found some extensions, "hstore" specifically, needed to be recreated in the new schema. Execute these SQL command in psql or otherwise.

alter schema public rename to public_original;
create schema public;
create extension hstore; -- etc.

Backup and Restore your database to the public schema. Adjust commands for your connection settings.

pg_dump --dbname=sourcedb --schema=public \
          --format=custom --file=sourcedb.dump
pg_restore --jobs=4 --dbname=targetdb sourcedb.dump

Your database should now be in the public schema. Now issue these SQL commands to rename the schema, and rename the original public schema.

alter schema public rename to sourcedb;
alter schema public_original rename to public;

If you are moving multiple databases like I did, create a new public schema and iterate for each. Afterwards, restore the original_public schema.

As an alternative, if you have no naming collisions between the existing public relations (tables, sequences, views, etc) and the imported ones, you could import everything together and then move the new tables into the new schema.

create schema newschema;
alter table table1 set schema newschema;
...

Just remember to test commands and do a test run on a test database. Scripting this is recommended ;-)

-1

I had to do the same thing, ended up dumping in text format and editing the dump file with a sed command.

Patterns to look for / replace (search and replace the whole line not to edit real user data stored in db) :

CREATE SCHEMA [schema_name]

ALTER SCHEMA [schema_name]

SET search_path = [schema_name]

COMMENT ON SCHEMA [schema_name] IS '[comment]';

I used in on a small DB (less than 500MB dump), this might take ages to execute on a large database as stated in the comment.

2
  • If nothing else in the dump has a the same name, or if it does not appear in user data. Besides, with a large database this sed operation might take forever. Sep 20, 2021 at 9:03
  • Yes, to be "sure" not to replace user data, I recommend to search / replace for the whole line : CREATE SCHEMA [schema name], not just [SCHEMA NAME]. And yes, that's not the approach recommended for a large database. I just dumped this solution here for a quick hack, will edit the answer this way.
    – Mat
    Sep 21, 2021 at 6:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.