Thread: Running psql in a docker container
I used to successfully run psql server 13 in a CentOS 7 docker container with CentOS 7 as the host operating system. I havenow upgraded that system to Rocky Linux 9 as the host operating system and modifying my container to also use Rocky Linux9 and psql server 16. I can successfully get the container up and running and can connect to it. However, I have not managed to modify the initdbstatement to use en_US.UTF-8 for all databases. In the old container I used: su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" but this does not work with psql 16. I tried: su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ -E 'UTF-8' --lc-lang='en_US'" and variations thereof with no success. Any suggestions on what I have missed? Thank you.
On 7/11/24 07:57, H wrote: > I used to successfully run psql server 13 in a CentOS 7 docker container with CentOS 7 as the host operating system. Ihave now upgraded that system to Rocky Linux 9 as the host operating system and modifying my container to also use RockyLinux 9 and psql server 16. > > I can successfully get the container up and running and can connect to it. However, I have not managed to modify the initdbstatement to use en_US.UTF-8 for all databases. > > In the old container I used: > > su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" > > but this does not work with psql 16. I tried: > > su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ -E 'UTF-8' --lc-lang='en_US'" > > and variations thereof with no success. Without a definition of what '... no success' means there is no real way to answer this. > > Any suggestions on what I have missed? > > Thank you. > > -- Adrian Klaver adrian.klaver@aklaver.com
On July 11, 2024 11:06:02 AM GMT-04:00, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >On 7/11/24 07:57, H wrote: >> I used to successfully run psql server 13 in a CentOS 7 docker >container with CentOS 7 as the host operating system. I have now >upgraded that system to Rocky Linux 9 as the host operating system and >modifying my container to also use Rocky Linux 9 and psql server 16. >> >> I can successfully get the container up and running and can connect >to it. However, I have not managed to modify the initdb statement to >use en_US.UTF-8 for all databases. >> >> In the old container I used: >> >> su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ >-E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" >> >> but this does not work with psql 16. I tried: >> >> su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ >-E 'UTF-8' --lc-lang='en_US'" >> >> and variations thereof with no success. > >Without a definition of what '... no success' means there is no real >way >to answer this. > >> >> Any suggestions on what I have missed? >> >> Thank you. >> >> Good question! ”No success” meant that I have not been able to figure out how to have new databases default to en_US.UTF-8,instead they default to C.UTF-8. What is the proper syntax for pgsql 16 for this? I could not get the example given in the docs to work...
H <agents@meddatainc.com> writes:
> Good question! ”No success” meant that I have not been able to figure out how to have new databases default to
en_US.UTF-8,instead they default to C.UTF-8.
The default for an installation is determined at initdb time, either
with an explicit locale switch or from the environment locale
settings.
regards, tom lane
On Thu, Jul 11, 2024 at 11:16 AM H <agents@meddatainc.com> wrote:
What is the proper syntax for pgsql 16 for this? I could not get the example given in the docs to work...
The documentation says this still works:
su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'"
If it isn't for you please show the evidence of the non-working-ness.
David J.
On July 11, 2024 2:20:43 PM EDT, Tom Lane <tgl@sss.pgh.pa.us> wrote: >H <agents@meddatainc.com> writes: >> Good question! ”No success” meant that I have not been able to figure >out how to have new databases default to en_US.UTF-8, instead they >default to C.UTF-8. > >The default for an installation is determined at initdb time, either >with an explicit locale switch or from the environment locale >settings. > > regards, tom lane Understood but how should formulate the initdb statement to accomplish what I want on pgsql 16 since the syntax I used forpgsql 13 does not work in my container?
On 7/11/24 11:14, H wrote: > On July 11, 2024 11:06:02 AM GMT-04:00, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> On 7/11/24 07:57, H wrote: >>> I used to successfully run psql server 13 in a CentOS 7 docker >> container with CentOS 7 as the host operating system. I have now >> upgraded that system to Rocky Linux 9 as the host operating system and >> modifying my container to also use Rocky Linux 9 and psql server 16. >>> >>> I can successfully get the container up and running and can connect >> to it. However, I have not managed to modify the initdb statement to >> use en_US.UTF-8 for all databases. >>> >>> In the old container I used: >>> >>> su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ >> -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" >>> >>> but this does not work with psql 16. I tried: >>> >>> su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ >> -E 'UTF-8' --lc-lang='en_US'" 1) Per: https://www.postgresql.org/docs/16/app-initdb.html lc_lang does not exist and I get: /usr/lib/postgresql/16/bin/initdb: unrecognized option '--lc-lang=en_US' 2) On Ubuntu 22.04 doing: /usr/lib/postgresql/16/bin/initdb -D postgres/16/data/ -E 'UTF-8' --lc-collate='en_US.utf8' --lc-ctype='en_US.utf8' I get: postgres=# \l List of databases -[ RECORD 1 ]-----+-------------------- Name | postgres Owner | aklaver Encoding | UTF8 Locale Provider | libc Collate | en_US.utf8 Ctype | en_US.utf8 ICU Locale | ICU Rules | Access privileges | -[ RECORD 2 ]-----+-------------------- Name | template0 Owner | aklaver Encoding | UTF8 Locale Provider | libc Collate | en_US.utf8 Ctype | en_US.utf8 ICU Locale | ICU Rules | Access privileges | =c/aklaver + | aklaver=CTc/aklaver -[ RECORD 3 ]-----+-------------------- Name | template1 Owner | aklaver Encoding | UTF8 Locale Provider | libc Collate | en_US.utf8 Ctype | en_US.utf8 ICU Locale | ICU Rules | Access privileges | =c/aklaver + | aklaver=CTc/aklaver 3) What does locale -a return on your machine? >>> >>> and variations thereof with no success. >> >> Without a definition of what '... no success' means there is no real >> way >> to answer this. >> >>> >>> Any suggestions on what I have missed? >>> >>> Thank you. >>> >>> > > Good question! ”No success” meant that I have not been able to figure out how to have new databases default to en_US.UTF-8,instead they default to C.UTF-8. > > What is the proper syntax for pgsql 16 for this? I could not get the example given in the docs to work... -- Adrian Klaver adrian.klaver@aklaver.com
H <agents@meddatainc.com> writes:
> Understood but how should formulate the initdb statement to accomplish what I want on pgsql 16 since the syntax I
usedfor pgsql 13 does not work in my container?
You still haven't shown us the actual error message, so we're all
just guessing.
I will offer a guess though. This *should* work, since Rocky 9
is a direct descendant of RHEL/CentOS 7. The only reason I can
think why it wouldn't is that you haven't installed the OS package
that defines en_US.UTF-8. Try doing "locale -a" and see if
en_US.UTF-8 is among the listed locales.
On my RHEL8 box, it looks like glibc-locale-source is what
provides most non-C locales.
regards, tom lane
On July 11, 2024 3:48:42 PM EDT, Tom Lane <tgl@sss.pgh.pa.us> wrote: >H <agents@meddatainc.com> writes: >> Understood but how should formulate the initdb statement to >accomplish what I want on pgsql 16 since the syntax I used for pgsql 13 >does not work in my container? > >You still haven't shown us the actual error message, so we're all >just guessing. > >I will offer a guess though. This *should* work, since Rocky 9 >is a direct descendant of RHEL/CentOS 7. The only reason I can >think why it wouldn't is that you haven't installed the OS package >that defines en_US.UTF-8. Try doing "locale -a" and see if >en_US.UTF-8 is among the listed locales. > >On my RHEL8 box, it looks like glibc-locale-source is what >provides most non-C locales. > > regards, tom lane I run locale på in the container and found that the appropriate locale is called en_US.utf8, ie. not en_US.UTF-8... Problem now solved! Thank you all!
On July 11, 2024 3:31:37 PM EDT, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >On 7/11/24 11:14, H wrote: >> On July 11, 2024 11:06:02 AM GMT-04:00, Adrian Klaver ><adrian.klaver@aklaver.com> wrote: >>> On 7/11/24 07:57, H wrote: >>>> I used to successfully run psql server 13 in a CentOS 7 docker >>> container with CentOS 7 as the host operating system. I have now >>> upgraded that system to Rocky Linux 9 as the host operating system >and >>> modifying my container to also use Rocky Linux 9 and psql server 16. >>>> >>>> I can successfully get the container up and running and can connect >>> to it. However, I have not managed to modify the initdb statement to >>> use en_US.UTF-8 for all databases. >>>> >>>> In the old container I used: >>>> >>>> su - postgres -c "/usr/pgsql-13/bin/initdb -D >/var/lib/pgsql/13/data/ >>> -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" >>>> >>>> but this does not work with psql 16. I tried: >>>> >>>> su - postgres -c "/usr/pgsql-16/bin/initdb -D >/var/lib/pgsql/16/data/ >>> -E 'UTF-8' --lc-lang='en_US'" > >1) Per: > >https://www.postgresql.org/docs/16/app-initdb.html > >lc_lang does not exist and I get: > >/usr/lib/postgresql/16/bin/initdb: unrecognized option >'--lc-lang=en_US' > >2) On Ubuntu 22.04 doing: > > /usr/lib/postgresql/16/bin/initdb -D postgres/16/data/ -E 'UTF-8' >--lc-collate='en_US.utf8' --lc-ctype='en_US.utf8' > >I get: > >postgres=# \l >List of databases >-[ RECORD 1 ]-----+-------------------- >Name | postgres >Owner | aklaver >Encoding | UTF8 >Locale Provider | libc >Collate | en_US.utf8 >Ctype | en_US.utf8 >ICU Locale | >ICU Rules | >Access privileges | >-[ RECORD 2 ]-----+-------------------- >Name | template0 >Owner | aklaver >Encoding | UTF8 >Locale Provider | libc >Collate | en_US.utf8 >Ctype | en_US.utf8 >ICU Locale | >ICU Rules | >Access privileges | =c/aklaver + > | aklaver=CTc/aklaver >-[ RECORD 3 ]-----+-------------------- >Name | template1 >Owner | aklaver >Encoding | UTF8 >Locale Provider | libc >Collate | en_US.utf8 >Ctype | en_US.utf8 >ICU Locale | >ICU Rules | >Access privileges | =c/aklaver + > | aklaver=CTc/aklaver > >3) What does locale -a return on your machine? > > >>>> >>>> and variations thereof with no success. >>> >>> Without a definition of what '... no success' means there is no real >>> way >>> to answer this. >>> >>>> >>>> Any suggestions on what I have missed? >>>> >>>> Thank you. >>>> >>>> >> >> Good question! ”No success” meant that I have not been able to figure >out how to have new databases default to en_US.UTF-8, instead they >default to C.UTF-8. >> >> What is the proper syntax for pgsql 16 for this? I could not get the >example given in the docs to work... You are right, the locale is now called en_US.utf8 and with that correction I get the expected result.
In the docker-compose.yml file it can be passed in the environment variables. POSTGRES_INITDB_ARGS: "--encoding=UTF-8" On Thursday, July 11, 2024 at 10:58:14 AM GMT-4, H <agents@meddatainc.com> wrote: I used to successfully run psql server 13 in a CentOS 7 docker container with CentOS 7 as the host operating system. I havenow upgraded that system to Rocky Linux 9 as the host operating system and modifying my container to also use Rocky Linux9 and psql server 16. I can successfully get the container up and running and can connect to it. However, I have not managed to modify the initdbstatement to use en_US.UTF-8 for all databases. In the old container I used: su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'" but this does not work with psql 16. I tried: su - postgres -c "/usr/pgsql-16/bin/initdb -D /var/lib/pgsql/16/data/ -E 'UTF-8' --lc-lang='en_US'" and variations thereof with no success. Any suggestions on what I have missed? Thank you.