How to restore a public schema in another schema

Question

I have a dump where the data and structure are in a public schema. I want to restore it to a schema with a custom name - how can I do this?

EDIT V 2:

My dump file is from a hero, and at the beginning it looks like this:

PGDMP ! pd6rq1i7f3kcath9.1.59.1.6<Y 0ENCODINENCODINGSET client_encoding = 'UTF8'; falseZ 00 STDSTRINGS STDSTRINGS)SET standard_conforming_strings = 'off'; false[ 126216385d6rq1i7f3kcatDATABASE?CREATE DATABASE d6rq1i7f3kcath WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; DROP DATABASE d6rq1i7f3kcath; uc0lt9t3fj0da4false26152200publicSCHEMACREATE SCHEMA public; DROP SCHEMA public; postgresfalse\ SCHEMA publicCOMMENT6COMMENT ON SCHEMA public IS 'standard public schema'; postgresfalse5?307916392plpgsql EXTENSION?CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; DROP EXTENSION plpgsql; false] 00EXTENSION plpgsqlCOMMENT@COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; false212?125516397_final_mode(anyarrayFUNCTION?CREATE FUNCTION _final_mode(anyarray) RETURNS anyelement LANGUAGE sql IMMUTABLE AS $_$ SELECT a FROM unnest($1) a GROUP BY 1 ORDER BY COUNT(1) DESC, 1 LIMIT 1; $_$; ,DROP FUNCTION public._final_mode(anyarray); publicuc0lt9t3fj0da4false5?125516398mode(anyelement) AGGREGATE?CREATE AGGREGATE mode(anyelement) ( SFUNC = array_append, STYPE = anyarray, INITCOND = '{}', FINALFUNC = _final_mode ); (DROP AGGREGATE public.mode(anyelement); publicuc0lt9t3fj0da4false5224?125916399 advert_candidate_collector_failsTABLECREATE TABLE advert_candidate_collector_fails ( id integer NOT NULL, advert_candidate_collector_status_id integer, exception_message text, stack_trace text, url text, created_at timestamp without time zone, updated_at timestamp without time zone ); 4DROP TABLE public.advert_candidate_collector_fails; publicuc0lt9t3fj0da4false5?125916405'advert_candidate_collector_fails_id_seSEQUENCE?CREATE SEQUENCE advert_candidate_collector_fails_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; >DROP SEQUENCE public.advert_candidate_collector_fails_id_seq; publicuc0lt9t3fj0da4false1615^ 00'advert_candidate_collector_fails_id_seqSEQUENCE OWNED BYeALTER SEQUENCE advert_candidate_collector_fails_id_seq OWNED BY advert_candidate_collector_fails.id; publicuc0lt9t3fj0da4false162_ 00'advert_candidate_collector_fails_id_seq SEQUENCE SETRSELECT pg_catalog.setval('advert_candidate_collector_fails_id_seq', 13641, true); publicuc0lt9t3fj0da4false162?125916407#advert_candidate_collector_statusesTABLE?CREATE TABLE advert_candidate_collector_statuses ( id integer NOT NULL, data_source_id character varying(120), state character varying(15) DEFAULT 'Queued'::character varying, source_name character varying(30), collector_type character varying(30), started_at timestamp without time zone, ended_at timestamp without time zone, times_failed integer DEFAULT 0, created_at timestamp without time zone, updated_at timestamp without time zone ); 7DROP TABLE public.advert_candidate_collector_statuses; publicuc0lt9t3fj0da4false240424055?125916412*advert_candidate_collector_statuses_id_seSEQUENCE?CREATE SEQUENCE advert_candidate_collector_statuses_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ADROP SEQUENCE public.advert_candidate_collector_statuses_id_seq; publicuc0lt9t3fj0da4false1635` 00*advert_candidate_collector_statuses_id_seqSEQUENCE OWNED BYkALTER SEQUENCE advert_candidate_collector_statuses_id_seq OWNED BY advert_candidate_collector_statuses.id; publicuc0lt9t3fj0da4false164a 00*advert_candidate_collector_statuses_id_seq SEQUENCE SETVSELECT pg_catalog.setval('advert_candidate_collector_statuses_id_seq', 133212, true); publicuc0lt9t3fj0da4false164?125916414advertsTABLE"CREATE TABLE adverts ( id integer NOT NULL, car_id integer NOT NULL, source_name character varying(20), url text, first_extraction timestamp without time zone, last_observed_at timestamp without time zone, created_at timestamp without time zone, updated_at timestamp without time zone, source_id character varying(255), deactivated_at timestamp without time zone, seller_id integer NOT NULL, data_source_id character varying(100), price integer, availability_state character varying(15) ); ROP TABLE public.adverts; publicuc0lt9t3fj0da4false5?125916420adverts_id_seSEQUENCEpCREATE SEQUENCE adverts_id_seq START WITH 1 INCREMENT BY 1 

Answer 1

Decision

@Tometzky is not entirely correct, at least with 9.2 pg_dump . It will create a table in a new schema, but pg_dump schema-qualifies the ALTER TABLE ... OWNER TO statements, so they will not be executed:

 postgres=# CREATE DATABASE demo; \cCREATE DATABASE postgres=# \c demo You are now connected to database "demo" as user "postgres". demo=# CREATE TABLE public.test ( dummy text ); CREATE TABLE demo=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | test | table | postgres (1 row) demo=# \q $ $ pg_dump -U postgres -f demo.sql demo $ sed -i 's/^SET search_path = public, pg_catalog;$/SET search_path = testschema, pg_catalog;/' demo.sql $ grep testschema demo.sql SET search_path = testschema, pg_catalog; $ dropdb -U postgres demo $ createdb -U postgres demo $ psql -U postgres -c 'CREATE SCHEMA testschema;' demo CREATE SCHEMA $ psql -U postgres -f demo.sql -v ON_ERROR_STOP=1 -v QUIET=1 demo psql:demo.sql:40: ERROR: relation "public.test" does not exist $ psql demo demo=> \d testschema.test Table "testschema.test" Column | Type | Modifiers --------+------+----------- dummy | text | 

You will also need to edit the dump to remove the schema qualification on public.test or change it to the new schema name. sed is a useful tool for this.

I could swear that the correct way to do this was with pg_dump -Fc -n public -f dump.dbbackup then pg_restore in the new scheme, but I can’t find out exactly how right now.

Update: No, it seems sed is your best bet. See I want to restore the database using a different scheme.

Answer 2

At the beginning of the dump file (created using pg_dump databasename ) is the line:

 SET search_path = public, pg_catalog; 

Just change it to:

 SET search_path = your_schema_name, pg_catalog; 

You will also need to search

 ALTER TABLE public. 

and replace with:

 ALTER TABLE your_schema_name.