Thread: : PostgreSQL Online Backup

Venkat Balaji wrote:
> We have had situations where-in "rsync" was executed without executing
"pg_start_backup()" on the
> production data directory and on the next runs, "pg_start_backup()"
has been executed with "rsync".
> This was to avoid high IO load on production. We ended up getting
unmatched files (especially in
> pg_clog) and not sure about "base" directory.
>
> Postgres is asking for WAL Archive files dated sometime around 15 days
ago. We are absolutely not sure
> whats going on.
>
> Is this dangerous for production (like corruption) ? or just the
backup will be invalid ? Please help
> us know if we have to perform any precautionary checks on the
production cluster.
>
> Apart from firing a checkpoint, does "pg_start_backup()" updates any
dictionary tables or views ? or
> it updates anything in "pg_xlog"
>
> Looking forward for your help !

I am not sure what the problem is.

Do you have problems starting the original PostgreSQL cluster,
or do you have problems restoring a backup?

Running pg_start_backup() will not harm the cluster.
End online backup mode by running pg_stop_backup() or removing
the backup_label file in the cluster directory.

Yours,
Laurenz Albe

On September 26, 2011 05:49:50 AM Venkat Balaji wrote:
> I tried restoring the backup, after taking the full backup.
>
> Below is what i see in the "archive destination".
>
> Postgres was asking for "00000001000001930000006F" and i tried to find the
> same and below is what i find...
>
> -rw------- 1 postgres postgres 3.3M Sep 26 02:06
> 00000001000001930000006F.gz -rw------- 1 postgres postgres  219 Sep 26
> 02:53
> 00000001000001930000006F.00328508.backup.gz
>
> Why is PG (9.0) putting an extension for the WAL Archive file as
> > "backup.gz" ??
>

The archive files are created by your archive_command, as specified in
postgresql.conf. My guess would be that your archive command runs the files
through gzip as part of archiving (which is fine).

However, the restore_command you specify in recovery.conf  must undo this
compression. So instead of (for example) 'cp -f "%f" "%p"', it might instead
need to look like 'zcat "%f" > "%p"'.

Hope this helps.

Venkat Balaji wrote:
> Our problem is -
>
> We had mistakenly executed  "rsync" on the running PostgreSQL data
directory (production) and we did
> not run "pg_start_backup()".
>
> Will this harm production ? can this lead to corruption ?

I assume that you used rsync to copy *from* the data directory.

This cannot lead to data corruption.
Only performance might suffer temporarily due to the additional I/O.

The backup made with rsync will be unusable without pg_start_backup().

Yours,
Laurenz Albe

On October 3, 2011 05:33:35 AM Venkat Balaji wrote:
> Did anyone observe this behavior ?? Please help !
>
> This is critical for us. I want to recommend not to use "rsync" (use cp or
> scp instead) for production backup.
>

rsync works fine. Why exactly can't the recovery find the backed up copy of
000000010000053900000076? Please post your archive_command settings, the
contents of any script(s) called by that, and the recovery.conf file you're
using that's having problems, as well as the complete process you followed to
initiate recovery. I strongly suspect you're missing part of the process of
actually saving the WAL files needed for recovery.

> > rsync works fine. Why exactly can't the recovery find the backed up copy
> > of 000000010000053900000076? Please post your archive_command settings,
> > the contents of any script(s) called by that, and the recovery.conf file
> > you're using that's having problems, as well as the complete process you
> > followed to
> > initiate recovery. I strongly suspect you're missing part of the process
> > of actually saving the WAL files needed for recovery.

> The recovery is unable to find the WAL archive because, it was generated on
> 26th September.
>
> Whereas the backup is as taken on Oct 2nd, 2011. We deleted all the files.
>
> I do not have that WAL archive copy.
>
> The problem area -
>
> I found that a pg_clog file dated 26th Sep, 2011 is not synced (its not
> 256K).
>

I'm going to need the rest of what I asked for to offer any further suggestions
- especially the full and exact steps you took to initiate recovery and the
contents of recovery.conf. Also, please don't top-post.