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. 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.
    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)
  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 and BIGSERIAL data types for more details.