3 ms·

i use templates to create a "quick save" backup of a database quickly during development: createdb -T old_db new_db

silviogutierrez 6y ago
Neat trick. Is this any faster than pg_dump and pg_load? Note: there's plenty of optimization you can apply to pg_dump and even use pg_restore, none applied below. Like so: function save_database() { mkdir -p /tmp/database-snapshots DATABASE_NAME=$PROJECT_NAME pg_dump -Fc "$DATABASE_NAME" >"/tmp/database-snapshots/$DATABASE_NAME.dump" } function restore_database() { DATABASE_NAME=$PROJECT_NAME DATABASE_DUMP="/tmp/database-snapshots/$DATABASE_NAME.dump" if [ ! -f "$DATABASE_DUMP" ]; then echo "No dump to restore" return else psql postgres -c 'SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid()' psql postgres -c "DROP DATABASE IF EXISTS \"$DATABASE_NAME\"" psql postgres -c "CREATE DATABASE \"$DATABASE_NAME\"" pg_restore -Fc -j 8 -d "$DATABASE_NAME" "$DATABASE_DUMP" fi }
- gkop 6y ago
  Yes, it's significantly faster because it doesn't require rebuilding indexes (among other reasons probably). Downside is it consumes more disk space. Stellar is a tool that wraps the template mechanism, and has some benchmarks: https://github.com/fastmonkeys/stellar https://github.com/fastmonkeys/stellar
  - jpitz 6y ago
    My understanding has always been that creating a database is essentially a series of file copy operations from the template.
    - gkop 6y ago
      That may be true! The point is the template has the indexes already built ready to copy. Where the dump file does not.
  - gkop 6y ago
    Oh another key downside I remembered of the template mechanism, at least with Postgres 9.6, is that you can't have any connections open to the source database when making a snapshot copy.

Neat trick. Is this any faster than pg_dump and pg_load? Note: there's plenty of optimization you can apply to pg_dump and even use pg_restore, none applied below. Like so: function save_database() { mkdir -p /tmp/database-snapshots DATABASE_NAME=$PROJECT_NAME pg_dump -Fc "$DATABASE_NAME" >"/tmp/database-snapshots/$DATABASE_NAME.dump" } function restore_database() { DATABASE_NAME=$PROJECT_NAME DATABASE_DUMP="/tmp/database-snapshots/$DATABASE_NAME.dump" if [ ! -f "$DATABASE_DUMP" ]; then echo "No dump to restore" return else psql postgres -c 'SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid()' psql postgres -c "DROP DATABASE IF EXISTS \"$DATABASE_NAME\"" psql postgres -c "CREATE DATABASE \"$DATABASE_NAME\"" pg_restore -Fc -j 8 -d "$DATABASE_NAME" "$DATABASE_DUMP" fi }

gkop 6y ago
Yes, it's significantly faster because it doesn't require rebuilding indexes (among other reasons probably). Downside is it consumes more disk space. Stellar is a tool that wraps the template mechanism, and has some benchmarks: https://github.com/fastmonkeys/stellar https://github.com/fastmonkeys/stellar
- jpitz 6y ago
  My understanding has always been that creating a database is essentially a series of file copy operations from the template.
  - gkop 6y ago
    That may be true! The point is the template has the indexes already built ready to copy. Where the dump file does not.
- gkop 6y ago
  Oh another key downside I remembered of the template mechanism, at least with Postgres 9.6, is that you can't have any connections open to the source database when making a snapshot copy.