Package:postgresql-common
Version:273.pgdg24.04+1

Subject:Limitation in `pg_lsclusters` and `pg_dropcluster --stop` on
Debian-based Systems

Description:

There are a couple of limitations observed with the PostgreSQL management
utilities `pg_lsclusters` and `pg_dropcluster --stop` on Debian-based
systems. These limitations may impact users managing PostgreSQL clusters
manually or with custom configurations. Below are the details:


1. `pg_lsclusters` Does Not List Manually Created Clusters**
Limitation:
The `pg_lsclusters` utility currently does not list clusters that are
manually created using `initdb` and `pg_ctl`. These clusters are running
and functional but do not appear in the `pg_lsclusters` output.

Suggested Enhancement:
It would be useful if `pg_lsclusters` could detect and display all running
PostgreSQL clusters, including those created manually, for better
visibility and management.

---

2. `pg_lsclusters` Does Not Reflect Custom Ports

Limitation:
When a PostgreSQL cluster is started with a custom port using
`pg_ctlcluster` (e.g., `-o "-p <port>"`), `pg_lsclusters` reports the
default port from the `postgresql.conf` file, not the actual port in use by
the running instance.

Suggested Enhancement:
`pg_lsclusters` could be updated to reflect the actual runtime port of the
PostgreSQL process, providing accurate information for users.

3. `pg_dropcluster --stop` Deletes Clusters Without Confirmation

Limitation:
The `pg_dropcluster --stop` command deletes the PostgreSQL cluster
immediately without any confirmation prompt or warning.

Suggested Enhancement:
Adding a confirmation prompt or warning message before proceeding with the
deletion of a PostgreSQL cluster would help prevent accidental data loss.


Expected Behavior:

1. For `pg_lsclusters`:
   The utility should list manually created clusters and reflect the actual
runtime port in use.

2. For `pg_dropcluster --stop`:
   The command should prompt for confirmation before dropping a cluster to
avoid unintentional data loss.


Suggested Fixes:

1. For `pg_lsclusters`:
   Modify the utility to scan for all PostgreSQL clusters, including
manually created ones, and check the actual running PostgreSQL process for
port information.

2. For `pg_dropcluster --stop`:
   Introduce a confirmation prompt or warning before dropping a cluster to
protect users from accidental deletion.

Environment:

- **Operating System:** Debian-based system (e.g., Ubuntu 24.04)
- **PostgreSQL Version:** 17
- **Debian Package Version:** `postgresql-common` 273.pgdg24.04+1

Additional Information:

Replication images are included to provide further clarification of the
observed limitations. Please refer to the attached images for more detailed
examples.

Example:

When running the following command to drop a cluster:

sudo pg_dropcluster 17 main
The following warning message appears:
WARNING: You are about to delete the PostgreSQL cluster 17/main.
This action is irreversible and will permanently delete all data.
Are you sure? (yes/no):


A prompt for deletion and backup confirmation, as per the added feature, is
displayed.


Thank you for considering this request. Please feel free to contact me if
you need additional information or clarification on the suggestions
described.

Re: AKASH

That won't really work - there is no cluster name for these, and we
couldn't manage them anyway.

That has some value, but might be hard to figure out at run time,
especially if the user running pg_lsclusters doesn't have read access
to the postmaster.pid file in the data directory.

That extra safety switch is already there, it's spelled `--stop`.

Christoph

Thank you for your response. I appreciate the clarification regarding the
existing behavior of pg_dropcluster --stop. However, I would like to
further explain why adding a confirmation prompt or an additional --confirm
flag would be a valuable improvement.
Current Behavior of pg_dropcluster --stop

   - By default,  pg_dropcluster --stop does not automatically stop the
   cluster before deletion.
   - If the cluster is running, users must manually stop it before
   executing the deletion.
   - If attempted while the cluster is active, it results in an error:

blessy@blessy-Aspire-Lite-AL15-41:~$ pg_dropcluster 17 custom --stop
Warning: stopping the cluster using pg_ctlcluster will mark the
systemd unit as failed. Consider using systemctl:
  sudo systemctl stop postgresql@17-custom
Error: You must run this program as the cluster owner (postgres) or root
Error: could not stop server, aborting


   - The user must then manually stop the cluster using:

sudo systemctl stop postgresql@17-custom


   - After stopping, pg_dropcluster --stop can proceed with deletion.

Proposed Improvements 1. Modify --stop to Handle Cluster Stopping
Automatically

Instead of requiring manual intervention, pg_dropcluster --stop could:

   1. Attempt to stop the cluster automatically.
   2. Prompt a warning before deletion (to prevent accidental loss).

Example behavior:

sudo pg_dropcluster 17 main --stop
Stopping PostgreSQL cluster 17/main...
Cluster 17/main stopped successfully.
WARNING: You are about to delete the PostgreSQL cluster 17/main.
This action is irreversible and will permanently delete all data.
Are you sure? (yes/no): _


   - This change improves usability by eliminating the need for manual
   stopping.

2. Introduce a --confirm Flag for Direct Deletion

For users who prefer the current behavior, a --confirm flag could be added:

sudo pg_dropcluster 17 main --confirm
Deleting PostgreSQL cluster 17/main...
Removed data directory: /var/lib/postgresql/17/main
Removed configuration directory: /etc/postgresql/17/main
Removed log file: /var/log/postgresql/postgresql-17-main.log
PostgreSQL cluster 17/main deleted successfully.


   - --stop → Stops cluster (if running) and requires confirmation
   - --confirm → Skips confirmation and deletes cluster immediately

Why This Matters

   - Eliminates manual steps, making pg_dropcluster --stop more intuitive.
   - Reduces errors due to forgotten cluster stopping.
   - Aligns with best practices for preventing unintended data loss.

Would your team be open to these improvements? I’d be happy to discuss
further or contribute to implementation.

Best regards,
Akash

Re: AKASH

Are you saying that adding a manual confirmation step eliminates a
manual step?

Sorry, this is not going to change.

Christoph