Cannot set initdb_locale on Ubuntu/Debian

[aried3r]

Cookbook version

7.1.2

Chef-client version

14.8.12

Platform Details

Ubuntu 16.04 LTS

Scenario:

Trying to set initdb_locale for repo-installation of PostgreSQL.

Steps to Reproduce:

postgresql_server_install 'Install server from Repo' do
  action     :install
  version    POSTGRESQL_VERSION
  password   'MyP4$$w0rd'
  setup_repo true
  initdb_locale 'de_DE.utf-8'
end

Expected Result:

The template1 DB that is being initialized by initdb should have a de_DE.utf-8 locale but is using the system locale instead.

The initdb_locale value is only used for some linux distros as well:

postgresql/resources/server_install.rb

Lines 48 to 53 in a66d05c

	execute 'init_db' do
	command rhel_init_db_command(new_resource)
	user new_resource.user
	not_if { initialized? }
	only_if { platform_family?('rhel', 'fedora', 'amazon') }
	end

postgresql/libraries/helpers.rb

Lines 206 to 217 in a66d05c

	# determine the appropriate DB init command to run based on RHEL/Fedora/Amazon release
	# initdb defaults to the execution environment.
	# https://www.postgresql.org/docs/9.5/static/locale.html
	def rhel_init_db_command(new_resource)
	cmd = if platform_family?('amazon')
	'/usr/bin/initdb'
	else
	"/usr/pgsql-#{new_resource.version}/bin/initdb"
	end
	cmd << " --locale '#{new_resource.initdb_locale}'" if new_resource.initdb_locale
	cmd << " -D '#{data_dir(new_resource.version)}'"
	end

Actual Result:

On Debian/Ubuntu using the repo versions the initdb locale is set automatically to the system locale (if I understood the links below correctly), in this case en_US.utf8 because that's what we use on our host system. We could try to overwrite the LC_* environment variables to manipulate this, but instead what we're currently doing at work is using an explicit locale when creating databases, using the template0 database instead of template1.

For a given locale category, say the collation, the following environment variables are consulted in this order until one is found to be set: LC_ALL, LC_COLLATE (or the variable corresponding to the respective category), LANG. ^source

Debian PostgreSQL installation automatically calls the initdb i.e. it initializes the cluster with default encoding and locale. Encoding can be changed later but the locale cannot. ^source

The above quote from the debian wiki might not exactly describe what's happening here, since setup_repo used the PGPD and not the official debian packages, but it's what I am experiencing.

[damacus]

I'm not sure whether this is actually a bug or enhancement. So I'm putting both.

I think, like you say. We can set the environment when we create the database to whatever locale we set

[PowerSchill]

I ran head on into this problem with the new docker images. In an attempt to fix it I've updated rhel_init_db_command to include debian but get the following results. I can delete the files in that directory and it runs successfully but I don't know the appropriate action to take.

---- Begin output of /usr/lib/postgresql/9.6/bin/initdb --locale 'en_US.utf8' -D '/var/lib/postgresql/9.6/main' ----
STDOUT: The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
STDERR: initdb: directory "/var/lib/postgresql/9.6/main" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/postgresql/9.6/main" or run initdb
with an argument other than "/var/lib/postgresql/9.6/main".
---- End output of /usr/lib/postgresql/9.6/bin/initdb --locale 'en_US.utf8' -D '/var/lib/postgresql/9.6/main' ----
Ran /usr/lib/postgresql/9.6/bin/initdb --locale 'en_US.utf8' -D '/var/lib/postgresql/9.6/main' returned 1

[JanStevens]

For ubuntu 18.04 I ended up with the same issue. For me it worked by setting the locale so postgres picks the right one when initialising the cluster. I do see we are passing in --locale flags but I think we are missing --encoding flag. I have the following work around

locale 'set locale' do
  lang 'en_US.UTF-8'
  lc_env(
    'LC_COLLATE' => 'en_US.UTF-8',
    'LC_CTYPE' => 'en_US.UTF-8',
    'LC_MESSAGES' => 'en_US.UTF-8',
    'LC_MONETARY' => 'en_US.UTF-8',
    'LC_NUMERIC' => 'en_US.UTF-8',
    'LC_TIME' => 'en_US.UTF-8'
  )
end

# This is needed since `locale` resource ensure it's set for next boot
bash 'configure locale' do
  code <<~BASH
    localectl set-locale LANG=en_US.UTF-8
    update-locale LANG=en_US.UTF-8
  BASH
  only_if { ::File.readlines('/etc/default/locale').grep(/en_US\.UTF-8/).empty? }
end

postgresql_server_install 'package' do
  version node['postgresql']['version']
  password nil
  initdb_locale 'en_US.UTF-8'
  action [:install, :create]
end

[PowerSchill]

So that works but isn't that just changing the default locale and then installing the server? That is not something we should do in a cookbook right?

[JanStevens]

Yea that isn't a recommended way of dealing with it in a cookbook. What we should do is add the -E flag on initdb next to locale to set the encoding.

After some digging, it seems that on ubuntu and debian by default a cluster is created. They add wrapper tools pg_ctlcluster and friends so you can have multiple versions of postgres running on one host.

What we can do (and it's a bit more involved) is first install postgres-common and then edit the file /etc/postgresql-common/createcluster.conf there you have a bunch of options including

# Options to pass to initdb.
#initdb_options = ''

We can populate that with the passed locale / encoding and then install postgres package. This will then use postgres-common and create the cluster. Another idea would be to disable automatic cluster creation by editing the same file and setting create_main_cluster = false.