pgtricks 1.1.0
PostgreSQL utilities
License: BSD
Requires Python: >=3.7
Keywords: database, postgresql, backup, git

==========
 pgtricks
==========

|build-badge| |license-badge| |pypi-badge| |downloads-badge| |black-badge| |changelog-badge|

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



.. _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:

| ``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 ✨
===============

See README.rst_ for the list of contributors.

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

.. _README.rst: https://github.com/akaihola/pgtricks/blob/master/README.rst
.. _emoji key: https://allcontributors.org/docs/en/emoji-key
.. _all-contributors: https://allcontributors.org


GitHub stars trend
==================

|stargazers|_

.. |stargazers| image:: https://starchart.cc/akaihola/pgtricks.svg
.. _stargazers: https://starchart.cc/akaihola/pgtricks