13.1. Installing PostgreSQL

13.1.1. Installing PostgreSQL In Windows Host

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

Consult Chapter 20 if PostgreSQL can not run.

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.

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.

13.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
			

13.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 13-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 13.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 20 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. 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 17.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

13.1.4. Verify PostgreSQL Accepting Connection Requests

• Windows users:

  Open a DOS window and issue this command:

  "C:\Program Files\PostgreSQL\11\bin\psql.exe" -h 192.168.0.1 -U postgres -l

• Unix users will send this command:

  psql -h 192.168.0.1 -U postgres -l

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