Installing Postgres Plus Advanced Server 9.5.x

For information about what version release to use for Postgres Plus Advanced Server, see Software requirements Red Hat Enterprise Linux.

This section covers only new installations on the database server.

Installing Postgres Plus Advanced Server 9.5.x

  1. Log on to the database server as root.
  2. Create the group enterprisedb. To create, run this command:
    # groupadd enterprisedb
  3. Create the user enterprisedb. To create, run this command:
    # useradd -d /home/enterprisedb -g enterprisedb -m -s /bin/bash enterprisedb
  4. Change password for user enterprisedb. To change, run this command:
    # passwd enterprisedb
  5. In the home directory for the user enterprisedb, modify the .bash_profile.

    Replace the content with this text:

    # .bash_profile
    
    # Get the aliases and functions
    if [ -f ~/.bashrc ]; then
            . ~/.bashrc
    fi
    
    # User specific environment and startup programs
    
    PATH=$PATH:$HOME/bin
    export PATH
    export PG_VERSION=ppas
    
    case $PG_VERSION in
          ppas)
                    export PGHOME=/opt/PostgresPlus/9.5AS
                    export PGDATA=/u01/app/pgdata
                    export PGDATABASE=edb
                    export PGUSER=enterprisedb
                    export PGPORT=5444
                    export PGLOCALEDIR=$PGHOME/share/locale
                    export MANPATH=$MANPATH:$PGHOME/share/man
            ;;
          *)
            ;;
    esac
    
    export PATH=$PGHOME/bin:$PATH:.
    
    alias dta='cd $PGDATA'
    alias log='cd $PGDATA/pg_log'
    alias ppas='cd $PGHOME'
    alias stoppg='$PGHOME/bin/pg_ctl -D $PGDATA -mf stop'
    alias startpg='$PGHOME/bin/pg_ctl -D $PGDATA start'
  6. Create directory structure for tablespaces and others:
    1. To create a directory structure, run this command:
      # mkdir -pv /u01/app/pgdata/ /u02/tmvxsd/ /u03/tmvxsi/ /u04/tmvxrunt/ /u04/tmvxtemp/ /u05/tmvxarch/
    2. To change owner of these directories, run this command:
      # chown enterprisedb:enterprisedb /u01/app/pgdata/ /u02/tmvxsd/ /u03/tmvxsi/ /u04/tmvxrunt/ /u04/tmvxtemp/ /u05/tmvxarch/
    3. To change permissions of these directories, run this command:
      # chmod 775 /u01/app/pgdata/ /u02/tmvxsd/ /u03/tmvxsi/ /u04/tmvxrunt/ /u04/tmvxtemp/ /u05/tmvxarch/
  7. Install Postgres Plus Advanced Server (PPAS).

    To install, follow the installation manual for PPAS and our the guidelines here. You can download the PPAS manual from http://www.enterprisedb.com/docs/en/9.5/instguide/toc.html.

    During installation, use these guidelines:

    Installation Directory

    /opt/PostgresPlus

    Data Directory

    /u01/app/pgdata

    Write-Ahead Log (WAL) Directory

    /u01/app/pgdata/pg_xlog

    Configuration Mode

    [1] Oracle Compatible

    Locale

    Default locale

    Dynatune Dynamic Tuning Server Utilization

    [2] General Purpose or [3] Dedicated

    Dynatune Dynamic Tuning Workload Profile

    [1] Transaction Processing or [2] General Purpose

  8. Disable Linux Firewall. To disable, run these commands:
    # iptables-save
    # service firewalld stop
  9. Turn off Linux Firewall on boot. To turn off, run this command:
    # systemctl disable firewalld
  10. The cluster database is now installed and started. For a better understanding of the following steps refer to Cluster database file layout – PGDATA(/u01/app/pgdata).
  11. Log on as enterprisedb and modify some of the variables in the postgres configuration file ($PGDATA/postgresql.conf):
    Parameter Recommended value Comment
    stats_temp_directory 'pg_stat_tmp' The directory for storing temporary statistics data should be linked to RAM disk.
    fsync on Turns forced synchronization on or off.
    effective_cache_size xxGB ~50-60% of available memory
    shared_buffers xxGB ~20-25% of available memory
    max_connections 1000 The value depends on the number of subsystems and active jobs in M3 BE.
    max_wal_size 10GB Sets the WAL size that triggers a checkpoint
    min_wal_size 1GB Sets the minimum size to shrink the WAL to
    autovacuum on Enable autovacuum subprocess 'on'
    autovacuum_max_workers 10 max number of autovacuum subprocesses
    log_min_duration_statement 20

    ms, sql with >= duration will be logged

    For more information and an example of setting up the activity log for Postgres, see Example of Setting up the Activity Log for Postgres.

    maintenance_work_mem 1GB  
    work_mem 4MB  
    temp_tablespaces 'tmvxtemp'  
    commit_delay 50% of the time reported by pg_test_fsync

    For more information, see the postgres documentation on WAL configuration, corresponding to this link:http://www.enterprisedb.com/docs/en/9.5/pg/wal-configuration.html

  12. Change the postgres configuration.
    Note: 

    By default, TCP/IP connection is disabled. This is to prevent users from other computers to access postgres.

    1. To allow user connection from other computers, edit the $PGDATA/postgresql.conf file. Look for these lines:
      [...]
      #listen_addresses = 'localhost'
      [...]
      #port = 5444
      [...]
    2. Uncomment both lines and set the IP address of your postgres server, or set "*" to listen from all clients, for example:
      listen_addresses = '*'
      port = 5444 
  13. Change the security settings for PPAS. To change, edit the $PGDATA/pg_hba.conf file.

    Change from:

    # IPv4 local connections:
    host 	all 		all 		127.0.0.1/32 		ident
    # IPv6 local connections:
    host 	all 		all 		::1/128 		ident
    

    Change to:

    # IPv4 local connections:
    host 	all 		all 		0.0.0.0/0 		md5
    # IPv6 local connections:
    host 	all 		all 		::/0 			md5
    

    WHERE:

    0.0.0.0/0 represents all IPv4 addresses, and

    ::/0 represents all IPv6 addresses

    For more information, see http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html.

  14. Restart the PPAS to apply new configuration settings. To restart, run this command:
    # systemctl restart ppas-9.5.service
  15. Check the status of the PPAS server. To check, run this command:
    # systemctl status ppas-9.5.service

    For more information, see Log directories in Postgres.