PostgreSQL

PostgreSQL is Article description::a free and open source relational database management system (RDBMS). It supports such things as transactions, schemata and foreign keys, and is often touted to more strictly adhere to the SQL standards and to be more secure, by default, than any other database, commercial or otherwise.

Visit the About page on postgresql.org for more information.

Installation, Upgrading, and Migration

See Gentoo PostgreSQL Quick Start Guide for details about installing, upgrading, or migrating PostgreSQL.

Add User

psql (9.1.1)
Type "help" for help.

postgres=# CREATE ROLE username WITH LOGIN;
CREATE ROLE
postgres=# \password username
Enter new password: 
Enter it again:
postgres=# CREATE DATABASE testdb WITH OWNER username; -- username has all privileges on testdb
CREATE DATABASE
postgres=# GRANT CONNECT ON DATABASE otherdb TO username; -- username can now connect to otherdb
GRANT
postgres=# \c otherdb
You are now connected to database "otherdb" as user "postgres".
otherdb=# GRANT SELECT ON test TO username; -- username can now query (SELECT statements) the test table on otherdb.
GRANT

See Chapter 20. Database Roles of the official PostgreSQL documentation for more on role management.

See the PostgreSQL documentation for more on GRANT and REVOKE,

Changing the Default Encoding of New Databases

When creating a new database (e.g. with createdb mydb) 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 PostgreSQL shell (psql) 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. (Gotcha: In PostgreSQL, Unicode is synonymous with UTF-8.)

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:

psql (9.1.1)
Type "help" for help.

postgres=# \l
                              List of databases
  Name    |  Owner   | Encoding  | Collation | Ctype |   Access privileges
----------+----------+-----------+-----------+-------+----------------------
testdb    | 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     |