0

There are two ways to try.

Method 1

Use JDBC connect the PostgreSQL cluster (primary, standby 2 servers only):

jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/jiradb

But failed:

The configuration of your dbconfig.xml file is incorrect (user, password, or database URL etc.)

How to connect multiple servers from jdbc?

Method 2

Use pgbouncer and repmgr

Three servers:

  • pgbouncer
  • PostgreSQL Primary (192.168.0.1) Installed repmgr, running repmgrd
  • PostgreSQL Standby (192.168.0.2) Installed repmgr, running repmgrd

Set config in pgbouncer server(pgbouncer.ini):

[databases]
postgres = host=192.168.0.1 port=5432 dbname=postgres
postgres = host=192.168.0.2 port=5432 dbname=postgres

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres
unix_socket_dir = /tmp

When primary down, repmgr can switch to standby server as primary. But pgbouncer didn't automatic connect the second db server. Why? The config setting for databases does not work. Is it the wrong usage? Or pgbouncer doesn't has this automatic switch feature?

  • Are you saying that JIRA complains if you use the URL with multiple hosts in its configuration file? Then you should file a bug with them. – Laurenz Albe Dec 6 '19 at 8:24
  • @LaurenzAlbe Yes, it's about JIRA. Is it a bug? So the connection string is right syntax? – rawmain Dec 6 '19 at 9:45
0

Compare the JDBC documentation:

To support simple connection fail-over it is possible to define multiple endpoints (host and port pairs) in the connection url separated by commas. The driver will try to once connect to each of them in order until the connection succeeds. If none succeed, a normal connection exception is thrown.

The syntax for the connection url is:

jdbc:postgresql://host1:port1,host2:port2/database

So your URL is fine. If JIRA complains about it, that would be a JIRA bug.

  • Thank you. By the way, if I choose Method 2, can't pgbounder do the automatic switch to the primary db? Is haproxy necessary for it? – rawmain Dec 7 '19 at 1:19
  • pgBouncer is not really a high-availability tool. HAProxy or PGPool can do that. – Laurenz Albe Dec 8 '19 at 19:24
  • Thank you very much! – rawmain Dec 8 '19 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.