• Stars
    star
    711
  • Rank 63,679 (Top 2 %)
  • Language
    Python
  • License
    Other
  • Created almost 9 years ago
  • Updated about 1 month ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Backup and recovery manager for PostgreSQL

GitHub release Build Status

pg_probackup

pg_probackup is a utility to manage backup and recovery of PostgreSQL database clusters. It is designed to perform periodic backups of the PostgreSQL instance that enable you to restore the server in case of a failure.

The utility is compatible with:

  • PostgreSQL 11, 12, 13, 14, 15;

As compared to other backup solutions, pg_probackup offers the following benefits that can help you implement different backup strategies and deal with large amounts of data:

  • Incremental backup: page-level incremental backup allows you to save disk space, speed up backup and restore. With three different incremental modes, you can plan the backup strategy in accordance with your data flow.
  • Incremental restore: page-level incremental restore allows you dramatically speed up restore by reusing valid unchanged pages in destination directory.
  • Merge: using this feature allows you to implement "incrementally updated backups" strategy, eliminating the need to do periodical full backups.
  • Validation: automatic data consistency checks and on-demand backup validation without actual data recovery
  • Verification: on-demand verification of PostgreSQL instance with the checkdb command.
  • Retention: managing WAL archive and backups in accordance with retention policy. You can configure retention policy based on recovery time or the number of backups to keep, as well as specify time to live (TTL) for a particular backup. Expired backups can be merged or deleted.
  • Parallelization: running backup, restore, merge, delete, verificaton and validation processes on multiple parallel threads
  • Compression: storing backup data in a compressed state to save disk space
  • Deduplication: saving disk space by not copying unchanged non-data files, such as _vm or _fsm
  • Remote operations: backing up PostgreSQL instance located on a remote system or restoring a backup remotely
  • Backup from standby: avoid extra load on master by taking backups from a standby server
  • External directories: backing up files and directories located outside of the PostgreSQL data directory (PGDATA), such as scripts, configuration files, logs, or SQL dump files.
  • Backup Catalog: get list of backups and corresponding meta information in plain text or JSON formats
  • Archive catalog: getting the list of all WAL timelines and the corresponding meta information in plain text or JSON formats
  • Partial Restore: restore only the specified databases or exclude the specified databases from restore.

To manage backup data, pg_probackup creates a backup catalog. This directory stores all backup files with additional meta information, as well as WAL archives required for point-in-time recovery. You can store backups for different instances in separate subdirectories of a single backup catalog.

Using pg_probackup, you can take full or incremental backups:

  • Full backups contain all the data files required to restore the database cluster from scratch.
  • Incremental backups only store the data that has changed since the previous backup. It allows to decrease the backup size and speed up backup operations. pg_probackup supports the following modes of incremental backups:
    • PAGE backup. In this mode, pg_probackup scans all WAL files in the archive from the moment the previous full or incremental backup was taken. Newly created backups contain only the pages that were mentioned in WAL records. This requires all the WAL files since the previous backup to be present in the WAL archive. If the size of these files is comparable to the total size of the database cluster files, speedup is smaller, but the backup still takes less space.
    • DELTA backup. In this mode, pg_probackup read all data files in PGDATA directory and only those pages, that where changed since previous backup, are copied. Continuous archiving is not necessary for it to operate. Also this mode could impose read-only I/O pressure equal to Full backup.
    • PTRACK backup. In this mode, PostgreSQL tracks page changes on the fly. Continuous archiving is not necessary for it to operate. Each time a relation page is updated, this page is marked in a special PTRACK bitmap for this relation. As one page requires just one bit in the PTRACK fork, such bitmaps are quite small. Tracking implies some minor overhead on the database server operation, but speeds up incremental backups significantly.

Regardless of the chosen backup type, all backups taken with pg_probackup support the following strategies of WAL delivery:

  • Autonomous backups streams via replication protocol all the WAL files required to restore the cluster to a consistent state at the time the backup was taken. Even if continuous archiving is not set up, the required WAL segments are included into the backup.
  • Archive backups rely on continuous archiving.

