...
The goal of this primer is to provide a starting point for the most common actions for working with PostgreSQL, with pointers to more in-depth documentation where applicable.
Table of Contents | ||||
---|---|---|---|---|
|
Stoc |
---|
As of Servoy 5.2 Servoy comes bundled with PostgreSQL. This means that when the option to install the databases in the All-In-One installer is checked, the installer will install the PostgreSQL database engine.
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.
While While Servoy comes bundled with PostgreSQL, Servoy is 100% database agnostic. The PostgreSQL database can therefor be replaced by any type of database.
...
As of Servoy 5.2 Servoy comes bundles with PostgreSQL. When selecting the Database option in the All-In-One installer, PostgreSQL is automatically installed and Servoy is setup to connect to it.
....
While PostgreSQL comes bundles with Servoy, the PostgreSQL installation provided through the Servoy All-In-One installer is a basic installation. Thus many people prefer
Unix variants
TODO
Windows
As of Servoy 5.2 PostgreSQL is bundled with Servoy by default. The bundles PostgreSQL is however a very basic install. For production purposes it is also possible to use a dedicated PostgreSQL installer from EnterpriseDB.
...
...
TBD
...
...
TODO
...
...
Installing an update, lets say 9.0.1 to 9.0.2 literally means you just run the installer of 9.0.2 and you are done. Just make sure you stop As of Servoy 6.0 the Servoy All-In-One installer provides 2 different options to install PostgreSQL:
When using Servoy 5.2, the PostgreSQL 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.
Compared to the 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.
The 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.
For production purposes, it's advised to use the 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.
To be described
Updating an PostgreSQL Server installation from EDB to the next maintenance release, let's say 11.1 to 11.2, can be done by running the 11.2 installer. It will automatically find the 11.1 installation and update it. Make sure to stop Servoy (and any other client connecting to PostgreSQL) before you do thisperforming the upgrade.
Upgrading PostgreSQL, lets major versions, let's say from 911.0.2 to 9.1, you can run the 9.1 installer and this will be installed next to the 9.0 x to 12.x is not possible in the same manner. Running the 12.x installer will install 12.x next to the 11.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.
Now to To get your the databases over to the new major version you need to backup your whole a backup can be made of the entire database cluster using the command-line pg_dumpall backup utility and load it , after which the backup can be loaded back into the new instance using the psql utility. You could also use PgAdmin for this.It 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.
...
Through the pg_ctl tool that comes with PostgreSQL, it is easy to start and stop a PostgreSQL database. In a default installation the pg_ctl tool is located in
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:
OSX
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 |
---|
postgres_db\bin\pg_ctl start -D database -l postgres_db\postgres_log.txt
|
When using the EDB installed version of PostgreSQL then the command is the following:
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.
Stopping PostgreSQL
Using the bundled PostgreSQL (assuming you run this from within the application_server directory):
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.
After you installed Servoy and PostgreSQL you might find that PostgreSQL is not running.
This usually could mean 2 things:
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.
The pg_ctl utility that comes with PostgreSQL has a built-in command to register the PostgreSQL database as a Windows Service
Registering the Windows Service:
Code Block |
---|
postgres_db\bin\pg_ctl register -N PostgreSQL -D database
|
Unregistering the Windows Service:
Code Block |
---|
postgres_db\bin\pg_ctl unregister -N PostgreSQL
|
...
The -N option specifies the name for the Windows Service. Make sure the name is unique.
...
...
TODO
...
...
TODO
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.
See https://www.postgresql.org/docs/current/server-start.html
See https://www.postgresql.org/docs/current/server-start.html
PostgreSQL stores it's data in a 'database cluster' which is a collection of files and directories that together make up the entire database. It's not recommended to backup these files, but instead use the backup utilities that come with PostgreSQL.
PostgreSQL provides a pg_dump and a pg_dumpall utility to make a backup file of a running database. In a default installation of Servoy, the utilities are located in ../application_server/postgres_db/bin/. These utilities can be used command-line, but also through the PgAdmin utility.
Backup a specific database
Code Block |
---|
postgres_db\bin\pg_dump -U dba -Fc servoy_repository > servoy_repository.dump
|
...
The samples above assume execution from the ../application_server/ directory.
...
...
TODO: see http://www.servoy.com/forum/viewtopic.php?f=4&t=15208#p81450 for input
...
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 | ||
---|---|---|
| ||
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 | ||
---|---|---|
| ||
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); |
In PostgreSQL, the use of quotes and double quotes is more strict than in some other databases.
Quoting for column Aliasses:
Code Block | ||
---|---|---|
| ||
SELECT some_column as "A nice name" FROM table |
Quoting literal strings:
Code Block | |||
---|---|---|---|
|
| ||
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.