Re: PostgreSQL File System Based Backup Restartability

girish R G peetle <giri.anamika0@gmail.com> writes:

> Thanks a lot Kevin for the detailed explanation.
> In response to your concerns
>
> 1) I am using the original list, won't create a new list. So we are fine as per your explanation.
>
> 2) I have a question about partially copied file. What if a file that got partially copied disappears (say it
belongedto a table n table was dropped ) when I resume 
> backup. Should I mark this partially copied file invalid in backup media ? And continue with the next entry in the
list? 

Stop complicating all of this.  Don't rescan the origin server.

Just restart copying your files with the one that was incomplete when
the failure occurred and then proceed through  the list till you reach
the end.

WAL replay will account for whatever changes took place on the origin
since pg_start_backup() was run.

This assumes that any files completely copied  are not themselves
corrupt for some reason.  If your copy process is untrustworth in that
regard then all of this is pointless anyway.

I'd be a lot more comfortable using rsync rather than a home-grown
solution or other archiver not commonly used for this porpose.

HTH.


> Girish
>
> On Feb 18, 2015 9:00 PM, "Kevin Grittner" <kgrittn@ymail.com> wrote:
>
>     girish R G peetle <giri.anamika0@gmail.com> wrote:
>
>     > "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 ?
>
>     No.
>
>     Think of it this way: for every file that existed both when
>     pg_start_backup() and pg_stop_backup() were run, every OS-level
>     page must represent the state of that page at some point between
>     when those functions were run.  *Which* point in time each page
>     represents is not important, and it is not expected that all files
>     (or all pages within a file) represent the same point in time.  WAL
>     replay is guaranteed to fix up all pages modified between those
>     function executions.  The backup_label file specifies which WAL
>     records are needed to do that.
>
>     There were two concerns I had with what you described.
>
>     (1)  That when you resume with the 20th file, that is not an
>     ordinal position in a new list which might have fewer files ahead
>     of the 20th position, resulting in skipping some files.  If you're
>     continuing to use the original list, using the position in that
>     list is fine.
>
>     (2)  That if the error occurred part-way through reading a file,
>     leaving a portion uncopied, that the missing portion be copied.
>     (Of course re-copying the whole file works, too; but you could
>     safely resume just past the last page successfully copied before
>     the network problems.)
>
>     --
>     Kevin Grittner
>     EDB: http://www.enterprisedb.com
>     The Enterprise PostgreSQL Company
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800