Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@matthewrudy
Created February 26, 2017 23:40
Show Gist options
  • Star 7 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save matthewrudy/4400b3eee9f8dd7e15e4674359886c58 to your computer and use it in GitHub Desktop.
Save matthewrudy/4400b3eee9f8dd7e15e4674359886c58 to your computer and use it in GitHub Desktop.
Clean differences from your rake db:structure:dump - it seems to work for 9.4, 9.5 and 9.6
# frozen_string_literal: true
namespace :db do
namespace :structure do
STRUCTURE_PATH = 'db/structure.sql'
def clean_structure_file
original = File.read(STRUCTURE_PATH)
cleaned = original.dup
# trailing whitespace
cleaned.gsub!(/ +$/, '')
# last line
cleaned.gsub!(/\n+\Z/, "\n")
# postgresql 9.5 dumps the row lock status
cleaned.gsub!(/SET row_security = off;\n/, "")
# postgresql 9.6 dumps the idle transaction timeout
cleaned.gsub!(/SET idle_in_transaction_session_timeout = 0;\n/, "")
# postgresql 9.5 dumps its details
cleaned.gsub!(/\-\- Dumped from database version 9\.\d\.\d\n\-\- Dumped by pg_dump version 9.\d.\d\n\n/, "")
# postgresql 9.5 doesn't show empty Tablespace
cleaned.gsub!(/\-\- Name: ([A-Za-z0-9_ ]+); Type: (TABLE|CONSTRAINT|INDEX); Schema: public; Owner: \-$/) do |_|
"-- Name: #{$1}; Type: #{$2}; Schema: public; Owner: -; Tablespace:"
end
# postgresql 9.6 shows the table and the name
cleaned.gsub!(/\-\- Name: ([A-Za-z0-9_]+) ([A-Za-z0-9_]+);/) do |_|
if $1 == "EXTENSION"
"-- Name: #{$1} #{$2};"
else
"-- Name: #{$2};"
end
end
# postgresql 9.5 puts a space here
cleaned.gsub!(/SET search_path TO "\$user", public;/, "SET search_path TO \"$user\",public;")
# postgresql 9.6 quotes the position field
cleaned.gsub!(/"position" integer,/, "position integer,")
return if original == cleaned
File.open(STRUCTURE_PATH, 'w') do |f|
f.write(cleaned)
end
end
Rake::Task['dump'].enhance do
clean_structure_file
end
desc "clean the structure.sql file"
task :clean do
clean_structure_file
end
end
end
@formigarafa
Copy link

also added this in the beginning:

      # use LF only for new lines
      cleaned.gsub!(/\r\n?/,"\n")

@formigarafa
Copy link

and to also ignore 10.1 versions

      # postgresql 9.5 dumps its details
      cleaned.gsub!(/\-\- Dumped from database version (9|10)\.\d+(\.\d+)?\n\-\- Dumped by pg_dump version (9|10).\d+(.\d+)?\n\n/, "")

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment