PostgreSQL

PostgreSQL is 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.

PostgreSQL Connection
To make successful connection to PostgreSQL database in Gentoo you can use this PostgreSQL Connection guide or follow these steps:


 * 1.Download PostgreSQL ODBC driver for Linux and install it.
 * 2.After the driver is installed, it can be configured. For this, in the Control Panel open Administrative Tools and find the Data Sources (ODBC) tool
 * 3.Now you have to add the Devart ODBC Driver for PostgreSQL to the list of System Data Sources or User Data Sources or File Data Sources using the ODBC Data Source Administrator dialog.
 * 4.For this, click the Add button - the Create New Data Source dialog will appear with a list of available drivers. Select Devart ODBC Driver for PostgreSQL and click Finish. Then you will be suggested to configure the driver.
 * 5.In the appeared connection dialog, you can set the connection options. Two connection modes are available: using client library and Direct. Direct Mode is used for direct connection to a data source via TCP\IP avoiding client software. Some of the connection options are available in Direct Mode and not required when using client software.
 * 6.Then you can click OK and finish configuration or switch to the Advanced settings tab to specify more detailed Connection String.

PostgreSQL ODBC Connection String

''Login Prompt=False;Data Source=DBMSSQL;Initial Catalog=master;User ID=sa; ''

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 [http://www.postgresql.org/docs/current/interactive/user-manag.html 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 ) 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 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     |