How to configure Dokerfile for available swedish collate ?

[shubaivan]

I use docker image for postgres. And after some time I needed change default collate or collate for some column, whould be great convert data to new collate, but I faced witth some problem for set another collate to column. My image

postgres:
    image: postgres:9.6
    ports:
        - '5432:5432'
    container_name: 'postgresql'
    working_dir: /app
    restart: always
    environment:
        POSTGRES_DB: ${POSTGRES_DB}
        POSTGRES_USER: ${POSTGRES_USER}
        POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
        LC_COLLATE: 'sv_SE.UTF-8' // I added some time ago after create db
        LC_CTYPE: 'sv_SE.UTF-8' // I added some time ago after create db
    volumes:
        - ./data/postgresql:/var/lib/postgresql/data
        - ./postgres/locale.conf:/etc/locale.conf
    networks:
        - php

I want to set swedish collate to my column category_name in category table, For that I just try to execute this query

ALTER TABLE category ALTER COLUMN category_name SET DATA TYPE character varying(255) COLLATE "sv_SE.UTF-8"

> ERROR:  collation "sv_SE.UTF-8" for encoding "UTF8" does not exist

after that I executed manually in my container

root@95494dd4339b:/app# locale-gen sv_SE.utf8 && dpkg-reconfigure locales
Generating locales (this might take a while)...
Generation complete.
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LC_COLLATE = "sv_SE.UTF-8 UTF-8",
	LC_CTYPE = "sv_SE.UTF-8 UTF-8",
	LANG = "en_US.utf8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)
debconf: falling back to frontend: Readline
Configuring locales
-------------------

Locales are a framework to switch between multiple languages and allow users to
use their language, country, characters, collation order, etc.

Please choose which locales to generate. UTF-8 locales should be chosen by
default, particularly for new installations. Other character sets may be useful
for backwards compatibility with older systems and software.

  1. All locales 

Locales to be generated: 1

and choice 1 and after loading all locales I saw

416 sv_SE.UTF-8
Default locale for the system environment: 416

but after that still faced with the same error

> ERROR:  collation "sv_SE.UTF-8" for encoding "UTF8" does not exist

when I check pg_collation I did not found my sv_SE.UTF-8

myuser=# SELECT * FROM pg_collation;
  collname  | collnamespace | collowner | collencoding | collcollate | collctype
  
------------+---------------+-----------+--------------+-------------+----------
--
 default    |            11 |        10 |           -1 |             | 
 C          |            11 |        10 |           -1 | C           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 C.UTF-8    |            11 |        10 |            6 | C.UTF-8     | C.UTF-8
 en_US      |            11 |        10 |            6 | en_US.utf8  | en_US.utf
8
 en_US.utf8 |            11 |        10 |            6 | en_US.utf8  | en_US.utf
8
 ucs_basic  |            11 |        10 |            6 | C           | C
(7 rows)

myuser=# 

how to set swedish collate for my column ?

UPDATE

after manually executed in postgres container locale-gen sv_SE.utf8 && dpkg-reconfigure have that result:

root@95494dd4339b:/app# locale -a | grep sv_SE
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_COLLATE to default locale: No such file or directory
sv_SE
sv_SE.iso88591
sv_SE.iso885915
sv_SE.utf8

I try to create new database but faced with the same error... what wrong ??

myuser=# CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'sv_SE.UTF-8' LC_CTYPE = 'sv_SE.UTF-8';
ERROR:  invalid locale name: "sv_SE.UTF-8"
myuser=# 

maybe need reload service or something like that for apply changes?

maybe need reload service or something like that for apply changes?

[ImreSamu]

imho: the official way for the 'Locale Customization' https://hub.docker.com/_/postgres
is the extending the docker image

original de_DE.utf8 example ; you have to adapt

FROM postgres:9.4
RUN localedef -i de_DE -c -f UTF-8 -A /usr/share/locale/locale.alias de_DE.UTF-8
ENV LANG de_DE.utf8

[shubaivan]

@ImreSamu but you told about localization. When I set LANG to 'sv_SE.UTF-8' I had desciption about error example in swedish... but I need COLLATE attribute column in swedish

    postgres:
        image: postgres:9.6
        ports:
            - '5432:5432'
        container_name: 'postgresql'
        working_dir: /app
        restart: always
        environment:
            POSTGRES_DB: ${POSTGRES_DB}
            POSTGRES_USER: ${POSTGRES_USER}
            POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
            LANG: 'sv_SE.UTF-8'
            LANGUAGE: 'sv_SE.UTF-8'
            LC_ALL: 'sv_SE.UTF-8'
            LC_COLLATE: 'sv_SE.UTF-8'
            LC_CTYPE: 'sv_SE.UTF-8'
        volumes:
            - ./data/postgresql:/var/lib/postgresql/data
        networks:
            - php

whoud be great if could be LANG set english but default collate set in swedish.. Or first my goal set collate for separate column in swedish example ... or set collate C for use collate like optional in query, if it's possible

SELECT * FROM category ORDER BY category_name COLLATE "sv_SE";

but still faced with error > ERROR: collation "sv_SE" for encoding "UTF8" does not exist

