On Wed, Jun 10, 2020 at 1:53 AM Peter <pmc@xxxxxxxxxxxxxxxxxxxxxxx> wrote:
On Tue, Jun 09, 2020 at 12:34:38PM -0700, Adrian Klaver wrote:
! The backup solution is?
https://www.bareos.com/
! Fine rant below. Go forth and work your wonders.
I don't need to, anymore. I did that, for about 20 years - people
I used to work for as a consultant (major banks and insurance shops)
would usually run Informix or Oracle. Postgres is just my own private
fancy.
On Tue, Jun 09, 2020 at 03:42:48PM -0400, Stephen Frost wrote:
! * Peter (pmc@xxxxxxxxxxxxxxxxxxxxxxx) wrote:
! > This professional backup solution also offers support for postgres.
! > Sadly, it only covers postgres up to Rel.9, and that piece of software
! > wasn't touched in the last 6 or 7 years.
!
! Then it certainly doesn't work with the changes in v12, and probably has
! other issues, as you allude to.
Just having a look at their webpage, something seems to have been updated
recently, they now state that they have a new postgres adapter:
https://www.bareos.com/en/company_news/postgres-plugin-en1.html
Enjoy reading, and tell us what You think.
This one unfortunately rings out of somebody who doesn't know how to back up postgres, at least in the past 10-15 years.
They are using an API that has been deprecated for years - in what's announced as a brand new product. They are advocating local archiving, which basically guarantees dataloss in the event of a disaster.
That's from a 3 minute look, but that's definitely enough to suggest this is not something I'd consider using.
! > Actually, I am getting very tired of reading that something which can
! > easily be done within 20 lines of shell scripting, would need special
!
! This is just simply false- you can't do it properly in 20 lines of shell
! scripting.
Well, Your own docs show how to do it with a one-liner. So please
don't blame me for improving that to 20 lines.
Yes, those docs are unfortunately "known bad" and should definitely be improved on. it does very clearly state that the example is just an example. But it doesn't clearly state *why* it shouldn't be used.
In my understanding, backup is done via pgdump. The archive logs are
for emergencies (data corruption, desaster), only. And emergencies
would usually be handled by some professional people who know what
they have to do.
I'd say it's the exact opposite. backups are done via pg_basebackup or manual basebackups. Archive logs are for point in time recovery. pg_dump can be used as a secondary "backup to the backups" option, but it is most interesting for things that are not backups (such as inspecting data, or provisioning partial test systems).
Different for different scenarios of course, but that would be the base scenario. And pg_dump are definitely as far from good backups are you can get while still having something that can be called approximately backups. It might be enough for small databases, but even in those cases pg_basebackup (without archive logging) is easier...
And yes, I read that whole horrible discussion, and I could tear my
hair out, really, concerning the "deprecated API". I suppose You mean
the mentioning in the docs that the "exclusive low-level backup" is
somehow deprecated.
Yes. There is no "somehow", it's deprecated.
This is a very big bad. Because: normally you can run the base backup
as a strictly ordinary file-level backup in "full" mode, just as any
backup software can do it. You will simply execute the
pg_start_backup() and pg_stop_backup() commands in the before- and
after- hooks - and any backup software will offer these hooks.
But now, with the now recommended "non-exclusive low-level backup",
the task is different: now your before-hook needs to do two things
at the same time:
1. keep a socket open in order to hold the connection to postgres
(because postgres will terminate the backup when the socket is
closed), and
2. invoke exit(0) (because the actual backup will not start until
the before- hook has properly delivered a successful exit code.
And, that is not only difficult, it is impossible.
It is not impossible. It is harder if you limit your available tools yes, but it also *works*.
So yes, this is really a LOT of work. But the point is: this all is
not really necessary, because currently the stuff works fine in the
old way.
It does not, no. It works in the simple cases, but it has multiple failure scenarios that *cannot* be fixed without changing those fundamentals.
But you can always go for the actual old way -- just stop postgres in the pre-job and start it again in the post-job. That's by far the easiest. And that *does* work and is fully supported.
! > The only really interesting thing there is the pg_probackup. These
! > folks seem to have found a way to do row-level incremental backups.
!
! pg_probackup doesn't do row-level incremental backups, unless I've
! missed some pretty serious change in its development, but it does
! provide page-level,
Ah, well, anyway that seems to be something significantly smaller
than the usual 1 gig table file at once.
pg_probackup does page level incremental *if* you install a postgres extension that some people have questioned the wisdom of (disclaimer: I have not looked at this particular extension, so I cannot comment on said wisdom). I think it also has some ability to do page level incremental by scanning WAL. But the bottom line is it's always page level, it's never going to be row level, based on the fundamentals of how PostgreSQL works.
