Running psql in a docker container

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.

--
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 for pgsql 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 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

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 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.

Any suggestions on what I have missed?

Thank you.