galaxyproject
set a default postgresql timezone to UTC
this prevents cutoff issues at https://github.com/galaxyproject/galaxy/issues/16021
this change seems more galaxy-centric than the rest of the role :(
Well we are overriding postgres's default with galaxy-centric UTC, aren't we?
You're right - postgres uses the system timezone by default, so this behavior would come as a surprise if someone were using this outside of galaxy. Provided we want to keep this role galaxy-agnostic, would you consider adding this setting to the best practices example in the ansible galaxy role readme instead? https://github.com/galaxyproject/ansible-galaxy?tab=readme-ov-file#best-practice
well I wanted to do something more default than a readme entry, I suspect many Galaxies using this role have this set wrongly
Do you know how yours got set to the local TZ in the first place? I can't recall having seen the timezone being set in the past.
What's the OS/install method (OS package vs. PGDG) in your case?
@natefoo I wasn't around but it is my understanding that this role has been used to install postgres on our debian systems. I am not sure how to check the os/install method you'd like to know though, could you give me a hint please?
edit: I also remember that in that Galaxy issue I linked above about my dev env that was postgres installed via homebrew
apt show postgresql-16 (or whatever your package name is) should show the source, particularly under APT-Sources.
@natefoo
APT-Sources: http://ftp.zcu.cz/pub/linux/debian bookworm/main amd64 Packages
Do I understand this correctly, that somebody may have prepared the packages for local timezone? This is likely a source repo specifically for our org.
I had the same behavior on macos with homebrew though, so it is not super rare, but maybe not as important as I though.
That looks like just a standard Debian mirror to me, meaning you are probably using the standard Debian postgresql package. I guess what you're saying though is that it always uses the system timezone if a timezone on initial setup, which is a surprise to me. But I just checked and our database VMs at TACC (Rocky9, PGDG PostgreSQL) have timezone = 'US/Central' (the system timezone), which is the first I've ever noticed it. Presumably initdb, which generates the initial postgresql.conf did this, possibly influenced by the value of $TZ?
I wonder if rather than defaulting this in the role, we ought to have a postgresql_timezone that sets this in an included conf but defaults to unset (don't override the default)? And then update our docs to recommend setting it?
Or don't change the role and just update our docs to recommend setting it in the postgresql_conf as you've done in this PR.
@natefoo with database timezone US/Central I think usegalaxy.org has also broken admin jobs query, just to the other direction (selecting multiple hours more of data for 1min cutoff, hitting the 500 limit)
I am just surprised this does not surface in the app more, or maybe it does.
I agree, it is strange we don't see this more, I have noticed the weirdness in /admin/jobs before but I think that's the only place (and update times on TS repos but I think that is a different issue). Is there something different about that query?