Re: Add function to return backup_label and tablespace_map

On 7/8/22 08:22, Julien Rouhaud wrote:
> On Fri, Jul 8, 2022 at 7:42 PM David Steele <david(at)pgmasters(dot)net> wrote:
>>
>> On 7/7/22 12:43, Fujii Masao wrote:
>>
>>> Since an exclusive backup method was dropped in v15, in v15 or later, we
>>> need to create backup_label and tablespace_map files from the result of
>>> pg_backup_stop() when taking a base backup using low level backup API.
>>> One issue when doing this is that; there is no simple way to create
>>> those files from two columns "labelfile" and "spcmapfile" that
>>> pg_backup_stop() returns if we execute it via psql. Probaby we need to
>>> store those columns in a temporary file and run some OS commands or
>>> script to separate that file into backup_label and tablespace_map.
>>
>> Why not just select these columns into a temp table:
>>
>> create temp table backup_result as select * from pg_backup_stop(...);
>>
>> Then they can be easily dumped with \o by selecting from the temp table.
>
> That wouldn't help people making backups from standby servers.

Ah, yes, good point. This should work on a standby, though:

select quote_literal(labelfile) as backup_label from pg_backup_stop(...)
\gset
\pset tuples_only on
\pset format unaligned
\o /backup_path/backup_label
select :backup_label;

Regards,
-David