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.kapil...@gmail.com> wrote:
> > On Fri, Oct 8, 2021 at 4:39 PM Bharath Rupireddy
> > <bharath.rupireddyforpostg...@gmail.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.ge12...@telsasoft.com
--
Justin
