Connecting to Postgres over the network

[dadkins6]

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 own computer. I tried editing it a bit, by making a few changes. I changed db.url to "jdbcostgresql://192.168.1.11:5431/${dbname}" in the alfresco-global.properties file.  I would think this would make it connect to the remote Postgres database instead of the one on my computer, but it gives me an error each time I try to login, saying "The remote server may be unavailable or your authentication details have not been recognized." The other computer has had the proper databases and users created.

Is there a step I'm missing while trying to set this up?

[dadkins6]

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

[lotharmärkle]

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.

[dadkins6]

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?

[lotharmärkle]

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

[dadkins6]

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.

[lotharmärkle]

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

[dadkins6]

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"

[lotharmärkle]

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

[dadkins6]

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.