Re: [pgadmin-support] Best backup strategy for production systems

On Fri, Jun 27, 2014 at 9:55 PM, Oliver <ofabelo(at)gmail(dot)com> wrote:

> Thank you very much for your reply.
> I've spoken with my boss, databases aren't so important, so if there is a
> little of data lost, there isn't problem .. so I'm configuring this with
> continuous archiving and base backups. If you are expert, please, I would
> like make you some questions ..
> I only have a server (there isn't slave server). I have doing continuous
> archiving to another filesystem (of other physical disks), I think that it
> is working well .. One question is, original wal files are keeped how many
> time?
>
2 days? I only have control archived wal files, about how many time I want
> keep them, isn't it?
>

> When I do a base backup, is it normal that it doesn't copy pg_xlog data,
> isn't? Because it is doing continuous archiving :-?
>
A base backup can be used as-is with the archived WAL files that it can use
for recovery.

> The command that I'm using is (I would like that it copies all
> tablespaces, but I'm not sure if it is doing it):
> pg_basebackup -D 27062014 -Ft -z -Z 5 -l "base_backup_27062014" -P
>
A base backup taken in tar format creates a set of tarballs: one for the
base (base tablespace with configuration file, etc.) and one for each
tablespace named with the OID of the tablespace. It is actually better to
use a tar format as plain format will try to link to existing folders when
taking a base backup on the same server as the node it is connecting to if
this server has extra tablespaces.

> Output:
>
> 20290/20290 kB (100%), 1/1 tablespace
> NOTICE: pg_stop_backup completado, todos los segmentos de WAL requeridos
> han sido archivados
>
> I have now only default databases (postgres and templates), is it normal
> that it puts only 1/1 tablespace?
>
Yes.

> Why it says that all required wal segmens have been archived if continuous
> archiving is enabled?
>
It is necessary to perform a WAL file switch when calling pg_stop_backup to
ensure that all the necessary WAL files for the backup have been archived.
--
Michael