0

This might seem trivial, but how can I connect one machine ('A') to another machine's ('B') so that databases in 'A' and 'B' are synced at all times. Both machines run on macOS High Sierra and PostgreSQL v10 was installed with homebrew. Basically, I want 'A' to be a client of 'B' - which acts as the server and 'A' should be able to modify the database in 'B' and that changes are seen in both. I enabled ssh connections on 'B'. I modified the postgresql.conf and pg_hba.conf according to several online fixes in 'B'.

However, I get always the same error in my shell in 'A':

$ psql -h [ip 'B'] -p 5432 -U devil -W test_db
psql: FATAL:  no pg_hba.conf entry for host "ip 'A'", user "devil", database "test_db", SSL off

PostgreSQL files in 'B' (directory: /usr/local/var/postgres):

In postgresql.conf [excerpt]:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

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

In pg_hba.conf [excerpt] - which I cp from a file called pg_hba.conf.sample in the same folder:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

@remove-line-for-nolocal@ # "local" is for Unix domain socket 
connections only
@remove-line-for-nolocal@
local   all             all                                     
@authmethodlocal@
# IPv4 local connections:
host    all             all             127.0.0.1/32            
@authmethodlocal@
# IPv6 local connections:
host    all             all             ::1/128                 
@authmethodlocal@
# Allow replication connections from localhost, by a user with the
# replication privilege.
@remove-line-for-nolocal@local   replication     all                                     
@authmethodlocal@
host        replication     all             127.0.0.1/32          trust
host        replication     all             ::1/128               trust
host        all             all             0.0.0.0/0         md5#ipv4 range

I restarted the server with:

$ brew services restart postgresql
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
roberto swiss
  • 155
  • 8

1 Answers1

0

I think you are confused.

The error messages you quote are failures to connect with the database client on one machine to the database server on the other machine. This is caused by misconfiguration. Your pg_hba.conf file is syntactically incorrect - you cannot simply copy the template.

Something entirely different is to keep two databases synchronized.

If you are happy with B being read-only, you should use streaming replication and hot standby which is simple to set up and robust.

If you want to modify both A and B, that is called multi-master replication, and there is no solution built into PostgreSQL. You could look into BDR by 2ndQuadrant. But multi-master replication is complicated, and there are a lot of problems and pitfalls. Stay away from it if you can.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you very much Laurenz, I will look into streaming replication and hot standby. I will be less confused this way :) – roberto swiss Feb 02 '18 at 13:08