Install PostgreSQL and create a database - database configuration/design tasks

If you are tasked with installing and configuring the database, here is a list of steps to be taken:

  1. Compile and install the PostgreSQL Server on your computer. PostgreSQL is a free database, you can download the sources from www.postgresql.org.
  2. Read PostgreSQL installation notes for details about the "data" directory creation with the initdb utility.
  3. Set configuration parameters in postgresql.conf:
    1. PostgreSQL 9.1 and higher have by default the standard_conforming_strings parameter set to on.
      The ODI drivers for PostgreSQL 9.1 and + do no longer escape the backslash characters in string literals with a second backslash.
    2. If ROWIDs are used in the applications, these are converted to PostgreSQL OIDs. However, this feature is disabled by default.
      In order to get OIDs in PostgreSQL, set the default_with_oid server parameter to on.
    Start a postmaster process to listen to database client connections.
    Important: If you want to connect through TCP (for example from a Windows™ PostgreSQL client), you must start postmaster with the -i option and setup the "pg_hba.conf" file for security (trusted hosts and users).
  4. Create a PostgreSQL database with the createdb utility, by specifying the character set of the database. 
    $ createdb -h hostname dbname --encoding encoding --locale locale
  5. If you plan to use SERIAL emulation, you need the plpgsql procedure language, because the database interface uses this language to create serial triggers.

    Make sure that the plpgsql language is supported by the PostgreSQL version you are using. Starting with PostgreSQL 9.0, this stored procedure language is available by default.

  6. Connect to the database as the administrator user and create a database user dedicated to your application, the application administrator:
    dbname=# CREATE USER appadmin PASSWORD 'password';
CREATE USER
dbname=# GRANT ALL PRIVILEGES ON DATABASE dbname TO appadmin;
GRANT
dbname=# \q
  7. Create the application tables.
    Convert Informix® data types to PostgreSQL data types. See Data type conversion table: Informix to PostgreSQL for more details.
  8. If you plan to use the SERIAL emulation, you must prepare the database.
    See SERIAL data types for more details.