14.1. Installing PostgreSQL

This section introduces PostgreSQL installation procedure. Please refer to PostgreSQL for precise explanation.

Note

The examples provided in this document assume the following:

  • You are installing PostgreSQL version 11.

  • Your organization's internal network block is 192.168.0.0/24.

  • The IP address of the machine running PostgreSQL is 192.168.0.1.

  • The IP address of the machine running PostERP is 192.168.0.2.

Substitute the above constants for the examples provided hereafter according to your real environment.

Important

Note:

  • Set BIOS time basing on UTC (Coordinated Universal Time, or GMT0) time zone and specify the local time zone for the operating system running PostgreSQL.

    Take Taipei, falling in time zone UTC+8, as an example. Suppose Taipei's local time is 14:00 now, then set the BIOS time to 06:00.

    Windows users will then set the time zone to Taipei. Unix users will set operating system's localtime to Taipei.

  • PostERP demands PostgreSQL be UTF8 characterset encoding capable. If you manually make PostgreSQL from source, make sure add parameter --enable-multibyte=UNICODE during configure.

14.1.1. Installing PostgreSQL In Windows Host

Download the newest version of PostgreSQL for Windows from PostgreSQL official site and install PostgreSQL.

Tip

Consult Chapter 21 if PostgreSQL can not run.

Tip

on-premises PostERP runs not only on Windows operating system but also on WINE version 2.0 running on Linux. To run server program, simply place files server.exe and libpq.dll under the same sub-directory in Linux and type the following command wine server.exe & in X window to run application server:

Likewise, to run client program, type command wine tc.exe & instead.

Tip

If you are a Windows user and drop PostgreSQL cluster space by deleting C:\Program Files\PostgreSQL\11\data and then recreating the cluster space using command C:\Program Files\PostgreSQL\11\bin\initdb.exe, and can no longer start PostgreSQL service since then, and Windows event log says could not create lock file "postmaster.pid": Permission denied, this suggestion excerpted from http://forumserver.twoplustwo.com/114/pokertracker/cant-connect-db-422617/ might help:

Click Start, and then click My Computer. On the Tools menu, click Folder Options and click the View tab. In the Advanced Settings section, click to clear the Use simple file sharing (Recommended) check box. Click OK.
	
Then click Start --> Run type C:\Program Files\ and hit enter. Right click on PostgreSQL, click Properties. Go to the Security tab, click Add up top, then click Advanced, and Find Now. Find the postgres user at the bottom and click 'ok' twice. Then with the postgres user highlighted up top, check the Full Access checkbox on the bottom and click OK.
	
Then you should be able to start the PostgreSQL service properly.
				

14.1.2. Installing PostgreSQL using Debian or Ubuntu

Issue the following command as root login to install PostgreSQL:

apt-get install postgresql-11
apt-get install postgresql-client-11
			

14.1.3. Configuring PostgreSQL

  1. Type the following command under psql prompt to make sure that UTF8 is the default Encoding of database cluster:

    \l
    					

    Verify that Database Cluster Space is intialized to use UTF8 as default characterset encoding:

    Figure 14-1. Database Cluster Uses UTF8 Characterset encoding

    Previous versions of PostgreSQL installation packages for Windows and Debian wheezy probably create database cluster using SQL_ASCII as default characterset encoding rather than UTF8

    If this database cluster does not use UTF8 as the default character set encoding, you will encounter large number of errors when you try to restore PostERP database Section 14.2 from backup file! Debian users can wipe out the new SQL_ASCII database cluster all together and manually recreate it forcing it to use UTF8:

    pg_dropcluster --stop 11 main
    pg_createcluster -u postgres -e UTF8 11 main
    					

    If you are a Windows user and drop PostgreSQL cluster space by deleting C:\Program Files\PostgreSQL\11\data and then recreating the cluster space using command C:\Program Files\PostgreSQL\11\bin\initdb.exe, and can no longer start PostgreSQL service since then, and Windows event log says could not create lock file "postmaster.pid": Permission denied, resource Chapter 21 might help.

  2. Enable connecting to PostgreSQL server with role postgres

    PostgreSQL role postgres will be used to perform the following tasks:

    • You will restore our database backup file to your PostgreSQL database.

    • You will do various management jobs like making backups for your PostgreSQL database.

    • PostERP server will connect via TCP/IP and log in to PostgreSQL in order to manipulate database without restriction.

  3. Important

    PostERP's architecture is 3-tier computing: Database server communicates only with PostERP server; PostERP server communicates only with PostERP clients. Hence IT personnel must grant only PostERP server and no one else the acceess privilige to PostgreSQL server.

    • For Windows version of PostgreSQL, configure this file "C:\Program Files\PostgreSQL\11\data\pg_hba.conf" as below.

    • For Debian or Debian versions of PostgreSQL, configure this file /etc/postgresql/11/main/pg_hba.conf as below.

    Ensure TCP connection is enabled so that role postgres can connect to PostgreSQL. Three examples of configuration:

    host	all		postgres	127.0.0.1/32	md5
    host	all		postgres	192.168.0.2/32	password
    host	all		postgres	192.168.0.2/32	trust
    					

    Tell PostgreSQL server to respond to the requests sent to all its interfaces by setting postgresql.conf:

    listen_addresses = "*"
    					

    PostERP server establishes a certain number of connections to PostgreSQL server for every language. If the maximal number of connection is set too low, PostERP server will not start. Refer to Section 18.2 for calculation of connection number. The default limit 20 specified in postgresql.conf is sufficient in most cases:

    max_connections = 20			# (change requires restart)
    					

    Restart PostgreSQL server:

    • Windows users:

      Stop postgres service, then restart it.

    • Debian users:

      /etc/init.d/postgresql restart
      							

14.1.4. Verify PostgreSQL Accepting Connection Requests

Enter the password for account postgres. If no error message pops out, then most likely PostgreSQL server is ready to provid services.