postgresml
add cli subcommands
Hi all.
From the @sebastianwebber's work on adding clap and a --help I saw the opportunity to improve pgcat with some cli commands to eliminate some "toil".
The current code is a work in progress and implements a config subcommand to pgcat with the goal to works as a "CRUD" for configuration, so I can edit config file without open it any editor.
With this PR we can list pools with pgcat config pools list. See:
$ ./target/debug/pgcat -h
PgCat: Nextgen PostgreSQL Pooler
Usage: pgcat [OPTIONS] [CONFIG_FILE] [COMMAND]
Commands:
config
help Print this message or the help of the given subcommand(s)
Arguments:
[CONFIG_FILE] [env: CONFIG_FILE=] [default: pgcat.toml]
Options:
-l, --log-level <LOG_LEVEL> [env: LOG_LEVEL=] [default: INFO]
-F, --log-format <LOG_FORMAT> [env: LOG_FORMAT=] [default: text] [possible values: text, structured, debug]
-n, --no-color disable colors in the log output [env: NO_COLOR=]
-h, --help Print help
-V, --version Print version
$ ./target/debug/pgcat config
Usage: pgcat config <COMMAND>
Commands:
pools
shards
help Print this message or the help of the given subcommand(s)
Options:
-h, --help Print help
$ ./target/debug/pgcat config pools
Usage: pgcat config pools <COMMAND>
Commands:
list
add
remove
help Print this message or the help of the given subcommand(s)
Options:
-h, --help Print help
$ ./target/debug/pgcat config pools list
2023-08-13T23:35:29.250990Z INFO pgcat: Welcome to PgCat! Meow. (Version 1.1.2-dev)
Pools list
+------------+-------------+---------------------+--------------+
| pool_name | pool_mode | load_balancing_mode | default_role |
+------------+-------------+---------------------+--------------+
| sharded_db | transaction | random | any |
| simple_db | session | random | primary |
+------------+-------------+---------------------+--------------+
$
Now it prints pool's users:
➤ cargo run -- .circleci/pgcat.toml config pools list
2023-08-15T00:17:46.243804Z INFO pgcat: Welcome to PgCat! Meow. (Version 1.1.2-dev)
🌀 Pools list
┌────────────┬─────────────┬─────────────────────┬──────────────┬───────────────────────────────────────────────────┐
│ pool_name │ pool_mode │ load_balancing_mode │ default_role │ users │
├────────────┼─────────────┼─────────────────────┼──────────────┼───────────────────────────────────────────────────┤
│ sharded_db │ transaction │ random │ any │ ┌───────────────┬───────────┬───────────────────┐ │
│ │ │ │ │ │ username │ pool_size │ statement_timeout │ │
│ │ │ │ │ ├───────────────┼───────────┼───────────────────┤ │
│ │ │ │ │ │ sharding_user │ 9 │ 0 │ │
│ │ │ │ │ ├───────────────┼───────────┼───────────────────┤ │
│ │ │ │ │ │ other_user │ 21 │ 30000 │ │
│ │ │ │ │ └───────────────┴───────────┴───────────────────┘ │
├────────────┼─────────────┼─────────────────────┼──────────────┼───────────────────────────────────────────────────┤
│ simple_db │ session │ random │ primary │ ┌─────────────┬───────────┬───────────────────┐ │
│ │ │ │ │ │ username │ pool_size │ statement_timeout │ │
│ │ │ │ │ ├─────────────┼───────────┼───────────────────┤ │
│ │ │ │ │ │ simple_user │ 5 │ 30000 │ │
│ │ │ │ │ └─────────────┴───────────┴───────────────────┘ │
└────────────┴─────────────┴─────────────────────┴──────────────┴───────────────────────────────────────────────────┘
Now it prints users, shards and servers. I also work on to save some screen space by shorting some column's names.
➤ cargo run -- .circleci/pgcat.toml config pools list
Compiling pgcat v1.1.2-dev (/home/guedes/Programas/Rust/pgcat)
Finished dev [unoptimized + debuginfo] target(s) in 7.32s
Running `target/debug/pgcat .circleci/pgcat.toml config pools list`
2023-08-15T21:30:27.043024Z INFO pgcat: Welcome to PgCat! Meow. (Version 1.1.2-dev)
🌀 Pools list
┌────────────┬─────────────┬─────────┬──────────┬───────────────────────────────────────────┬─────────────────────────────────────────┐
│ name │ mode │ lb mode │ def role │ users │ shards │
├────────────┼─────────────┼─────────┼──────────┼───────────────────────────────────────────┼─────────────────────────────────────────┤
│ sharded_db │ transaction │ random │ any │ ┌───────────────┬────────┬──────────────┐ │ ┌──────────┬──────────────────────────┐ │
│ │ │ │ │ │ username │ p size │ stmt timeout │ │ │ database │ servers │ │
│ │ │ │ │ ├───────────────┼────────┼──────────────┤ │ ├──────────┼──────────────────────────┤ │
│ │ │ │ │ │ sharding_user │ 9 │ 0 │ │ │ shard0 │ 127.0.0.1:5432 (Primary) │ │
│ │ │ │ │ ├───────────────┼────────┼──────────────┤ │ │ │ localhost:5432 (Replica) │ │
│ │ │ │ │ │ other_user │ 21 │ 30000 │ │ ├──────────┼──────────────────────────┤ │
│ │ │ │ │ └───────────────┴────────┴──────────────┘ │ │ shard1 │ 127.0.0.1:5432 (Primary) │ │
│ │ │ │ │ │ │ │ localhost:5432 (Replica) │ │
│ │ │ │ │ │ ├──────────┼──────────────────────────┤ │
│ │ │ │ │ │ │ shard2 │ 127.0.0.1:5432 (Primary) │ │
│ │ │ │ │ │ │ │ localhost:5432 (Replica) │ │
│ │ │ │ │ │ └──────────┴──────────────────────────┘ │
├────────────┼─────────────┼─────────┼──────────┼───────────────────────────────────────────┼─────────────────────────────────────────┤
│ simple_db │ session │ random │ primary │ ┌─────────────┬────────┬──────────────┐ │ ┌──────────┬──────────────────────────┐ │
│ │ │ │ │ │ username │ p size │ stmt timeout │ │ │ database │ servers │ │
│ │ │ │ │ ├─────────────┼────────┼──────────────┤ │ ├──────────┼──────────────────────────┤ │
│ │ │ │ │ │ simple_user │ 5 │ 30000 │ │ │ some_db │ 127.0.0.1:5432 (Primary) │ │
│ │ │ │ │ └─────────────┴────────┴──────────────┘ │ │ │ localhost:5432 (Replica) │ │
│ │ │ │ │ │ └──────────┴──────────────────────────┘ │
└────────────┴─────────────┴─────────┴──────────┴───────────────────────────────────────────┴─────────────────────────────────────────┘
This is super cool!
Nice! Thank you!
Let me know if you'd like me to merge this as is, or you'd like to keep working on it.
Let me clean the code a bit and removing the "not implemented" things then I'll poke when I'm done.
Just one suggestion:
Commands: pools Manage pool's configuration users not implemented shards not implemented helpBoth
usersandshards, as far as I understand their purpose here, would only make sense in the context of a specific pool. So I think they should be subcommands ofpools, maybe something likepools editorpools shards add,pools users remove, etc.
Yes. It makes sense. I'll get rid of that. Thanks!
More things I think would be cool to implement as well:
- show the values from
Generalconfig, e.g. timeouts, port, host, etc.
Sure! How should we call that?
pgcat config showpgcat config listpgcat config general showpgcat ... ?
- limit how many pools are listed, with a search function; some configs in multi-tenant situations can get very long.
Yeah! It would be nice to have some search feature!
Should I create a PR to work on search so we separate things and make the review work easier?
- limit how many pools are listed, with a search function; some configs in multi-tenant situations can get very long.
Yeah! It would be nice to have some search feature!
Should I create a PR to work on search so we separate things and make the review work easier?
Sounds good to me!
Sure! How should we call that?
* `pgcat config show` * `pgcat config list` * `pgcat config general show` * `pgcat ... ?`
I think pcat config show should show the general settings, while pgcat config pools list should show the pools, like it does now.
Hi @levkk!
More things I think would be cool to implement as well:
- show the values from
Generalconfig, e.g. timeouts, port, host, etc.
I cleaned the code a bit and implemented your suggestion. There are many fields in General so I just expose some of them.
➤ cargo run -- .circleci/pgcat.toml config show
Finished dev [unoptimized + debuginfo] target(s) in 0.68s
Running `target/debug/pgcat .circleci/pgcat.toml config show`
2023-08-16T11:08:37.929225Z INFO pgcat: Welcome to PgCat! Meow. (Version 1.1.2-dev)
🌀 General configuration
┌─────────┬──────┬──────────────┬──────────────┬──────────────────────────┬──────────────────┬────────────┐
│ host │ port │ conn timeout │ idle timeout │ idle cli in xact timeout │ shutdown timeout │ adm user │
├─────────┼──────┼──────────────┼──────────────┼──────────────────────────┼──────────────────┼────────────┤
│ 0.0.0.0 │ 6432 │ 1000 │ 600000 │ 0 │ 5000 │ admin_user │
└─────────┴──────┴──────────────┴──────────────┴──────────────────────────┴──────────────────┴────────────┘
General has as many attributes as Pools, so I propose merging this (unless you have objections). I'll also work on other PRs to introduce commands for managing configurations, search capabilities, and some way for the user to choose a more 'extended' output.
What do you think?
Once checks passes and unless you have objections, the code is ready to merge. :blue_heart:
Hey @guedes , sorry for a late reply. One last nit pick before we can merge: could you make the general stats be a vertical table? As you mentioned, there are a lot of values in there. The way one typically deals with those situations is by flipping the table, e.g.:
| Setting | Value |
|---|---|
host |
0.0.0.0 |
port |
6432 |
etc.
Hey @guedes , sorry for a late reply. One last nit pick before we can merge: could you make the general stats be a vertical table? As you mentioned, there are a lot of values in there. The way one typically deals with those situations is by flipping the table, e.g.:
Setting Value
host0.0.0.0port6432etc.
Sure! I'll do it.
Thanks for your feedback!
Hi @levkk!
Done! This is good for you?
cargo run -- config show
Finished dev [unoptimized + debuginfo] target(s) in 0.07s
Running `target/debug/pgcat config show`
2023-08-18T22:00:57.034616Z INFO pgcat: Welcome to PgCat! Meow. (Version 1.1.2-dev)
🛠️ General configuration
┌────────────────────────────────────┬────────────┐
│ Setting │ Value │
├────────────────────────────────────┼────────────┤
│ host │ 0.0.0.0 │
├────────────────────────────────────┼────────────┤
│ port │ 6432 │
├────────────────────────────────────┼────────────┤
│ connect_timeout │ 5000 │
├────────────────────────────────────┼────────────┤
│ idle_timeout │ 30000 │
├────────────────────────────────────┼────────────┤
│ idle_client_in_transaction_timeout │ 0 │
├────────────────────────────────────┼────────────┤
│ shutdown_timeout │ 60000 │
├────────────────────────────────────┼────────────┤
│ tcp_user_timeout │ 10000 │
├────────────────────────────────────┼────────────┤
│ admin_username │ admin_user │
├────────────────────────────────────┼────────────┤
│ tcp_keepalives_idle │ 5 │
├────────────────────────────────────┼────────────┤
│ tcp_keepalives_count │ 5 │
├────────────────────────────────────┼────────────┤
│ tcp_keepalives_interval │ 5 │
├────────────────────────────────────┼────────────┤
│ dns_cache_enabled │ false │
├────────────────────────────────────┼────────────┤
│ dns_max_ttl │ 30 │
├────────────────────────────────────┼────────────┤
│ log_client_connections │ false │
├────────────────────────────────────┼────────────┤
│ log_client_disconnections │ false │
├────────────────────────────────────┼────────────┤
│ healthcheck_timeout │ 1000 │
├────────────────────────────────────┼────────────┤
│ healthcheck_delay │ 30000 │
├────────────────────────────────────┼────────────┤
│ ban_time │ 60 │
├────────────────────────────────────┼────────────┤
│ server_lifetime │ 86400000 │
├────────────────────────────────────┼────────────┤
│ server_round_robin │ true │
├────────────────────────────────────┼────────────┤
│ worker_threads │ 5 │
├────────────────────────────────────┼────────────┤
│ autoreload │ 15000 │
├────────────────────────────────────┼────────────┤
│ tls_certificate │ │
├────────────────────────────────────┼────────────┤
│ tls_private_key │ │
├────────────────────────────────────┼────────────┤
│ server_tls │ false │
├────────────────────────────────────┼────────────┤
│ verify_server_certificate │ false │
├────────────────────────────────────┼────────────┤
│ auth_query │ │
├────────────────────────────────────┼────────────┤
│ auth_query_user │ │
├────────────────────────────────────┼────────────┤
│ auth_query_password │ │
├────────────────────────────────────┼────────────┤
│ enable_prometheus_exporter │ true │
├────────────────────────────────────┼────────────┤
│ prometheus_exporter_port │ 9930 │
├────────────────────────────────────┼────────────┤
│ validate_config │ true │
└────────────────────────────────────┴────────────┘
Hi @levkk! I just rebased and squashed commits. Now waiting for circleci check to pass. Once test passes this PR could be merged.
I'll work on other features in separated PR's.
:rocket: