Posted 9 months ago. Visible to the public.

Rails: How to restore a postgres dump from the past

It sometimes happen that a database dump, that would want to insert into your development database, does not match the current schema of the database. This often happens when you have an old dump, but your current setup is up to date with the the master.

Hint: In most cases it is sufficient to delete and recreate the local database in order to import the dump. If any problems occur, proceed as follows:

1. Figure out the original migration status of the dumpfile

  • Convert your dump to plaintext: pg_restore -f some.dump > some.dump.plain
  • Search for the schema_migrations table and note the latest migration timestamp. This is also the name of the migration file.

Example:

Copy
COPY public.schema_migrations (version) FROM stdin; 20200515080547 20200609102043 20200729124233

Here the latest migration would be 20200729124233.

2. Find the commit for this migration and check it out

  • Find the corresponding file for the timestamp:
Copy
find . -name '20200729124233*' ./db/migrate/20200729124233_add_users.rb
  • Find the commit that created this file first. If the migration was fixed by a later commit, you need to figure out manually which version was used in the dump. In this example we use the initial migration without the fix.
Copy
git log --reverse ./db/migrate/20200729124233_add_users.rb commit d4848ad598b2f02cbca7580a2b928d02996abeb4 Author: Some user <some.user@example.com> Date: Thu Jul 30 16:07:51 2020 +0200 Add users commit 7b7fa26b838e576a70dee79d71f194f6a673c500 Author: Some user <some.user@example.com> Date: Tue Aug 11 09:11:20 2020 +0200 Fix error in users migration
  • Checkout this commit SHA
Copy
git checkout d4848ad598b2f02cbca7580a2b928d02996abeb4

3. Delete the database, import the dump and migrate to the current branch

Copy
git checkout d4848ad598b2f02cbca7580a2b928d02996abeb4 bundle exec rake db:drop db:create db:migrate psql -f some.dump -U some_user -h localhost some_database # or geordi dump -l some.dump git checkout master # or your current working branch bundle exec rake db:migrate

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Avatar
Emanuel De
Last edit:
7 months ago
by Emanuel De
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Emanuel De to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more