Hacker News Reader

Comment by aftabh | Hacker News Reader

>To my surprise, PostgreSQL v9.5 on Ubuntu 14.04 does not default to unicode schema.

PostgreSQL does (if unicode character set was specified when PostgreSQL database cluster is initialized first time); it's Ubuntu OS which creates PostgreSQL database cluster with ASCII character set (encoding) after PostgreSQL's installation.

> Here is the snippet that I had to use: ...

Instead of resorting to those hacks, follow the PostgreSQL documentation[1] to do it the right way. The simplest way is to initialize your PostgreSQL database cluster as:

initdb --encoding=UTF8 --pgdata=<database-cluster-dir>

If you want to use an existing database cluster, PostgreSQL documentation shows how you can do it.

[1] - https://www.postgresql.org/docs/9.5/static/multibyte.html

View on HN

Using the distro-supplied packages for PG (or any database server) is typically not the best choice for a production deployment.

    > Using the distro-supplied packages for PG
    > (or any database server)

Or almost any of your application environment, really.

Distro supplied packages are a handy way of receiving timely security fixes without keeping too close an eye on recent CVEs + application changelogs.

For regular packages, sure. But for highly-active, well-maintained codebases like postgres – who package their software for your OS, upstream is going to be quicker with security fixes than distro.

I would like to hear more about that. Our Server Team swears on using CentOS Packages with Backports for everything for security purposes. This slows us a bit down, but so far we haven't had any security or audit related issues. How do you deal with CVEs and these staying audited and secure?

We use CentOS but PostgreSQL's official packages from their repo, just like we did when we were Debian based. http://yum.postgresql.org/

We use the official PG packages from their repo on CentOS. It appears there is an apt repository for Ubuntu too: https://www.postgresql.org/download/linux/ubuntu/

Ubuntu has lots of poor defaults and this is a good example of one.

Wouldn't this be worth raising a bug report for?

Probably, uphill battle though IMO, it's not like Ubuntu has a good reputation of sane defaults or quality packing or timely security updates.

actually that is not true for ubuntu 16.04 there it will use the locale setting:

newly setup:

                                      List of databases
       Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
    -----------+----------+----------+-------------+-------------+-----------------------
     postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
     template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
     template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
               |          |          |             |             | postgres=CTc/postgres
    (3 rows)

It's good news that they've changed the default PostgreSQL character set (encoding) from ASCII to UTF8 in Ubuntu 16.04. It'll definitely save a good amount of time of new PostgreSQL users who need UTF8 encoding as default.

No it's not the "default" the default is 'use locale settings'.

So LANG=de_DE.UTF-8 will load a de_DE.UTF-8 Database, while my LANG previously was en_US.UTF-8, i can change LANG to something different and it will create the cluster to the value I've set my LANG to.

> Instead of resorting to those hacks, follow the PostgreSQL documentation[1] to do it the right way. The simplest way is to initialize your PostgreSQL database cluster as:

This is actually poor advice. The right way on Ubuntu is to use the packages provided by PostgreSQL Development Group (https://wiki.postgresql.org/wiki/Apt), and in that setup you use pg_createcluster instead of initdb.

The snippet in the original article was so hacky because it fixed an existing cluster instead of destroying it and creating a new one in UTF-8.

> This is actually poor advice. The right way on Ubuntu is to use the packages provided by PostgreSQL Development Group (https://wiki.postgresql.org/wiki/Apt), and in that setup you use pg_createcluster instead of initdb.

initdb is part of standard PostgreSQL utilities/tools[1] for initializing your PostgreSQL server that's why I suggested using it. On the other hand, I don't see any reference to pg_createcluster command in the official PostgreSQL documentation. So I don't know why you think using a non-standard PostgreSQL tool (in place of a standard one i.e. initdb) is the right way of initializing a PostgreSQL database cluster.

[1] https://www.postgresql.org/docs/9.5/static/reference-server....

Since we're talking about Ubuntu, I would use the Ubuntu tools. They make it very easy to maintain several Postgres clusters side-by-side, e.g. /var/lib/postgresql/9.4/main vs /var/lib/postgresql/9.5/main. You can also control which binaries you use with the PGCLUSTER variable, like this:

    PGCLUSTER=9.4/main pg_dump foo
    PGCLUSTER=9.5/main pg_dump foo

Reply on news.ycombinator.com