[ImreSamu]

@shubaivan
imho: the default image: postgres:9.6 not contains the localisations files ...

with this Dockerfile the COLLATE "sv_SE" is working.

FROM postgres:9.6
RUN localedef -i sv_SE -c -f UTF-8 -A /usr/share/locale/locale.alias sv_SE.UTF-8
ENV LANG sv_SE.utf8

$ docker run --rm -it --link pg_sv:postgres \
>      postgres96_sv \
>      sh -c 'PGPASSWORD=mysecretpw exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres '
psql (9.6.17)
Skriv "help" för hjälp.

postgres=# CREATE table hello as 
postgres-#           select 'hej världen'::text as m
postgres-# union all select 'hello world'::text as m   
postgres-# ;
SELECT 2
postgres=# SELECT * FROM hello ORDER BY m COLLATE "sv_SE";
      m      
-------------
 hej världen
 hello world
(2 rader)

postgres=# SELECT * FROM pg_collation;
  collname  | collnamespace | collowner | collencoding | collcollate | collctype  
------------+---------------+-----------+--------------+-------------+------------
 default    |            11 |        10 |           -1 |             | 
 C          |            11 |        10 |           -1 | C           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 C.UTF-8    |            11 |        10 |            6 | C.UTF-8     | C.UTF-8
 en_US      |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 en_US.utf8 |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 sv_SE      |            11 |        10 |            6 | sv_SE.utf8  | sv_SE.utf8
 sv_SE.utf8 |            11 |        10 |            6 | sv_SE.utf8  | sv_SE.utf8
 ucs_basic  |            11 |        10 |            6 | C           | C
(9 rader)

my test commands:

docker build -t postgres96_sv .
# Start Server
docker run --name pg_sv \
    -e POSTGRES_PASSWORD=mysecretpw \
    -d postgres96_sv

# start psql
docker run --rm -it --link pg_sv:postgres \
     postgres96_sv \
     sh -c 'PGPASSWORD=mysecretpw exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres '

[shubaivan]

    postgres_sv:
        build: ./postgres_sv
        ports:
            - '5433:5433'
        container_name: 'postgres_sv'
        working_dir: /app
        restart: always
        environment:
            POSTGRES_DB: ${POSTGRES_DB}
            POSTGRES_USER: ${POSTGRES_USER}
            POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
        volumes:
            - ./data/postgresql:/var/lib/postgresql/data
        networks:
            - php

postgres_sv/Dockerfile

FROM postgres:9.6
RUN localedef -i sv_SE -c -f UTF-8 -A /usr/share/locale/locale.alias sv_SE.UTF-8
ENV LANG sv_SE.utf8

docker build
docker-compose up -d

ivan@ivan-laptop:~/hosts/docker-symfony$ docker exec -it cac3f23432fb bash
root@cac3f23432fb:/app# psql --username=testuser
psql (9.6.17)
Skriv "help" för hjälp.

testuser=# SELECT * FROM pg_collation;
  collname  | collnamespace | collowner | collencoding | collcollate | collctype  
------------+---------------+-----------+--------------+-------------+------------
 default    |            11 |        10 |           -1 |             | 
 C          |            11 |        10 |           -1 | C           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 C.UTF-8    |            11 |        10 |            6 | C.UTF-8     | C.UTF-8
 en_US      |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 en_US.utf8 |            11 |        10 |            6 | en_US.utf8  | en_US.utf8
 ucs_basic  |            11 |        10 |            6 | C           | C
(7 rader)

testuser=# CREATE table hello as 
select 'hej världen'::text as m
union all select 'hello world'::text as m
;
SELECT 2
testuser=# SELECT * FROM hello ORDER BY m COLLATE "sv_SE";
ERROR:  collation "sv_SE" for encoding "UTF8" does not exist
RAD 1: SELECT * FROM hello ORDER BY m COLLATE "sv_SE";
                                      ^
testuser=# 

[ImreSamu]

@shubaivan

• "Locale support is automatically initialized when a database cluster is created " https://www.postgresql.org/docs/9.6/locale.html

imho: probably your volumes ( ./data/postgresql:/var/lib/postgresql/data ) initialized with the postgres:9.6 image ... and not the new image.
please backup you ./data/postgresql folder ; and try with empty data folder.

[wglambert]

I have a working example of setting LC_ALL and/or LC_COLLATE here #703 (comment)

Modified for Swedish

FROM postgres
RUN sed -i -e 's/# sv_SE.UTF-8 UTF-8/sv_SE.UTF-8 UTF-8/' /etc/locale.gen && \
    locale-gen
ENV LANG sv_SE.UTF-8 
ENV LANGUAGE sv_SE:sv
ENV LC_ALL sv_SE.UTF-8

postgres=# SELECT * FROM pg_collation;
  oid  |        collname        | collnamespace | collowner | collprovider | collisdeterministic | collencoding |   collcollate    |    collctype     | collversion 
