At present, the user name is obtained from the USER environment variable:

https://github.com/allenap/pgdo/blob/e9c001bec09f419fb75ebdfc96f1dc555613ad06/pgdo-lib/src/cluster.rs#L298C1-L307

But this isn't always set, especially in containers. We could obtain this more reliably from the OS directly, e.g.:

use nix::unistd::{getuid, User};
let user: Option<User> = User::from_uid(getuid());
let name: Option<String> = user.map(|user| user.name);

For example, create wrappers in pgdo-lib for using:

• https://crates.io/crates/postgres
• https://crates.io/crates/tokio-postgres
• https://crates.io/crates/sqlx

But that's just in Rust.

There could also be a wrapper for Python, say, around https://www.psycopg.org.

And so on.

e.g. AWS S3 and similar.

Already a bare pgdo command will expand to pgdo shell. In practice I find myself wanting to type pgdo a_thing to run a_thing in configured PostgreSQL environment – I keep forgetting to type exec. It would be cool if pgdo could retain its present behaviour, but when extra arguments are provided they should elicit an exec-like behaviour. Then it might be worth removing pgdo shell in favour of pgdo psql, perhaps pgdo exec too.

It's noisy and not first-timer friendly. Too much choice on a first outing. Maybe its functionality can be moved to another command, or somehow made less prominent.

This would display a formatted version of postmaster.log.

Emitting logs as JSON might be useful here.

The machinery is there, but not exposed via the restore command.

Hey! I'm looking into postgresfixture for running tests, and after submitting allenap/rust-postgresfixture#124 I noticed that it does not actually seem to support setting postgresql.conf parameters from the API.

Is it true, or did I miss the place to do it?

I'd need it in order to implement the optimizations listed here (eg. fsync=off), for test running.

(Maybe postgresqlfixture could even come with an unsafe set of settings optimized for speed so that not everyone has to read through the same documentation in order to get fast tests)

What do you think?

Just pass the parameters when starting the cluster.

It could be an option, or even the default, to create a database with random name (e.g. petname), so that concurrent processes by default do not tread on one another's toes.

$ postgresfixture shell
enough-lamprey# …

$ postgresfixture shell
divine-minnow# …

Some care might be needed to avoid accidental collisions. CREATE DATABASE will error if one tries to create a database that already exists, so either it will need to recover from that, or need to look before it leaps.

The default "mdy" (month, day, year) configuration is appropriate for the US, but that's about it. It's confusing for everyone else. It would be nice to find a default that is not ambiguous/confusing for everyone. Failing that, "dmy" might be a more appropriate default than "mdy". This may equally be a non-issue if clients like psql do something locale-specific already.

Perhaps stick to the same support policy as PostgreSQL itself (see Versioning Policy). At the time of writing, that means even version 10 is no longer supported.

I want to be able to upgrade a cluster as easily as:

$ pgdo -D data upgrade --to 16.0

and that's it.

https://www.postgresql.org/docs/16/upgrading.html talks about different ways to do this, but the most interesting option is pg_upgrade.

There are run_and_stop and run_and_destroy at present, but sometimes you want to leave the cluster running even if you're the last user. This can be relevant with two or more concurrent processes each using the cluster for brief periods to avoid stop-start churn as they interleave.

Especially after restoring from a backup, it's important to know a database role to use, otherwise it's not actually possible to connect to the database. For example, a cluster created as system user gavin will have a superuser role, by default, also named gavin. If this cluster is restored in another account, say bobby, then pgdo will not be able to connect to the restored cluster.

A superuser role can be determined in single-user mode like so:

$ postgres --single -D cluster postgres \
>   <<< 'select rolname from pg_roles where rolsuper and rolcanlogin' \
>     2> /dev/null | sed 's/^.*\brolname\s*=\s*"\(.*\)".*/\1/p;d'
gavin

(Update: function that does this added as cluster::determine_superuser_role_names.)

so pgdo could use this to guess, but perhaps something more/different can be done to make it Just Work.

Note, this might all be moot if pg_hba.conf doesn't allow local connections, for example.

Want to be able to prune, say, backups older than 30 days, or limit to a certain amount of disk space, or some combination.

For example, on macOS with Homebrew we can find out what PostgreSQL runtimes we could have with:

1. Obtain list of PostgreSQL formulae: brew formulae | egrep ^postgresql@.
2. Get JSON information (e.g. version) for these with brew info --json ....

We can select an appropriate runtime the same as now, but then install it if necessary.

Similar story for Debian/Ubuntu and other platforms.

If the backup location no longer exists or was moved, this will fail. It's also probably not a good idea to automatically backup a restored cluster to the same backup location, especially because a fully restored cluster (i.e. restored from all WAL, not to a particular point in time) doesn't appear to get a new timeline.

The cleanest option: set archive_mode to "off" and clear archive_command.

At present the runtime can only be selected at the command-line by manipulating PATH in advance. It would be a better experience if postgresfixture could discover runtimes and allow selection by version, say, by path matching, or providing a path. Perhaps postgresfixture also has some local smarts, e.g. it knows that Homebrew puts runtimes in /usr/local/Cellar/postgresql@${version} on macOS.

By version:

$ postgresfixture shell --runtime 12

Match by path:

$ postgresfixture shell --runtime postgresql@11

Provide a path to the runtime. To be honest, this isn't much more convenient than PATH=/path/to/runtime:$PATH postgresfixture shell but it is more discoverable:

$ postgresfixture shell --runtime /usr/local/Cellar/postgresql@11/11.15_1

NOTE that selecting the runtime is only useful before the cluster has been created. When moving between major versions (in one direction: up) one must upgrade the cluster explicitly, e.g. with pg_upgrade.

There should be tests in CI for backups and restores.