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:
- Convert your dump to plaintext:
pg_restore -f some.dump > some.dump.plain
- Search for the
schema_migrationstable and note the latest migration timestamp. This is also the name of the migration file.
COPY public.schema_migrations (version) FROM stdin; 20200515080547 20200609102043 20200729124233
Here the latest migration would be
- Find the corresponding file for the timestamp:
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.
git log --reverse ./db/migrate/20200729124233_add_users.rb commit d4848ad598b2f02cbca7580a2b928d02996abeb4 Author: Some user <email@example.com> Date: Thu Jul 30 16:07:51 2020 +0200 Add users commit 7b7fa26b838e576a70dee79d71f194f6a673c500 Author: Some user <firstname.lastname@example.org> Date: Tue Aug 11 09:11:20 2020 +0200 Fix error in users migration
- Checkout this commit SHA
git checkout d4848ad598b2f02cbca7580a2b928d02996abeb4
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