-------+------------------------+---------------+-----------+--------------+---------------------+--------------+------------------+------------------+-------------
   100 | default                |            11 |        10 | d            | t                   |           -1 |                  |                  | 
   950 | C                      |            11 |        10 | c            | t                   |           -1 | C                | C                | 
   951 | POSIX                  |            11 |        10 | c            | t                   |           -1 | POSIX            | POSIX            | 
 12326 | ucs_basic              |            11 |        10 | c            | t                   |            6 | C                | C                | 
 12327 | C.UTF-8                |            11 |        10 | c            | t                   |            6 | C.UTF-8          | C.UTF-8          | 
 12328 | sv_SE.utf8             |            11 |        10 | c            | t                   |            6 | sv_SE.utf8       | sv_SE.utf8       | 
 12329 | sv_SE                  |            11 |        10 | c            | t                   |            6 | sv_SE.utf8       | sv_SE.utf8       | 
 12330 | und-x-icu              |            11 |        10 | i            | t                   |           -1 | und              | und              | 153.88
 12331 | af-x-icu               |            11 |        10 | i            | t                   |           -1 | af               | af               | 153.88.34
 12332 | af-NA-x-icu            |            11 |        10 | i            | t                   |           -1 | af-NA            | af-NA            | 153.88.34
 12333 | af-ZA-x-icu            |            11 |        10 | i            | t                   |           -1 | af-ZA            | af-ZA            | 153.88.34
 12334 | agq-x-icu              |            11 |        10 | i            | t                   |           -1 | agq              | agq              | 153.88
 12335 | agq-CM-x-icu           |            11 |        10 | i            | t                   |           -1 | agq-CM           | agq-CM           | 153.88
 12336 | ak-x-icu               |            11 |        10 | i            | t                   |           -1 | ak               | ak               | 153.88
 12337 | ak-GH-x-icu            |            11 |        10 | i            | t                   |           -1 | ak-GH            | ak-GH            | 153.88
 12338 | am-x-icu               |            11 |        10 | i            | t                   |           -1 | am               | am               | 153.88.34
 12339 | am-ET-x-icu            |            11 |        10 | i            | t                   |           -1 | am-ET            | am-ET            | 153.88.34
 12340 | ar-x-icu               |            11 |        10 | i            | t                   |           -1 | ar               | ar               | 153.88.34
 12341 | ar-001-x-icu           |            11 |        10 | i            | t                   |           -1 | ar-001           | ar-001           | 153.88.34
 12342 | ar-AE-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-AE            | ar-AE            | 153.88.34
 12343 | ar-BH-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-BH            | ar-BH            | 153.88.34
 12344 | ar-DJ-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-DJ            | ar-DJ            | 153.88.34
 12345 | ar-DZ-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-DZ            | ar-DZ            | 153.88.34
 12346 | ar-EG-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-EG            | ar-EG            | 153.88.34
 12347 | ar-EH-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-EH            | ar-EH            | 153.88.34
 12348 | ar-ER-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-ER            | ar-ER            | 153.88.34
 12349 | ar-IL-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-IL            | ar-IL            | 153.88.34
 12350 | ar-IQ-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-IQ            | ar-IQ            | 153.88.34
 12351 | ar-JO-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-JO            | ar-JO            | 153.88.34
 12352 | ar-KM-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-KM            | ar-KM            | 153.88.34
 12353 | ar-KW-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-KW            | ar-KW            | 153.88.34
 12354 | ar-LB-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-LB            | ar-LB            | 153.88.34
 12355 | ar-LY-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-LY            | ar-LY            | 153.88.34
 12356 | ar-MA-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-MA            | ar-MA            | 153.88.34
 12357 | ar-MR-x-icu            |            11 |        10 | i            | t                   |           -1 | ar-MR            | ar-MR            | 153.88.34       

[shubaivan]

After I removed and created again data/postgresql I had image with empty db, with correct collate and opportunity set others collate too.

    postgres:
        image: postgres:11-alpine
        ports:
            - '5432:5432'
        container_name: 'postgresql_alpine'
        working_dir: /app
        restart: always
        environment:
            POSTGRES_DB: ${POSTGRES_DB}
            POSTGRES_USER: ${POSTGRES_USER}
            POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
            LC_COLLATE: 'sv_SE.UTF-8'
            LC_CTYPE: 'sv_SE.UTF-8'
        volumes:
            - ./data/postgresql:/var/lib/postgresql/data
        networks:
            - php

But one thing, how to use convert like in mysql for convert data from en_US to sv_SE ? And or how to migrate to new version postgress in docker ?

[wglambert]

For upgrading to newer postgres versions Tianon has a repo with upgrade-containers to invoke pg_upgrade
https://github.com/tianon/docker-postgres-upgrade
Postgres documentation on pg_upgrade

#37 (comment)

There actually is support for migrating data files in PostgreSQL, it's called pg_upgrade. The Docker container is special in that it's difficult to use this support because it needs both the older and the newer versions installed on the same system. The Docker container isn't set up to work this way. Alternately, you can also look into replication if you have large data sets or absolutely can't have any downtime. Options are actually discussed on this official documentation page.

You could also try asking over at the Docker Community Forums, Docker Community Slack, or Stack Overflow. Since these repos aren't really a user-help forum