The three forms serve somehow for backup.
SQL Dump is the most flexible way, but the text file can get very large as the database grows. For import and partial export, consider the command COPY
, which allows export using SELECT
.
File System Level Backup is better from the point of view of system administrator, as it is only a copy of system files. However, the database process needs to be properly terminated to ensure that all information is properly updated. A copy at any time can generate a corrupted or partial backup (which are being written at that time).
A better way to backup files without needing to stop the process is by using the command pg_basebackup
. It ensures that the copied file is intact and updated.
Continuous Archiving is the most rigid form of backup. It allows you to restore the status of the bank at any point in time. However, it will consume many resources such as disk space and data recording time, generating extensive information logs. To restore a particular state it is necessary to have the logs from the last backup performed up to that point.
Techniques such as "Continuous Archiving" are usually used in a mixed way with normal backups. While the backup ensures complete data recovery at predefined time intervals, the Continuous Archiving guarantees recovery at virtually any time.
It is important to note that binary files copied directly or generated by COPY
may be dependent on the Postgresql version. Text files, on the other hand, are usually version-independent.