Phyisical backups 

    Example using pg_basebackup:

    pg_basebackup -D /tmp/pgbackup -F tar -z -X stream -c fast -P -U postgres

    If want to run this remotely need to add the -h option with the IP address of the PostgreSQL server:

    pg_basebackup -h <postgresql-ip> -p 5432 -D /tmp/pgbackup -F tar -z -X stream -c fast -P -U postgres

    This will backup all the data into a tar file in /tmp/pgbackup. and create the file:

    - base.tar.gz
- pg_wal.tar.gz
- backup_manifest

    To restore, you need to delete the PGDATA directory and extract the tar files:

    rm -rf /var/lib/postgresql/16

    Then extract the tar files:

    tar -xzf /tmp/pgbackup/base.tar.gz -C /var/lib/postgresql/16
tar -xzf /tmp/pgbackup/pg_wal.tar.gz -C /var/lib/postgresql/16/pg_wal

    Start the PostgreSQL server:

    pg_ctl -D /var/lib/postgresql/16 start

    Logical backups 

    A logical backup captures the database contents as SQL commands that can recreate the schema and data from scratch. It differs from a physical backup, which copies the raw data files from disk.

    Logical backups are:

    • Portable across PostgreSQL versions and platforms.
    • Human-readable (plain SQL).
    • Ideal for small to medium databases or versioned schema backups.

    They are created using pg_dump (for one database) or pg_dumpall (for all databases or global objects).

    Using pg_dump 

    Example command to back up a single database:

    pg_dump \
  -h <your IP>\
  -U <your user>\
  -F p \
  --encoding=UTF8 \
  --no-owner \
  --no-privileges \
  --blobs \
  --serializable-deferrable \
  mydatabase \
  | gzip > mydatabase_$(date +%F).sql.gz

    This will not backup roles and tablespaces, you need to recreate them manually.

    FlagPurposeWhy It Matters
    -F pPlain SQL formatWidely portable and readable
    --encoding=UTF8Force UTF-8 outputAvoids locale/charset issues
    --no-ownerSkip ownershipAvoids permission mismatches
    --no-privilegesSkip GRANTsSimplifies cross-environment restore
    --blobsInclude large objectsEnsures all data included
    --serializable-deferrableTransaction-safe dumpConsistency for live systems
    --no-commentsSkip commentsReduces noise, increases diffability

    To restore the backup, use the following command:

    gunzip -c mydatabase_2025-10-29.sql.gz | psql -U postgres -d mydatabase

    Using pg_dumpall 

    To back up all databases and global objects (like roles and tablespaces), use pg_dumpall:

    pg_dumpall -U postgres > full_backup.sql

    To restore the backup created by pg_dumpall, use the following command:

    psql -U postgres -f full_backup.sql

    Example for full logical backup and restore 

    Backup only globals (roles and tablespaces):

    pg_dumpall -U postgres --globals-only > globals.sql

    Backup all databases separately for more granular control:

    pg_dump -U postgres mydb1 > mydb1.sql
pg_dump -U postgres mydb2 > mydb2.sql
pg_dump -U postgres mydb3 > mydb3.sql
...