On Fri, Mar 9, 2018 at 10:00 AM, Aldrin Martoq Ahumada <
[email protected]> wrote:
> Hi Andre,
>
> Yes, here is the issue: https://github.com/influitive/apartment/issues/532
>
> It happens if you configured apartment with use_sql=true, which means it
> clones the schema from pg_dump. My first attempt was to “fix” the script
> generated by pg_dump, but I feel it will be a mess. We solved our issue
> going back to use_sql=false, which is the default (creates the schema from
> db/schema.rb). But there is people that have other requirements, like
> functions, so the easier way for them is to keep use_sql and replace
> strings in the script.
>
>
> Cheers,
>
> On Mar 9, 2018, at 11:51 AM, Andre Oliveira Freitas <
> [email protected]> wrote:
>
> Hello Aldrin,
>
> I'm also using apartment with postgresql 9.6.6, and I don't see any
> issue with it. Are you using Apartment::Tenant.create?
>
> 2018-03-09 10:26 GMT-03:00 Aldrin Martoq Ahumada <[email protected]
> >:
>
> Hi,
>
> For a multi tenant system, we are using the following command to blindly
> clone a schema into another:
> pg_dump -s -x -O -n #{default_tenant} #{dbname}
>
>
> This is done for us by a rails gem, which then feeds that script into the
> new created schema for the new tenant.
> https://github.com/influitive/apartment/blob/
> 80a21f2e1cdcbe5b0bd976f88c14332657804536/lib/apartment/
> adapters/postgresql_adapter.rb#L150
>
>
>
>
> When we upgraded from 9.5.11 to 9.5.12, the format of the dump changed (it
> now always appends the schema name), so this is broken. We could patch the
> SQL generated, but that’s not a generic/robust solution.
>
> # pg_dump postgresql 9.5.11
> SET search_path = public, pg_catalog;
> CREATE TABLE ahoy_events (
> id bigint NOT NULL,
> visit_id integer,
> user_id integer,
> name character varying,
> properties jsonb,
> "time" timestamp without time zone
> );
>
> # pg_dump postgresql 9.5.12
> CREATE TABLE public.ahoy_events (
> id bigint NOT NULL,
> visit_id integer,
> user_id integer,
> name character varying,
> properties jsonb,
> "time" timestamp without time zone
> );
>
>
>
> Thinking in the long term, how could be the best way to clone a schema into
> another?
>
>
>
>
> —
> Aldrin
>
>
>
>
> --
>
> André Luis O. Freitas
> System Architect
>
> Rua do Rócio, 220 - Cj. 72
> São Paulo - SP - 04552-000
> 55 11 4063 4222
>
> [email protected]
> www.callix.com.br
>
>
>
* >...how could be the best way to clone a schema into another? The safest
way is to use pgdump -F p -n <the_schema_name> > schema.sql Then edit
schema.sql and change all references to old_schema name to new_schema
name. Finally, use psql < schema.sql to create the new_schema. That being
said, a year ago I optimized a function originally written by Emanuel
'3manuek' called clone_schema, which is added to the public schema. It
clones all sequences, tables, indexes, rules, triggers, data(optional),
views & functions from any existing schema to a new schema SAMPLE
CALL: SELECT clone_schema('public', 'new_schema', TRUE); I've attached it
for your convenience. disclaimer: I do not accept any responsibility for
any unknow bugs in the function. Test first and use at your own risk.-- *
*Melvin DavidsonMaj. Database & Exploration SpecialistUniverse Exploration
Command – UXC*
Employment by invitation only!
-- Function: public.clone_schema(text, text, boolean)
-- DROP FUNCTION public.clone_schema(text, text, boolean);
CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
RETURNS void AS
$BODY$
-- Initial code by Emanuel '3manuek'
-- Last revision 2017-04-17 by Melvin Davidson
-- Added SELECT REPLACE for schema views
--
-- This function will clone all sequences, tables, indexes, rules, triggers,
-- data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);
DECLARE
src_oid oid;
tbl_oid oid;
func_oid oid;
con_oid oid;
v_path text;
v_func text;
v_args text;
v_conname text;
v_rule text;
v_trig text;
object text;
buffer text;
srctbl text;
default_ text;
v_column text;
qry text;
dest_qry text;
v_def text;
v_stat integer;
seqval bigint;
sq_last_value bigint;
sq_max_value bigint;
sq_start_value bigint;
sq_increment_by bigint;
sq_min_value bigint;
sq_cache_value bigint;
sq_log_cnt bigint;
sq_is_called boolean;
sq_is_cycled boolean;
sq_cycled char(10);
BEGIN
-- Check that source_schema exists
SELECT oid INTO src_oid
FROM pg_namespace
WHERE nspname = quote_ident(source_schema);
IF NOT FOUND
THEN
RAISE NOTICE 'source schema % does not exist!', source_schema;
RETURN ;
END IF;
-- Check that dest_schema does not yet exist
PERFORM nspname
FROM pg_namespace
WHERE nspname = quote_ident(dest_schema);
IF FOUND
THEN
RAISE NOTICE 'dest schema % already exists!', dest_schema;
RETURN ;
END IF;
EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
-- Add schema comment
SELECT description INTO v_def
FROM pg_description
WHERE objoid = src_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' ||
quote_literal(v_def);
END IF;
-- Create sequences
-- TODO: Find a way to make this sequence's owner is the correct table.
FOR object IN
SELECT sequence_name::text
FROM information_schema.sequences
WHERE sequence_schema = quote_ident(source_schema)
LOOP
EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' ||
quote_ident(object);
srctbl := quote_ident(source_schema) || '.' || quote_ident(object);
EXECUTE 'SELECT last_value, max_value, start_value, increment_by,
min_value, cache_value, log_cnt, is_cycled, is_called
FROM ' || quote_ident(source_schema) || '.' ||
quote_ident(object) || ';'
INTO sq_last_value, sq_max_value, sq_start_value,
sq_increment_by, sq_min_value, sq_cache_value, sq_log_cnt, sq_is_cycled,
sq_is_called ;
IF sq_is_cycled
THEN
sq_cycled := 'CYCLE';
ELSE
sq_cycled := 'NO CYCLE';
END IF;
EXECUTE 'ALTER SEQUENCE ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' INCREMENT BY ' || sq_increment_by
|| ' MINVALUE ' || sq_min_value
|| ' MAXVALUE ' || sq_max_value
|| ' START WITH ' || sq_start_value
|| ' RESTART ' || sq_min_value
|| ' CACHE ' || sq_cache_value
|| sq_cycled || ' ;' ;
buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
IF include_recs
THEN
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value ||
', ' || sq_is_called || ');' ;
ELSE
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value
|| ', ' || sq_is_called || ');' ;
END IF;
-- add sequence comments
SELECT oid INTO tbl_oid
FROM pg_class
WHERE relkind = 'S'
AND relnamespace = src_oid
AND relname = quote_ident(object);
SELECT description INTO v_def
FROM pg_description
WHERE objoid = tbl_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON SEQUENCE ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' IS ''' || v_def || ''';';
END IF;
END LOOP;
-- Create tables
FOR object IN
SELECT TABLE_NAME::text
FROM information_schema.tables
WHERE table_schema = quote_ident(source_schema)
AND table_type = 'BASE TABLE'
LOOP
buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
quote_ident(source_schema) || '.' || quote_ident(object)
|| ' INCLUDING ALL)';
-- Add table comment
SELECT oid INTO tbl_oid
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = src_oid
AND relname = quote_ident(object);
SELECT description INTO v_def
FROM pg_description
WHERE objoid = tbl_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON TABLE ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' IS ''' || v_def || ''';';
END IF;
IF include_recs
THEN
-- Insert records from source table
EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' ||
quote_ident(source_schema) || '.' || quote_ident(object) || ';';
END IF;
FOR v_column, default_ IN
SELECT column_name::text,
REPLACE(column_default::text, quote_ident(source_schema) || '.',
quote_ident(dest_schema) || '.' )
FROM information_schema.COLUMNS
WHERE table_schema = dest_schema
AND TABLE_NAME = object
AND column_default LIKE 'nextval(%' || quote_ident(source_schema) ||
'%::regclass)'
LOOP
EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || v_column || ' SET
DEFAULT ' || default_;
END LOOP;
END LOOP;
-- set column statistics
FOR tbl_oid, srctbl IN
SELECT oid, relname
FROM pg_class
WHERE relnamespace = src_oid
AND relkind = 'r'
LOOP
FOR v_column, v_stat IN
SELECT attname, attstattarget
FROM pg_attribute
WHERE attrelid = tbl_oid
AND attnum > 0
LOOP
buffer := quote_ident(dest_schema) || '.' || quote_ident(srctbl);
-- RAISE EXCEPTION 'ALTER TABLE % ALTER COLUMN % SET STATISTICS %',
buffer, v_column, v_stat::text;
EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' ||
quote_ident(v_column) || ' SET STATISTICS ' || v_stat || ';';
END LOOP;
END LOOP;
-- add FK constraint
FOR qry IN
SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' ||
quote_ident(rn.relname)
|| ' ADD CONSTRAINT ' || quote_ident(ct.conname) || '
' || replace(pg_get_constraintdef(ct.oid), quote_ident(source_schema) || '.',
quote_ident(dest_schema) || '.') || ';'
FROM pg_constraint ct
JOIN pg_class rn ON rn.oid = ct.conrelid
WHERE connamespace = src_oid
AND rn.relkind = 'r'
AND ct.contype = 'f'
LOOP
EXECUTE qry;
END LOOP;
-- Add constraint comment
FOR con_oid IN
SELECT oid
FROM pg_constraint
WHERE conrelid = tbl_oid
LOOP
SELECT conname INTO v_conname
FROM pg_constraint
WHERE oid = con_oid;
SELECT description INTO v_def
FROM pg_description
WHERE objoid = con_oid;
IF FOUND
THEN
EXECUTE 'COMMENT ON CONSTRAINT ' || v_conname || ' ON ' ||
quote_ident(dest_schema) || '.' || quote_ident(object)
|| ' IS ''' || v_def || ''';';
END IF;
END LOOP;
-- Create views
FOR object IN
SELECT table_name::text,
view_definition
FROM information_schema.views
WHERE table_schema = quote_ident(source_schema)
LOOP
buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
SELECT view_definition INTO v_def
FROM information_schema.views
WHERE table_schema = quote_ident(source_schema)
AND table_name = quote_ident(object);
SELECT REPLACE(v_def, source_schema, dest_schema) INTO v_def;
-- RAISE NOTICE 'view def, % , source % , dest % ', v_def, source_schema,
dest_schema;
EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ;
-- Add comment
SELECT oid INTO tbl_oid
FROM pg_class
WHERE relkind = 'v'
AND relnamespace = src_oid
AND relname = quote_ident(object);
SELECT description INTO v_def
FROM pg_description
WHERE objoid = tbl_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON VIEW ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' IS ' || quote_literal(v_def);
END IF;
END LOOP;
-- Create functions
FOR func_oid IN
SELECT oid, proargnames
FROM pg_proc
WHERE pronamespace = src_oid
LOOP
SELECT pg_get_functiondef(func_oid) INTO qry;
SELECT proname, oidvectortypes(proargtypes) INTO v_func, v_args
FROM pg_proc
WHERE oid = func_oid;
SELECT replace(qry, quote_ident(source_schema) || '.',
quote_ident(dest_schema) || '.') INTO dest_qry;
EXECUTE dest_qry;
-- Add function comment
SELECT description INTO v_def
FROM pg_description
WHERE objoid = func_oid
AND objsubid = 0;
IF FOUND
THEN
-- RAISE NOTICE 'func_oid %, object %, v_args %', func_oid::text,
quote_ident(object), v_args;
EXECUTE 'COMMENT ON FUNCTION ' || quote_ident(dest_schema) || '.' ||
quote_ident(v_func) || '(' || v_args || ')'
|| ' IS ' || quote_literal(v_def) ||';' ;
END IF;
END LOOP;
-- add Rules
FOR v_def IN
SELECT definition
FROM pg_rules
WHERE schemaname = quote_ident(source_schema)
LOOP
IF v_def IS NOT NULL
THEN
SELECT replace(v_def, 'TO ', 'TO ' || quote_ident(dest_schema) ||
'.') INTO v_def;
EXECUTE ' ' || v_def;
END IF;
END LOOP;
-- add triggers
FOR v_def IN
SELECT pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgname NOT LIKE 'RI_%'
AND tgrelid IN (SELECT oid
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = src_oid)
LOOP
SELECT replace(v_def, ' ON ', ' ON ' || quote_ident(dest_schema) || '.')
INTO dest_qry;
EXECUTE dest_qry;
END LOOP;
-- Disable inactive triggers
-- D = disabled
FOR tbl_oid IN
SELECT oid
FROM pg_trigger
WHERE tgenabled = 'D'
AND tgname NOT LIKE 'RI_%'
AND tgrelid IN (SELECT oid
FROM pg_class
WHERE relkind = 'r'
AND relnamespace = src_oid)
LOOP
SELECT t.tgname, c.relname INTO object, srctbl
FROM pg_trigger t
JOIN pg_class c ON c.oid = t.tgrelid
WHERE t.oid = tbl_oid;
IF FOUND
THEN
EXECUTE 'ALTER TABLE ' || dest_schema || '.' || srctbl || ' DISABLE
TRIGGER ' || object || ';';
END IF;
END LOOP;
-- Add index comment
FOR tbl_oid IN
SELECT oid
FROM pg_class
WHERE relkind = 'i'
AND relnamespace = src_oid
LOOP
SELECT relname INTO object
FROM pg_class
WHERE oid = tbl_oid;
SELECT description INTO v_def
FROM pg_description
WHERE objoid = tbl_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' IS ''' || v_def || ''';';
END IF;
END LOOP;
-- add rule comments
FOR con_oid IN
SELECT oid, *
FROM pg_rewrite
WHERE rulename <> '_RETURN'::name
LOOP
SELECT rulename, ev_class INTO v_rule, tbl_oid
FROM pg_rewrite
WHERE oid = con_oid;
SELECT relname INTO object
FROM pg_class
WHERE oid = tbl_oid
AND relkind = 'r';
SELECT description INTO v_def
FROM pg_description
WHERE objoid = con_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON RULE ' || v_rule || ' ON ' ||
quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
END IF;
END LOOP;
-- add trigger comments
FOR con_oid IN
SELECT oid, *
FROM pg_trigger
WHERE tgname NOT LIKE 'RI_%'
LOOP
SELECT tgname, tgrelid INTO v_trig, tbl_oid
FROM pg_trigger
WHERE oid = con_oid;
SELECT relname INTO object
FROM pg_class
WHERE oid = tbl_oid
AND relkind = 'r';
SELECT description INTO v_def
FROM pg_description
WHERE objoid = con_oid
AND objsubid = 0;
IF FOUND
THEN
EXECUTE 'COMMENT ON TRIGGER ' || v_trig || ' ON ' ||
quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
END IF;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.clone_schema(text, text, boolean)
OWNER TO postgres;
COMMENT ON FUNCTION public.clone_schema(text, text, boolean) IS 'Duplicates
sequences, tables, indexes, rules, triggers, data(optional),
views & functions from the source schema to the destination schema';
