pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBest backup strategy for production systems
16+ messages / 4 participants
[nested] [flat]
* Best backup strategy for production systems
@ 2014-06-17 09:51 Oliver <ofabelo@gmail.com>
0 siblings, 2 replies; 16+ messages in thread
From: Oliver @ 2014-06-17 09:51 UTC (permalink / raw)
To: pgadmin-support@postgresql.org
Hi,
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?
Can base backup be done with the system up (postgres up), isn't it?
Would it be ok if I do a base backup each week and archiving backup each
day?
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?
Thanks beforehand.
Cheers...
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Best backup strategy for production systems
@ 2014-06-17 10:04 Raymond O'Donnell <rod@iol.ie>
parent: Oliver <ofabelo@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Raymond O'Donnell @ 2014-06-17 10:04 UTC (permalink / raw)
To: Oliver <ofabelo@gmail.com>; pgadmin-support@postgresql.org
On 17/06/2014 10:51, Oliver wrote:
> Hi,
> 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?
Hi there,
You should post to the pgsql-general mailing list; this one is
specifically for PgAdmin, the administration GUI for PostgreSQL.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Best backup strategy for production systems
@ 2014-06-17 10:17 Oliver <ofabelo@gmail.com>
parent: Raymond O'Donnell <rod@iol.ie>
0 siblings, 1 reply; 16+ messages in thread
From: Oliver @ 2014-06-17 10:17 UTC (permalink / raw)
To: rod@iol.ie; +Cc: pgadmin-support@postgresql.org
Ah thanks Ray, I'm sorry.
Cheers...
2014-06-17 11:04 GMT+01:00 Raymond O'Donnell <rod@iol.ie>:
> On 17/06/2014 10:51, Oliver wrote:
> > Hi,
> > 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?
>
> Hi there,
>
> You should post to the pgsql-general mailing list; this one is
> specifically for PgAdmin, the administration GUI for PostgreSQL.
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Best backup strategy for production systems
@ 2014-06-17 10:20 Raymond O'Donnell <rod@iol.ie>
parent: Oliver <ofabelo@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Raymond O'Donnell @ 2014-06-17 10:20 UTC (permalink / raw)
To: Oliver <ofabelo@gmail.com>; +Cc: pgadmin-support@postgresql.org
On 17/06/2014 11:17, Oliver wrote:
> Ah thanks Ray, I'm sorry.
>
> Cheers...
Hi Oliver,
It's not a problem. :-) But you'll get advice from a much wider range of
people on that list, at least regarding general PG questions such as yours.
All the best,
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
^ permalink raw reply [nested|flat] 16+ messages in thread
* Best backup strategy for production systems
@ 2014-06-17 10:20 Oliver <ofabelo@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Oliver @ 2014-06-17 10:20 UTC (permalink / raw)
To: pgsql-admin
Hi,
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?
Can base backup be done with the system up (postgres up), isn't it?
Would it be ok if I do a base backup each week and archiving backup each
day?
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?
Thanks beforehand.
Cheers...
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Best backup strategy for production systems
@ 2014-06-17 12:44 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Oliver <ofabelo@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Albe Laurenz @ 2014-06-17 12:44 UTC (permalink / raw)
To: Oliver *EXTERN* <ofabelo@gmail.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Best backup strategy for production systems
@ 2014-06-19 09:54 Oliver <ofabelo@gmail.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 2 replies; 16+ messages in thread
From: Oliver @ 2014-06-19 09:54 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
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
<http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-ARCHIVE-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
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Best backup strategy for production systems
@ 2014-06-19 10:44 Oliver <ofabelo@gmail.com>
parent: Oliver <ofabelo@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Oliver @ 2014-06-19 10:44 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
I'm seeing now that my wal files are rotated each 5min, and each one has
16MB of size .. So I'm not understanding very well why this occurs, if I
would have 60 in my archive_timeout value.
Thanks beforehand.
Cheers...
2014-06-19 10:54 GMT+01:00 Oliver <ofabelo@gmail.com>:
> 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
> <http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-ARCHIVE-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
>>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Best backup strategy for production systems
@ 2014-06-19 10:44 Oliver <ofabelo@gmail.com>
parent: Oliver <ofabelo@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Oliver @ 2014-06-19 10:44 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
I'm seeing now that my wal files are rotated each 5min, and each one has
16MB of size .. So I'm not understanding very well why this occurs, if I
would have 60 in my archive_timeout value.
Thanks beforehand.
Cheers...
2014-06-19 10:54 GMT+01:00 Oliver <ofabelo@gmail.com>:
> 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
> <http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-ARCHIVE-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
>>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Best backup strategy for production systems
@ 2014-06-23 09:50 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Oliver <ofabelo@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Albe Laurenz @ 2014-06-23 09:50 UTC (permalink / raw)
To: Oliver *EXTERN* <ofabelo@gmail.com>; +Cc: pgsql-admin
Oliver wrote:
>> About many wal generated, reading documentation, I've done a error I think .. :
[...]
>> 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.
> I'm seeing now that my wal files are rotated each 5min, and each one has 16MB of size .. So I'm not
> understanding very well why this occurs, if I would have 60 in my archive_timeout value.
I'd say that you are hitting the default value of "checkpoint_timeout" there.
A checkpoint will generate some WAL.
As far as I remember, running into "archive_timeout" will not cause the current
segment to be archived if there has been no activity at all.
So if the database is completely idle, that's what I'd expect.
You could drastically reduce the size of archived WAL segments that are almost empty
by compressing them with something like "gzip -1".
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [pgadmin-support] Best backup strategy for production systems
@ 2014-06-27 12:55 Oliver <ofabelo@gmail.com>
parent: Oliver <ofabelo@gmail.com>
1 sibling, 3 replies; 16+ messages in thread
From: Oliver @ 2014-06-27 12:55 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
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 :-?
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
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? Why it says that all required wal segmens
have been archived if continuous archiving is enabled? I'm seeing now that
it has copied some files and there is one file named .backup in the
archived wal filesystem.
Thanks beforehand.
Cheers...
2014-06-25 14:49 GMT+01:00 Andreas <maps.on@gmx.net>:
> Hi
> you might also set up a slave server that replicates all write commands as
> hot standby.
> It's pretty easy to do that and you won't lose any data if your productive
> server goes up in flames.
> It won't slow down your system.
>
> In case you don't want to run another PC as the slave server you can have
> it as a virtual machine on your workstation. Obviously you shouldn't run it
> as guest on the db machine. ;)
> I had a VBox do this for some time. It had only 1GB virtual RAM.
>
> I do hourly full db-dumps as a cron job just to be sure. :-}
>
> Every other strategy like regular dumps or automatic log shipping has a
> certain amount of time where all modifications to the db would be lost in
> case something happens to your server.
>
> PG supports automatic log shipping but the log segments are 16MB big.
> Depending on how much write access your db gets it can take a while before
> the segment is full and gets shipped out.
>
> You might also consider to use a RAID 1 to store the data directoy on.
> In my case there are 3 drives. One for the OS and 2 as a software RAID.
> Again that is easy to set up and saves the db in case the harddrive dies.
> This even speeds up read access to the db a bit.
>
>
>
> Am 17.06.2014 11:51, schrieb Oliver:
>
> Hi,
>> 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?
>> Can base backup be done with the system up (postgres up), isn't it?
>> Would it be ok if I do a base backup each week and archiving backup each
>> day?
>> 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?
>> Thanks beforehand.
>>
>> Cheers...
>>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [pgadmin-support] Best backup strategy for production systems
@ 2014-06-27 12:59 Oliver <ofabelo@gmail.com>
parent: Oliver <ofabelo@gmail.com>
2 siblings, 0 replies; 16+ messages in thread
From: Oliver @ 2014-06-27 12:59 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
Another question please.
I've had to modify pg_hba.conf and postgresql.conf for doing work
pg_basebackup. About max_wal_senders, it would have 0 value, I've changed
it to 1 for doing work to pg_basebackup. Continuous archiving was working
with the value to 0, so I understand that max_wal_senders can be to 1 only
for base backups and continuous archiving doesn't need that parameter with
>0? I understand well?
Thanks beforehand.
Cheers...
2014-06-27 13:55 GMT+01:00 Oliver <ofabelo@gmail.com>:
> 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 :-?
> 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
>
> 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? Why it says that all required wal segmens
> have been archived if continuous archiving is enabled? I'm seeing now that
> it has copied some files and there is one file named .backup in the
> archived wal filesystem.
> Thanks beforehand.
>
> Cheers...
>
>
> 2014-06-25 14:49 GMT+01:00 Andreas <maps.on@gmx.net>:
>
> Hi
>> you might also set up a slave server that replicates all write commands
>> as hot standby.
>> It's pretty easy to do that and you won't lose any data if your
>> productive server goes up in flames.
>> It won't slow down your system.
>>
>> In case you don't want to run another PC as the slave server you can have
>> it as a virtual machine on your workstation. Obviously you shouldn't run it
>> as guest on the db machine. ;)
>> I had a VBox do this for some time. It had only 1GB virtual RAM.
>>
>> I do hourly full db-dumps as a cron job just to be sure. :-}
>>
>> Every other strategy like regular dumps or automatic log shipping has a
>> certain amount of time where all modifications to the db would be lost in
>> case something happens to your server.
>>
>> PG supports automatic log shipping but the log segments are 16MB big.
>> Depending on how much write access your db gets it can take a while before
>> the segment is full and gets shipped out.
>>
>> You might also consider to use a RAID 1 to store the data directoy on.
>> In my case there are 3 drives. One for the OS and 2 as a software RAID.
>> Again that is easy to set up and saves the db in case the harddrive dies.
>> This even speeds up read access to the db a bit.
>>
>>
>>
>> Am 17.06.2014 11:51, schrieb Oliver:
>>
>> Hi,
>>> 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?
>>> Can base backup be done with the system up (postgres up), isn't it?
>>> Would it be ok if I do a base backup each week and archiving backup each
>>> day?
>>> 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?
>>> Thanks beforehand.
>>>
>>> Cheers...
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [pgadmin-support] Best backup strategy for production systems
@ 2014-06-29 12:28 Michael Paquier <michael.paquier@gmail.com>
parent: Oliver <ofabelo@gmail.com>
2 siblings, 1 reply; 16+ messages in thread
From: Michael Paquier @ 2014-06-29 12:28 UTC (permalink / raw)
To: Oliver <ofabelo@gmail.com>; +Cc: Andreas <maps.on@gmx.net>; pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
On Fri, Jun 27, 2014 at 9:55 PM, Oliver <ofabelo@gmail.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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [pgadmin-support] Best backup strategy for production systems
@ 2014-06-30 10:24 Oliver <ofabelo@gmail.com>
parent: Oliver <ofabelo@gmail.com>
2 siblings, 1 reply; 16+ messages in thread
From: Oliver @ 2014-06-30 10:24 UTC (permalink / raw)
To: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
Ok, thanks.
I mean by original wal segments to files generated in pg_xlog, yes. I have
to 0 the parameter wal_keep_segments. They are being generated with 16MB of
size and they are being rotated. What parameter controls how many files can
be generated? Or will it grow indefinitely?
Thanks beforehand.
Cheers...
2014-06-27 15:36 GMT+01:00 <jayknowsunix@gmail.com>:
> Oliver,
>
> You want to retain all the segments archived between backups. That's the
> safest method that I know of. What do you mean by original WAL segments?
> Are you speaking about what PostgreSQL writes into pg_xlog? That's
> controlled by the wal_keep_segments parameter in the Postgresql.conf file.
>
> Sent from my iPad
>
> On Jun 27, 2014, at 8:55 AM, Oliver <ofabelo@gmail.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 :-?
> 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
>
> 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? Why it says that all required wal segmens
> have been archived if continuous archiving is enabled? I'm seeing now that
> it has copied some files and there is one file named .backup in the
> archived wal filesystem.
> Thanks beforehand.
>
> Cheers...
>
>
> 2014-06-25 14:49 GMT+01:00 Andreas <maps.on@gmx.net>:
>
>> Hi
>> you might also set up a slave server that replicates all write commands
>> as hot standby.
>> It's pretty easy to do that and you won't lose any data if your
>> productive server goes up in flames.
>> It won't slow down your system.
>>
>> In case you don't want to run another PC as the slave server you can have
>> it as a virtual machine on your workstation. Obviously you shouldn't run it
>> as guest on the db machine. ;)
>> I had a VBox do this for some time. It had only 1GB virtual RAM.
>>
>> I do hourly full db-dumps as a cron job just to be sure. :-}
>>
>> Every other strategy like regular dumps or automatic log shipping has a
>> certain amount of time where all modifications to the db would be lost in
>> case something happens to your server.
>>
>> PG supports automatic log shipping but the log segments are 16MB big.
>> Depending on how much write access your db gets it can take a while before
>> the segment is full and gets shipped out.
>>
>> You might also consider to use a RAID 1 to store the data directoy on.
>> In my case there are 3 drives. One for the OS and 2 as a software RAID.
>> Again that is easy to set up and saves the db in case the harddrive dies.
>> This even speeds up read access to the db a bit.
>>
>>
>>
>> Am 17.06.2014 11:51, schrieb Oliver:
>>
>> Hi,
>>> 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?
>>> Can base backup be done with the system up (postgres up), isn't it?
>>> Would it be ok if I do a base backup each week and archiving backup each
>>> day?
>>> 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?
>>> Thanks beforehand.
>>>
>>> Cheers...
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [pgadmin-support] Best backup strategy for production systems
@ 2014-06-30 10:29 Oliver <ofabelo@gmail.com>
parent: Michael Paquier <michael.paquier@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Oliver @ 2014-06-30 10:29 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; +Cc: Andreas <maps.on@gmx.net>; pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
Thank you very much for your replies.
Cheers...
2014-06-29 13:28 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:
>
>
>
> On Fri, Jun 27, 2014 at 9:55 PM, Oliver <ofabelo@gmail.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
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [ADMIN] [pgadmin-support] Best backup strategy for production systems
@ 2014-06-30 11:34 Michael Paquier <michael.paquier@gmail.com>
parent: Oliver <ofabelo@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Michael Paquier @ 2014-06-30 11:34 UTC (permalink / raw)
To: Oliver <ofabelo@gmail.com>; +Cc: pgsql-admin; Forums postgresql <pgsql-general@postgresql.org>
Please avoid top-posting...
On Mon, Jun 30, 2014 at 7:24 PM, Oliver <ofabelo@gmail.com> wrote:
> Ok, thanks.
> I mean by original wal segments to files generated in pg_xlog, yes. I have
> to 0 the parameter wal_keep_segments. They are being generated with 16MB of
> size and they are being rotated. What parameter controls how many files can
> be generated? Or will it grow indefinitely?
>
checkpoint_segments. A maximum of (3 * checkpoint_segments + 1) WAL
segments can generated. This can be more though as this is a soft limit,
but once this limit is reached server will remove WAL segments instead of
keeping them. Per the docs:
http://www.postgresql.org/docs/9.3/static/wal-configuration.html
--
Michael
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2014-06-30 11:34 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-06-17 09:51 Best backup strategy for production systems Oliver <ofabelo@gmail.com>
2014-06-17 10:04 ` Raymond O'Donnell <rod@iol.ie>
2014-06-17 10:17 ` Oliver <ofabelo@gmail.com>
2014-06-17 10:20 ` Raymond O'Donnell <rod@iol.ie>
2014-06-27 12:55 ` Oliver <ofabelo@gmail.com>
2014-06-27 12:59 ` Oliver <ofabelo@gmail.com>
2014-06-29 12:28 ` Michael Paquier <michael.paquier@gmail.com>
2014-06-30 10:29 ` Oliver <ofabelo@gmail.com>
2014-06-30 10:24 ` Oliver <ofabelo@gmail.com>
2014-06-30 11:34 ` Michael Paquier <michael.paquier@gmail.com>
2014-06-17 10:20 Best backup strategy for production systems Oliver <ofabelo@gmail.com>
2014-06-17 12:44 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-06-19 09:54 ` Oliver <ofabelo@gmail.com>
2014-06-19 10:44 ` Oliver <ofabelo@gmail.com>
2014-06-23 09:50 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-06-19 10:44 ` Oliver <ofabelo@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox