Defect #33863

Highly Available Multi-Node Redmine PostgreSQL Cluster

Added by john karippery over 1 year ago. Updated over 1 year ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:-
Target version:-
Resolution:Invalid Affected version:

Description

Hello all I need a help,

I have some issue while try to synchronize redmine database on 3 servers. i installed redmine (pgsql) in 3 servers. and i create virtual IP to access redmine using pacemaker. And I Set-Up Master-Slave Replication for PostgreSQL 9.6. synchronize is work fine until when I stop server1 (master). server2 redmine is showing authentication error. server2 and 3 only have read only access. so far I understand redmine only allow server1 to access permission. why redmine can't give access to server2 or server3?

Currently i have 3 server one master and 2 client. I installed redmine 3.3.1.stable with postgresql 9.6. and installed pacemaker in 3 servers. for synchronize database I follow the documentation. every thing is work fine until when I stop active server. server2 redmine is showing authentication error.

Redmine error when I try to login form client after connect servers.

@
Completed 500 Internal Server Error in 11ms (ActiveRecord: 3.5ms)

ActiveRecord::StatementInvalid (PG::ReadOnlySqlTransaction: ERROR: cannot execute UPDATE in a read-only transaction
: UPDATE "users" SET "last_login_on" = '2020-08-17 13:05:11.001886' WHERE "users"."type" IN ('User', 'AnonymousUser') AND "users"."id" = $1):
app/models/user.rb:238:in `try_to_login'
app/controllers/account_controller.rb:204:in `password_authentication'
app/controllers/account_controller.rb:199:in `authenticate_user'
app/controllers/account_controller.rb:40:in `login'
lib/redmine/sudo_mode.rb:63:in `sudo_mode'
@

so far I unterstand redmine only allow server1 to access permission. why redmine can't give access to server2 or server3

Below i give more information about my step so far.

pcs config

@pcs config
Cluster Name: mycluster
Corosync Nodes:
server1 server2 server3
Pacemaker Nodes:
server1 server2 server3

Resources:
Resource: MasterVip (class=ocf provider=heartbeat type=IPaddr2)
Attributes: ip=101.226.189.208 nic=lo cidr_netmask=32 iflabel=pgrepvip
Meta Attrs: target-role=Started
Operations: start interval=0s timeout=20s (MasterVip-start-interval-0s)
stop interval=0s timeout=20s (MasterVip-stop-interval-0s)
monitor interval=90s (MasterVip-monitor-interval-90s)
Resource: Apache (class=ocf provider=heartbeat type=apache)
Attributes: configfile=/etc/apache2/apache2.conf statusurl=http://localhost/server-status
Operations: start interval=0s timeout=40s (Apache-start-interval-0s)
stop interval=0s timeout=60s (Apache-stop-interval-0s)
monitor interval=1min (Apache-monitor-interval-1min)

Stonith Devices:
Fencing Levels:

Location Constraints:
Resource: Apache
Enabled on: server1 (score:INFINITY) (role: Started) (id:cli-prefer-Apache)
Ordering Constraints:
Colocation Constraints:
Apache with MasterVip (score:INFINITY) (id:colocation-Apache-MasterVip-INFINITY)
Ticket Constraints:

Alerts:
No alerts defined

Resources Defaults:
migration-threshold: 5
resource-stickiness: 10
Operations Defaults:
No defaults set

Cluster Properties:
cluster-infrastructure: corosync
cluster-name: mycluster
dc-version: 1.1.16-94ff4df
have-watchdog: false
no-quorum-policy: ignore
stonith-enabled: false

Quorum:
Options:@

master postgresql.conf

# Add settings for extensions here
listen_addresses = '*'
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'
max_wal_senders = 10
wal_keep_segments = 30
synchronous_standby_names = 'server2'
synchronous_standby_names = 'server3'
hot_standby = on

master pg_hba.conf

@ # Localhost
host replication postgres 127.0.0.1/32 md5

  1. PostgreSQL Master IP address
    host replication postgres 101.226.189.205/32 md5
  1. PostgreSQL SLave IP address
    host replication postgres 101.226.189.206/32 md5
    ho

st replication postgres 101.226.189.207/32 md5@

copy config to client from Master

pg_basebackup -h server1 -U postgres -D /var/lib/postgresql/9.6/main -X stream -P

Database connection status

postgres@oreo:/etc/postgresql/9.6/main$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid | 18174
usesysid | 10
usename | postgres
application_name | server3
client_addr | 101.226.189.207
client_hostname |
client_port | 35236
backend_start | 2020-08-17 15:56:40.687282+02
backend_xmin |
state | streaming
sent_location | 0/7005430
write_location | 0/7005430
flush_location | 0/7005430
replay_location | 0/7005430
sync_priority | 1
sync_state | sync
-[ RECORD 2 ]----+------------------------------
pid | 18175
usesysid | 10
usename | postgres
application_name | server2
client_addr | 101.226.189.206
client_hostname |
client_port | 45862
backend_start | 2020-08-17 15:56:40.717087+02
backend_xmin |
state | streaming
sent_location | 0/7005430
write_location | 0/7005430
flush_location | 0/7005430
replay_location | 0/7005430
sync_priority | 0
sync_state | async

if anyone have experiance this problem please help me.

History

#1 Updated by Go MAEDA over 1 year ago

  • Status changed from New to Closed
  • Resolution set to Invalid

I am closing this issue because it seems that the error is due to your configuration, not a problem of Redmine itself.

I recommend you to ask in Forums.

#2 Updated by Pavel Rosick√Ĺ over 1 year ago

according to your description, you're using a Single Master Replication. So once your master's node is gone, you can't write/update to the database and unfortunately, redmine won't work on a read-only database. This would require some non-trivial work to make it possible.

Multi-Master Replication might be a solution, but it seems to be more complicated. I don't have experience with the exact settings on postgres, sry.
https://www.percona.com/blog/2020/06/09/multi-master-replication-solutions-for-postgresql/

Also available in: Atom PDF