Connect Zoho Creator on-premise to external PostgreSQL database

• Connect Creator to PostgreSQL database
• Version Upgrade to PostgreSQL V17

Zoho Creator on-premise allows connecting to an external database, PostgreSQL, through which you can use your organization's existing PostgreSQL infrastructure. By connecting, your Creator application data will be stored and managed in the external PostgreSQL database instead of the default built-in PostgreSQL database.

When to consider using an external PostgreSQL database

• Existing database and Centralized data storage: If you already have a PostgreSQL Enterprise Edition instance and need to maintain a centralized database for all your applications, connecting Zoho Creator to the same PostgreSQL instance simplifies configuration and management.
• Common database for MAS setup: When a Multiple App Server (MAS) setup is used for Creator, they require a common database that can be accessed by all application servers.

Supported versions

Connect Creator to PostgreSQL database

Connecting PostgreSQL database with Creator involves a two-step process.

• Set up PostgreSQL server
• Update database configuration in Creator

Set up PostgreSQL server

1. Open the terminal or cmd, navigate to the PostgreSQL bin, and run the following command to initialize the PostgreSQL data directory

   ./initdb -D /path/to/data/directory -U postgres -W 
   Example : ./initdb -D ../data -U postgres -W
2. Start the external PostgreSQL server and connect to the PostgreSQL console.
3. Create a user and log in.

   1. Create a user:

      CREATE USER <username> WITH CREATEDB LOGIN REPLICATION PASSWORD '<password>'; 
      Example : CREATE USER creator WITH CREATEDB LOGIN REPLICATION PASSWORD 'password';

   2. Log out:

      \q

   3. Log in again as a new user:

      ./psql -h <host> -p <port> -d postgres -U <newly created user>

   4. List all roles and users:

      \du
4. Create a database.

   1. Create a new database:

      CREATE DATABASE <new_db_name> WITH OWNER = <your_owner_name> ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0; 
      Example : CREATE DATABASE "CreatorDB" WITH OWNER = creator ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0;

   2. Navigate to the new database:

      \c <database_name>

   3. List all databases:

      \l
5. Create required Extensions

   1. Execute the following commands to create an extension.
      For PostgreSQL Version 17

      CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
      CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
      CREATE EXTENSION IF NOT EXISTS pgadmin WITH SCHEMA public;
       

      For PostgreSQL Version 11

      CREATE EXTENSION IF NOT EXISTS citext;
      CREATE EXTENSION IF NOT EXISTS pgcrypto;
      CREATE EXTENSION IF NOT EXISTS pgadmin;
       

   2. Verify extensions:

      \dx

6. Configure the IP address.

   If the application and the PostgreSQL server are hosted on different machines, configure the address of the machine where the application is hosted.

   Edit the pg_hba.conf file and add a host row to enable connections.

   host all all XX.XX.XX.XX/XX md5
   Warning: Avoid using '0.0.0.0/0' as it allows access from all machines.

7. Edit the postgresql.conf file and add the following command at the end of the file:

   listen_addresses = '<IP address(es)>'

Update database configuration in Creator

1. Navigate to the Creator installed folder/Creator/bin, open the terminal or cmd from bin folder, and run the below script. 
   Linux: InstallationSettings.sh 
   Windows: InstallationSettings.bat
2. The script will prompt to switch the installation type to Multiple App Server mode. Enter N to continue with single-server setup.
   • Prompt : Switch to Multiple App Server Mode? (Y/N)
   • Action : Enter N (or Y if MAS architect was suggested for your requirement)
3. The Creator Configuration menu will appear. Select the option to update the database.
   • Prompt : Enter your choice's index :
   • Action : Enter the index of Update DB Configuration.
4. Confirm you are using a separately installed (external) database. Enter Y to proceed with external database configuration.
   • Prompt : Use a Database which you have already installed separately? (Y/N)
   • Action : Enter Y.
5. Select PostgeSQL as database type.
   • Prompt : Choose DB Type :
   • Action : Enter 1 (for PostgreSQL).

6. Provide the connection details for your external PostgeSQL server:

   Prompt	Action
   Enter DB Hostname/IP(it should be reachable from all servers) :	Enter the PostgreSQL server IP/Hostname  (e.g., 192.168.90.2).
   Enter DB port :	Enter the PostgreSQL Port (e.g., 1433).
   Enter DB username :	Enter the PostgreSQL username  (e.g., creatoruser).
   Enter DB user's password :	Enter the password  (e.g., CreatorUser@135).

7. After the database configuration is updated successfully, the main menu will reappear. Exit the utility.
   • Prompt : Enter your choice's index :
   • Action : Enter 9 (Exit).

Your Zoho Creator on-premise setup will be connected to your PostgreDQL database.

Version Upgrade to PostgreSQL V17 

If you've already connected PostgreSQL v11 with your Creator, you can upgrade it to v17 for an improved performance.

Prerequisites to upgrade:

• PostgreSQL 11 server must be running.
• PostgreSQL 17 server must be installed and initialized.
• Need admin user access to both the above servers.

Steps to upgrade

1. Take a backup of your existing creator database from v11. Open the terminal or cmd, navigate to the PostgreSQL bin, and run the following command.

   ./pg_dump -U postgres -F c -b -d <Database Name> -f CreatorDB11.dump -p <port of pgsql 11 server> 
   Example: ./pg_dump -U postgres -F c -b -d CreatorDB -f CreatorDB11.dump -p 5430
   • Binary :The binary (pg_dump) must be from the Postrges version (17).
   • Port: Ensure you use the port of the postrges V11 server.
2. Setup External PostgreSQL 17 by following the steps provided in this section.

3. Restore the Dump into PostgreSQL 17

   ./pg_restore -U postgres -p <port of pgsql 17 server> -d <Database Name> /path/to/CreatorDB11.dump 
   Example : ./pg_restore -U postgres -p 5431 -d CreatorDB CreatorDB11.dump
   • Port: Ensure you use the port of the new (17) server, e.g., 5432.
   • Important: You must restore into a clean database that has the required extensions already created.

Common error during upgrade

While upgrading to PostgreSQL v17, you might encounter the below error.

This occurs because the pg_start_backup() function was removed in PostgreSQL 15 and later. You can safely ignore this warning during dump or restore process, as it does not impact the data migration.