Обсуждение: pg_utility ?

Hello,

One of the things that came up during the pgconf.eu talk about REPACK,
proposed by Christoph Berg, is that adding another utility pg_repackdb
to run it from the command line adds more noise to an already noisy tool
neighbourhood.  He asked, how about we instead add a generic tool to run
utility commands?  So the user would be able to do things such as 

pg_utility vacuum -t tab1 -t tab2        # what vacuumdb does
pg_utility analyze -t tab1 -t tab2        # what vacuumdb -Z does
pg_utility vacuum analyze -t tab1 -t tab2    # what vacuumdb -z does
pg_utility cluster -t tab1 -t tab2        # what clusterdb does
pg_utility reindex -t tab1 -t tab2        # what reindexdb does

Each such tool would retain more or less its current behavior, i.e., its
ability to run things in parallel, to discover tables to operate on
based on circumstances, to silently ignore objects depending on the user
lacking specific privilege bits, and so on.

This way, instead of an entire pg_repackdb tool, we would add just a new
mode to pg_utility:

pg_utility repack -t tab1 -t tab2        # what pg_repackdb would do

Christoph, can you please confirm that this is approximately what you
had in mind?  Other names are of course possible.

I didn't immediately love this idea, but I'm not totally opposed to it
either, and perhaps it makes things better rather than adding yet
another very narrowly-focused tool.  Also, pg_ctl already kinda has a
somewhat similar facet with its "pg_ctl init" mode.

Thanks

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Once again, thank you and all of the developers for your hard work on
PostgreSQL.  This is by far the most pleasant management experience of
any database I've worked on."                             (Dan Harris)
http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php

> On Tue, Nov 04, 2025 at 06:52:19PM +0100, Álvaro Herrera wrote:
> One of the things that came up during the pgconf.eu talk about REPACK,
> proposed by Christoph Berg, is that adding another utility pg_repackdb
> to run it from the command line adds more noise to an already noisy tool
> neighbourhood.  He asked, how about we instead add a generic tool to run
> utility commands?  So the user would be able to do things such as 
> 
> pg_utility vacuum -t tab1 -t tab2        # what vacuumdb does
> pg_utility analyze -t tab1 -t tab2        # what vacuumdb -Z does
> pg_utility vacuum analyze -t tab1 -t tab2    # what vacuumdb -z does
> pg_utility cluster -t tab1 -t tab2        # what clusterdb does
> pg_utility reindex -t tab1 -t tab2        # what reindexdb does
> 
> Each such tool would retain more or less its current behavior, i.e., its
> ability to run things in parallel, to discover tables to operate on
> based on circumstances, to silently ignore objects depending on the user
> lacking specific privilege bits, and so on.
> 
> This way, instead of an entire pg_repackdb tool, we would add just a new
> mode to pg_utility:
> 
> pg_utility repack -t tab1 -t tab2        # what pg_repackdb would do

FWIW I find the idea interesting, it would help structure the tooling
landscape. Looking around, looks like it's common to have some sort of
manager or a toolbox for similar purposes.

Would it only be allowed to run anything involving CMD_UTILITY, or are
"utility commands" meant here in more broader sense?

> pg_utility vacuum -t tab1 -t tab2               # what vacuumdb does
> pg_utility analyze -t tab1 -t tab2              # what vacuumdb -Z does
> pg_utility vacuum analyze -t tab1 -t tab2       # what vacuumdb -z does
> pg_utility cluster -t tab1 -t tab2              # what clusterdb does
> pg_utility reindex -t tab1 -t tab2              # what reindexdb does

Is the idea to get rid of most of bin/scripts and replace them with a single
pg_utility? or will it just be a wrapper for the existing utilities? meaning
they will still work stand-alone.

--
Sami Imseih
Amazon Web Services (AWS)

Re: Álvaro Herrera
> Christoph, can you please confirm that this is approximately what you
> had in mind?  Other names are of course possible.

Sorry for the late reply, the mail got lost in my inbox.

Yes that's what I had in mind.

> I didn't immediately love this idea, but I'm not totally opposed to it
> either, and perhaps it makes things better rather than adding yet
> another very narrowly-focused tool.  Also, pg_ctl already kinda has a
> somewhat similar facet with its "pg_ctl init" mode.

I would keep the server and client bits separate, though, so not merge
these into pg_ctl.

I don't have an idea for the ideal name, perhaps only that it should
be short, and distinct from pg_ctl so people don't get confused. (So
not pg_cmd or pg_cli.)

Perhaps pg_util? ("pg" is taken by that classic pager thingy.)

Christoph

