How to make my postgresql database use a case insensitive collation?

A lot has changed since this question. Native support for case-insensitive collation has been added in PostgreSQL v12. This basically deprecates the citext extension, as mentioned in the other answers.

In PostgreSQL v12, one can do:

    CREATE COLLATION case_insensitive (
      provider = icu,
      locale="und-u-ks-level2",
      deterministic = false
    );

    CREATE TABLE names(
      first_name text,
      last_name text
    );

    insert into names values
      ('Anton','Egger'),
      ('Berta','egger'),
      ('Conrad','Egger');

    select * from names
      order by
        last_name collate case_insensitive,
        first_name collate case_insensitive;

See https://www.postgresql.org/docs/current/collation.html for more information.