Subject: Re: Postgres restart in the middle of exclusive backup and the
 presence of backup_label file
To: Nathan Bossart <nathandbossart@gmail.com>,
 David Steele <david@pgmasters.net>
References: <20220301173208.GB1031413@nathanxps13>
 <1f685b3a-7ec2-825e-bb2b-d87d78ad5bd9@pgmasters.net>
 <621E6C2E.2050801@anastigmatix.net>
 <20220301191441.GW10577@tamriel.snowman.net>
 <621E7A5C.1030203@anastigmatix.net>
 <20220301200523.GX10577@tamriel.snowman.net>
 <20220302010302.GA1182956@nathanxps13> <621FC447.90502@anastigmatix.net>
 <20220308200107.GA85558@nathanxps13>
 <eafdd15c-6597-f205-c2df-3fb3817ccbd4@pgmasters.net>
 <20220308221253.GA169440@nathanxps13>
Cc: Stephen Frost <sfrost@snowman.net>, pgsql-hackers@lists.postgresql.org
From: Chapman Flack <chap@anastigmatix.net>
Message-ID: <622801BD.9020605@anastigmatix.net>
Date: Tue, 8 Mar 2022 20:24:13 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101
 Thunderbird/38.7.0
MIME-Version: 1.0
In-Reply-To: <20220308221253.GA169440@nathanxps13>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/622801BD.9020605%40anastigmatix.net>
Precedence: bulk

On 03/08/22 17:12, Nathan Bossart wrote:
> I spent some time trying to come up with a workable script to replace the
> existing one.  I think the main problem is that you need to write out both
> the backup label file and the tablespace map file, but I didn't find an
> easy way to write the different output columns of pg_backup_stop() to
> separate files via psql.

Something like this might work:

SELECT * FROM pg_backup_stop(true) \gset

\out /tmp/backup_label   \qecho :labelfile
\out /tmp/tablespace_map \qecho :spcmapfile
\out
\! ... tar command adding /tmp/{backup_label,tablespace_map} to the tarball

I notice the \qecho adds a final newline (and so if :spcmapfile is empty,
a file containing a single newline is made). In a quick test with a bogus
restore_command, I did not see any error messages specific to the format
of the backup_label or tablespace_map files, so maybe the final newline
isn't a problem.

Assuming the newline isn't a problem, that might be simple enough to
use in an example, and maybe it's not a bad thing that it highlights a few
psql capabilities the reader might not have stumbled on before. Or, maybe
it is just too confusing to bother.

While agreeing that pg_basebackup is the production-ready thing that
does it all for you (with tests for likely errors and so on), I think
there is also some value in a dead-simple example that concretely
shows you what "it" is, what the basic steps are that happen beneath
pg_basebackup's chrome.

If the added newline is a problem, I haven't thought of a way to exclude
it that doesn't take the example out of the realm of dead-simple.

Regards,
-Chap