Skip to content
/ DSLR Public

Take lightning fast snapshots of your local Postgres databases.

License

Notifications You must be signed in to change notification settings

mixxorz/DSLR

Repository files navigation



The DSLR logo


PyPI version PyPI Supported Python Versions GitHub Actions (Code quality and tests)

A terminal showing DSLR's command line interface.


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.

A chart comparing the execution time between DSLR and pg_dump/pg_restore. For snapshot and restore, DSLR took 4.125 seconds and 4.431 seconds respectively. pg_dump/pg_restore took 36.602 seconds and 13.257 seconds respectively.

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

Install using pipx


pipx install DSLR[psycopg2] # or psycopg2-binary

Note: The DSLR export and import snapshot commands require pg_dump and pg_restore to be present in your PATH, so you will need the Postgres CLI utilities if you want to use those commands.

Shell completion

Bash

Add this to ~/.bashrc:

eval "$(_DSLR_COMPLETE=bash_source dslr)"

Zsh

Add this to ~/.zshrc:

eval "$(_DSLR_COMPLETE=zsh_source dslr)"

Fish

Add this to ~/.config/fish/completions/dslr.fish:

eval (env _DSLR_COMPLETE=fish_source dslr)

This is the same file used for the activation script method below. For Fish it’s probably always easier to use that method.

Using eval means that the command is invoked and evaluated every time a shell is started, which can delay shell responsiveness. To speed it up, write the generated script to a file, then source that.

Bash

Save the script somewhere.

_DSLR_COMPLETE=bash_source dslr > ~/.dslr-complete.bash

Source the file in ~/.bashrc.

. ~/.dslr-complete.bash

Zsh

Save the script somewhere.

_DSLR_COMPLETE=zsh_source dslr > ~/.dslr-complete.zsh

Source the file in ~/.zshrc.

. ~/.dslr-complete.zsh

Fish

Save the script to ~/.config/fish/completions/foo-bar.fish:

_DSLR_COMPLETE=fish_source dslr > ~/.config/fish/completions/dslr.fish

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            Size
 ─────────────────────────────────────────────
  my-first-snapshot   2 minutes ago   3253 kB

$ 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

To force overwriting an existing snapshot in non-interactive shell use the flag -y:

$ dslr snapshot my-first-snapshot -y
Updated snapshot my-first-snapshot

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.

Contributors

Contributors

License

MIT

About

Take lightning fast snapshots of your local Postgres databases.

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages