Re: Something else about Redo Logs disappearing

On Wed, Jun 10, 2020 at 1:53 AM Peter <pmc(at)citylink(dot)dinoex(dot)sub(dot)org> 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(at)citylink(dot)dinoex(dot)sub(dot)org) 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.

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