Child pages
  • PostgreSQL Primer

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Install the embedded PostgreSQL installation
  • Download and install the EnterpriseDB PostgreSQL Standard Server installer

When using Servoy 5.2, the EnterpriseDB PostgreSQL Standard Server installer can be manually downloaded from the EnterpriseDB website here.

As of Servoy 2019.06 Servoy comes only as an archive installer which has Java and PostgreSQL already installed.

Embedded PostgreSQL database engine

Compared to the EnterpriseDB PostgreSQL Standard EnterpriseDB PostgreSQL Server the embedded PostgreSQL database engine is a no frills PostgreSQL installation, just just the bare minimal options installed to run, backup and upgrade the PostgreSQL database. 

The PostgreSQL database engine is installed in the ../application_server/postgres_db/ directory. The actual databases and configuration files are stored in the ../application_server/database/ directory. As of Servoy 6.0, the location of the database engine and database files can be configured during installation.

...

As of Servoy 2019.06 Servoy comes only as archive installers which has PostgreSQL already installed for you.

EnterpriseDB PostgreSQL Server

The Standard Server offering of EnterpriseDb adds PostgreSQL Server installer of EnterpriseDB adds to enterprise level tools to PostgreSQL, like replication. Besides that the Standard Server installer is more sophisticated than the Servoy installer. It will for example install the PostgreSQL database under it's own user.

As of PostgreSQL 11, EnterpriseDB doesn't provide Linux installers anymore. The PostgreSQL community maintains repositories for several Linux flavours:

Debian and Ubuntu: https://wiki.postgresql.org/wiki/Apt

Centos, RHEL, Fedora and Oracle Enterprise Linux: https://wiki.postgresql.org/wiki/YUM_Installation


NOTE:  EnterpriseDB is a commercial party with its own (commercial) version of PostgreSQL, they also create and host the community (FOSS) installers of PostgreSQL. In this document we are always referring to the community installers.


Which installation to choose

For production purposes, it's advised to use the EnterpriseDb Standard PostgreSQL Server installer of EnterpriseDB as these come with all the security best practices out of the box. For development and test purposes, both the embedded PostgreSQL database  engine and the EnterpriseDB option would be sufficient.

...

EnterpriseDB installation

Upgrading Updating an EnterpriseDB Standard PostgreSQL Server installation from EDB to the next maintenance release, lets say 9.0.1 to 9.0let's say 11.1 to 11.2, can be done by running the 9.011.2 installer. It will automatically find the 911.0.1 installation and update it. Make sure to stop Servoy (and any other client connecting to PostgreSQL) performing the upgrade.

Upgrading major or minor versions, lets let's say from 911.0.2 x to 912.1 x is not possible in the same manner. Running the 912.1 x installer will install 912.1 x next to the 911.0 x installation. The installer automatically set the port of this version to one that doesn't collide with the one already installed. So the 2 versions can run at the same time.

To get the databases over to the new major version a backup can be made of the entire database cluster using the command-line pg_dumpall backup utility, after which the backup can be loaded back into the new instance using the psql utility. This operation an also be performed using PgAdmin. It is recommended to use the tools from the newer version to backup the databases from the older version.

...

Code Block
../application_server/postgres_db/bin/

When you have used the community installer from EnterpriseDB (EDB) installer then  then all of PostgreSQL (binary and database cluster) is installed in the following directory:

...

Code Block
/Library/PostgreSQL/912.0/

Windows

Code Block
C:\Program Files\PostgreSQL\912.0\

Linux

Code Block
/opt/PostgreSQL/912.0/

Where the subdirectory bin holds the command-line tools and other binaries and the subdirectory data holds the database cluster. Also PostgreSQL will be running under the system user postgres and the data directory is only accesible by that user. All this is for security.

On linux however you can also use ready made packages specially made for the Linux distribution and have their own places of storing the various parts of PostgreSQL.

For example on Centos/RHEL you will find the database cluster in 

Code Block
/var/lib/pgsql/12/data/

and the binary in 

Code Block
/usr/pgsql-12/bin/


Starting PostgreSQL

Using the bundled PostgreSQL (assuming you run this from within the application_server directory):

...

Code Block
sudo -u postgres /Library/PostgreSQL/912.0/bin/pg_ctl start -D /Library/PostgreSQL/912.0/data

The EDB installed EnterpriseDB installed version already writes logs in the data/pg_log/ directory so no need to add the -l argument.

...

Code Block
postgres_db\bin\pg_ctl stop -D database

When using the EDB EnterpriseDB installed version of PostgreSQL then the command is the following:

Code Block
 sudo -u postgres /Library/PostgreSQL/912.0/bin/pg_ctl stop -D /Library/PostgreSQL/912.0/data

You need to provide the location of the database cluster because you could have instances of PostgreSQL running using different data directories.

Troubleshooting

After you installed Servoy and PostgreSQL you might find that PostgreSQL is not running.

This usually could mean 2 things:

  • PostgreSQL wasn't yet initialised. In other words there is no cluster created yet to host the databases. This means that the database or data directory is empty.
  • There is another PostgreSQL instance running on port 5432 (PostgreSQL's standard port)


Initialise the database cluster

Using the bundled PostgreSQL (assuming you run this from within the application_server directory):

Code Block
postgres_db\bin\pg_ctl init -D database

When using the EnterpriseDB installed version of PostgreSQL then the command is the following:

Code Block
 sudo -u postgres /Library/PostgreSQL/12.0/bin/pg_ctl init -D /Library/PostgreSQL/12.0/data


Changing the port number

The port number is setup in the postgresql.conf file. This file is located in PostgreSQL's data directory.

Using the bundled PostgreSQL you'll find it here

Code Block
application_server/database/postgresql.conf

When using the EnterpriseDB installed version of PostgreSQL you will find it here:

Code Block
/Library/PostgreSQL/12.0/data/postgresql.conf

You can edit it with your favourite text editor and change the following line (by default it's a commented line)

Code Block
#port = 5432

to this

Code Block
port = 5433

IMPORTANT: After you changed the port number make sure your database connections use this port number as well.

Running PostgreSQL as a Service

...

When also running the Servoy Application Server using the Service Component, a dependency can be setup between the Windows Service for the database and the Application Server. See Running the Server As a Service for more information.

OSX

See httphttps://www.postgresql.org/docs/9.0/interactivecurrent/server-start.html

*nix

See httphttps://www.postgresql.org/docs/9.0/interactivecurrent/server-start.html

Performing backups & restores

...

PostgreSQL doesn't do automatic casting of types. This means that when sending in parameters into a prepared statement, it is up to the developer to make sure to send in the right type.

For example:

Code Block
languagejs
var value = 1;
var query = '"SELECT * FROM table WHERE textcolumn = ?'"
var args = new Array();
args[0] = value;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, maxReturnedRows);

Has to become:

Code Block
languagejs
var value = 1;
var query = '"SELECT * FROM table WHERE textcolumn = ?'"
var args = new Array();
args[0] = value + ""; //forcing the integer value to become a string
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, maxReturnedRows);

...

Quoting for column Aliasses:

Code Block
languagesql
SELECT some_column as "A nice name" FROM table

Quoting literal strings:

Code Block
languagesql
SELECT * FROM table where text_column = 'someValue'

...

When the amount of data to be moved to the new database is massive or required pre-processing, a better solution is to dump the data to a generic format and import it directly into the new database.When migrating from Sybase to PostgreSQL, PgExperts provides a special migration service for Servoy customers. For more information on this service check the PgExperts website.