Handy helpers for PostgreSQL users
BSD-3-CLAUSE License
|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.
+------------------------------------------------+--------------------------------+
| |you-can-help| | |support| |
+================================================+================================+
| We're asking the community kindly for help to | We have a |
| review pull requests for |next-milestone|_ . | community support channel
_ |
| If you have a moment to spare, please take a | on GitHub Discussions. Welcome |
| look at one of them and shoot us a comment! | to ask for help and advice! |
+------------------------------------------------+--------------------------------+
.. |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
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 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.
The pg_incremental_backup
script
pg_dump
pg_dump_splitsort
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
_):
.. raw:: html
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
|stargazers|_
.. |stargazers| image:: https://starchart.cc/akaihola/pgtricks.svg .. _stargazers: https://starchart.cc/akaihola/pgtricks