Logical backup is a backup in text format whose content can be read and changed with
a text editor. In small builds, logical backup (pg_dump) can be used for builds where
restoring to the previous day isn’t a problem, such as web pages with little movement.
However, backup policies should be decided by the institution.
Physical backup is a backup type that also contains binary data. Even if offline physical
backup is done automatically by script, it is not a very sustainable method to stop the
database and take a backup and restart it.
It cannot be used in organizations that do not tolerate being interrupted. Taking online
backups is a must for databases. Online backup is a must in institutions that provide
24/7 service such as online sales sites, health sector, and financial institutions. They are
systems that have been working for years without stopped for maintenance, update,
etc. Even if it is necessary to close due to necessity, they are organizations that can be
closed by planning and taking interruptions.
Note: On servers with pre-installed packages with apt/yum, $PGDATA would be
addressing /var/lib/postgresql/$version, i.e, example, the directory/var/lib/postgresql/14.
Physical Backup
Offline File System (user managed / Cold) Backup
postgres@srv1:~$ sudo systemctl stop postgresql@14-main.service postgres@srv1:~$ tar -cf pg_bck.tar /var/lib/postgresql/14/ #or if PGDATA variable is defined in .profile (echo $PGDATA) postgres@srv1:~$ tar -cf pg_bck.tar $PGDATA sudo systemctl start postgresql@14-main.service
It is taken with operating system commands/scripts.
This type of backup is also called consistent backup, cold/offline backup.
Disadvantages;
It is necessary to stop the database.
Can be restored to backup time.
Note: When the database is open, storage (SAN) image snap-shot, veam image etc.
backups are not supported.
Continuous Archiving
The wal files created in the $PGDATA/pg_wal directory are archived by being copied
to a different location with operating system commands. It can be copied with cp, rsync
and script. It can also be sent to a different server or disk (NFS) on the network.
The following parameters are assigned values according to the number of transactions
per second (TPS), IOPS capacity and network speed.
In order to take backup (online/hot) without stopping the database (instance), we need
to enable the archive mode (archive_mode=on).
Note: In postgresql 9.6 and earlier, pg_xlog instead of pg_wal.
wal_level: Defines which records WAL files contain.
The default value is replica. This value is sufficient for WAL archiving, hot standby (readonly).
At the least, it deletes all logs except the information required during the crash and
immediate shutdown situations. Not suitable for base backup. The database does not
contain the necessary information for base backup restore and recovery operations.
For base backup, it should be replica or logical.
In addition to replica, it also contains decoding information required for logical standby.
wal_writer_delay: Determines how often the wal buffer will be flushed and its
contents written to disk.
wal_keep_size: Determines the minimum WAL size that should be kept in the
pg_wal directory. If your standby servers lag behind for any reason, it will update itself
with the WALs in pg_wal.
min_wal_size: As long as the disk usage of WAL files is smaller than this size, it will
be overwritten and reused in checkpoint operations instead of deleting old WAL files.
Thus, the disk space specified here is reserved. The default value is 80 MB.
max_wal_size: The size that is allowed to grow during the automatic checkpoint
process. It is the soft limit, and it may exceed the size specified here when archive_
command receives an error and cannot archive under heavy load, or when wal_keep_
size is given a high value.