postgresqlbackup

Best method for PostgreSQL incremental backup


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.


Solution

  • 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