Remote PostgreSQL database

(Tbaatar) #1

Hi,


I'm having trouble setting up a separate database to connect Matrix 4.0.3 CMS.



I edit the following setting on the database server 

    
    nano /etc/postgresql/8.3/main/pg_hba.conf
    nano /etc/postgresql/8.3/main/postgresql.conf


And I setup the db host connection with the IP address on the Matrix server.

    
    nano data/private/conf/db.inc



Is there anything else I need to configure?



Thanks.
(Chris Smith) #2

Hi,

[quote]

Hi,



I'm having trouble setting up a separate database to connect Matrix 4.0.3 CMS.



I edit the following setting on the database server 

    
    nano /etc/postgresql/8.3/main/pg_hba.conf
    nano /etc/postgresql/8.3/main/postgresql.conf


And I setup the db host connection with the IP address on the Matrix server.

    
    nano data/private/conf/db.inc



Is there anything else I need to configure?



Thanks.
[/quote]

That should be it. Can you connect to it remotely from the command line?

psql -U $username -h $hostname -d $dbname

if not, what error do you get?
(Tbaatar) #3

When I try to connect it remotely from the App Server i get this message:

    psql: could not connect to server: Connection refused
    	Is the server running on host "46.38.163.254" and accepting
    	TCP/IP connections on port 5432?


This is my setting for db.inc for the App Server

    
    <?php
    $db_conf = array (
                'db' => array (
                                'DSN'     => 'pgsql:dbname=mysource_matrix;host=46.38.163.254',
                                'user'   => 'matrix',
                                'password' => '',
                                'type'   => 'pgsql',
                           ),
                'db2' => array (
                                'DSN'     => 'pgsql:dbname=mysource_matrix;host=46.38.163.254',
                                'user'   => 'matrix',
                                'password' => '',
                                'type'   => 'pgsql',
                           ),
                'db3' => array (
                                'DSN'     => 'pgsql:dbname=mysource_matrix;host=46.38.163.254',
                                'user'   => 'matrix_secondary',
                                'password' => '',
                                'type'   => 'pgsql',
                           ),
                'dbcache' => NULL,
                'dbsearch' => NULL,
                );

return $db_conf;
?>








This is the setting for the database server - nano /etc/postgresql/8.3/main/pg_hba.conf


    
    local   all         postgres                          ident sameuser
    
    # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
    
    # "local" is for Unix domain socket connections only
    local   all         all                               ident sameuser
    # IPv4 local connections:
    host    all         all         46.38.163.249/32      trust
    # IPv6 local connections:
    host    all         all         ::1/128               md5


and nano /etc/postgresql/8.3/main/postgresql.conf

    
    listen_addresses = '46.38.163.249, localhost'      # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
    port = 5432                             # (change requires restart)
    max_connections = 100                   # (change requires restart)






I added this to the IP Table Rule


    
    -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 46.38.163.249  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
    
    -A OUTPUT -p tcp -s 46.38.163.249 --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT




Both servers are Debian and I have the following installed on:

1. Database Server - Apache2 and PostgresSQL
2. App Server - Apache2, PHP5, Pear, Required Modules, Step 1 Matrix Install
(Benjamin Pearson) #4

[quote]
When I try to connect it remotely from the App Server i get this message:


    psql: could not connect to server: Connection refused
    	Is the server running on host "46.38.163.254" and accepting
    	TCP/IP connections on port 5432?


[/quote]

You are connecting to 46.38.163.254, but Postgres looks to be configured for 46.38.163.249?
(Tbaatar) #5

Do I need to install Postgres on the App Server?


Otherwise the settings for the Database Server (46.38.163.254) is configured to listen to the App Server (46.38.163.249) with the above configuration.





I havn't touched the host setting for both the servers and also main.inc (Step 1 config) is set to default for the time being.





edit: installed Postgres on both servers with no difference.

(Chris Smith) #6

Hi

[quote]

Do I need to install Postgres on the App Server?

[/quote]



No. Matrix can talk to a remote db just fine.


[quote]

Otherwise the settings for the Database Server (46.38.163.254) is configured to listen to the App Server (46.38.163.249) with the above configuration.



I havn't touched the host setting for both the servers and also main.inc (Step 1 config) is set to default for the time being.





edit: installed Postgres on both servers with no difference.

[/quote]



The problem is here:

    
    listen_addresses = '46.38.163.249, localhost'      # what IP address(es) to listen on;


You're telling postgres to try and bind to ip address '46.38.163.249' which is on the app server. This doesn't mean "listen for connections FROM this ip address" it means "listen TO this ip address". For example, your server has 10.1.1.5, 10.1.1.10 and 10.1.1.20 ip's set up on it.

You tell postgres which specific ip to listen to instead of accepting connections on all of those ip's:
    
    listen_addresses = '10.1.1.5, localhost'


So if you change that listen_address line to your db server public ip (46.38.163.254), restart postgres and you should be ready to rock and roll.

Once you do that, a `netstat -lnp` should show something like:
    
    # netstat -lnp | grep 5432
    tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      XXXXX/postgres  
    tcp        0      0 46.38.163.254:5432       0.0.0.0:*               LISTEN      XXXXX/postgres


(the XXXXX's are process id's so will be different on your server).
The rest of the configs look fine.
(Tbaatar) #7

Hi Chris Smith,


Thanks for the feedback. At the end we got it working.