Dump and restore DB data on PostgreSQL for Ruby on Rails


This article shows a flow to dump and restore DB data on PostgreSQL.

🗻 Dump

Dump DB data and compress the data.

PGPASSWORD=DB_PASSWORD pg_dump -U USER_NAME -h localhost -p 5432 DB_NAME -f DUMP_FILE_PATH
tar -czvf DUMP_FILE_PATH.tar.gz DUMP_FILE_PATH

🐹 Restore

De-compress the compressed data and import the data to DB on PostgreSQL.

tar zxvf DUMP_FILE_PATH.tar.gz -C DUMP_FILE_PATH

# Re-create DB
DISABLE_DATABASE_ENVIRONMENT_CHECK=1 bundle exec rake db:drop && bundle exec rake db:create

# Import DB configuration
psql DB_NAME < OUTPUT_FILE_PATH

😼 Repair sequence

The upper restore flow does not support to restore sequences on DB. So the following rake task repairs the sequences on DB.

Please create ./lib/tasks/db_restore.rake and set the following code.

namespace :db_repair do
desc "Repair sequences in PostgreSQL"
task sequences: :environment do
ActiveRecord::Base.connection.tables.each do |table|
if ActiveRecord::Base.connection.column_exists?(table, "id")
result = ActiveRecord::Base.connection.select_one("SELECT max(id) FROM #{table}")
if result["max"]
puts "Update public.#{table}_id_seq = #{result['max']}"
ActiveRecord::Base.connection.select_one("SELECT setval('public.#{table}_id_seq', #{result['max']}, true);")
end
end
end
end
end

After creating the upper file, please execute rake db_repair:sequences.

🐠 References

🖥 Recommended VPS Service

VULTR provides high performance cloud compute environment for you. Vultr has 15 data-centers strategically placed around the globe, you can use a VPS with 512 MB memory for just $ 2.5 / month ($ 0.004 / hour). In addition, Vultr is up to 4 times faster than the competition, so please check it => Check Benchmark Results!!