ptrack support

PTRACK backup support provided via following options:

  • vanilla PostgreSQL 11, 12, 13, 14, 15 with ptrack extension
  • Postgres Pro Standard 11, 12, 13, 14
  • Postgres Pro Enterprise 11, 12, 13, 14

Limitations

pg_probackup currently has the following limitations:

  • The server from which the backup was taken and the restored server must be compatible by the block_size and wal_block_size parameters and have the same major release number.
  • Remote backup via ssh on Windows currently is not supported.
  • When running remote operations via ssh, remote and local pg_probackup versions must be the same.

Documentation

Documentation can be found at github and Postgres Professional documentation

Development

  • Stable version state can be found under the respective release tag.
  • master branch contains minor fixes that are planned to the nearest minor release.
  • Upcoming major release is developed in a release branch i.e. release_2_6.

For detailed release plans check Milestones

Installation and Setup

Windows Installation

Installers are available in release assets. Latests.

Linux Installation

pg_probackup for vanilla PostgreSQL

#DEB Ubuntu|Debian Packages
sudo sh -c 'echo "deb [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb/ $(lsb_release -cs) main-$(lsb_release -cs)" > /etc/apt/sources.list.d/pg_probackup.list'
sudo wget -O - https://repo.postgrespro.ru/pg_probackup/keys/GPG-KEY-PG_PROBACKUP | sudo apt-key add - && sudo apt-get update
sudo apt-get install pg-probackup-{15,14,13,12,11}
sudo apt-get install pg-probackup-{15,14,13,12,11}-dbg

#DEB-SRC Packages
sudo sh -c 'echo "deb-src [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb/ $(lsb_release -cs) main-$(lsb_release -cs)" >>\
  /etc/apt/sources.list.d/pg_probackup.list' && sudo apt-get update
sudo apt-get source pg-probackup-{15,14,13,12,11}

#DEB Astra Linix Orel
sudo sh -c 'echo "deb [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb/ stretch main-stretch" > /etc/apt/sources.list.d/pg_probackup.list'
sudo wget -O - https://repo.postgrespro.ru/pg_probackup/keys/GPG-KEY-PG_PROBACKUP | sudo apt-key add - && sudo apt-get update
sudo apt-get install pg-probackup-{15,14,13,12,11}{-dbg,}

#RPM Centos Packages
rpm -ivh https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm
yum install pg_probackup-{15,14,13,12,11}
yum install pg_probackup-{15,14,13,12,11}-debuginfo

#RPM RHEL Packages
rpm -ivh https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-rhel.noarch.rpm
yum install pg_probackup-{15,14,13,12,11}
yum install pg_probackup-{15,14,13,12,11}-debuginfo

#RPM Oracle Linux Packages
rpm -ivh https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-oraclelinux.noarch.rpm
yum install pg_probackup-{15,14,13,12,11}
yum install pg_probackup-{15,14,13,12,11}-debuginfo

#SRPM Centos|RHEL|OracleLinux Packages
yumdownloader --source pg_probackup-{15,14,13,12,11}

#RPM SUSE|SLES Packages
zypper install --allow-unsigned-rpm -y https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-suse.noarch.rpm
zypper --gpg-auto-import-keys install -y pg_probackup-{15,14,13,12,11}
zypper install pg_probackup-{15,14,13,12,11}-debuginfo

#SRPM SUSE|SLES Packages
zypper si pg_probackup-{15,14,13,12,11}

#RPM ALT Linux 8
sudo sh -c 'echo "rpm https://repo.postgrespro.ru/pg_probackup/rpm/latest/altlinux-p8 x86_64 vanilla" > /etc/apt/sources.list.d/pg_probackup.list'
sudo apt-get update
sudo apt-get install pg_probackup-{15,14,13,12,11}
sudo apt-get install pg_probackup-{15,14,13,12,11}-debuginfo

