If I load structure.sql using this command and it fails because of an error within the structure.sql file:
$ bundle exec rake db:schema:load RAILS_ENV=test
psql:/path/to/app/db/structure.sql:13342: ERROR: column "column_id" does not exist
rake aborted!
failed to execute:
psql --set ON_ERROR_STOP=1 --quiet --no-psqlrc --output /dev/null --file /path/to/app/db/structure.sql test
Please check the output above for any errors and make sure that `psql` is installed in your PATH and has proper permissions.
/Users/jmiller/.rbenv/versions/2.7.6/bin/bundle:23:in `load'
/Users/jmiller/.rbenv/versions/2.7.6/bin/bundle:23:in `<main>'
Tasks: TOP => db:schema:load
(See full trace by running task with --trace)
and then attempt to rerun after resolving the error, it fails again due to an existing enum type that is defined within structure.sql:
$ bundle exec rake db:schema:load RAILS_ENV=test
psql:/path/to/app/db/structure.sql:33: ERROR: type "status" already exists
rake aborted!
failed to execute:
psql --set ON_ERROR_STOP=1 --quiet --no-psqlrc --output /dev/null --file /path/to/app/db/structure.sql test
Please check the output above for any errors and make sure that `psql` is installed in your PATH and has proper permissions.
/Users/jmiller/.rbenv/versions/2.7.6/bin/bundle:23:in `load'
/Users/jmiller/.rbenv/versions/2.7.6/bin/bundle:23:in `<main>'
Tasks: TOP => db:schema:load
(See full trace by running task with --trace)
This enum type could only exist if the structure.sql got loaded in some capacity, and therefore suggests that it is a non-atomic transaction. This can cause odd an unexpected database states.
It looks like rake db:schema:dump ultimately calls pg_dump:
def structure_dump(filename, extra_flags)
search_path = \
case ActiveRecord.dump_schemas
when :schema_search_path
configuration_hash[:schema_search_path]
when :all
nil
when String
ActiveRecord.dump_schemas
end
args = ["--schema-only", "--no-privileges", "--no-owner"]
args.concat(["--file", filename])
args.concat(Array(extra_flags)) if extra_flags
unless search_path.blank?
args += search_path.split(",").map do |part|
"--schema=#{part.strip}"
end
end
ignore_tables = ActiveRecord::SchemaDumper.ignore_tables
if ignore_tables.any?
args += ignore_tables.flat_map { |table| ["-T", table] }
end
args << db_config.database
run_cmd("pg_dump", args, "dumping")
remove_sql_header_comments(filename)
File.open(filename, "a") { |f| f << "SET search_path TO #{connection.schema_search_path};\n\n" }
end
ref: https://stackoverflow.com/questions/78110476/is-loading-structure-sql-using-rake-an-atomic-transaction
If I load structure.sql using this command and it fails because of an error within the structure.sql file:
and then attempt to rerun after resolving the error, it fails again due to an existing enum type that is defined within structure.sql:
This enum type could only exist if the structure.sql got loaded in some capacity, and therefore suggests that it is a non-atomic transaction. This can cause odd an unexpected database states.
It looks like
rake db:schema:dump
ultimately callspg_dump
:Therefore,
bundle exec rake db:schema:load
should be able to be called with the--single-transaction
flag per pg_restore ( https://www.postgresql.org/docs/current/app-pgrestore.html ), potentially by adding to the array here: