PostgreSQL Database Server

Modified on Tue, Nov 7, 2023 at 6:55 AM

TABLE OF CONTENTS

Frequently Asked Questions
Installation on Microsoft Windows Server
Upgrading between major versions (15.1 to 16.1)
Verifying the installation
- Genesis
- SchoolFi

Frequently Asked Questions

Can I convert our existing database server from Oracle to PostgreSQL?

Yes, reach out to Genesis support for assistance on the process.

Should PostgreSQL be installed on the same server as Apache Tomcat?

Yes, this is the recommended configuration. The performance of both SchoolFi and Genesis will be significantly faster.

Is PostgreSQL free?

Yes. PostgreSQL is a free and opensource database server. There is no licensing cost to use this database server.

Where can I find the documentation for PostgreSQL?

https://www.postgresql.org/docs/current/index.html

How do I upgrade between minor versions (15.1 to 15.2)?

Download the latest binary and run the installer. The installer will update the existing version in place. This process is extremely simple to perform.

How do I upgrade between major versions (15.1 to 16.1)?

You will need to perform a full backup of the database. Uninstall 15.1, Install 16.1 and then Restore the full backup of the database. More complete notes on this process are in this document.

Installation on Microsoft Windows Server

Downloading the installer for Windows

This link is where you will find the official distribution of PostgreSQL for Windows.

https://www.postgresql.org/download/

Click the windows icon

Click the Download the Installer link in the first paragraph.

Click the download icon in the Windows x86-64 column for the latest version of PostgreSQL.

Running the installer

Change the Installation Directory to X:\PostgreSQL\15 where X is the drive you which to install the database server to.

Uncheck the Stack Builder option from the list of components to install

Accept the default Data Directory (It should default to X:\PostgreSQL\15\data)

Choose a secure password to the postgres account (This is the system administrator account). Do not lose this password.

Accept the default TCP Port of 5432

Accept the default Local: Default Locale

When the installation is complete, add X:\PostgreSQL \15\bin to the system path.

Once installation is complete, open a new command prompt and test that postgreSQL is in the path by typing:

c:\Users\genesis> psql --version
psql (PostgreSQL) 16.0

Update the default schema security

Open a command prompt and run these commands:

c:\Users\genesis> psql postgres postgres 
-- It will then ask you for the password in the previous step
-- ...
postgres=# revoke all on schema public from public;
postgres=# \q

You can safely ignore this error: schema "public" does not exist

Exclude X:\PostgreSQL from any Antivirus and Backup software

If this step is not performed, there is a strong possibility the database will at best: perform very slow, at worst crash.

Configuring the server for optimal performance

The information in this section is based upon https://pgtune.leopard.in.ua/#/

Make a backup of the X:\PostgreSQL\15\data\postgresql.conf before applying any changes.

The 15 in the above directory path reflects the newest version of PostgreSQL at the time of the writing of this document.

The default installation of PostgreSQL is sized for a server with 128 MB (Yes, Megabytes). It is highly advisable to increase the base configuration for

optimal performance.

Apply these changes to the X:\PostgreSQL\15\data\postgresql.conf file.

Selecting a value for each parameter

There are 3 values listed next to each parameter. You are going to choose one of them based on the memory installed in the database server.

Parameter	8 GB	32 GB	64+ GB
max_connections	200	200	200
shared_buffers	4GB	8GB	16GB
effective_cache_size	12GB	24GB	48GB
maintenance_work_mem	1G	2G	2G
work_mem	10MB	20MB	40MB
min_wal_size	1GB	1GB	1GB
max_wal_size	4GB	4GB	4GB

Do to services; stop then start the postgresql-x64-15 service. Do not use the restart button. Stop and then start it.

Configuring the server to allow connections from outside of the machine

Step 1: Configure Listening Addresses

By default, PostgreSQL will only allow connections from the server it is installed on. It is possible that this may change in a future version of PostgreSQL.

If Apache Tomcat is installed on a different machine, then you may need to update the listener configuration.

Open X:\PostgreSQL\15\data\postgresql.conf in an editor of your choice.

Search for the phrase listen_addresses, read the instructions in the configuration file and apply changes as neccessary.

Step 2: Configure Host Based Access

Open X:\PostgreSQL\15\data\pg_hba.conf in an editor of your choice.

The easiest way to open it up is to place an entry in the # IPv4 local connections section:

To allow all machines in the 192.168.1.x network to connect, your section may look like this:

# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.1.0/24 scram-sha-256

Documentation for this file can be found here:

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Go to services; stop then start the postgresql-x64-15 service. Do not use the restart button. Stop and then

start it.

Connection Pool setup for Apache Tomcat 10.1.x

This fragment goes into the x:\tomcat\conf\Catalina\localhost\genesis.xml (or schoolfi.xml)

For Genesis

<!-- Update the username, password and url attributes -->
<Resource name="jdbc/genesis"
               factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
               type="javax.sql.DataSource"
               auth="Container"
               minIdle="2"
               maxActive="10"
               maxWait="10000"
               initialSize="0"
               username="demo"
               password="demo"
               driverClassName="org.postgresql.Driver"
               url="jdbc:postgresql://127.0.0.1/postgres"/>

For SchoolFi

<!-- Update the username, password and url attributes -->
<Resource name="jdbc/backoffice"
               factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
               type="javax.sql.DataSource"
               auth="Container"
               minIdle="2"
               maxActive="10"
               maxWait="10000"
               initialSize="0"
               username="sfdemo"
               password="sfdemo"
               driverClassName="org.postgresql.Driver"
               url="jdbc:postgresql://127.0.0.1/postgres"/>

Install the latest JDBC Driver

Obtain the latest version of the JDBC driver from https://jdbc.postgresql.org/

Get the one for the highest version of Java (As of this writing it is 8)

Click the download button and place the jar file in the x:\tomcat\lib directory.

The filename will be something similar to postgresql-42.6.0.jar

Be sure to delete older versions in the lib directory

Upgrading between major versions (15.1 to 16.1)

Step 1: Backup the data

Look in the x:\tomcat\conf\Catalina\localhost\genesis.xml (or schoolfi.xml) file for the username and password. In this example, we will use sfdemo as the

username and sfdemo as the password.

Open a command prompt and run these commands:

cd \
mkdir postgresBackup
cd postgreBackup
pg_dump -b -n sfdemo postgresql://sfdemo:sfdemo@127.0.0.1:5432/postgres >fullBackup.dmp

This command may take a while to run and there will be output until it is finished.

Step 2: Uninstall the current version of PostgreSQL

Using the standard method for uninstalling applications on the version of Windows on your server, uninstall Postgres.

Choose Entire Application in the uninstall dialog.

Once the uninstall is complete, be sure to completely remove the directory (or rename it if you wish to hold only it just in case)

Step 3: Install the latest version of PostgreSQL

Step 4: Load the backup

Open a command prompt and run these commands:

psql postgres postgres (It will then ask you for the password in the previous step)
create user sfdemo with password 'sfdemo';
create schema authorization sfdemo;
grant sfdemo to postgres;
\q
cd \postgresBackup
psql postgresql://sfdemo:sfdemo@127.0.0.1:5432/postgres <fullBackup.dmp

Verifying the installation

Verify that the versions of the important components match what was just installed / upgraded.

Genesis

Go to the Core -> Diagnostics -> Config screen

SchoolFi

Go to the Systems -> Diags -> Diags screen