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

Prev