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.
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
>...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.
