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>;
\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 databse.
  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 pgsql\ext_conf\postgres_ext.conf file.
    • 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?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

                      • 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. ...

                      • 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, |, ...

                      • How to connect to the ServiceDesk database?

                        Open command prompt on the server and change the directory to MangeEngine\ServiceDesk\bin, invoke the batch file 'changeDBServer.bat' For MSSQL database  Connect to the query analyzer of the SQL server and execute the queries. For PostgreSQL database ...

                      • How to connect to the AssetExplorer database?

                        To find the database connected to your installation, open Command Prompt with elevated permission, navigate to [AE-Home]\bin directory and invoke the batch file 'changeDBServer.bat'. [AE-Home] is Drive:\ManageEngine\AssetExplorer\ directory. ...

                      • 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