On 11/19/25 12:52 PM, Christoph Berg wrote:
> Re: Álvaro Herrera
>> I didn't immediately love this idea, but I'm not totally opposed to it
>> either, and perhaps it makes things better rather than adding yet
>> another very narrowly-focused tool.  Also, pg_ctl already kinda has a
>> somewhat similar facet with its "pg_ctl init" mode.
> 
> I would keep the server and client bits separate, though, so not merge
> these into pg_ctl.
> 
> I don't have an idea for the ideal name, perhaps only that it should
> be short, and distinct from pg_ctl so people don't get confused. (So
> not pg_cmd or pg_cli.)
> 
> Perhaps pg_util? ("pg" is taken by that classic pager thingy.)

I like the name pg_util. In the MySQL world it is called mysqladmin, 
which is a does of pg_ctl and tools like createdb.

https://dev.mysql.com/doc/refman/9.5/en/mysqladmin.html

Maybe pg_util should only be for tools which connect to PostgreSQL over 
the TCP (or a unix socket) while the all other tools, which need access 
to the data directory, should have their own executables? Because in my 
opinion we really have two kinds of frontend tools: those which need to 
run on the same machine and with the same user as PostgreSQL and those 
which connect to PostgreSQL, possibly from another machine, and run some 
commands.

--
Andreas
Percona https://www.percona.com/

Re: Andreas Karlsson
> > Perhaps pg_util? ("pg" is taken by that classic pager thingy.)
> 
> I like the name pg_util. In the MySQL world it is called mysqladmin, which
> is a does of pg_ctl and tools like createdb.

"pg_adm" would also be a candidate. (Or is that too close to pgadmin?)

> Maybe pg_util should only be for tools which connect to PostgreSQL over the
> TCP (or a unix socket) while the all other tools, which need access to the
> data directory, should have their own executables?

That would be the idea, yes.

Christoph

Hi Álvaro,

> One of the things that came up during the pgconf.eu talk about REPACK,
> proposed by Christoph Berg, is that adding another utility pg_repackdb
> to run it from the command line adds more noise to an already noisy tool
> neighbourhood.  He asked, how about we instead add a generic tool to run
> utility commands?  So the user would be able to do things such as
>
> pg_utility vacuum -t tab1 -t tab2               # what vacuumdb does
> pg_utility analyze -t tab1 -t tab2              # what vacuumdb -Z does
> pg_utility vacuum analyze -t tab1 -t tab2       # what vacuumdb -z does
> pg_utility cluster -t tab1 -t tab2              # what clusterdb does
> pg_utility reindex -t tab1 -t tab2              # what reindexdb does
>
> Each such tool would retain more or less its current behavior, i.e., its
> ability to run things in parallel, to discover tables to operate on
> based on circumstances, to silently ignore objects depending on the user
> lacking specific privilege bits, and so on.
>
> This way, instead of an entire pg_repackdb tool, we would add just a new
> mode to pg_utility:
>
> pg_utility repack -t tab1 -t tab2               # what pg_repackdb would do
>
> Christoph, can you please confirm that this is approximately what you
> had in mind?  Other names are of course possible.
>
> I didn't immediately love this idea, but I'm not totally opposed to it
> either, and perhaps it makes things better rather than adding yet
> another very narrowly-focused tool.  Also, pg_ctl already kinda has a
> somewhat similar facet with its "pg_ctl init" mode.

I'm not necessarily opposed to the idea in general, but  I'm a bit
sceptical about the particular implementation. I fail to understand
the exact value of having "pg_utility (something)" instead of
"pg_(something)". To me it seems like we either end up supporting an
all-in-one utility, which will increase code complexity, or having an
additional utility which is going to be a wrapper for the existing
ones, which arguably has little value.

I get a feeling that the actual idea might be to refactor our CLI
tools to make the overall interface more consistent. Right now we have
several pg_* tools, and also tools like vacuumdb and createdb. Perhaps
what we might do instead is renaming/splitting the later ones into
pg_analyze, pg_vacuum, etc. Which of course is going to bring a
question about backward compatibility. I believe it can be provided by
symlinks or wrappers for several major releases.

--
Best regards,
Aleksander Alekseev

On 11/20/25 10:31 AM, Christoph Berg wrote:
> Re: Andreas Karlsson
>>> Perhaps pg_util? ("pg" is taken by that classic pager thingy.)
>>
>> I like the name pg_util. In the MySQL world it is called mysqladmin, which
>> is a does of pg_ctl and tools like createdb.
> 
> "pg_adm" would also be a candidate. (Or is that too close to pgadmin?)

Yeah, that would be way too confusing.

>> Maybe pg_util should only be for tools which connect to PostgreSQL over the
>> TCP (or a unix socket) while the all other tools, which need access to the
>> data directory, should have their own executables?
> 
> That would be the idea, yes.

Then I like it!

Andreas

Re: Darafei "Komяpa" Praliaskouski
> For the sake of simpler dictation over voice media when debugging over
> phone can we please have no underscore in the command, it adds an extra
> word to say.

Simpler as in "oh no extra underscore after pg in this one, all the
other commands like pg_ctl, pg_basebackup and pg_dump have one, but
this one does not, please remove it"? :)

Christoph