==========
pgtricks

.. |build-badge| image:: https://github.com/akaihola/pgtricks/actions/workflows/python-package.yml/badge.svg :alt: master branch build status :target: https://github.com/akaihola/pgtricks/actions/workflows/python-package.yml?query=branch%3Amaster .. |license-badge| image:: https://img.shields.io/badge/License-BSD%203--Clause-blue.svg :alt: BSD 3 Clause license :target: https://github.com/akaihola/pgtricks/blob/master/LICENSE .. |pypi-badge| image:: https://img.shields.io/pypi/v/pgtricks :alt: Latest release on PyPI :target: https://pypi.org/project/pgtricks/ .. |downloads-badge| image:: https://pepy.tech/badge/pgtricks :alt: Number of downloads :target: https://pepy.tech/project/pgtricks .. |black-badge| image:: https://img.shields.io/badge/code%20style-black-000000.svg :alt: Source code formatted using Black :target: https://github.com/psf/black .. |changelog-badge| image:: https://img.shields.io/badge/-change%20log-purple :alt: Change log :target: https://github.com/akaihola/pgtricks/blob/master/CHANGES.rst .. |next-milestone| image:: https://img.shields.io/github/milestones/progress/akaihola/pgtricks/3?color=red&label=release%202.0.0 :alt: Next milestone :target: https://github.com/akaihola/pgtricks/milestone/3

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

.. |you-can-help| image:: https://img.shields.io/badge/-You%20can%20help-green?style=for-the-badge :alt: You can help .. |support| image:: https://img.shields.io/badge/-Support-green?style=for-the-badge :alt: Support .. _community support channel: https://github.com/akaihola/pgtricks/discussions

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:

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