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:

CopyCOPY 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:

Copyfind . -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.

Copygit 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

Copygit checkout d4848ad598b2f02cbca7580a2b928d02996abeb4

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

Copygit 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