{"id":2850,"date":"2025-04-18T14:54:21","date_gmt":"2025-04-18T14:54:21","guid":{"rendered":"https:\/\/bynatree.com\/?p=2850"},"modified":"2025-04-18T14:54:21","modified_gmt":"2025-04-18T14:54:21","slug":"seamless-backup-strategies-in-postgresql-native-tools-that-work","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/04\/18\/seamless-backup-strategies-in-postgresql-native-tools-that-work\/","title":{"rendered":"Seamless Backup Strategies in PostgreSQL: Native Tools That Work"},"content":{"rendered":"<blockquote><p><span style=\"font-weight: 400;\">In continuation of our blog series on effectively implementing backup strategies in a production environment, this article delves into the topic of Seamless Backup Strategies in PostgreSQL: Native Tools That Work. We present the following options for taking backups, exploring each in detail here, while additional strategies will be covered in dedicated posts in upcoming blogs.<\/span><\/p><\/blockquote>\n<p>&nbsp;<\/p>\n<p><b>Backup in Postgresql:<\/b><\/p>\n<p><span style=\"font-weight: 400;\">A backup is a reliable and secure copy of a database cluster that serves multiple purposes, such as establishing a new standby, performing Point-In-Time Recovery (PITR), or recovering from hardware failures. In PostgreSQL, backups can be executed using various strategies to optimize storage utilization, recovery speed, and overall performance.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">PostgreSQL employs Write-Ahead Logging (WAL) extensively to enable incremental-like backups. Tools such as pg_basebackup (for physical backups) and pg_dump\/pg_dumpall (for logical backups) play a significant role in the backup process. PITR is achieved by combining a full backup with WAL files, effectively supporting incremental recovery.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400;\">Backup Option in Postgres for Integrated Choices:<\/span><\/p>\n<p><b>Full Backup<\/b><\/p>\n<p><span style=\"font-weight: 400;\">A full backup is a complete copy of the entire PostgreSQL database cluster (including all databases, tables, indexes, and transaction logs).<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">It serves as the base for differential and incremental backups.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Recovery from a full backup alone restores the database to the exact state at the time of the backup.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Example: pg_dumpall or pg_basebackup for physical backups.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><b>Differential Backup<\/b><\/p>\n<p><span style=\"font-weight: 400;\">A differential backup captures only the changes made since the last full backup.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">It does not depend on previous differential backups, only on the last full backup.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Over time, differential backups grow in size but simplify recovery (only the full backup + latest differential backup are needed).<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Example: pg_basebackup only takes full backups, but WAL archiving allows differential-like recovery.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><b>Incremental Backup<\/b><\/p>\n<p><span style=\"font-weight: 400;\">An incremental backup stores only the changes made since the last backup of any kind (full, differential, or incremental).<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">This results in smaller backup sizes but requires a chain of backups for recovery.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Recovery involves applying all incremental backups in sequence since the last full or differential backup.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Example: Using WAL archiving and replaying logs (pg_wal files) from a specific point in time.<\/span><\/p>\n<p>&nbsp;<\/p>\n<h3><b>Integrated Choices:<\/b><\/h3>\n<p><span style=\"font-weight: 400;\">The two initiatives that come with PostgreSQL serve as a base for the integrated choices.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">pgdump \/ pgdumpall (logical)<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Pg_basebackup (physical)<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Starting with the Integrated choices we have two options (pgdump \/ pgdumpall) and pg_basebackup respectively.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">\u00a0<\/span><\/p>\n<h3><b>pgdump \/ pgdumpall (logical):<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">pgdump works on a single database, while pg_dumpall backs up all databases within a PostgreSQL cluster.Restoring requires re-executing the SQL scripts, which is slower but allows fine-grained control over the data.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">pg_dump dumps a database as a text file or to other formats.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">For this example we are using sample database dvdrental.<\/span><\/p>\n<pre class=\"theme:solarized-light lang:default decode:true \">[postgres@mysql-02 ~]$ pg_dump -U postgres -p 5432 -d dvdrental -f \/var\/lib\/pgsql\/dvdrental_t1.sql\npg_dumpall extracts a PostgreSQL database cluster into an SQL script file.\n\nUsage:\n  pg_dumpall [OPTION]...\n\nGeneral options:\n  -f, --file=FILENAME          output file name\n  -v, --verbose                verbose mode\n  -V, --version                output version information, then exit\n  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock\n  -?, --help                   show this help, then exit\n\nOptions controlling the output content:\n  -a, --data-only              dump only the data, not the schema\n  -c, --clean                  clean (drop) databases before recreating\n  -E, --encoding=ENCODING      dump the data in encoding ENCODING\n  -g, --globals-only           dump only global objects, no databases\n  -O, --no-owner               skip restoration of object ownership\n  -r, --roles-only             dump only roles, no databases or tablespaces\n  -s, --schema-only            dump only the schema, no data\n  -S, --superuser=NAME         superuser user name to use in the dump\n  -t, --tablespaces-only       dump only tablespaces, no databases or roles\n  -x, --no-privileges          do not dump privileges (grant\/revoke)\n  --binary-upgrade             for use by upgrade utilities only\n  --column-inserts             dump data as INSERT commands with column names\n  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting\n  --disable-triggers           disable triggers during data-only restore\n  --exclude-database=PATTERN   exclude databases whose name matches PATTERN\n  --extra-float-digits=NUM     override default setting for extra_float_digits\n  --if-exists                  use IF EXISTS when dropping objects\n  --inserts                    dump data as INSERT commands, rather than COPY\n  --load-via-partition-root    load partitions via the root table\n  --no-comments                do not dump comments\n  --no-publications            do not dump publications\n  --no-role-passwords          do not dump passwords for roles\n  --no-security-labels         do not dump security label assignments\n  --no-subscriptions           do not dump subscriptions\n  --no-sync                    do not wait for changes to be written safely to disk\n  --no-table-access-method     do not dump table access methods\n  --no-tablespaces             do not dump tablespace assignments\n  --no-toast-compression       do not dump TOAST compression methods\n  --no-unlogged-table-data     do not dump unlogged table data\n  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands\n  --quote-all-identifiers      quote all identifiers, even if not key words\n  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts\n  --use-set-session-authorization\n                               use SET SESSION AUTHORIZATION commands instead of\n                               ALTER OWNER commands to set ownership\n\nConnection options:\n  -d, --dbname=CONNSTR     connect using connection string\n  -h, --host=HOSTNAME      database server host or socket directory\n  -l, --database=DBNAME    alternative default database\n  -p, --port=PORT          database server port number\n  -U, --username=NAME      connect as specified database user\n  -w, --no-password        never prompt for password\n  -W, --password           force password prompt (should happen automatically)\n  --role=ROLENAME          do SET ROLE before dump\n\nIf -f\/--file is not used, then the SQL script will be written to the standard\noutput.\n[postgres@mysql-02 ~]$ pg_dumpall -U postgres -p 5432 -f \/var\/lib\/pgsql\/dvdrental_t2.sql\n\n<\/pre>\n<p><b>To restore the backups use the psql to restore the sql format backups (by default pg_dumpall supports only plain SQL format).<\/b><\/p>\n<p><b>For Single database Restore:<\/b><\/p>\n<pre class=\"theme:solarized-light lang:pgsql decode:true \">psql -U postgres -p 5432 -d dvdrental -f \/var\/lib\/pgsql\/dvdrental_t1.sql<\/pre>\n<p><b>For Restoring all the databases, roles and globals:<\/b><\/p>\n<pre class=\"theme:solarized-light lang:pgsql decode:true \">psql -U postgres -f \/var\/lib\/pgsql\/dvdrental_t2.sql<\/pre>\n<h3><b>Pg_basebackup (physical):<\/b><\/h3>\n<p><span style=\"font-weight: 400;\">\u00a0 \u00a0 A physical backup tool that creates a binary copy of the entire PostgreSQL database cluster, including configuration files.Used for setting up replication or disaster recovery since it captures everything in its current state.Restoring is faster, as it simply requires copying files, but it lacks the flexibility of logical backups.<\/span><\/p>\n<p><b>Limitations:<\/b><span style=\"font-weight: 400;\"> In any mode, pg_basebackup ensures the server transitions in and out of backup mode seamlessly. It exclusively performs full backups of the entire database cluster, as backing up specific databases or objects is not supported. For more selective backups, tools like pg_dump should be utilized.<\/span><\/p>\n<h4><span style=\"font-weight: 400;\">Let&#8217;s dive into implementation of pg_basebackup for fullbackup<\/span><\/h4>\n<p>pg_basebackup takes a base backup of a running PostgreSQL server.<\/p>\n<pre class=\"theme:solarized-light lang:sh decode:true \">Usage:\n  pg_basebackup [OPTION]...\n\nOptions controlling the output:\n  -D, --pgdata=DIRECTORY receive base backup into directory\n  -F, --format=p|t       output format (plain (default), tar)\n  -r, --max-rate=RATE    maximum transfer rate to transfer data directory\n                         (in kB\/s, or use suffix \"k\" or \"M\")\n  -R, --write-recovery-conf\n                         write configuration for replication\n  -t, --target=TARGET[:DETAIL]\n                         backup target (if other than client)\n  -T, --tablespace-mapping=OLDDIR=NEWDIR\n                         relocate tablespace in OLDDIR to NEWDIR\n      --waldir=WALDIR    location for the write-ahead log directory\n  -X, --wal-method=none|fetch|stream\n                         include required WAL files with specified method\n  -z, --gzip             compress tar output\n  -Z, --compress=[{client|server}-]METHOD[:DETAIL]\n                         compress on client or server as specified\n  -Z, --compress=none    do not compress tar output\n\nGeneral options:\n  -c, --checkpoint=fast|spread\n                         set fast or spread checkpointing\n  -C, --create-slot      create replication slot\n  -l, --label=LABEL      set backup label\n  -n, --no-clean         do not clean up after errors\n  -N, --no-sync          do not wait for changes to be written safely to disk\n  -P, --progress         show progress information\n  -S, --slot=SLOTNAME    replication slot to use\n  -v, --verbose          output verbose messages\n  -V, --version          output version information, then exit\n      --manifest-checksums=SHA{224,256,384,512}|CRC32C|NONE\n                         use algorithm for manifest checksums\n      --manifest-force-encode\n                         hex encode all file names in manifest\n      --no-estimate-size do not estimate backup size in server side\n      --no-manifest      suppress generation of backup manifest\n      --no-slot          prevent creation of temporary replication slot\n      --no-verify-checksums\n                         do not verify checksums\n  -?, --help             show this help, then exit\n\nConnection options:\n  -d, --dbname=CONNSTR   connection string\n  -h, --host=HOSTNAME    database server host or socket directory\n  -p, --port=PORT        database server port number\n  -s, --status-interval=INTERVAL\n                         time between status packets sent to server (in seconds)\n  -U, --username=NAME    connect as specified database user\n  -w, --no-password      never prompt for password\n  -W, --password         force password prompt (should happen automatically)\n<\/pre>\n<p>Command for taking fullbackup using pg_basebackup:<\/p>\n<pre class=\"theme:solarized-light lang:pgsql decode:true \">[postgres@mysql-02 ~]$ pg_basebackup -U postgres -p 5432 -D \/var\/lib\/pgsql\/dvdrental_backup --progress --format=tar --gzip\n53632\/53632 kB (100%), 1\/1 tablespace<\/pre>\n<p><span style=\"font-weight: 400;\">Explanation:<\/span><\/p>\n<p><span style=\"font-weight: 400;\">-h localhost: Specifies the host; replace `localhost` with the appropriate hostname or IP address.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">-U postgres: Uses the `postgres` user for authentication.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">-p 5432: Connects to the PostgreSQL server on port 5432.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">-D \/var\/lib\/pgsql\/dvdrental_backup: Defines the destination directory where the backup will be stored\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">&#8211;progress: Shows real-time progress of the backup.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">&#8211;format=tar: Saves the backup in tar format.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">&#8211;gzip: Compresses the tar file to save space.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><b>Restoring from fullbackup:<\/b><\/p>\n<pre class=\"theme:solarized-light lang:pgsql decode:true \">[postgres@mysql-02 ~]$ sudo tar -xvzf \/var\/lib\/pgsql\/dvdrental_backup\/base.tar.gz -C \/var\/lib\/pgsql\/extracted_backup\n[postgres@mysql-02 ~]$ sudo systemctl stop postgresql-16\n[postgres@mysql-02 ~]$ sudo cp -r \/var\/lib\/pgsql\/extracted_backup \/var\/lib\/pgsql\/16\/data\n[postgres@mysql-02 ~]$ sudo chown -R postgres:postgres \/var\/lib\/pgsql\/16\/data\n[postgres@mysql-02 ~]$ sudo chmod 700 \/var\/lib\/pgsql\/16\/data\n[postgres@mysql-02 ~]$ touch \/var\/lib\/pgsql\/16\/data\/recovery.signal\n[postgres@mysql-02 ~]$ sudo systemctl start postgresql-16\n[postgres@mysql-02 ~]$ sudo systemctl status postgresql-16\n\u25cf postgresql-16.service - PostgreSQL 16 database server\n   Loaded: loaded (\/usr\/lib\/systemd\/system\/postgresql-16.service; enabled; vendor preset: disabled)\n   Active: active (running) since Wed 2025-04-16 16:07:09 EDT; 18s ago\n     Docs: https:\/\/www.postgresql.org\/docs\/16\/static\/\n  Process: 6535 ExecStartPre=\/usr\/pgsql-16\/bin\/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0\/SUCCESS)\n Main PID: 6540 (postgres)\n    Tasks: 8 (limit: 10938)\n   Memory: 59.6M\n   CGroup: \/system.slice\/postgresql-16.service<\/pre>\n<blockquote><p><span style=\"font-weight: 400;\">We will be covering the latest feature of incremental backups using the pg_basebackup in our next blog.<\/span><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>In continuation of our blog series on effectively implementing backup strategies in a production environment, this article delves into the topic of Seamless Backup Strategies in PostgreSQL: Native Tools That Work. We present the following options for taking backups, exploring each in detail here, while additional strategies will be covered in dedicated posts in upcoming&hellip;<\/p>\n","protected":false},"author":1,"featured_media":2851,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[101,102,108,110,112,144,154,162,171,193,244,245,246,256,258,271,279,282,295,342],"class_list":["post-2850","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-data-protection","tag-data-recovery","tag-database-backup-strategies","tag-database-cluster-backup","tag-database-management","tag-differential-backup","tag-full-backup","tag-incremental-backup","tag-integrated-backup-solutions","tag-logical-backups","tag-pg_basebackup","tag-pg_dump","tag-pg_dumpall","tag-physical-backups","tag-point-in-time-recovery","tag-postgresql-backup","tag-postgresql-performance","tag-postgresql-recovery","tag-production-environment","tag-wal-logging","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2850","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2850"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2850\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2851"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2850"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2850"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2850"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}