Recently I worked on massive data migration in single postgres database. It involed running a bunch of scripts, that modify rows in several DB tables.
It was a repetitive process, that required multiple runs of the same script for debugging purposes. And having a convenient way of resetting the database was ‘a must’.
Restoring the whole database from an SQL dump is quite a painful process, that takes too much time(aroung 20 minutes on my machine). So I decided to find a faster way, that can make my ‘run-review-debug’ cycle shorter.
The solution, that I want to share is creating postgres DB from template DB.
- First, you should import full dump in some database, called for example
- Create a new database, using previous DB as template
create database database_development template database_development_template;
I extracted this task into a separate script on my github, so check it out.