topic Connecting to Postgres over the network in Alfresco Archive

Connecting to Postgres over the network

dadkins6 — Wed, 14 Dec 2011 20:43:42 GMT

Is there any way to configure Alfresco to connect to a Postgres database on a computer on the same network (or perhaps a computer that is connected to the internet even)?I went through the instructions in the Alfresco documentation here, but that is only setup for running the Postgres server on your

Re: Connecting to Postgres over the network

dadkins6 — Fri, 16 Dec 2011 13:59:18 GMT

I guess I should mention I'm on Alfresco 4.0 and using the PostgreSQL packaged with Alfresco.

Re: Connecting to Postgres over the network

lotharmärkle — Fri, 16 Dec 2011 14:09:29 GMT

Maybe the connection problem is on the postgres side and not alfresco?

One possible issue is the allowed-host configuration of the postgres.
The postgres config file pg_hba.conf lists hosts that are allowed to connect.

the default config only allows connections from the local host:


# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
‍‍‍‍

Simple add a new line matching your network layout.

Re: Connecting to Postgres over the network

dadkins6 — Fri, 16 Dec 2011 14:43:50 GMT

I added a line to that file on the host computer and the client. It read:

host   all            all            192.168.1.0/24         password‍

Would I actually have to list the ip address? i.e. 192.168.1.11?

Re: Connecting to Postgres over the network

lotharmärkle — Fri, 16 Dec 2011 14:52:58 GMT

no, you only have to change the host where the database is running on.

And also make sure that the database on the remote host is listening actually on the interface at 192.168.1.11,
not just 127.0.0.1.

You can check this using netstat on the database server or do a telnet 192.168.1.122 5432 from your alfresco box.''

Re: Connecting to Postgres over the network

dadkins6 — Tue, 20 Dec 2011 19:19:07 GMT

I still can't get it to connect. These are the only changes I've done. In the pg_hba.conf on the host, I added this line: (the client's IP address)

host   all            all            192.168.1.11/24         password‍

and in postgresql.conf changed the listen addresses to include the client's IP.

On the host machine, I changed db.url in the alfresco-global.properties to use the host's IP and port.

Is there anything else you can think of that would make this work? I do appreciate the help you've given so far.

Re: Connecting to Postgres over the network

lotharmärkle — Tue, 20 Dec 2011 19:33:46 GMT

Sorry, its messed up. Please clearify which system you mean by host and client. Is it the system running alfresco or the system running the remote database? Which Ip?

and in postgresql.conf changed the listen addresses to include the client's IP.

thats not the intention, the postgres service should listen on the ip of the postgres host. Please verify this setup first.

For basic "how to connect to a remote postgres database" you may be better of asking at the postgres forums.

lothar

Re: Connecting to Postgres over the network

dadkins6 — Tue, 20 Dec 2011 20:19:05 GMT

When we deploy this project, we want to have the PostgreSQL database on a separate server from Alfresco because we already have a PostgreSQL server. I'm just experimenting with Alfresco on two computers now to figure out how to achieve this. Both computers have Alfresco installed with the included version of PostgreSQL , but I'm trying to access the PostgreSQL server of the 'host' computer with the 'client' computer running Alfresco.

The PostgreSQL documentation says the listen_addresses variable "Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications"

Re: Connecting to Postgres over the network

lotharmärkle — Tue, 20 Dec 2011 22:35:02 GMT

So the postgres listen address is the public ip of the postgres host, not the ip of the client.

Re: Connecting to Postgres over the network

dadkins6 — Wed, 21 Dec 2011 14:59:08 GMT

The permissions are setup now so that I can connect to the host computer from the client in pgAdmin, but still get an error when I try to connect with Alfresco. This tells me PostgreSQL is configured properly, but I'm missing something on the Alfresco side.

Re: Connecting to Postgres over the network

dadkins6 — Wed, 21 Dec 2011 15:20:26 GMT

Actually I figured out how to get it working. I had to edit the share-config-custom.xml file in [Alfresco_home]\Tomcat\shared\classes\alfresco\web-extension. instead of saying localhost, it needs the ip address of the host computer.

Thanks for your help lothar.

Re: Connecting to Postgres over the network

lotharmärkle — Wed, 21 Dec 2011 15:43:34 GMT

For connecting to a remote database host, no changes below shared/classes/alfresco/web-extension (share-custom-config.xml) has to be done.

The Alfresco share webapp (share.war) can be configured to use a remote Alfresco repository webapp instance (alfresco.war) this way.

This is very different than using a remote database for the Alfresco repository webapp (alfresco.war), which is configured in the alfresco-global.properties file only (db.url=jdbcostgresql://rem.ote.iph.ost/ALFRESCO entry).

What did you tried to achive?