pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL Backup Strategies
6+ messages / 6 participants
[nested] [flat]
* PostgreSQL Backup Strategies
@ 2018-02-22 03:01 Luis Marin <luismarinaray@gmail.com>
2018-02-22 03:08 ` RE: PostgreSQL Backup Strategies Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2018-02-22 17:37 ` Re: PostgreSQL Backup Strategies Evan Rempel <erempel@uvic.ca>
2018-02-22 17:48 ` Re: PostgreSQL Backup Strategies Simon Riggs <simon@2ndquadrant.com>
2018-02-22 21:04 ` Re: PostgreSQL Backup Strategies Tim Cross <theophilusx@gmail.com>
0 siblings, 4 replies; 6+ messages in thread
From: Luis Marin @ 2018-02-22 03:01 UTC (permalink / raw)
To: pgsql-admin
Dear Friends,
Please, somebody knows a good source of information about PostgreSQL 9
backup strategies for production systems, if you have read a good book with
this subject, could you share the author and name of the book ?
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: PostgreSQL Backup Strategies
2018-02-22 03:01 PostgreSQL Backup Strategies Luis Marin <luismarinaray@gmail.com>
@ 2018-02-22 03:08 ` Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2018-02-22 11:59 ` Re: PostgreSQL Backup Strategies Stephen Frost <sfrost@snowman.net>
3 siblings, 1 reply; 6+ messages in thread
From: Alvaro Aguayo Garcia-Rada @ 2018-02-22 03:08 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Luis Marin <luismarinaray@gmail.com>
Sorry I have no book to recommend, but my recommendation will always pass through pg_basebackup, which will perform a full backup of you running database without needing to stop anything, and will also(with adequate args) add all transactiins committed zuring the backup to the backup itself.
I do myself use pg_basebackup and a simple shellscript to do automated backups of a production system.
I've also heard about pgbarman, but have no knowledge on it.
Regards,
Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.
Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe
Sent from my Sony Xperia™ smartphone
---- Luis Marin wrote ----
Dear Friends,
Please, somebody knows a good source of information about PostgreSQL 9
backup strategies for production systems, if you have read a good book with
this subject, could you share the author and name of the book ?
Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL Backup Strategies
2018-02-22 03:01 PostgreSQL Backup Strategies Luis Marin <luismarinaray@gmail.com>
2018-02-22 03:08 ` RE: PostgreSQL Backup Strategies Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
@ 2018-02-22 11:59 ` Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 6+ messages in thread
From: Stephen Frost @ 2018-02-22 11:59 UTC (permalink / raw)
To: Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>; +Cc: pgsql-admin@postgresql.org, Luis Marin <luismarinaray@gmail.com>
Greetings,
* Alvaro Aguayo Garcia-Rada (aaguayo@opensysperu.com) wrote:
> Sorry I have no book to recommend, but my recommendation will always pass through pg_basebackup, which will perform a full backup of you running database without needing to stop anything, and will also(with adequate args) add all transactiins committed zuring the backup to the backup itself.
Note that you *must* ensure that the write-ahead-log / xlog is captured
for the duration of the backup, regardless of how you do the backup.
The above seems to imply that you only need to if you want the
transactions committed during the backup- that is *not* the case, you
need the WAL to have a *consistent* backup. Without the WAL, the backup
isn't consistent and isn't valid and therefore can't be used.
> I do myself use pg_basebackup and a simple shellscript to do automated backups of a production system.
pg_basebackup with the --xlog or --wal option is a good way to get a
snapshot of a running database. You can also use archive_command or
pg_receivexlog / pg_receivewal to collect the WAL but then you have to
verify that you have all of the WAL generated during the backup.
> I've also heard about pgbarman, but have no knowledge on it.
For doing file-based backups of PG, you should definitely be using an
existing well maintained PG backup implementation, such as pgBackRest,
barman, or perhaps WAL-E/WAL-G. These have, literally, years of
development resources put into them to make sure that the backups taken
are consistent and valid (though you should also, always, be testing
your backups). Unless you're planning to invest serious time
understanding all of the details involved in taking a backup of PG, I'd
strongly recommend you use an existing solution.
If you're looking for logical backups, there's also pg_dump, of course,
but you can't do point-in-time-recovery with pg_dump in the same way you
can with a file-based backup, and restore time is longer as indexes have
to be rebuilt and constraints re-checked with pg_dump.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL Backup Strategies
2018-02-22 03:01 PostgreSQL Backup Strategies Luis Marin <luismarinaray@gmail.com>
@ 2018-02-22 17:37 ` Evan Rempel <erempel@uvic.ca>
3 siblings, 0 replies; 6+ messages in thread
From: Evan Rempel @ 2018-02-22 17:37 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
The concepts are the same regardless of what tools you use. These concepts are documented very well in the on-line PostgreSQL documentation at www.posgresql.org
Depending on what your final destination is for your backups (i.e. what product you use to manage your backups) you may wish to alter the backup tool you use.
At our site we use Tivoli Storage Manager (Spectrum Protect) to perform backups of a variety of things (Linux, Windows, Oracle, MySQL, Exchange) so we wanted to integrate our PostgreSQL backups with this product.
The result of this was that tools like pg_basebackup were not a good fit for us.
If you are using Tivoli Storage Manager for backups I can provide some guidance with regards to that integration.
Evan.
On 02/21/2018 07:01 PM, Luis Marin wrote:
> Dear Friends,
>
> Please, somebody knows a good source of information about PostgreSQL 9 backup strategies for production systems, if you have read a good book with this subject, could you share the author and name of the book ?
>
> Thanks
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL Backup Strategies
2018-02-22 03:01 PostgreSQL Backup Strategies Luis Marin <luismarinaray@gmail.com>
@ 2018-02-22 17:48 ` Simon Riggs <simon@2ndquadrant.com>
3 siblings, 0 replies; 6+ messages in thread
From: Simon Riggs @ 2018-02-22 17:48 UTC (permalink / raw)
To: Luis Marin <luismarinaray@gmail.com>; +Cc: pgsql-admin
On 22 February 2018 at 03:01, Luis Marin <luismarinaray@gmail.com> wrote:
> Dear Friends,
>
> Please, somebody knows a good source of information about PostgreSQL 9
> backup strategies for production systems, if you have read a good book with
> this subject, could you share the author and name of the book ?
PostgreSQL Admin Cookbook
PostgreSQL HA Cookbook
are both up to date with 9.6 backup and replication features
https://www.postgresql.org/docs/books/
Admin Cookbook for PG10 should be out soon
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL Backup Strategies
2018-02-22 03:01 PostgreSQL Backup Strategies Luis Marin <luismarinaray@gmail.com>
@ 2018-02-22 21:04 ` Tim Cross <theophilusx@gmail.com>
3 siblings, 0 replies; 6+ messages in thread
From: Tim Cross @ 2018-02-22 21:04 UTC (permalink / raw)
To: Luis Marin <luismarinaray@gmail.com>; +Cc: pgsql-admin
Luis Marin <luismarinaray@gmail.com> writes:
> Dear Friends,
>
> Please, somebody knows a good source of information about PostgreSQL 9
> backup strategies for production systems, if you have read a good book with
> this subject, could you share the author and name of the book ?
>
For me, the best sources to start with has been the docs on
postgresql.org. I think these are some of the best docs for an open
source product I've come across. The books I've looked at have been OK,
but few have added any real depth of information.
These days, your backup strategy will be strongly influenced by your
environment (size of databases, data storage infrastructure, whether you
have replication/standby systems, rate of change and how frequently
you need to backup, uptime requirements etc). Given your question, I
would start with just reading the backup and restore section of the
documentation and then come back with specific questions.
IMO the most important part and most overlooked component of a backup
strategy is restoration testing. I've lost count of the number of times
I've seen people get into strife because they had what they thought was
a comprehensive backup strategy only to find the first time they needed
it, it didn't work or was incomplete. Regardless of the strategy you
adopt, ensure you do a full recovery from one of your backups to verify
your strategy works. Make sure you test the restored system to verify
everything is working correctly. Really helps to document things well
too - when you need a backup, it is often during a high stress situation
and these are the times it is easy to overlook important steps. Having
good documentation really makes things easier.
regards,
Tim
--
Tim Cross
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-02-22 21:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-22 03:01 PostgreSQL Backup Strategies Luis Marin <luismarinaray@gmail.com>
2018-02-22 03:08 ` Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>
2018-02-22 11:59 ` Stephen Frost <sfrost@snowman.net>
2018-02-22 17:37 ` Evan Rempel <erempel@uvic.ca>
2018-02-22 17:48 ` Simon Riggs <simon@2ndquadrant.com>
2018-02-22 21:04 ` Tim Cross <theophilusx@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