Troubleshooting Puppet PostgreSQL Locale Mismatches

by Bob Plankers on September 25, 2013

in System Administration,Virtualization

I’ve been doing some work lately with VMware Hyperic Server and Puppet, and I’ve been having issues where the Puppet Labs PostgreSQL module refuses to create a PostgreSQL database for me. I try to call it with:

class { 'postgresql':
  charset => 'UTF8',
}->
class { 'postgresql::server':
  config_hash => {
    'listen_addresses' => '127.0.0.1',
    'manage_redhat_firewall' => true,
    'postgres_password' => 'goatsaresupercool',
  },
  require => Mount['/var/lib/pgsql'],
}

postgresql::db { 'HQ':
  user => 'hyperic',
  password => 'sheeparecooltoo',
  require => Class['postgresql::server'],
}

…and it throws this error into Puppet’s output:

Error: /usr/bin/initdb --encoding 'UTF8' --pgdata '/var/lib/pgsql/data' returned 1 instead of one of [0]

“No problem,” I said. Since Puppet is kind enough to give me the command it’s trying I switched to the postgres user and tried to run the command myself:

$ sudo su – postgres
[sudo] password for username: *****
$ -bash-4.1$ /usr/bin/initdb --encoding 'UTF8' --pgdata '/var/lib/pgsql/data'
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.
initdb: encoding mismatch
The encoding you selected (UTF8) and the encoding that the
selected locale uses (LATIN1) do not match.  This would lead to
misbehavior in various character string processing functions.
Rerun initdb and either do not specify an encoding explicitly,
or choose a matching combination.

Well that’s interesting. The default locale is en_US, which should be correct, but it still complains that LATIN1 encoding isn’t cool.

Turns out what you want is en_US.UTF8, which encodes with UTF8, and not en_US, which is a LATIN1 encoding:

class { 'postgresql':
  charset => 'UTF8',
  locale => 'en_US.UTF8',
}->
class { 'postgresql::server':
  config_hash => {
    'listen_addresses' => '127.0.0.1',
    'manage_redhat_firewall' => true,
    'postgres_password' => 'goatsaresupercool',
  },
  require => Mount['/var/lib/pgsql'],
}

postgresql::db { 'HQ':
  user => 'hyperic',
  password => 'sheeparecooltoo',
  require => Class['postgresql::server'],
  locale => 'en_US.UTF8',
}

Up until about 30 minutes ago I had no idea there were even UTF8 versions of the locales. Now you do, too.

This concludes this episode of “encodings are arcane and not well documented on the Internet except deep in comments so I hope this helps others spend two minutes solving the problem and not twenty.”

Comments on this entry are closed.

Previous post:

Next post: