This package contains two tools for backing up PostgreSQL database dumps.
We're asking the community kindly for help to review pull requests for _ . If you have a moment to spare, please take a look at one of them and shoot us a comment! | We have a community support channel on GitHub Discussions. Welcome to ask for help and advice! |
To install in a virtualenv or globally as a superuser:
pip install pgtricks
To install only for the current user:
pip install --user pgtricks
pg_dump_splitsort
is a handy script for pre-processing PostgreSQL's pg_dump
output to make it more suitable for diffing and storing in version control.
Usage:
pg_dump_splitsort <filename>.sql
The script splits the dump into the following files:
0000_prologue.sql
: everything up to the first COPY0001_<schema>.<table>.sql
:
:
NNNN_<schema>.<table>.sql
: COPY data for each table sorted by the first field9999_epilogue.sql
: everything after the last COPYThe files for table data are numbered so a simple sorted concatenation of all files can be used to re-create the database:
$ cat *.sql | psql <database>
I've found that a good way to take a quick look at differences between dumps is to use the meld tool on the whole directory:
$ meld old-dump/ new-dump/
Storing the dump in version control also gives a decent view on the differences. Here's how to configure git to use color in diffs:
# ~/.gitconfig
[color]
diff = true
[color "diff"]
frag = white blue bold
meta = white green bold
commit = white red bold
Note: If you have created/dropped/renamed tables, remember to delete all .sql files before post-processing the new dump.
The pg_incremental_backup
script
- makes a database dump using
pg_dump
- splits the dump into per-table files using
pg_dump_splitsort
- creates or commits changes into a local Git repository containing the dump
- pushes the changes to the remote repository
Usage:
pg_incremental_backup [-h] [--output-dir OUTPUT_DIR] database [remote]
positional arguments:
database
remote
optional arguments:
-h, --help show this help message and exit
--output-dir OUTPUT_DIR, -o OUTPUT_DIR
Thanks goes to these wonderful people (emoji key):
Albert Cai 🐛 |
Antti Kaihola 💻 |
Connor Sherson 💻 |
Jonathan 💻 |
Jose Luis 🐛 |
Phil Ayres 🐛 |
Seweryn Niemiec 🐛 |
mihuman 💻 |
tyctor 🐛 |
This project follows the all-contributors specification. Contributions of any kind are welcome!