vCAC 6.0 Appliance External PostgreSQL Database Installation and Configuration

[DarrenGoff2]

Has anyone managed to successfully configure an external PostgreSQL database for a distributed installation? I have installed PostgreSQL on CentOS 6.4 but receive an error when attempting to connect the primary vCAC Appliance to the database. I "believe" I have configured PostgreSQL 9.3.2 on the CentOS 6.4 server to allow remote connections to the vCAC DB for the vCAC user and the vCAC Appliance IP Addresses and configured the firewall to allow port 5432. I have followed the vCAC installation guide to create the PostgreSQL DB and user but get the error "Error connecting database on jdbc:postgresql://%hostname%:5432/%vCAC_DB% as user %vCAC_DB_User%: The connection"

[igorstoyanov]

Did you configure it from the VCAC Appliance Console (vCAC Settings/Database) and you see this exception from there? The first thing to do is to try to connect to this database with any database tool in order to make sure that the database is accessible. It looks like the vCAC can't connect to it.

[DarrenGoff2]

Yes, I get the error from the vCAC Appliance Web Management interface when I tried to add in vCAC Settings > Database > Save Settings. Do you know any others tools to test? Sorry but I'm new to PostgresSQL and CentOS.

[igorstoyanov]

This is the tool that comes with postgres: http://www.pgadmin.org You can download and try to connect/verify the settings with it.

[DarrenGoff2]

I have downoaded the tool and tried connecting but that also fails. I have reviewed the PostgresSQL server settings, the postgresql service is running, pg_hba.conf has been updated with "host all all 172.16.6.0 255.255.255.0 trust" to allow remote IP access for all servers on the 172.16.6.0 subnet, I have updated the postgresql.conf file and updated "listen_addresses='*', port = 5432 to allow TCP/IP communication on all IP addresses on port 5432. I can ping the PostgreSQL server from my test server with pgadmin installed but cannot connect to PostgreSQL.

[igorstoyanov]

I am not sure what might be the problem. I suspect some of the rules about which ips can assess it. Could you try to to remove them temporary and see if this could help. BTW, you might use the VCAC VA for external Postgresql. Basically, deploy one vCAC VA, stop all other services (tc-servers a few others) and it will be left with only the postgressql.

[awjudge]

I have this working in my environment. The only things I changed from the defaults were the postgresql.conf file to add "listen_addresses = '*'

Then in the pg_hba.conf file, I've added lines for each host I want to be able to connect:

Type   Database  User       Address     Method

host    vcacdb      vcac    10.1.1.1/24     trust

The only other thing to check is whether iptables is running. Do a 'service iptables status' from the command line. If it is running, do 'service iptables stop' and try connecting then.

Hope this helps.

Regards,
Alex

[DarrenGoff2]

I reviewed the settings last night in the postgresql.conf, pg_hba.conf and iptables last night. I used the pgadmin tool as this was a quick reliable tool to use to test. After disabling the firewall it worked so I updated iptables with some different settings I found in a blog and it now works. Thanks everyone for your help with this.