Re: logical decoding/replication: new functions pg_ls_logicaldir and pg_ls_replslotdir

On Fri, Oct 22, 2021 at 04:18:04PM +0530, Bharath Rupireddy wrote:
> On Fri, Oct 22, 2021 at 3:18 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > On Fri, Oct 8, 2021 at 4:39 PM Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> > >
> > > At times, users want to know what are the files (snapshot and mapping
> > > files) that are available under pg_logical directory and also the
> > > spill files that are under pg_replslot directory and how much space
> > > they occupy.
> >
> > Why can't you use pg_ls_dir to see the contents of pg_replslot? To
>
> Thanks Amit!
>
> pg_ls_dir gives the list of directories and files, but not their sizes.

Returning sizes is already possible by using pg_stat_file:

ts=# SELECT dd, a, ls, stat.* FROM (SELECT current_setting('data_directory') AS dd, 'pg_logical' AS a) AS a, pg_ls_dir(a) AS ls, pg_stat_file(dd ||'/'|| a ||'/'|| ls) AS stat ;
dd | a | ls | size | access | modification | change | creation | isdir
------------------------+------------+-----------------------+------+------------------------+------------------------+------------------------+----------+-------
/var/lib/pgsql/14/data | pg_logical | replorigin_checkpoint | 8 | 2021-10-22 08:20:30-06 | 2021-10-22 08:20:30-06 | 2021-10-22 08:20:30-06 | | f
/var/lib/pgsql/14/data | pg_logical | mappings | 4096 | 2021-10-21 19:54:19-06 | 2021-10-15 19:50:35-06 | 2021-10-15 19:50:35-06 | | t
/var/lib/pgsql/14/data | pg_logical | snapshots | 4096 | 2021-10-21 19:54:19-06 | 2021-10-15 19:50:35-06 | 2021-10-15 19:50:35-06 | | t

I agree that this isn't a very friendly query, so I had created a patch adding
pg_ls_dir_metadata():
https://commitfest.postgresql.org/33/2377/

postgres=# SELECT * FROM pg_ls_dir_metadata('pg_logical');
filename | size | access | modification | change | creation | type | path
-----------------------+------+------------------------+------------------------+------------------------+----------+------+----------------------------------
mappings | 4096 | 2021-10-22 09:15:29-05 | 2021-10-22 09:15:29-05 | 2021-10-22 09:15:29-05 | | d | pg_logical/mappings
replorigin_checkpoint | 8 | 2021-10-22 09:15:47-05 | 2021-10-22 09:15:45-05 | 2021-10-22 09:15:45-05 | | - | pg_logical/replorigin_checkpoint
. | 4096 | 2021-10-22 09:16:23-05 | 2021-10-22 09:15:45-05 | 2021-10-22 09:15:45-05 | | d | pg_logical/.
.. | 4096 | 2021-10-22 09:16:01-05 | 2021-10-22 09:15:47-05 | 2021-10-22 09:15:47-05 | | d | pg_logical/..
snapshots | 4096 | 2021-10-22 09:15:29-05 | 2021-10-22 09:15:29-05 | 2021-10-22 09:15:29-05 | | d | pg_logical/snapshots
(5 rows)

I concluded that it's better to add a function to list metadata of an arbitrary
dir, rather than adding more functions to handle specific, hardcoded dirs:
https://www.postgresql.org/message-id/flat/20191227170220(dot)GE12890(at)telsasoft(dot)com

--
Justin