I want to restore the database with a different schema

Question

I have taken a dump of a database named temp1, by using the follwing command

$  pg_dump -i -h localhost  -U postgres -F c -b -v -f pub.backup temp1

Now I want to restore the dump in a different database called "db_temp" , but in that I just want that all the tables should be created in a "temp_schema" ( not the default schema which is in the fms temp1 database ) which is in the "db_temp" database.

Is there any way to do this using pg_restore command?

Any other method also be appreciated!

i just can't accept that this is still happening in 2017, and the solution is to hack the dump file. — Sharky, Feb 28 '17 at 15:40
You could use DBeaver to export it into another schema. All in one go! — user1880957, Jul 06 '19 at 09:54
2020 my friend! @user1880957 question is about automatic built-in way and not manual with dependency to some GUI tool — Ameba Brain, May 27 '20 at 12:07
Agree @AmebaBrain. I just gave an alternative in the absence of a credible alternative. But having it in-built is definitely the perfect solution, now that it is 2020 :-). — user1880957, May 30 '20 at 04:44
It's 2021 indeed, and know what? pipes have been working perfectly for nearly half a century by now. — dezso, May 31 '21 at 14:51
@user1880957 can you provide a link for how to do it in DBeaver with Postgres? I can backup and restore but it keeps importing data to the same schema — zoran, Jul 12 '21 at 14:16

score 75 · Answer 1 · answered May 01 '13 at 02:28

A quick and dirty way:

1) rename default schema:

alter schema public rename to public_save;

2) create new schema as default schema:

create schema public;

3) restore data

pg_restore -f pub.backup db_temp [and whatever other options]

4) rename schemas according to need:

alter schema public rename to temp_schema;
alter schema public_save rename to public;

score 68 · Answer 2 · edited Jun 20 '20 at 09:12

68

There is a simple solution:

Create your backup dump in plain SQL format (format "p" using the parameter --format=p or -F p)
Edit your pub.backup.sql dump with your favorite editor and add the following two lines at the top of your file:

create schema myschema;

SET search_path TO myschema;

Now you can restore your backup dump with the command

psql -f pub.backup.sql

The set search_path to <schema> command will set myschema as the default, so that new tables and other objects are created in this schema, independently of the "default" schema where they lived before.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 01 '13 at 15:46

fraber

1,204
1
8
21

Is there a neat way to actually edit only the head of a large dump file? Even things like this involve duplicating it: http://superuser.com/questions/246837/how-do-i-add-text-to-the-beginning-of-a-file-in-bash – Peter Ehrlich May 05 '15 at 02:04
1

@PeterEhrlich Depending on how large your files are and available RAM, you can use vim to open a gzip'd dump file directly. It still gets expanded in memory but I was able to open, edit, and save a 750MB (.gz) file with only a short pause when saving. YMMV. – David May 12 '15 at 16:48
I found my dumps already had one or more SET search_path TO ...; lines that I needed to remove. You may want to search for "search_path" when editing and before restoring.You'll have at least one SET search_path TO ...; line for each schema from which objects were dumped. It would be nice if there were a --without-schema option that worked like the --no-owner option. – David May 12 '15 at 17:07
2

@PeterEhrlich You can use a subprocess to write something to a file before the contents of the dump. For example: `( echo "CREATE SCHEMA myschema; SET search_path TO myschema;" ; pg_dump -F p ... ) | gzip -9 > dump.sql.gz` – gldnspud Feb 18 '16 at 22:59
@PeterEhrlich - I find it easiest to use sed: `sed -i s/SET search_path TO old_schema/SET search_path TO new_schema/ mydumpfilepath` – J-DawG Mar 07 '17 at 17:44
1

@gldnspud but as I understand it this will just mean there's 2 "SET search_path"s in the file? The second one being the one we don't want. – Andy Smith Jul 14 '17 at 13:45
31

This won't work in Postgres 9.6.8 / 10.3: `pg_dump` no longer uses `SET search_path`; instead, it prefixes the schema name to all commands in the dump – Mar 13 '18 at 18:56
and what to do in case of large project with up to 300 tables? also single non-compressed sql file? you will also loose parallel feature during restore – Ameba Brain May 27 '20 at 12:17

score 18 · Accepted Answer · answered Nov 16 '10 at 13:47

18

There's no way in pg_restore itself. What you can do is use pg_restore to generate SQL output, and then send this through for example a sed script to change it. You need to be careful about how you write that sed script though, so it doesn't match and change things inside your data.

answered Nov 16 '10 at 13:47

Magnus Hagander

23,890
5
56
43

1

I know the answer is old, but for someone that just wants to use this accepted answer: @shaunc is way more secure and robust, and works with compressed dumps. – agoldev Jun 17 '22 at 16:02

score 14 · Answer 4 · answered Nov 16 '10 at 06:46

14

