How to Connect External Postgres

How to Connect External Postgres

 Install a External Postgres such that the version of that postgres is currently in the series of inbuilt postgres of our Application. Say for example, In Servicedeskplus Application version 14010, which has postgres version 10.21, then you should install a external postgres server which has version in 10.x series <=10.21. We recommend to use Linux , for External Postgres Server machine instead of Windows.
you can check the inbuilt postgres version by navigating to <Application Folder>/pgsql/bin/ and executing the command mentioned below. 
./psql --version   #for Linux                 or                 psql.exe --version         #for Windows 
Steps to Follow at the Postgres Server :

      A. Start the External Postgres Server and open PSQL console. say in linux, you can start by invoking the below command
  1. sudo service postgresql start
  2. pg_lsclusters                                          # To list out the available postgresql clusters
  3. sudo -u postgres  psql                          # To enter into default postgres console with port number 5432 and postgres database
      B. Create an user in the Server by executing the following commands.
                If your postgres version is greater than or equal to 10,
  1. CREATE USER <username> WITH CREATEDB LOGIN REPLICATION ENCRYPTED PASSWORD <password>;
                else,
  1. CREATE USER <username> WITH SUPERUSER CREATEDB LOGIN REPLICATION ENCRYPTED PASSWORD <password>;

                 After creating the user, logout of <current_user> console and login as the newly created user with the commands below.
  1. \q
  2. psql -h <host> -p <port> -d postgres -U <newly created user>

\du                                 # Lists the roles & users present in the External postgres server.
      C. Create a database, which is going to configure in application and Navigate to that database.
  1. CREATE DATABASE <database_name>;
  2. \c <database_name>
\l                                     # Lists the databases available in the server
      D. Execute the following commands to create a list of extensions.
  1. CREATE EXTENSION IF NOT EXISTS pg_trgm;
  2. CREATE EXTENSION IF NOT EXISTS pgcrypto;
  3. CREATE EXTENSION IF NOT EXISTS citext;
\dx                                    # verifies whether the list of extensions created by listing it.
      E. If the Application is running in a different server machine other than the external postgresql server machine(say incase of common database server), we need to configure Application machine address (machine where product is hosted) as mentioned below. 
  •       In the External postgres server machine, open pg_hba.conf (references :  pg_hba.conf , say in linux it is placed in /etc/postgresql/<version>/main)
    • add the host row with "IP address of the Application machine/a CIDR mask length" to enable postgres server accept connection from Application as mentioned in the below example. you can also add  0.0.0.0/0  to enable access for all machines, but which is not recommended.
    1. host    all             all             XX.XX.XX.XX/XX            md5

  • open postgresql.conf file of the same external postgres server, 
    • add an entry listen_addresses= '<ip address(es)>'  at the end of file.which Specifies the TCP/IP address(es) on which the External Postgres server is to listen for connections from Application. Similar to the host records, The special entry * corresponds to all available IP interfaces but which is not recommended.
    • Note : It is not advised to modify the default postgresql.conf file bundled along with postgres database. All the configuration changes are to be done in file in ext_conf folder of postgres database. 
  • Restart the External Postgres Server.
Steps to follow at Product :
  1. Navigate to <prod_home>/ bin directory.
    1. Run changeDBServer.sh or chaneDBServer.bat , and configure the database, port, username and password.
  2. Navigate to <prod_home>/conf/ directory.
    1. Change the value of StartDBServer as "FALSE" in customer-config.xml.
  3. Start the Application.


      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • How to connect to external postgres(external DB)

                      1. From the postgres installed path navigate to bin folder and open a command prompt 2. Execute the command --> pg_ctl -D "C:\Program Files\PostgreSQL\10\data" start (Postgres server gets started) 3. Connect to DB using the command-->psql -U postgres ...

                    • External Postges - PG Extension Issue

                      Error: If cxs who are using external postgres(not bundled postgres) faces below error, 1. Migration fails with "Upgrade halted. Please create postgres extension pg_trgm and try again" error. (or) 2. Start-up fails with "Application startup halted. ...

                    • How to resolve Connectivity issues with Postgresql Database from our product?

                      Log Traces Feb 20, 2024 6:40:00 PM [SYSERR] [INFO] : java.sql.SQLException: java.lang.Exception: Exception during getConnection from pool Exception occurred during get connection from datasource Nov 15, 2023 1:29:10 PM [SYSERR] [INFO] : Caused by: ...

                    • Migration failure for Postgres Database with a blank space in it

                      Trace : \ManageEngine\ServiceDesk\Patch\AdventNet_ManageEngine_ServiceDesk_Plus-14.2.0-SP-1.0.0\SERVICEDESK\PreInstall\pg_migrate\pgsql_old\bin\pg_dumpall.exe", -U, postgres, -p, 65433, -h, 127.0.0.1, -r, -w, |, ...

                    • Remote read only access to database for Postgres customers

                      Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow ...

                      Related Products