pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSuggestion on Backup
4+ messages / 3 participants
[nested] [flat]
* Suggestion on Backup
@ 2013-10-15 21:09 Jeni Fifrick <jfifrick@incomm.com>
0 siblings, 2 replies; 4+ messages in thread
From: Jeni Fifrick @ 2013-10-15 21:09 UTC (permalink / raw)
To: pgsql-admin
We're having Master & Slave Postgres 9.1.3 on CentOS - using Streaming Replication, which serve as a read-only database, and as 'backup' for the production database.
The current backup mechanism is using pg_dump.
We're in need to do maintenance process that required the server to be taken down.
So, during that time, we only have 1 Production server.
I'm trying to setup a process that can be used to have the continuous backup of the production, to make sure if some problems happen on the production server, we won't loss the data.
Could you experts give me suggestion on the best way to achieve this?
And detail on how to achieve it?
Thanks in advance,
Jen
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Suggestion on Backup
@ 2013-10-16 03:41 Jov <amutu@amutu.com>
parent: Jeni Fifrick <jfifrick@incomm.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jov @ 2013-10-16 03:41 UTC (permalink / raw)
To: Jeni Fifrick <jfifrick@incomm.com>; +Cc: pgsql-admin
Why not configure one more slave.so you can maintenance any server with
other two on production.
Jov
blog: http:amutu.com/blog <http://amutu.com/blog;
2013/10/16 Jeni Fifrick <jfifrick@incomm.com>
> We’re having Master & Slave Postgres 9.1.3 on CentOS – using Streaming
> Replication, which serve as a read-only database, and as ‘backup’ for the
> production database.****
>
> The current backup mechanism is using pg_dump. ****
>
> ** **
>
> We’re in need to do maintenance process that required the server to be
> taken down.****
>
> So, during that time, we only have 1 Production server.****
>
> ** **
>
> I’m trying to setup a process that can be used to have the continuous
> backup of the production, to make sure if some problems happen on the
> production server, we won’t loss the data.****
>
> ** **
>
> Could you experts give me suggestion on the best way to achieve this?****
>
> And detail on how to achieve it? ****
>
> ** **
>
> Thanks in advance,****
>
> Jen****
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Suggestion on Backup
@ 2013-10-16 06:51 Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
parent: Jeni Fifrick <jfifrick@incomm.com>
1 sibling, 1 reply; 4+ messages in thread
From: Gabriele Bartolini @ 2013-10-16 06:51 UTC (permalink / raw)
To: Jeni Fifrick <jfifrick@incomm.com>; +Cc: pgsql-admin
Hi Jen,
On Tue, 15 Oct 2013 21:09:50 +0000, Jeni Fifrick <jfifrick@incomm.com>
wrote:
> We're having Master & Slave Postgres 9.1.3 on CentOS - using
> Streaming Replication, which serve as a read-only database, and as
> 'backup' for the production database.
Sure.
> I'm trying to setup a process that can be used to have the continuous
> backup of the production, to make sure if some problems happen on the
> production server, we won't loss the data.
It is important that you understand though that such an architecture
(with just replication) won't completely protect you from disasters
happening on the master. I am referring to data loss caused by
undeliberate DELETE or DROP operations, for example, or SQL injections.
Especially if you have a streaming replicated standby, data that
disappears on the master will disappear from the standby immediately
after.
Physical backup and continuous archiving will add this kind of
protection to your system. Using 9.1 unfortunately you can't benefit
from cascading replication using the streaming protocol (introduced in
9.2), however you can look into the pg_basebackup utility for this
purpose.
Otherwise, there are tools out there that help you achieving these
goals, such as OmniPITR (https://github.com/omniti-labs/omnipitr),
pg-rman (http://code.google.com/p/pg-rman/), WAL-E
(https://github.com/heroku/WAL-E, mainly for Amazon AWS users) and
barman (http://www.pgbarman.org/) - to name a few.
Being one of the developers, I can definitely recommend Barman. It does
not yet support streaming replication, but usually with WAL file
shipping you can still achieve very good results in terms of Recovery
Point Objective (which measures data loss). It is very easy to setup,
and already has packages for CentOS.
I hope this helps.
Cheers,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it
--
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] 4+ messages in thread
* Re: Suggestion on Backup
@ 2013-10-16 16:05 Jeni Fifrick <jfifrick@incomm.com>
parent: Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
0 siblings, 0 replies; 4+ messages in thread
From: Jeni Fifrick @ 2013-10-16 16:05 UTC (permalink / raw)
To: Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>; +Cc: pgsql-admin
Thank you Gabriele & all for your suggestions.
I'm going to review them.
-----Original Message-----
From: Gabriele Bartolini [mailto:Gabriele.Bartolini@2ndQuadrant.it]
Sent: Wednesday, October 16, 2013 2:51 AM
To: Jeni Fifrick
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Suggestion on Backup
Hi Jen,
On Tue, 15 Oct 2013 21:09:50 +0000, Jeni Fifrick <jfifrick@incomm.com>
wrote:
> We're having Master & Slave Postgres 9.1.3 on CentOS - using Streaming
> Replication, which serve as a read-only database, and as 'backup' for
> the production database.
Sure.
> I'm trying to setup a process that can be used to have the continuous
> backup of the production, to make sure if some problems happen on the
> production server, we won't loss the data.
It is important that you understand though that such an architecture (with just replication) won't completely protect you from disasters happening on the master. I am referring to data loss caused by undeliberate DELETE or DROP operations, for example, or SQL injections.
Especially if you have a streaming replicated standby, data that disappears on the master will disappear from the standby immediately after.
Physical backup and continuous archiving will add this kind of protection to your system. Using 9.1 unfortunately you can't benefit from cascading replication using the streaming protocol (introduced in 9.2), however you can look into the pg_basebackup utility for this purpose.
Otherwise, there are tools out there that help you achieving these goals, such as OmniPITR (https://github.com/omniti-labs/omnipitr),
pg-rman (http://code.google.com/p/pg-rman/), WAL-E (https://github.com/heroku/WAL-E, mainly for Amazon AWS users) and barman (http://www.pgbarman.org/) - to name a few.
Being one of the developers, I can definitely recommend Barman. It does not yet support streaming replication, but usually with WAL file shipping you can still achieve very good results in terms of Recovery Point Objective (which measures data loss). It is very easy to setup, and already has packages for CentOS.
I hope this helps.
Cheers,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it
--
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] 4+ messages in thread
end of thread, other threads:[~2013-10-16 16:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-15 21:09 Suggestion on Backup Jeni Fifrick <jfifrick@incomm.com>
2013-10-16 03:41 ` Jov <amutu@amutu.com>
2013-10-16 06:51 ` Gabriele Bartolini <Gabriele.Bartolini@2ndQuadrant.it>
2013-10-16 16:05 ` Jeni Fifrick <jfifrick@incomm.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