In PostgreSQL, is there a difference between running a dump or doing an export?

Question

In PostgreSQL, is there a difference between running a dump or doing an export?

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

This question really left me in doubt, because in postgres there are three types of backup / restore which are:

SQL Dump (Generates a text file with SQL command)

File System Level Backup

Continuous Archiving

I wonder if these forms are treated as dump and / or export?

postgresql backup

asked by anonymous 26.07.2014 / 18:31

2 answers

How to make a script run automatically? Check if a value corresponds to a value in an array

score 2 · Answer 1

Hello DiegoSoaresSub without a doubt is the SQL Dump by optimally generating a copy of your bank (s) that can easily be imported to other machines or servers. Note that File System Level has a number of restrictions ( link ) that you do not take it may not be possible to generate a faithful copy for example. Finally Continuous Archiving requires that the feature be activated before it can be recovered regardless of whether to dump or not, but you need to be aware that this requires extra storage and processing on a continuous basis ( link ).

score 1 · Answer 2

The three forms serve some form of backup.

SQL Dump is the most flexible mode, but the text file can get very large as the database grows. For partial import and export, consider the command COPY , which allows exporting using SELECT .

File System Level Backup is best from a systems administrator's point of view, because it is just 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 may generate a corrupted or partial backup (which is being written at that time).

A better way to back up files without stopping the process is to use the pg_basebackup . It ensures that the copied file is up-to-date and up-to-date.

Continuous Archiving is the most rigid form of backup. It allows you to restore the bank state at any point in time. However, it will consume many features like disk space and data write time, generating extensive information logs. To restore a particular state it is necessary to have the logs since the last backup performed up to that point.

Techniques such as "Continuous Archiving" are usually used in a mixed way with normal backups. While backup ensures full data recovery at predefined time intervals, Continuous Archiving ensures recovery at almost 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.