Re: a back up question

On Wed, Dec 6, 2017 at 9:52 PM, Martin Mueller <
martinmueller(at)northwestern(dot)edu> wrote:

>
>
> On 12/6/17, 4:39 AM, "karsten(dot)hilbert(at)gmx(dot)net" <karsten(dot)hilbert(at)gmx(dot)net>
> wrote:
>
> On Tue, Dec 05, 2017 at 09:52:28PM +0000, Martin Mueller wrote:
>
> > Are there rules for thumb for deciding when you can dump a
> > whole database and when you’d be better off dumping groups of
> > tables?
>
> It seems to me we'd have to define the objective of "dumping" first ?
>
> Regards,
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
>
>
> The objective is to create a backup from which I can restore any or all
> tables in the event of a crash. In my case, I use Postgres for my own
> scholarly purposes. Publications of whatever kind are not directly made
> public via the database. I am my only customer, and a service interruption,
> while a nuisance to me, does not create a crisis for others. I don’t want
> to lose my work, but a service interruption of a day or a week is no big
> deal.
>

If you reach the point where you have to consider splitting up the dumps
for performance reasons, then you have really reached the point where
pg_dump just isn't good enough for backups anymore. There are good uses for
pg_dump even on such large databases, but backups aren't one of them.

You should then instead use pg_basebackup, or if you need even more
functionality and performance than this provides, look at the external
tools like pgbackrest or pgbarman. These tools don't need to be any more
complicated to use than pg_dump, but will give you a much better backup.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>