Re: Best backup strategy for production systems

Oliver wrote:
> I'm a newbie in postgresql. I've mounted my first postgresql instance, it is empty now, only with
> default postgres DB.
> It is under Linux, with 2 filesystems, one for data and another for archiving (I've enabled archiving
> as it will be for production).
> Could someone recommend me a strategy for backups, scripts and so on?

Please read the documentation:
http://www.postgresql.org/docs/current/static/backup.html
That will make things clear.

The best strategy depends on your needs, like: do you have to restore
to any point in time or how much time do you have to restore a backup?

> Can base backup be done with the system up (postgres up), isn't it?

Yes.

> Would it be ok if I do a base backup each week and archiving backup each day?

Yes, but it will take longer to recover.
Also, if you lose one archived WAL, you cannot restore past that point.

> As I've not configured backups (and archiving deletion), I've had my first problem and it is that my
> archiving filesystem (FS) is full and archiver process is showing "failed" with the last wal file copy
> (normal as archiving FS is full).
> Please, recommend me what I should make now .. I should create another network FS for base backups and
> archiving backups? When I have my first base backup, could I then delete archiving files, isn't it?
> My archiving FS has 20GB, I don't understand as with a system without load (it will be for production,
> but it hasn't databases now .. only postgres), how it full the FS in a few days ... Is it normal?

The amount of WAL generated depends on the write activity on the database,
not on its size. Someone or something must have written a lot.

Yours,
Laurenz Albe