Probably the easiest method would be to simply rename the schema after restore, ie with the following SQL:

ALTER SCHEMA my_schema RENAME TO temp_schema

I believe that because you're using the compressed archive format for the output of pg_dump you can't alter it before restoring. The option would be to use the default output and do a search and replace on the schema name, but that would be risky and could perhaps cause data to be corrupted if you were not careful.

answered Nov 16 '10 at 06:46

Hamish

22,860
8
53
67

This solution can make a lot of mess isn't it – abubacker Nov 16 '10 at 12:21
4

In what way?? The SQL statement is the only safe way to change a schema name. The other solution is also suggested in another answer and we both explain that it's risky. Poor downvoting. – Hamish Nov 16 '10 at 18:33
2

I changed the schema name but, after that, I'm not able to change the serach path! User is not able to search any table after the rename! – Andrea Girardi Jan 27 '12 at 11:25

score 4 · Answer 5 · answered Nov 16 '10 at 22:49

If you only have a few tables then you can restore one table at a time, pg_restore accepts -d database when you specify -t tablename. Of course, you'll have to set up the schema before restoring the tables and then sort out the indexes and constraints when you're done restoring the tables.

Alternatively, set up another server on a different port, restore using the new PostgreSQL server, rename the schema, dump it, and restore into your original database. This is a bit of a kludge of course but it will get the job done.

If you're adventurous you might be able to change the database name in the dump file using a hex editor. I think it is only mentioned in one place in the dump and as long as the new and old database names are the same it should work. YMMV, don't do anything like this in a production environment, don't blame me if this blows up and levels your home town, and all the rest of the usual disclaimers.

score 3 · Answer 6 · edited May 22 '18 at 12:39

Rename the schema in a temporary database.

Export the schema:

pg_dump --schema-only --schema=prod > prod.sql

Create a new database. Restore the export:

psql -f prod.sql

ALTER SCHEMA prod RENAME TO somethingelse;

pg_dump --schema-only --schema=somethingelse > somethingelse.sql

(delete the database)

For the data you can just modify the set search_path at the top.

score 2 · Answer 7 · answered May 21 '18 at 03:36

2

As noted, there's no direct support in pg_dump, psql or pg_restore to change the schema name during a dump/restore process. But it's fairly straightforward to export using "plain" format then modify the .sql file. This Bash script does the basics:

rename_schema () {

  # Change search path so by default everything will go into the specified schema
  perl -pi -e "s/SET search_path = $2, pg_catalog/SET search_path = $3, pg_catalog, $2;/" "$1"

  # Change 'ALTER FUNCTION foo.' to 'ALTER FUNCTION bar.'
  perl -pi -e 's/^([A-Z]+ [A-Z]+) '$2'\./$1 '$3'./' "$1"

  # Change the final GRANT ALL ON SCHEMA foo TO PUBLIC
  perl -pi -e 's/SCHEMA '$2'/SCHEMA '$3'/' "$1"

}

Usage:

pg_dump --format plain --schema=foo --file dump.sql MYDB
rename_schema dump.sql foo bar
psql -d MYDB -c 'CREATE SCHEMA bar;'
psql -d MYDB -f dumpsql

answered May 21 '18 at 03:36

Steve Bennett

114,604
39
168
219

(Everyone: Please feel free to update this answer to improve the Bash.) – Steve Bennett May 21 '18 at 03:37
1

I would have done it with sed `"s/\b$OLD_SCHEMA/$NEW_SCHEMA/g"` – Pipo Apr 23 '19 at 14:41
2

As mentioned in one of the other answers, one needs to be careful if the schema name exists elsewhere (e.g. in the data). His perl script is slightly safer than plain substituting all appearances of `$OLD_SCHEMA`. – Sakari Cajanus Oct 07 '21 at 10:08

Dwhitz · Answer 8 · 2022-03-11T09:38:25.290

2

The question is pretty old, but maybe can help some one.

Streaming the output of pg_restore to sed and replace the schema name in order to import the dump to a different schema.

Something like:

pg_restore ${dumpfile} | \
    sed -e "s/OWNER TO ${source_owner}/OWNER TO ${target_owner}/" \
        -e "s/${source_schema}/${target_schema}/" | \
       psql -h ${pgserver} -d ${dbname} -U ${pguser}

edited Mar 11 '22 at 09:38

answered Jun 21 '21 at 08:28

Dwhitz

1,250
7
26
38

you meant `pg_dump` as the first command opposed to `pg_restore`? – John Smith Aug 10 '21 at 09:41
@JohnSmith no I mean pg_restore. You should do your dump and then perform the replace schema while importing the dump on the target db. – Dwhitz Aug 10 '21 at 09:57
@Dwhitz there's small typo error; it should be {"source_schema"/"target_schema"/} – snackbar Mar 11 '22 at 04:42

I want to restore the database with a different schema

8 Answers8

Linked