Re: Incremental Refresh - PostgreSQL 9.2

On Tue, Feb 23, 2016 at 8:36 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
wrote:

>
>
> On 24 February 2016 at 14:34, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On 24 February 2016 at 14:23, Scott Whitney <scott(at)journyx(dot)com> wrote:
>>
>>> Please do not refer me (or the list) to a URL when you are asking for
>>> help. Be clear and concise. If you have a URI that helps you, please be
>>> specific in the places you do not understand.
>>>
>>
>> Scott,
>>
>> I've asked for some tips about how could I do incremental backups in
>> Postgres WITHOUT stopping it.
>>
>> As I haven't had an expected answer, I decided to post here what I've
>> found.
>> So in the future others with the same question may be helped.
>>
>> I haven't referred you or the list to a URL********
>>
>> Lucas
>>
>
>
What you originally asked for and what you're referring to in that link are
two very different things. An incremental backup using the methods in that
link is possible without any third-party tools (look into pg_basebackup &
pg_receivexlogs which both come with 9.2+), it's just that the third party
tools can possibly make it slightly easier and provide some additional
options

However, once you restore that backup and bring it up as a standalone
database instance, you can no longer use the WAL stream from the original
instance to "replay" those changes. This is also the case with any sort of
rsync'ing of the data files from one instance to another.

You may be better off looking into a logical replication solution to copy
your data to a testing system if it needs to be a standalone system. That
is not currently built into Postgres, but it's on track for possible
inclusion with 9.6. In the mean time look into tools such as Mimeo or
Bucardo.

https://github.com/omniti-labs/mimeo
https://bucardo.org/wiki/Bucardo

Keith
https://www.keithf4.com/