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. For PostgreSQL 8.1 and 8.2 only: UPDATE / DELETE WHERE CURRENT OF needs oid column support. Starting with PostgreSQL version 8.1, user tables do not get the oid column by default. You must set the default_with_oid configuration parameter to "on" in order to get oid columns created.
    2. Starting with PostgreSQL 8.3 and the dbmpgs83x, WHERE CURRENT OF is supported by the server, and no longer requires setting the default_with_oid parameter. However, setting this parameter is still necessary if you plan to use ROWID keywords in SQL, as these can be converted to oid keywords.
    3. PostgreSQL 9.1 and higher have by default the standard_conforming_strings parameter set to on. Thus the ODI drivers >= 9.1 do no longer escape the backslash characters in string literals with a second backslash.
    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

  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. Starting with PostgreSQL version 9.0, the plpgsql language is available by default. Prior to version 9.0, you must create the language in your database with the following command:

    $ createlang -h hostname plpgsql dbname

  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 for more details.
  8. If you plan to use the SERIAL emulation, you must prepare the database. See SERIAL data types for more details.