This PostgreSQL Primer is by no means a replacement for the excellent PostgreSQL documentation.
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.
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 Servoy comes bundled with PostgreSQL, Servoy is 100% database agnostic. The PostgreSQL database can therefor be replaced by any type of database.
Bundled PostgreSQL version
Which version of PostgreSQL is shipped with a specific version of Servoy can be found in the Servoy documentation for the specific Servoy version. For the latest version, see the Servoy Stack Info here.
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
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 ../application_server/postgres_db/bin/ directory.
Starting PostgreSQL
postgres_db\bin\pg_ctl start -D database -l postgres_db\postgres_log.txt
Stopping PostgreSQL
postgres_db\bin\pg_ctl stop -D database
The commands above assume execution from the ../application_server/ directory. When executing from a different location, make sure to update the paths to the database (-D database) and the logfile (-l postgres_db\postgres_log.txt).
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
postgres_db\bin\pg_ctl register -N PostgreSQL -D database
Unregistering the Windows Service
postgres_db\bin\pg_ctl unregister -N PostgreSQL
The commands above assume execution from the ../application_server/ directory. When executing from a different location, make sure to update the paths to the database (-D database).
The -N option specifies the name for the Windows Service. Make sure the name is unique.
TODO
TODO
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
postgres_db\bin\pg_dump -Fc servoy_repository > servoy_repository.dump
Restore dump into a newly created database
postgres_db\bin\pg_restore -d new_servoy_repository servoy_repository.dump
Backup all databases
postgres_db\bin\pg_dumpall > db.out
Reload all database dump
postgres_db\bin\psql -f db.out postgres
Note that the examples above are very minimalistic. The utilities provided by PostgreSQL offer many options. Check the PostgreSQL documentation on pg_dump, pg_dumpall, pg_restore and pg_sql for detailed information.
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
For PostgreSQL there are multiple admin tools available, from commercial to open source. The most used admin tool for PostgreSQL is the open source, cross-platform PgAdmin.
PgAdmin can be downloaded here and the excellent documentation is available here.
The EnterpriseDB installer for PostgreSQL has the PgAdmin tool bundled.
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:
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:
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:
SELECT some_column as "A nice name" FROM table
Quoting literal strings:
SELECT * FROM table where text_column = 'someValue'
A good starting point to data migration towards PostgreSQL is the PostgreSQL wiki, which has a lot of tutorials migrating data into postgreSQL from many different source databases.
When migration an existing solution developed in Servoy from one type of database to PostgreSQL, Servoy's built-in mechanism to generate the required data model in a new database and to move sample data can also be used to migrate to PostgreSQL (or any other type of database). There are however some restrictions. Such a migration will not move views, stored procedures and functions, database level triggers and sequences. If the amount of records to be moved is very large, memory issues can occur due to the sheer amount of data. To make sure that PK-FK integrity stays in tact in import of the sample data, either Servoy Sequences must be used or the database level generation of sequence values (especially for PK sequences) must be temporarily disabled.
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.