dslr 0.2.0

Take lightning fast snapshots of your local Postgres databases.

---

Database Snapshot, List, and Restore

Take lightning fast snapshots of your local Postgres databases.

What is this?

DSLR is a tool that allows you to quickly take and restore database snapshots when you're writing database migrations, switching branches, or messing with SQL.

It's meant to be a spiritual successor to Stellar.

Important: DSLR is intended for development use only. It is not advisable to use DSLR on production databases.

Performance

DSLR is much faster than the standard pg_dump/pg_restore approach to snapshots.

DSLR is 8x faster at taking snapshots and 3x faster at restoring snapshots compared to the pg_dump/pg_restore approach.

Testing methodology

I spun up Postgres 12.3 using Docker, created a test database, and filled it with 1GB of random data using this script:

CREATE TABLE large_test (num1 bigint, num2 double precision, num3 double precision);

INSERT INTO large*test (num1, num2, num3)
SELECT round(random() * 10), random(), random() \_ 142
FROM generate_series(1, 20000000) s(i);

I used the following commands to measure the execution time:

time dslr snapshot my-snapshot
time dslr restore my-snapshot
time pg_dump -Fc -f export.dump
time pg_restore --no-acl --no-owner export.dump

I ran each command three times and plotted the mean in the chart.

Here's the raw data:

Command	Run	Execution time (seconds)
dslr snapshot	1	4.797
	2	4.650
	3	2.927
dslr restore	1	5.840
	2	4.122
	3	3.331
pg_dump	1	37.345
	2	36.227
	3	36.233
pg_restore	1	13.304
	2	13.148
	3	13.320

Install

pip install DSLR psycopg2 # or psycopg2-binary

Additionally, the DSLR export and import snapshot commands require pg_dump and pg_restore to be present in your PATH.

Configuration

You can tell DSLR which database to take snapshots of in a few ways:

DATABASE_URL

If the DATABASE_URL environment variable is set, DSLR will use this to connect to your target database.

export DATABASE_URL=postgres://username:password@host:port/database_name

dslr.toml

If a dslr.toml file exists in the current directory, DSLR will read its settings from there. DSLR will prefer this over the environment variable.

url = 'postgres://username:password@host:port/database_name'

--url option

Finally, you can explicitly pass the connection string via the --url option. This will override any of the above settings.

Usage

$ dslr snapshot my-first-snapshot
Created new snapshot my-first-snapshot

$ dslr restore my-first-snapshot
Restored database from snapshot my-first-snapshot

$ dslr list

  Name                Created
 ────────────────────────────────────
  my-first-snapshot   2 minutes ago

$ dslr rename my-first-snapshot fresh-db
Renamed snapshot my-first-snapshot to fresh-db

$ dslr delete some-old-snapshot
Deleted some-old-snapshot

$ dslr export my-feature-test
Exported snapshot my-feature-test to my-feature-test_20220730-075650.dump

$ dslr import snapshot-from-a-friend_20220730-080632.dump friend-snapshot
Imported snapshot friend-snapshot from snapshot-from-a-friend_20220730-080632.dump

How does it work?

DSLR takes snapshots by cloning databases using Postgres' Template Databases functionality. This is the main source of DSLR's speed.

This means that taking a snapshot is just creating a new database using the main database as the template. Restoring a snapshot is just deleting the main database and creating a new database using the snapshot database as the template. So on and so forth.

License

MIT