In Postgresql, is there a difference between running a dump or exporting?

Question

this question really left me in doubt, as in postgres there are three types of backup/Restore that are:

1. SQL Dump (Generates a text file with SQL command)
2. File System Level Backup (Save PG Data File)
3. Continuous Archiving (Backup via LOG)

I would like to know if these forms are treated as dump or/and export?

Answer 1

Hello Diegosoaressub the most suitable without a doubt is SQL Dump by optimally generating a copy of your database(s) that can easily be imported to other machines or servers. See that File System Level has a number of restrictions ( http://www.postgresql.org/docs/8.1/static/backup-file.html ) that if you are not careful you may not be able to generate a faithful copy for example. Finally, Continuous Archiving requires you to activate the feature before you can recover regardless of whether you dump or not, but you need to be aware that this requires extra storage and processing on an ongoing basis ( http://www.postgresql.org/docs/8.1/static/backup-online.html ) .

Answer 2

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.