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.3.tar.gz (14.2 kB view details)

Uploaded Source

Built Distribution

psqlsync-0.2.3-py3-none-any.whl (14.8 kB view details)

Uploaded Python 3

File details

Details for the file psqlsync-0.2.3.tar.gz.

File metadata

  • Download URL: psqlsync-0.2.3.tar.gz
  • Upload date:
  • Size: 14.2 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: poetry/1.3.2 CPython/3.10.12 Linux/6.2.0-1012-azure

File hashes

Hashes for psqlsync-0.2.3.tar.gz
Algorithm Hash digest
SHA256 a76dcfe7c43fe8ab15822e983059643259339cc4dc8ab91036891015b4122e88
MD5 df5c2827c7d82bb02ed9fd58f183fdfb
BLAKE2b-256 40451e4a9f914d49e0c4f1f94c6b2b93194753dedbb1b30e7d8b9942f1b4abef

See more details on using hashes here.

File details

Details for the file psqlsync-0.2.3-py3-none-any.whl.

File metadata

  • Download URL: psqlsync-0.2.3-py3-none-any.whl
  • Upload date:
  • Size: 14.8 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: poetry/1.3.2 CPython/3.10.12 Linux/6.2.0-1012-azure

File hashes

Hashes for psqlsync-0.2.3-py3-none-any.whl
Algorithm Hash digest
SHA256 fb7be6b9bb8120f727ce9c84e98992ea4c345112b863cb0a4912e5d47f9d489c
MD5 9225a39bff2afc57851e32bf5cad5cbc
BLAKE2b-256 b2c3c8ccd3e977da80c0b935f2ff0ce90d39aefeae44890e14dc35b4d0b2b97c

See more details on using hashes here.

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