Re: Something else about Redo Logs disappearing - Mailing list pgsql-general

On 6/9/20 10:55 AM, Peter wrote:
> On Mon, Jun 08, 2020 at 09:21:47PM -0700, Adrian Klaver wrote:
> !
> ! On 6/8/20 7:33 PM, Peter wrote:
> ! >
> ! > Actually, the affair had some good side: as usual I was checking
> ! > my own designs first and looking for flaws, and indeed I found one:
> ! > If you do copy out the archive logs not directly to tape, but to
> ! > some disk area for further processing, then there is an issue with
> ! > possible loss. If you do it like the docs say, with a command like
> ! > this:
> ! > archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p
> ! > +/mnt/server/archivedir/%f'  # Unix
> ! > That "cp" is usually not synchronous. So there is the possibility
> ! > that this command terminates successfully, and reports exitcode zero
> ! > back to the Postgres, and then the Postgres will consider that log
> ! > being safely away.
> !
> ! Which is why just following the above command in the docs is:
> !
> ! "(This is an example, not a recommendation, and might not work on all
> ! platforms.) "
> 
> So, what You are basically saying is: my worries are justified and
> correctly founded, and this is indeed a matter that needs to be taken
> care of.
> Thank You.
> 
> ! Generally for peace of mind folks use third party tools like:
> !
> ! pg_backrest(https://pgbackrest.org/),
> ! pg_probackup(https://postgrespro.com/products/extensions/pg_probackup) or
> ! Barman(https://www.pgbarman.org/).
> 
> Hmja. We may on occasion have a look into these...
> 
> ! I use pg_backrest, but it does not look promising for running on BSD:
> ! https://fluca1978.github.io/2019/03/04/pgbackrest_FreeBSD.html
> 
> That looks mostly like the usual things which can be fixed.
> 
> Now, for the facts: I am already using a professional backup
> solution. (It is actually a "dual-use/commercial" solution, of the
> kind which you can either fetch from github and use without support,
> or buy with a contract or whatever and get support.)
> 
> With this professional backup solution I have already identified 28
> (spell: twenty-eight) bugs, and fixed/workarounded these, until I got it
> properly working.
> 
> 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.
> But the bigger issue there is, that backup solution needs it's
> own postgres database as it's backend - and it cannot backup the
> database it is using. Looks quite pointless to me, then.
> So I just did it all with shell (and it wasn't many lines).

The backup solution is?

> 
> So now, as I've been thru identifying and handling all the issues in
> that one backup solution, and since it is supposed to handle *all*
> backup demands (and not only postgres), I will certainly not start
> and go thru the same process again with one of these supposed
> solutions, where 90% of the code tries to solve the same things
> redundantly again, but then only for PG.

They are not supposed. They are in use by many people/organizations 
across a wide variety of installations.

> 
> 
> Actually, I am getting very tired of reading that something which can
> easily be done within 20 lines of shell scripting, would need special
> "solutions", solutions that need to be compiled, solutions that would
> bring along their own fashion of interpreter, solutions that have a
> lot of their own dependencies and introduce mainly one thing: new bugs.

They where developed as they could not be done in 20 lines of shell 
scripting and work at a reliable level.

Fine rant below. Go forth and work your wonders.

> 
> Does nobody know anymore how to do proper systems management
> scripting? Using just the basic system tools which have proven to
> work for more than 50 years now!?
> 
> ! Not sure about pg_probackup.
> 
> Okay, I had a -very short- look into these. Just scanning the
> introductory pages.
> 
> The only really interesting thing there is the pg_probackup. These
> folks seem to have found a way to do row-level incremental backups.
> 
> And pgbarman seems to have an impressive understanding of ITIL (in
> case anybody bothers about that).
> 
> All these tools do only cover PG, but do that in any possible regards.
> 
> This is fine as long as you do not run any computers, and the only
> application you are using is Postgres.
> But, if you have other applications as well, or have computers, then
> you will need a different backup solution, something that will cover
> your site-wide backup demands, in a consistent fashion (think
> something in the style of ADSM, or nowadays called Spectrum Protect).
> 
> And then 90% of the things offered here become superfluous, because
> they are already handled site-wide. And then you will have to
> consider integration of both pieces - and that will most likely be
> more work and more error-prone than just writing a few adapters in
> shell.
> 
> 
> 
> cheerio,
> PMc
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com