News for hackers. Stuff that matters.
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by aftabh on Wednesday May 25, 2016 @12:00AM (#11767576)
>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

Re:How to move from Amazon RDS to a dedicated PostgreSQL server by dboreham on Wednesday May 25, 2016 @12:55AM (#11767737)
Using the distro-supplied packages for PG (or any database server) is typically not the best choice for a production deployment.
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by peteretep on Wednesday May 25, 2016 @01:07AM (#11767766)

    > Using the distro-supplied packages for PG
    > (or any database server)
Or almost any of your application environment, really.
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by elktea on Wednesday May 25, 2016 @02:59AM (#11768102)
Distro supplied packages are a handy way of receiving timely security fixes without keeping too close an eye on recent CVEs + application changelogs.
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by pritambaral on Wednesday May 25, 2016 @06:49AM (#11768863)
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.
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by Roritharr on Wednesday May 25, 2016 @04:03AM (#11768328)
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?
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by dboreham on Wednesday May 25, 2016 @08:44AM (#11769352)
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/
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by mrmondo on Wednesday May 25, 2016 @04:21AM (#11768393)
We use CentOS but PostgreSQL's official packages from their repo, just like we did when we were Debian based. http://yum.postgresql.org/
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by mrmondo on Wednesday May 25, 2016 @12:49AM (#11767711)
Ubuntu has lots of poor defaults and this is a good example of one.
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by voltagex_ on Wednesday May 25, 2016 @02:30AM (#11768008)
Wouldn't this be worth raising a bug report for?
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by mrmondo on Wednesday May 25, 2016 @04:19AM (#11768391)
Probably, uphill battle though IMO, it's not like Ubuntu has a good reputation of sane defaults or quality packing or timely security updates.
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by merb on Wednesday May 25, 2016 @05:20AM (#11768587)
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)
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by aftabh on Wednesday May 25, 2016 @08:05AM (#11769135)
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.
Re:How to move from Amazon RDS to a dedicated PostgreSQL server by merb on Wednesday May 25, 2016 @10:10AM (#11769854)
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.

Re:How to move from Amazon RDS to a dedicated PostgreSQL server by approachingtraj on Wednesday May 25, 2016 @06:31AM (#11768806)
> 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.

Re:How to move from Amazon RDS to a dedicated PostgreSQL server by aftabh on Wednesday May 25, 2016 @07:05AM (#11768912)
> 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....

Re:How to move from Amazon RDS to a dedicated PostgreSQL server by pjungwir on Wednesday May 25, 2016 @09:49AM (#11769718)
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