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). 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. 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. 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
