Calculating vm.nr_hugepages
Hello, I'm writing an Ansible play which is to set the correct value for vm.nr_hugepages on Linux servers where I hope to make Postgres make use of huge pages. However, I'm struggling to find the right formula. I assume I need to find the same value as I get from running "postgres -C shared_memory_size_in_huge_pages". I call that my target value. Note: I cannot simply run "postgres -C ...", because I need my Ansible play to work against a server where Postgres is running. I've tried using the formula described at https://www.cybertec-postgresql.com/en/huge-pages-postgresql/, but it produces a different value than my target: Using a shared_buffers value of 21965570048, like in Cybertec Postgresql's example: "postgres ... -C 21965570048B" yields: 10719 The formula from Cybertec Postgresql says: 10475 I've also tried doing what ChatGPG suggested: Number of Huge Pages when shared_buffers is set to 1 GiB = shared_buffers / huge_page_size = 1073741824 bytes / 2097152 bytes = 512 But that's also wrong compared to "postgres -C ..." (which said 542). Which formula can I use? It's OK for me for it to be slightly wrong compared to "postgres -C", but if it's wrong, it needs to be slightly higher than what "postgres -C" outputs, so that I'm sure there's enough huge pages for Postgres to be able to use them properly. -- Kind regards, Troels Arvin
Initiate backup from routine?
Hello, I would like to allow a co-worker to perform a backup of a database, such that the backup is saved to the database server itself. One use case is that (s)he would like an extra backup of a database, just before an application update is deployed. The co-worker doesn't have shell access on the DB server (so no sudo option), and we would like to allow this to happen without having to involve a DBA. Is it possible to call pg_dump (or equivalent action) through a procedure/function? -- Regards, Troels Arvin
Re: Initiate backup from routine?
Hello, David wrote: / Are you able to install an untrusted language handler into the database?/ Yes, if need be. -- Regards, Troels Arvin
utf8 vs UTF-8
Hello, In a Postgres installation, I have databases where the locale is slightly different. Which one is correct? Excerpt from "psql --list": test1 | loc_test | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 test3 | troels | UTF8 | libc | en_US.utf8 | en_US.utf8 OS is Ubuntu. -- Kind regards, Troels Arvin
Re: utf8 vs UTF-8
Hellok Hans Schou wrote: > test3 | troels | UTF8 | libc | en_US.utf8 | en_US.utf8 [...] how did you create test3? For this example, I used specified it at creation time: CREATE DATABASE test3 TEMPLATE template0 LOCALE 'en_US.utf8'; In the real-world example I'm working with I'm unsure how the database was originally created. -- Troels
Re: utf8 vs UTF-8
Hello, Tom Lane wrote: >> test1 | loc_test | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 >> test3 | troels | UTF8 | libc | en_US.utf8 | en_US.utf8 > > On most if not all platforms, both those spellings of the locale names > will be taken as valid. You might try running "locale -a" to get an > idea of which one is preferred according to your current libc > installation "locale -a" on the Ubuntu system outputs this: C C.utf8 en_US.utf8 POSIX On a CentOS7 system, it's sort-of the same: locale -a | grep -i en_us en_US en_US.iso88591 en_US.iso885915 en_US.utf8 So at first, I thought en_US.utf8 would be the most correct locale identifier. However, when I look at Postgres' own databases, they have the slightly different locale string: psql --list | grep -E 'postgres|template' postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | ... template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | ... template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | ... Also, when I try to create a database with "en_US.utf8" as locale without specifying a template: troels=# create database test4 locale 'en_US.utf8'; ERROR: new collation (en_US.utf8) is incompatible with the collation of the template database (en_US.UTF-8) HINT: Use the same collation as in the template database, or use template0 as template. Given the locale of Postgres' own databases and Postgres' error message, I'm leaning to en_US.UTF-8 being the most correct locale to use. Because why would Postgres care about it, if utf8/UTF-8 doesn't matter? but TBH, I doubt it's worth worrying about. But couldn't there be an issue, if for example the client's locale and the server's locale aren't exactly the same? I'm thinking maybe the client library has to perform unneeded translation of the stream of data to/from the database? -- Kind regards, Troels
