7 ms·
>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 da
by aftabh 10y ago
>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 https://www.postgresql.org/docs/9.5/static/multibyte.html
- mrmondo 10y agoUbuntu has lots of poor defaults and this is a good example of one.
- dboreham 10y agoUsing the distro-supplied packages for PG (or any database server) is typically not the best choice for a production deployment.
- peteretep 10y ago> Using the distro-supplied packages for PG > (or any database server) Or almost any of your application environment, really.
- elktea 10y agoDistro supplied packages are a handy way of receiving timely security fixes without keeping too close an eye on recent CVEs + application changelogs.
- pritambaral 10y agoFor 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.
- Roritharr 10y agoI 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?
- mrmondo 10y agoWe use CentOS but PostgreSQL's official packages from their repo, just like we did when we were Debian based. http://yum.postgresql.org/ http://yum.postgresql.org/
- dboreham 10y agoWe 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/ https://www.postgresql.org/download/linux/ubuntu/
- merb 10y agoactually 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)
- aftabh 10y agoIt'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.
- merb 10y agoNo 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.
- approachingtraj 10y ago> 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 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.
- aftabh 10y ago> 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 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.html https://www.postgresql.org/docs/9.5/static/reference-server....
- pjungwir 10y agoSince 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