Thread: unable to restore. pg_restore: implied data-only restore

I am trying to backup one database and restore it into a new schema in
another database. Database1 has the tables in the public schema
database2 has some tables in the public schema but their names will
clash so the database needs to be stored in a different schema.

I back up like this.

/usr/bin/pg_dump --host localhost --port 5432 --username tim --format
custom --blobs --verbose --file
"/usr/local/home/tim/tmp/database.backup" database1

/usr/bin/pg_restore --host localhost --port 5432 --username tim
--dbname database2 --schema database1_schema --verbose
"/usr/local/home/tim/tmp/database.backup"


pg_restore: connecting to database for restore
pg_restore: implied data-only restore

Nothing gets restored.

What is the proper way to restore databases into a particular schema?

Tim Uckun <timuckun@gmail.com> writes:
> I am trying to backup one database and restore it into a new schema in
> another database. Database1 has the tables in the public schema
> database2 has some tables in the public schema but their names will
> clash so the database needs to be stored in a different schema.

There is no support for that built into pg_dump.  You could try:

* dumping to a text script and doing search-and-replace for the schema
name on the script file.

* temporarily renaming the target database's public schema out of the
way, then renaming after the restore.

* doing the schema rename on the source database before you dump.

            regards, tom lane

>
> There is no support for that built into pg_dump.  You could try:

That's too bad.

>
> * dumping to a text script and doing search-and-replace for the schema
> name on the script file.

I did a dump without privileges or owners so I was thinking I could
just replace the

SET search_path = public, pg_catalog;

At the top and it might work. However I do see a lot of comments with
the schema name in them like.

--
-- TOC entry 18 (class 1255 OID 16417)
-- Dependencies: 6
-- Name: get_text_document(character varying); Type: FUNCTION; Schema:
public; Owner: -
--

Does pg_restore use these comments in some way? Should I change those as well?


> * temporarily renaming the target database's public schema out of the
> way, then renaming after the restore.
>
> * doing the schema rename on the source database before you dump.

This might be the easiest way now that I think about it.

Tim Uckun <timuckun@gmail.com> writes:
>> There is no support for that built into pg_dump.  You could try:
>> * dumping to a text script and doing search-and-replace for the schema
>> name on the script file.

> I did a dump without privileges or owners so I was thinking I could
> just replace the

> SET search_path = public, pg_catalog;

> At the top and it might work. However I do see a lot of comments with
> the schema name in them like.

The comments are not a problem, but in all but the simplest DB designs
there are likely to be some embedded references to the schema name, too.

            regards, tom lane