Thread: Regarding "Point-in-time Recovery" feature

On 2/23/15 5:39 AM, Saurabh Gupta A wrote:
> Hello,
>
> Has anybody used online backup feature of postgreSQL? In fact precise
> postgreSQL term is called: "Point-in-time Recovery" (PITR)
> This means enabling following additional options in config:
> ---
> archive_command = on
> archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f' # This is
> only example path
> ---
>
> If yes then may I know how it is used and how it impacts database
> performance?

Other than the overhead of the copy itself, it generally doesn't. There
are a very limited number of shortcuts we can take when wal_level is set
to minimal, such as not WAL logging the full contents of data inserted
into a table that was created/truncated in the same transaction, but
generally archiving doesn't impact performance (assuming you keep it
running correctly so pg_xlog doesn't fill up ;)
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

I have archiving enabled for PITR but it has been causing problems. So I changed the archive_command to ‘cd .’ to
disablefor now. 
However, I’m currently looking for a script/process that will rotate the logs every week or so, and also ensure
archivedwal files don’t pile up.  
I
> On Feb 27, 2015, at 2:55 AM, Jim Nasby <Jim.Nasby@BlueTreble.com> wrote:
>
> On 2/23/15 5:39 AM, Saurabh Gupta A wrote:
>> Hello,
>>
>> Has anybody used online backup feature of postgreSQL? In fact precise
>> postgreSQL term is called: "Point-in-time Recovery" (PITR)
>> This means enabling following additional options in config:
>> ---
>> archive_command = on
>> archive_command = 'cp %p /usr/local/pgsql/pgDataPITR/wals/%f' # This is
>> only example path
>> ---
>>
>> If yes then may I know how it is used and how it impacts database
>> performance?
>
> Other than the overhead of the copy itself, it generally doesn't. There are a very limited number of shortcuts we can
takewhen wal_level is set to minimal, such as not WAL logging the full contents of data inserted into a table that was
created/truncatedin the same transaction, but generally archiving doesn't impact performance (assuming you keep it
runningcorrectly so pg_xlog doesn't fill up ;) 
> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general