Giter VIP home page Giter VIP logo

dbtricks's Introduction

pgtricks

travis-badge license-badge pypi-badge downloads-badge

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

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

dbtricks's People

Contributors

akaihola avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    ๐Ÿ–– Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. ๐Ÿ“Š๐Ÿ“ˆ๐ŸŽ‰

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google โค๏ธ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.