PostgreSQL

PostgreSQL is a powerful, open source object-relational database system with a strong reputation for reliability, data integrity, and correctness.

Add user
Enter postgres password.

psql (9.1.1) Type "help" for help.

postgres=# CREATE USER username WITH PASSWORD 'password'; CREATE ROLE postgres=# CREATE DATABASE databasename; CREATE DATABASE postgres=# GRANT ALL PRIVILEGES ON DATABASE databasename to username; GRANT Change "username" "password" and "databasename".

Changing the default encoding of new databases to UTF-8
When creating a new database (e.g. with ) PostgreSQL actually copies a template database. There are two predefined templates: template0 is vanilla, while template1 is meant as an on-site template changeable by the administrator and is used by default. In order to change the default encoding of new databases, one of the options is to change on-site template1. To do this, log into PostgresSQL shell and execute the following:

1. First we need to drop template1. As templates cannot be dropped, we first need to change it to an ordinary database: UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1'; 2. After that, it is possible to drop it: DROP DATABASE template1; 3. The next step is to create a new database from template0 with a new default encoding: CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE'; 4. Now we need to change template1 back to the template: UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1'; 5. (OPTIONAL) If you do not want anyone connecting to this template, set datallowconn to FALSE: UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';

Now you can create a new database by running from regular shell:

If you log in back to psql and check the databases, you should see the proper encoding of your new database: \l returns List of databases Name   |  Owner   | Encoding  | Collation | Ctype |   Access privileges ---+--+---+---+---+-- mydb     | postgres | UTF8      | C         | C     | postgres | postgres | SQL_ASCII | C         | C     | template0 | postgres | SQL_ASCII | C        | C     | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8     | C         | C     |

Upgrading PostgreSQL 9.0 to 9.1
This will handle PostgreSQL Upgrade from 9.0 to 9.1 After emerging 9.1 copy configurationfiles from your old version to the new one:

Create initdb with user 'postgres'

Check starting postgres-9.1 if everything works, shut down postgres-9.1. There must be no postgres instance running while migration of databases! Create symlinks for configfiles. PostgreSQL's pg_upgrade needs configs in the Postgres data directory:

Upgrade process:

Check startup of the new postgres database and check daemons depending on postgres like postfix or openfire. If everything is working, unmerge old postgres-9.0 clean data directory: