Our French users' accents vanished in a UTF-8 column

We run a small SaaS with a mostly French user base. Postgres 14.9 on a managed instance in Paris, Django 4.2 in front, connection through PgBouncer in session mode. Last spring a support ticket came in: a customer in Lyon said their company name showed as “Société Générale” on invoices but looked fine in the settings page. Then three more tickets, all with accents or œ. About 40k rows in the accounts table, maybe 2,300 with non-ASCII characters, so it wasn’t obvious at first.

The settings page was reading fine, invoices were not. The invoice renderer pulled from a nightly export job. That job ran on a worker that connected with psql and dumped to CSV. The worker’s environment had no PGCLIENTENCODING, and the base image defaulted to LANG=C. So psql asked the server for SQL_ASCII-ish behavior and Postgres happily handed back bytes it thought were plain. The CSV then got read by Python as UTF-8 and the relayered garbage was already baked in.

What I did, in order. First, confirm the damage was in the pipeline, not the column:

SELECT id, name, encode(convert_to(name, 'UTF8'), 'hex')
FROM accounts
WHERE name ~ '[^\x00-\x7F]'
LIMIT 5;

Those hex values were valid UTF-8, so storage was fine. Then I checked the worker:

psql -c "SHOW client_encoding;"
# SQL_ASCII

The fix was two lines in the worker’s env and one in the dump command:

export LANG=C.UTF-8
export PGCLIENTENCODING=UTF8

psql --no-align --field-separator=',' -c "COPY (SELECT ...) TO STDOUT WITH CSV HEADER" > out.csv

I switched from psql’s default output to COPY ... TO STDOUT because COPY respects client_encoding explicitly and fails loudly if it can’t convert. Then I re-ran the affected month and diffed against the stored rows; 2,300-ish rows matched, no data loss, just presentation.

Why this shape: the column was never wrong. client_encoding is a per-session negotiation, and LANG=C on a slim container makes libpq fall back to SQL_ASCII. The server doesn’t convert, it just sends bytes. Any downstream reader assuming UTF-8 gets mojibake. The cost of fixing it in the env is that every future worker image must keep those two variables, which is easy to forget when someone copies a Dockerfile. The cost of fixing it in application code is worse: you paper over a transport bug and the next tool inherits it.

Where this breaks: if the column really did contain Latin-1 bytes, convert_to(name, 'UTF8') throws, and you need convert_from(convert_to(name,'LATIN1'),'UTF8') after checking. Also, COPY TO STDOUT on a table with a bytea column will surprise you. And PgBouncer in transaction mode can reset session settings between statements, so set client_encoding per connection, not once at startup. I learned that the hard way on an earlier project.

One thing I still don’t know: whether our managed instance’s default client_encoding changed after a minor upgrade, or whether it was always SQL_ASCII and we only noticed because a new customer used œ in their legal name. If you’ve audited this on a managed French-region Postgres, what did SHOW client_encoding return from a bare psql session?