#RPM ALT Linux 9
sudo sh -c 'echo "rpm https://repo.postgrespro.ru/pg_probackup/rpm/latest/altlinux-p9 x86_64 vanilla" > /etc/apt/sources.list.d/pg_probackup.list'
sudo apt-get update
sudo apt-get install pg_probackup-{15,14,13,12,11}
sudo apt-get install pg_probackup-{15,14,13,12,11}-debuginfo

#RPM ALT Linux 10
sudo sh -c 'echo "rpm https://repo.postgrespro.ru/pg_probackup/rpm/latest/altlinux-p10 x86_64 vanilla" > /etc/apt/sources.list.d/pg_probackup.list'
sudo apt-get update
sudo apt-get install pg_probackup-{15,14,13,12,11}
sudo apt-get install pg_probackup-{15,14,13,12,11}-debuginfo

Once you have pg_probackup installed, complete the setup.

For users of Postgres Pro products, commercial editions of pg_probackup are available for installation from the corresponding Postgres Pro product repository.

Building from source

Linux

To compile pg_probackup, you must have a PostgreSQL installation and raw source tree. Execute this in the module's directory:

make USE_PGXS=1 PG_CONFIG=<path_to_pg_config> top_srcdir=<path_to_PostgreSQL_source_tree>

The alternative way, without using the PGXS infrastructure, is to place pg_probackup source directory into contrib directory and build it there. Example:

cd <path_to_PostgreSQL_source_tree> && git clone https://github.com/postgrespro/pg_probackup contrib/pg_probackup && cd contrib/pg_probackup && make

Windows

Currently pg_probackup can be build using only MSVC 2013. Build PostgreSQL using pgwininstall or PostgreSQL instruction with MSVC 2013. If zlib support is needed, src/tools/msvc/config.pl must contain path to directory with compiled zlib. Example

CALL "C:\Program Files (x86)\Microsoft Visual Studio 12.0\VC\vcvarsall" amd64
SET PATH=%PATH%;C:\Perl64\bin
SET PATH=%PATH%;C:\msys64\usr\bin
gen_probackup_project.pl C:\path_to_postgresql_source_tree

License

This module available under the license similar to PostgreSQL.

Feedback

Do not hesitate to post your issues, questions and new ideas at the issues page.

Authors

Postgres Professional, Moscow, Russia.

Credits

pg_probackup utility is based on pg_arman, that was originally written by NTT and then developed and maintained by Michael Paquier.

Localization files (*.po)

Description of how to add new translation languages.

  1. Add a flag --enable-nls in configure.
  2. Build postgres.
  3. Adding to nls.mk in folder pg_probackup required files in GETTEXT_FILES.
  4. In folder pg_probackup do 'make update-po'.
  5. As a result, the progname.pot file will be created. Copy the content and add it to the file with the desired language.
  6. Adding to nls.mk in folder pg_probackup required language in AVAIL_LANGUAGES.

For more information, follow the link below: https://postgrespro.ru/docs/postgresql/12/nls-translator

More Repositories

1

rum

RUM access method - inverted index with additional information in posting lists
C
725
star
2

jsquery

JsQuery – json query language with GIN indexing support
C
702
star
3

pg_pathman

Partitioning tool for PostgreSQL
C
583
star
4

zson

ZSON is a PostgreSQL extension for transparent JSONB compression
C
539
star
5

aqo

Adaptive query optimization for PostgreSQL
C
428
star
6

imgsmlr

Similar images search for PostgreSQL
C
255
star
7

mamonsu

Python
186
star
8

vops

C
165
star
9

postgres_cluster

Various experiments with PostgreSQL clustering
C
151
star
10

pg_query_state

Tool for query progress monitoring in PostgreSQL
C
150
star
11

pg_wait_sampling

Sampling based statistics of wait events
C
144
star
12

testgres

Testing framework for PostgreSQL and its extensions
Python
141
star
13

