fast_restore_large_postgres_database.md

This is a really fast way to restore a large Postgres database by omiting the indexes and restoring them later (if necessary).

1. Dump database.

    pg_dump -Fc --no-owner my_db -f ~/my_db.dump
   

2. Create the summary files. One for the indexes and another for everything else.

    pg_restore -l ~/my_db.dump | grep -v 'INDEX public' > ~/my_db.list
    pg_restore -l ~/my_db.dump | grep 'INDEX public' > ~/my_db-indexes.list
   

3. Restore everything except the indexes (should be pretty fast).

    pg_restore --no-owner --jobs=4 --verbose --use-list=$HOME/my_db.list --dbname=my_db ~/my_db.dump
   

4. If desired, restore the indexes (this part's slow and index creation locks the database).

    pg_restore --no-owner --jobs=4 --verbose --use-list=$HOME/my_db-indexes.list --dbname=my_db ~/my_db.dump
   

Thanks for the gist. However, unless you really don't want the indexes, this doesn't really speed up the overall restore. I timed both methods on a 70GB database. The single-command restore took 99min, whereas this method took 97min (although only loading the data without indexes took just 36min).