Re: Add function to return backup_label and tablespace_map

At Fri, 8 Jul 2022 01:43:49 +0900, Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com> wrote in
> finishes. For example, this function allows us to take a backup using
> the following psql script file.
>
> ------------------------------
> SELECT * FROM pg_backup_start('test');
> \! cp -a $PGDATA /backup
> SELECT * FROM pg_backup_stop();
>
> \pset tuples_only on
> \pset format unaligned
>
> \o /backup/data/backup_label
> SELECT labelfile FROM pg_backup_label();
>
> \o /backup/data/tablespace_map
> SELECT spcmapfile FROM pg_backup_label();
> ------------------------------

As David mentioned, we can do the same thing now by using \gset, when
we want to save the files on the client side. (File copy is done on
the server side by the steps, though.)

Thinking about another scenario of generating those files server-side
(this is safer than the client-side method regarding to
line-separators and the pset settings, I think). We can do that by
using admingpack instead, with simpler steps.

SELECT lsn, labelfile, spcmapfile
pg_file_write('/tmp/backup_label', labelfile, false),
pg_file_write('/tmp/tablespace_map', spcmapfile, false)
FROM pg_backup_stop();

However, if pg_file_write() fails, the data are gone. But \gset also
works here.

select pg_backup_start('s1');
SELECT * FROM pg_backup_stop() \gset
SELECT pg_file_write('/tmp/backup_label', :'labelfile', false);
SELECT pg_file_write('/tmp/tablespace_map', :'spcmapfile', false);

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center