Обсуждение: 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

Hi,

thank you very much for your reply.

Ok, I've read again the official documentation about backup, slowly now ;-)

About restore, yes, I would like have possibility of restoring in any point in time, so, for that, I've enabled archiving wal files. Is it correct if I use same location for archiving wal files and base backups, isn't it? It will be in a different filesystem of $PGDATA.

About many wal generated, reading documentation, I've done a error I think .. :

The archive command is only invoked on completed WAL segments. Hence, if your server generates only little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To put a limit on how old unarchived data can be, you can setarchive_timeout to force the server to switch to a new WAL segment file at least that often. Note that archived files that are archived early due to a forced switch are still the same length as completely full files. It is therefore unwise to set a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable.

So I modified my archive_timeout parameter to 60 .. so I understand now that it is creating wal files each min. of 16MB each one, correct? Even not being fill (because there isn't activity in the database), it will create wal files each min. of 16MB, and for that, I've had my archiving filesystem full quickly. Correct? I've modified parameter now to original value, 0, so it is disabled now.

Thanks beforehand.

Cheers... 

2014-06-17 13:44 GMT+01:00 Albe Laurenz <laurenz.albe@wien.gv.at>:

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

Hi,

thank you very much for your reply.

Ok, I've read again the official documentation about backup, slowly now ;-)

About restore, yes, I would like have possibility of restoring in any point in time, so, for that, I've enabled archiving wal files. Is it correct if I use same location for archiving wal files and base backups, isn't it? It will be in a different filesystem of $PGDATA.

About many wal generated, reading documentation, I've done a error I think .. :

The archive command is only invoked on completed WAL segments. Hence, if your server generates only little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To put a limit on how old unarchived data can be, you can setarchive_timeout to force the server to switch to a new WAL segment file at least that often. Note that archived files that are archived early due to a forced switch are still the same length as completely full files. It is therefore unwise to set a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable.

So I modified my archive_timeout parameter to 60 .. so I understand now that it is creating wal files each min. of 16MB each one, correct? Even not being fill (because there isn't activity in the database), it will create wal files each min. of 16MB, and for that, I've had my archiving filesystem full quickly. Correct? I've modified parameter now to original value, 0, so it is disabled now.

Thanks beforehand.

Cheers... 

2014-06-17 13:44 GMT+01:00 Albe Laurenz <laurenz.albe@wien.gv.at>:

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