Slony Replication in PostgreSQL

Question

Objective : Set up two DB server if first server down then second can be up and running provided condition is that at evening time both server need to sync. with each other

I am new in DBA and we are using PostgreSQL 9.6 and thinking to use PostgreSQL 10.0. We have regular data in tables along with that we have large spatial data (GIS related data having 'postgis' extension) on Windows Platform

We checked few replication tools in which 'Slony' tool is comes with default installation hence we want to use for replication.

we are checking this link for reference as well as documentation.

If anyone knows proper technical article including required steps then it would great!!

Answer 1

Try this https://github.com/gplv2/haproxy-postgresql

It will guide you to create a better check than tcp-check , which is worse than pgsql-check which does a logical one.

The check here is to see if a server is a slave or not, if it is, it will be marked down, only the master server and only 1 master server will be able to be written to.

You should consider using repmgr https://repmgr.org/ to handle the pgsql streaming replication ecosystem.

Also, you're timings are a bit off, since pgsql sessions can last a longer time than 90 seconds (long running queries for example) which would be cut off using the ha config mentioned elsewhere in this post.

The python script will let you generate a haproxy config and tell you what to add in your pg_hba.conf to make this work. try it.

Answer 2

I would use some proxy in front of postgresql servers. I advise using HAProxy to proxy tcp traffic.

Example configuration might look more or less like this:

defaults
    mode tcp
    timeout connect 30s
    timeout client  90s
    timeout server  90s

frontend PGSQL-FRONT
    mode tcp
    bind *:5432
    default_backend PGSQL-BACK

backend PGSQL-BACK
    mode tcp
    balance leastconn
    option tcp-check
    tcp-check connect port 5432
    server s1 10.0.0.101:5432 check
    server s2 10.0.0.103:5432 check backup