Skip to content

akaihola/pgtricks

Repository files navigation

pgtricks

master branch build status BSD 3 Clause license Latest release on PyPI Number of downloads Source code formatted using Black Change log

This package contains two tools for backing up PostgreSQL database dumps.

You can help Support
We're asking the community kindly for help to review pull requests for |next-milestone|_ . 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!

Installing

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

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 COPY
0001_<schema>.<table>.sql
:
:
NNNN_<schema>.<table>.sql: COPY data for each table sorted by the first field
9999_epilogue.sql: everything after the last COPY

The 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.

pg_incremental_backup

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

Contributors ✨

Thanks goes to these wonderful people (emoji key):

@oldcai
Albert Cai

🐛
@akaihola
Antti Kaihola

💻
@connorsherson
Connor Sherson

💻
@jomonson
Jonathan

💻
@jescobar87
Jose Luis

🐛
@philayres
Phil Ayres

🐛
@thugcee
Seweryn Niemiec

🐛
@mihuman
mihuman

💻
@tyctor
tyctor

🐛

This project follows the all-contributors specification. Contributions of any kind are welcome!

GitHub stars trend

stargazers