I am currently using pg_dump
piped to gzip
piped to split
. But the problem with this is that all output files are always changed. So checksum-based backup always copies all data.
Are there any other good ways to perform an incremental backup of a PostgreSQL database, where a full database can be restored from the backup data?
For instance, if pg_dump
could make everything absolutely ordered, so all changes are applied only at the end of the dump, or similar.
If you're looking for a more efficient way to perform incremental backups in PostgreSQL, PostgreSQL 17 introduces a new feature that makes incremental backups much easier and more efficient.
PostgreSQL 17 has built-in support for incremental backups, which allows you to back up only the changes made since the last backup. This can significantly reduce the time and storage required for backups, especially for large databases.
Official documentation
I have made a sample dockerfile to explain and execute the concept of incremental backup :
# Use the official PostgreSQL 17 image as a base
FROM postgres:17
# Set environment variables for PostgreSQL
ENV POSTGRES_USER=postgres
ENV POSTGRES_PASSWORD=mysecretpassword
ENV POSTGRES_DB=testdb
# Install necessary tools as root
USER root
RUN apt-get update && apt-get install -y \
vim \
nano \
procps \
&& rm -rf /var/lib/apt/lists/*
# Switch to postgres user for initialization
USER postgres
# Reinitialize the database cluster
RUN rm -rf /var/lib/postgresql/data/* \
&& /usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/data
# RUN /usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/data
# Create directories for backups and archive with correct permissions
RUN mkdir -p /var/lib/postgresql/data/pg_archive \
/var/lib/postgresql/data/pg_base_backup \
/var/lib/postgresql/data/pg_incr_backup \
&& chown -R postgres:postgres /var/lib/postgresql/data/pg_archive \
/var/lib/postgresql/data/pg_base_backup \
/var/lib/postgresql/data/pg_incr_backup
# Copy the custom PostgreSQL configuration
COPY postgresql.conf /etc/postgresql/postgresql.conf
USER root
# Ensure custom configuration is included in the database configuration
RUN echo "include '/etc/postgresql/postgresql.conf'" >> /usr/share/postgresql/postgresql.conf.sample
# Expose PostgreSQL port
EXPOSE 5432
# Start the PostgreSQL server
CMD ["postgres"]
More Resources