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

[Oliver <ofabelo@xxxxxxxxx>]

Thank you very much for your replies.

Cheers...

2014-06-29 13:28 GMT+01:00 Michael Paquier <michael.paquier@xxxxxxxxx>:

On Fri, Jun 27, 2014 at 9:55 PM, Oliver <ofabelo@xxxxxxxxx> 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