postgresql – 如何将公共模式恢复到不同的模式
发布时间:2020-12-13 15:55:56 所属栏目:百科 来源:网络整理
导读:我有一个转储,数据和结构在公共架构中.我想将其恢复为具有自定义名称的模式 – 我该怎么做? 编辑V 2: 我的转储文件来自heroku,在开头看起来像这样: PGDMP !pd6rq1i7f3kcath9.1.59.1.6Y0ENCODINENCODINGSET client_encoding = 'UTF8';falseZ00STDSTRINGSST
我有一个转储,数据和结构在公共架构中.我想将其恢复为具有自定义名称的模式 – 我该怎么做?
编辑V 2: 我的转储文件来自heroku,在开头看起来像这样: 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,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),first_extraction timestamp without time zone,last_observed_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 解决方法
@ Tometzky的解决方案不太对,至少在9.2的pg_dump中.它将在新模式中创建表,但是pg_dump模式 – 限定ALTER TABLE … OWNER TO语句,因此这些将失败:
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 | 您还需要编辑转储以删除public.test上的架构限定或将其更改为新架构名称. sed是一个有用的工具. 我可以发誓这样做的正确方法是使用pg_dump -Fc -n public -f dump.dbbackup然后pg_restore进入一个新模式,但我现在似乎无法确切地知道如何. 更新:不,看起来sed是你最好的选择.见I want to restore the database with a different schema (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |