Re: Add function to return backup_label and tablespace_map

On 7/8/22 09:09, David Steele wrote:
> 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;

Looks like I made that more complicated than it needed to be:

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

Regards,
-David