Skip to main content

Tool to create basic PostgreSQL backups and restore them from local files.

Project description

PostgreSQLSync

Tool to create basic PostgreSQL dumps and restore them from local files.

Don't use this for production-critical backups, SQL dumps (the method used by this library) are neither efficient nor safe for that purpose. Instead, use a tool like Barman.

Based on postgres_manage_python by valferon. Thanks to him for the core logic.

This was forked to create a more minimal and maintainable package for the specific use case of syncing a populated testing database.

Getting Started

Setup

This library requires the installation of a PostgreSQL client, as it runs pg_restore and pg_dump directly from the command line, as there are no Python bindings for these functions, unfortunately. Use the below instructions to install the PostgreSQL 14 client (instructions from here). Note that this installs some basic tools (see the first line of the shell below), replace them at your discretion if e.g. you don't want to bloat your container environment.

sudo apt install curl ca-certificates gnupg lsb-release
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt update
sudo apt install postgresql-client-14
  • Create configuration file (ie. sample.toml)
[setup]
storage_engine= "LOCAL"

[local_storage]
path = "./backups/"

[postgresql]
host="<your_psql_addr(probably 127.0.0.1)>"
port="<your_psql_port(probably 5432)>"
db="<your_db_name>"
user="<your_username>"
password="<your_password>"

Usage

  • List databases on a PostgreSQL server

    psqlsync --config sample.toml --action list_dbs
    
  • Create database backup and store it (based on config file details)

    psqlsync --config sample.toml --action backup --verbose
    
  • List previously created database backups available on storage engine

    psqlsync --config sample.toml --action list
    
  • Restore previously created database backups available on storage engine (check available dates with list action, it matches the time string, so any unique part of the string suffices)

    pslsync --config sample.toml --action restore --date 2021
    
  • Restore previously created database backups into a new destination database

    pslsync --config sample.toml --action restore --date 20211219-14 --dest-db new_DB_name
    
  • Enter password in prompt, so it does not have to be stored in plaintext in the config file

    pslsync --config sample.toml --action backup --prompt-pass
    Password for database: 
    

Command help

usage: psqlsync [-h] --action action [--time YYYYMMdd-HHmmss] [--dest-db dest_db] [--verbose] --config CONFIG
                [--prompt-pass]

psqlsync

optional arguments:
  -h, --help            show this help message and exit
  --action action       'list' (backups), 'list_dbs' (available dbs), 'restore' (requires --time), 'backup'
  --time YYYYMMdd-HHmmss
                        Time to use for restore (show with --action list). If unique, will smart match. (If
                        there's just one backup matching YYYMM, providing that is enough)
  --dest-db dest_db     Name of the new restored database
  --verbose             Verbose output
  --config CONFIG       Database configuration file path (.toml)
  --prompt-pass         Show a password prompt instead of the password defined in the config.

Run programmatically

The backup and restore action have been seperated into easily callable Python functions in psqlsync.actions. You can import this module and call these functions from your Python code.

Authors

License

The original code, created by valferon in the postgres_manage_python repository, is licensed under the MIT License. This project as a whole, most notably my original code, is licensed under the Apache License v2.0.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

psqlsync-0.2.1.tar.gz (14.2 kB view hashes)

Uploaded Source

Built Distribution

psqlsync-0.2.1-py3-none-any.whl (14.8 kB view hashes)

Uploaded Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page