hunspell_dicts

Hunspell dictionaries for PostgreSQL
TSQL
63
star
14

pg_credereum

Prototype of PostgreSQL extension bringing some properties of blockchain to the relational DBMS
C
62
star
15

sr_plan

Save and restore query plans in PostgreSQL
C
61
star
16

mmts

multimaster
C
57
star
17

raft

Raft protocol implementation in C
C
49
star
18

ptrack

Block-level incremental backup engine for PostgreSQL
C
45
star
19

pg_trgm_pro

C
44
star
20

sqljson

C
38
star
21

postgresql.pthreads

Port of postgresql for pthreads
C
31
star
22

postgresql.builtin_pool

Version of PostgreSQL with built-in connection pooling
C
29
star
23

pg_dtm

Distributed transaction manager
C
27
star
24

postgrespro

Postgres Professional fork of PostgreSQL
C
27
star
25

lsm3

LSM tree implementation based on standard B-Tree
C
26
star
26

lsm

RocksDB FDW for PostgreSQL
C
24
star
27

tsvector2

Extended tsvector type for PostgreSQL
C
20
star
28

pg_backtrace

Show backtrace for errors and signals
C
20
star
29

pgwininstall

PostgreSQL Windows installer
Roff
19
star
30

monq

MonQ - PostgreSQL extension for MongoDB-like queries to jsonb data
C
17
star
31

pg_tsparser

pg_tsparser - parser for text search
C
16
star
32

pgsphere

PgSphere provides spherical data types, functions, operators, and indexing for PostgreSQL.
C
16
star
33

hstore_ops

Better operator class for hstore: smaller index and faster @> queries.
C
16
star
34

undam

Undo storage implementation
C
15
star
35

pg_logging

PostgreSQL logging interface
C
15
star
36

pg_ycsb

YCSB-like benchmark for pgbench
PLpgSQL
15
star
37

tsexample

Example of custom postgresql full text search parser, dictionaries and configuration
C
14
star
38

libblobstamper

Framework for Structure Aware Fuzzing. Allows to build own stamps that would convert pulp-data that came from fuzzer to data with structure you need
C++
14
star
39

pg_oltp_bench

Extension and scripts to run analogue of sysbench OLTP test using pgbench
PLpgSQL
13
star
40

pg_grab_statement

PostgreSQL extension for recoding workload of specific database
C
12
star
41

tsexact

PostgreSQL fulltext search addon
C
11
star
42

jsonbd

JSONB compression method for PostgreSQL
C
10
star
43

rusmorph

Russian morphological dictionary (rusmorph) for Postgres based on libmorph library: https://github.com/big-keva/libmorph
C++
10
star
44

pg_parallizator

C
9
star
45

memstat

C
9
star
46

plantuner

C
8
star
47

pg_pageprep

PostgreSQL extension which helps to prepare heap pages for migration to 64bit XID page format (PostgresPro Enterprise)
C
8
star
48

wildspeed

C
7
star
49

pgbouncer

C
6
star
50

bztree

C++
6
star
51

pg_pathman_build

Prerequisites for pg_pathman building
Shell
5
star
52

snapfs

Fast recoverry and snapshoting
C
4
star
53

pq2jdbc

Java
4
star
54

jsonb_schema

Store jsonb schema separately from data
C
4
star
55

postgrespro-os-templates

Packer templates for building minimal baseboxes
Shell
3
star
56

pg_variables

Session wide variables for PostgreSQL
C
3
star
57

pg_hint_plan

C
2
star
58

pgpro_redefinition

PLpgSQL
2
star
59

snowball_ext

The Snowball dictionary template extension for PostgreSQL
C
2
star
60

jsonb_plpython

PLpgSQL
1
star
61

dict_regex

C
1
star
62

pg-mark

Postgres benchmarking framework
R
1
star
63

anyarray

contrib package for working with 1-D arrays
C
1
star
64

libpq_compression

C
1
star