Re: PostgreSQL File System Based Backup Restartability

[girish R G peetle <giri.anamika0@xxxxxxxxx>]

Thanks Kevin.

"Be careful that when you resume after such an interruption you do
not skip any files and that you complete or re-copy any files that
were partially copied before the problems."

Here you mean, we should not skip any files that was already backed up before interruption ?
 I will have to backup entire content under DATA directory again ?

Thanks
Girish

On Tue, Feb 17, 2015 at 10:04 PM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote:

girish R G peetle <giri.anamika0@xxxxxxxxx> wrote:

> Steps
> - Database size is 1 TB
> - I'll execute pg_start_backup('label');
> - I will traverse through the DATA directory to get the list of
>   directories and files.
> - I will start moving the files to backup media. ( over network )
> - When I'm half way (500GB done), network error happens. So
>   movement of files is halted.
> - Network issue is resolve after 30 minutes (In this time files
>   might have got deleted or added with creation/deletion of
>   database/tables )
> - I'll resume moving the files to backup media from where it had
>   halted.
> - When movement of files is done, I'll execute pg_stop_backup()
> - I'll move all the transaction logs that got archived to backup
>   media.
>
> Is PostgreSQL server recovery to a consistent state possible with
> backup content obtained from above steps ?

As long as you follow all the rules for what files to copy (and not
copy), you should have a good backup.  Some things to be careful
about that I've seen people get wrong with some frequency:

- Be sure to exclude all files in and under the pg_xlog directory.
  The backup should contain the directory itself, and it is best to
  include its subdirectory, but copying the files can cause
  corruption.
- Exclude the postmaster.pid file.
- Be sure *not* to exclude the backup_label file.  Without this
  file, restoring from the backup may or may not have corruption,
  which may or may not be initially apparent.

Be careful that when you resume after such an interruption you do
not skip any files and that you complete or re-copy any files that
were partially copied before the problems.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company