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:
    $ initdb -D $PGDIR/data
  3. Set configuration parameters in postgresql.conf:
    1. Make sure that the DateStyle format is supported by the PostgreSQL ODI driver.
      The supported DateStyle ouput formats are ISO, SQL and German (Postgres is not supported). The supported specification for year/month/day ordering are DMY, MDY, or YMD. Use the "SHOW" command to check the current DateStyle format:
      show DateStyle;
      Consider using the default ISO DateStyle.
    2. When using a PostgreSQL version prior to version 12: If ROWIDs are used in the applications, these are converted to PostgreSQL OIDs. However, this feature is disabled by default. Starting with PostgreSQL version 12, OIDs are not longer supported.
      In order to get OIDs in PostgreSQL versions prior to version 12, set the default_with_oid server parameter to on.
  4. Start the PostgreSQL process to listen to database client connections:
    $ pg_ctl -D $PGDIR/data -l $PGDIR/logfile start -o "-p 5435"
    Important: If you want to connect through TCP (for example from a Windows™ PostgreSQL client), you must start PostgreSQL with the -i option and setup the "pg_hba.conf" file for security (trusted hosts and users).
  5. Create a PostgreSQL database with the createdb utility, by specifying the character set of the database:
    $ createdb dbname --host=hostname --port=tcp-port --template=template-name \
        --encoding=encoding --locale=locale
    For example, to create a UTF-8 database on Linux:
    $ createdb stores --port=5436 --template=template0 --encoding=utf8 --locale=en_US.utf8
  6. If you plan to use SERIAL emulation, you need the plpgsql procedure language, because the database interface uses this language to create serial triggers.
    Execute the following SQL to check that the plpgsql language is available in your PostgreSQL server:
    dbname=> SELECT lanowner, lanname FROM pg_language WHERE lanname = 'plpgsql';
     lanowner | lanname 
    ----------+---------
           10 | plpgsql
    (1 row)
  7. 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
  8. Create the application tables.
    Convert Informix® data types to PostgreSQL data types. See Data type conversion table: Informix to PostgreSQL for more details.
  9. If you plan to use the SERIAL emulation, you must prepare the database.
    See SERIAL and BIGSERIAL data types for more details.