Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

No extensions in structure.sql when schema_search_path is defined #17157

Closed
jimmykarily opened this issue Oct 3, 2014 · 15 comments · Fixed by #19347
Closed

No extensions in structure.sql when schema_search_path is defined #17157

jimmykarily opened this issue Oct 3, 2014 · 15 comments · Fixed by #19347

Comments

@jimmykarily
Copy link

@jimmykarily jimmykarily commented Oct 3, 2014

The rake task db:structure:dump is running this command to dump to database schema to the structure.sql file. There is a problem though, when schema_search_path is defined. When passing --schema flags to pg_dump extensions are skipped (not sure why) no matter which schema they live on.

I am using the pg_trgm extension (and others) and I have indexes that use this extension. Commands to create those indexes are dumped in the file but the extension itself is missing. This way rake tasks that create the test database generate errors (non blocking though). If I remove the --schema flags from the pg_dump command everything is dumped correctly but I'm not sure if that will suit everybody.

Versions:
Postgres 9.3.5
Rails 4.0.10

@senny

This comment has been minimized.

Copy link
Member

@senny senny commented Oct 7, 2014

/cc @matthewd

@rywall

This comment has been minimized.

Copy link
Contributor

@rywall rywall commented Mar 5, 2015

Some extra information I provided in a accidental duplicate ticket (#17261):

If the user has specified a schema_search_path and also has extensions, the resulting pg_dump call generated by rake db:structure:dump includes the --schema flag which means that no CREATE EXTENSION statements will be created in the resulting structure.sql. According to the pg_dump documentation:

When --schema is specified, pg_dump makes no attempt to dump any other database objects that the selected schema(s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database.

This will most likely cause any attempts to load this file to fail.

A concrete example of this is if you are using the PostGIS extension and have some columns of type "geometry". It will fail when calling rake db:structure:dump then rake db:structure:load since the PostGIS extension is lost in the dump call.

I'm not sure what the best solution is in this case... In my opinion, it would be better to disregard the schema_search_path and dump the structure of all schemas rather than create invalid SQL dumps.

Thoughts?

@jimmykarily

This comment has been minimized.

Copy link
Author

@jimmykarily jimmykarily commented Mar 5, 2015

The problem is that schema_search_path configuration has two responsibilities:

  • Defines the postgres search path
  • Defines what schemas are dumped with pg_dump

so if you want the first one, you are forced to define the second one too (with the described consequences). My only concern on what you propose is that some people might be using the schema_search_path to exclude some schemas from the dump or, even worse, to exclude the "other database objects that the selected schema(s) might depend upon" as described in the documentation above.

The safest way to go imho, is to add one more configuration option which, when it exists, will define whether the dump should include only the search path schemas or everything. This way we will have an option to separate the two responsibilities if so desired. I would prefer a cleaner solution but that might break apps depending on the existing functionality.

@rywall

This comment has been minimized.

Copy link
Contributor

@rywall rywall commented Mar 5, 2015

I like this idea. A configuration option would allow it to be backwards compatible while still providing users who run into this problem with a solution. @senny would you accept a pull request that adds an option? Something like:

config.active_record.dump_all_schemas = true
@senny

This comment has been minimized.

Copy link
Member

@senny senny commented Mar 5, 2015

@rywall This seems like a reasonable solution, we could use a more general option name and symbols for values:

config.active_record.dump_schemas = :all
config.active_record.dump_schemas = :schema_search_path
# not saying we should support the array right now but it would be a possibility if we decide to do that later on
config.active_record.dump_schemas = ["schema_1", "schema_2"]

what do you think?

@rywall

This comment has been minimized.

Copy link
Contributor

@rywall rywall commented Mar 5, 2015

Perfect 👏 😄

I can draft something up unless one of you wants to do it.

@jimmykarily

This comment has been minimized.

Copy link
Author

@jimmykarily jimmykarily commented Mar 5, 2015

@rywall feel free to send a pull request, I appreciate it.

rywall added a commit to rywall/rails that referenced this issue Mar 15, 2015
Fixes db:structure:dump when using schema_search_path and PostgreSQL
extensions.

Closes rails#17157.
rywall added a commit to rywall/rails that referenced this issue Mar 16, 2015
Fixes db:structure:dump when using schema_search_path and PostgreSQL
extensions.

Closes rails#17157.
rywall added a commit to rywall/rails that referenced this issue Mar 17, 2015
Fixes db:structure:dump when using schema_search_path and PostgreSQL
extensions.

Closes rails#17157.
@senny senny closed this in #19347 Mar 17, 2015
rywall added a commit to culturecode/rails that referenced this issue Jun 10, 2015
Fixes db:structure:dump when using schema_search_path and PostgreSQL
extensions.

Closes rails#17157.
@maca

This comment has been minimized.

Copy link

@maca maca commented Sep 2, 2015

In addition to defining dump_schemas configuration it would be nice to be able to have an exclude_schemas configuration.

pg_dump has a --exclude-schema argument that accepts an expression with wildcards and schemas matching the expression will not be dumped.

I am working on a multitenant app using postgres schemas, specifying a schema to dump won't work for me because extensions are not included in the dump.

I had monkey patched ActiveRecord::Tasks::PostgreSQLDatabaseTasks as follows: https://gist.github.com/maca/1b9af55dc49109b17e0c, hardcoding --exclude-schema='tenants*'
since I have a schema for each tenant, and whenever I dump it will also include the schemas for tenants created in the development db.

The implementation for this configuration option wouldn't be too complicated, how do I propose this as a feature? sall I just implement and make a PR?

@senny

This comment has been minimized.

Copy link
Member

@senny senny commented Sep 7, 2015

@maca thanks for your insights. The best place to discuss a feature is the ruby-on-rails core mailing list.

I think it would be wise to discuss how this addition could look before submitting a PR. I wonder wether we should just introduce a new option or make it that the users have more control over the specific flags passed to pg_dump.

@maca

This comment has been minimized.

Copy link

@maca maca commented Sep 10, 2015

Thanks @senny I will write to the mailing list

@LorneCurrie

This comment has been minimized.

Copy link

@LorneCurrie LorneCurrie commented Jul 29, 2016

Is there any traction on this issue?

@Ch4s3

This comment has been minimized.

Copy link

@Ch4s3 Ch4s3 commented Nov 4, 2016

What's the status on this?

@LorneCurrie

This comment has been minimized.

Copy link

@LorneCurrie LorneCurrie commented Aug 1, 2017

Looks like the code fix for this was put into Rails v5.0.0. Is there a way we can add it to Rails v4.2.9/10?

@agnessa

This comment has been minimized.

Copy link

@agnessa agnessa commented Jan 22, 2018

I don't think it is possible using the fix in v5 to exclude schemas using the --exclude-schema switch as @maca suggested, which is the only way to dump without selected schemas but with all the extensions. I couldn't find a relevant thread on the mailing list, I wonder if this was ever followed up?

@Aquaj

This comment has been minimized.

Copy link
Contributor

@Aquaj Aquaj commented May 31, 2018

I was looking for a way to get a structure.sql with CREATE EXTENSIONs but without a specific schema (so a combination of config.active_record.dump_schemas = :all and --exclude-schema).

I don't know if @maca's suggestion was followed up on but if you need to use --exclude-schema, @rainhead's comment in #19347 provides a way to do so and worked great.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
8 participants
You can’t perform that action at this time.