1057692 - puppetagainX.db.scl3: Postgres replication change made to live config but not committed to Puppet

Status: RESOLVED FIXED

(Infrastructure & Operations :: Infrastructure: Other, task)

Description

[MOC Nagios API]

Automated alert report from nagios1.private.scl3.mozilla.com:

Hostname: puppetagain1.db.scl3.mozilla.com
Service:  Disk - All
State:    WARNING
Output:   DISK WARNING - free space: / 72097 MB (10% inode=99%):

Runbook:  http://m.allizom.org/Disk+-+All

Comment 1

[david garvey:dgarvey]

Humm,

It looks like mysql has been migrated to postgres? Perhaps /var/log/mysql can be removed.


[[e-mail]3 log]# du -sh * | grep M
2.6M	hp-health
2.2M	messages
3.3M	messages-20140727
2.5M	messages-20140803
2.5M	messages-20140810
2.5M	messages-20140817
756M	mysql
204M	postgresql
17M	sa
1.1M	secure-20140727
1.1M	secure-20140803
1.1M	secure-20140810
1.1M	secure-20140817
1.1M	wtmp-20140801
[[e-mail]3 log]# cd mysql/
[[e-mail]3 mysql]# ls
mysql.err  mysql-slow.log  mysql-slow.log.old
[[e-mail]3 mysql]# ls -lrt
total 773440
-rw-rw---- 1 mysql mysql     13982 Jun  6  2013 mysql-slow.log.old
-rw-rw---- 1 mysql mysql   2485165 Apr 25 14:35 mysql-slow.log
-rw-rw---- 1 mysql root  789491756 Apr 25 16:40 mysql.err
[[e-mail]3 mysql]# ps -efww | grep mysql
root     23555 22037  0 07:28 pts/0    00:00:00 grep mysql
[[e-mail]3 mysql]# ps -efww | grep postgres
postgres  1601 23137  0 May29 ?        02:48:39 postgres: wal sender process replicator 10.22.70.108(55381) streaming BE/DFAF7890
postgres  5551 23137  0 May29 ?        00:04:23 postgres: wal writer process                                    
postgres  5552 23137  0 May29 ?        00:03:00 postgres: autovacuum launcher process                           
postgres  5553 23137  0 May29 ?        00:23:08 postgres: archiver process   failed on 0000000100000032000000F9 
postgres 19476 23137  0 06:41 ?        00:00:06 postgres: foreman_user puppetdash_pvt_build_mozilla_org 10.22.70.209(2065) idle
postgres 21346 23137  0 07:00 ?        00:00:06 postgres: foreman_user puppetdash_pvt_build_mozilla_org 10.22.70.209(40111) idle
postgres 23137     1  0 May20 ?        00:03:31 /usr/pgsql-9.2/bin/postmaster -p 5432 -D /var/lib/pgsql/9.2/data
postgres 23139 23137  0 May20 ?        00:00:50 postgres: logger process                                        
postgres 23142 23137  0 May20 ?        01:57:51 postgres: checkpointer process                                  
postgres 23143 23137  0 May20 ?        00:23:55 postgres: writer process                                        
postgres 23146 23137  0 May20 ?        00:32:44 postgres: stats collector process                               
root     23559 22037  0 07:28 pts/0    00:00:00 grep postgres
[[e-mail]3 mysql]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda3       676G  571G   71G  90% /
tmpfs           1.9G     0  1.9G   0% /dev/shm
/dev/sda1       993M  109M  834M  12% /boot
[[e-mail]3 mysql]#

Comment 2

[david garvey:dgarvey]

Created a bug for buildduty. please see bug 1057693.

Comment 3

[MOC Nagios API]

Automated alert acknowledgement: (dgarvey)bug 1057692

Comment 4

[Amy Rich [:arr] [:arich]]

This is a database server and should be handled by the DBA team, not releng. If it's the application that's the issue, then this should be handled by the systems team since they run the application (foreman).

Comment 6

[Amy Rich [:arr] [:arich]]

If there is documentation which directed you to open a bug with releng, please update that documentation so that the service owner is the systems group and the database owner is the DBA group.

Comment 7

[Brandon Johnson [:cyborgshadow]]

So the issue here is that the archive has failed and the pg_xlog file is heavily bloated.

The archive is failing because it has a weird/malformed archive command (or at least one I don't understand what "::wal/" means).

postgres: archiver process   failed on 0000000100000032000000F9

from the postgresql.conf:

  archive_command = 'rsync -aq %p ::wal/%f'

It won't be an issue over the weekend to let this go, but I'm going to speak with Matt on Monday.

Comment 8

[Matt Pressman [:mpressman]]

streaming replication is still up and running so the WAL wouldn't have been used anyway. It's safe to delete the WAL and reset the archive command to something more appropriate. The missing param appears to be the address of the host to ship the WAL to

Comment 9

[Matt Pressman [:mpressman]]

WAL is being shipped now. I manually set the changes in the postgresql.conf and reloaded the configs. Disk space is going down now that it's moving those over

Comment 10

[Matt Pressman [:mpressman]]

The puppet config for the host has wal_replica_addr => ['10.22.70.107'],

Two things I notice, but I'm not a puppet guy so perhaps someone else can fix this. The first is that the host it's shipping to is 10.22.70.108 and second, as Brandon pointed out, the configs for the param archive_command isn't getting an address

Comment 11

[Brandon Johnson [:cyborgshadow]]

DB portion fixed, passing this back to releng so they can address the missing/malformed config Matt confirmed in comment 10.

Comment 12

[Amy Rich [:arr] [:arich]]

Once again, that's not releng, that's the systems group; they run foreman and it's under IT puppet.

Comment 13

[:Atoll]

(In reply to Brandon Johnson [:cyborgshadow] from comment #11)
> DB portion fixed, passing this back to releng so they can address the
> missing/malformed config Matt confirmed in comment 10.

:dustin, do you need us to declare an outage window for the releng puppetdashboard for this change? I *presume* it requires some sort of DB restart but I honestly have no idea yet (see next question).

:mpressman, when we fix this in puppet, do we need to do anything other than simply run puppet on the host - like, for instance, restart postgres on either the primary or standby, or do some sort of postgres verification commands, or so forth? I'm not qualified to debug this fix if it breaks anything, so if :dustin says we can do this any time, I'd prefer to ask y'all to handle fixing it (if that's okay).

Comment 14

[Dustin J. Mitchell [:dustin] (he/him)]

No need for an outage window.

Comment 15

[Matt Pressman [:mpressman]]

When you add it, there is no need to do anything. The server is already running with the correct param. When you do run puppet, it won't trigger the server. To verify, just make sure postgresql.conf has the archive_command that looks like this:

archive_command = 'rsync -aq %p 10.22.70.108::wal/%f'

Comment 16

[Matt Pressman [:mpressman]]

WAL has now cleared on master:

[postgres@puppetagain1 data]$ df -hT
Filesystem     Type   Size  Used Avail Use% Mounted on
/dev/sda3      ext4   676G   16G  626G   3% /
tmpfs          tmpfs  1.9G     0  1.9G   0% /dev/shm
/dev/sda1      ext4   993M  109M  834M  12% /boot

Comment 17

[Sheeri Cabral [:sheeri]]

Passing this back to :atoll - comment 13 suggests that a puppet change was needed to close out this bug. Feel free to close if it's already done, but I didn't want to close it just in case the puppet change hadn't been done yet.

Comment 18

[:Atoll]

Have not made any changes to the database module for this host yet, sorry.

Comment 19

[:Atoll]

Hi, I'm having some trouble working out the precise change to make in puppet here.

(In reply to Matt Pressman [:mpressman] from comment #10)
> The puppet config for the host has wal_replica_addr => ['10.22.70.107'],
> 
> Two things I notice, but I'm not a puppet guy so perhaps someone else can
> fix this. The first is that the host it's shipping to is 10.22.70.108 and

So, the puppet config for puppetagain*2*.db has the wal_replica_addr => 107 line, but this bug appears to be about puppetagain*1*.db. Is this still the correct change to make?

> second, as Brandon pointed out, the configs for the param archive_command
> isn't getting an address

modules/postgres/templates/postgresql.conf.erb:<% if @archive_command_method == 'rsync' %>
modules/postgres/templates/postgresql.conf.erb:archive_command = 'rsync -aq %p <%= @wal_replica_addr %>::wal/%f'

I could diagnose this further once we work out the correct alteration to manifests/nodes/database.pp here, if it's still an issue.

Comment 20

[Matt Pressman [:mpressman]]

I'm not sure if this still needs my attention, but just to provide a quick answer, there are two methods of replication, both of which are being implemented. The first and primary is streaming and the second is log shipping which is used as a backup should streaming fail. For streaming, the replica connects to the master to get the streaming logs, so it needs to know the master's ip to connect to. This value is stored in a file called recovery.conf, not in the postgresql.conf. The archive_command is for for the second method of log shipping and is located in the postgresql.conf, but it doesn't have an ip in the command itself. I see this was setup using rsync as the log shipping method. There is a more robust script that can be used to ship the logs as it does a much better job of ensuring that the log file is shipped and received on the replica.

Anyway, this is probably moot, but if you have any questions, please let me know

Comment 21

[:Atoll]

:mpressman, can you audit this cluster's replication configuration and confirm that, as currently deployed via puppet and live on the servers, it is satisfactory and you wish no further changes to it?

In comments 8, 9, and 10 you indicated that you had to make *manual* (non-puppeted) changes to replication on this cluster, and so puppetizing whatever you did at the time is the entire remaining work here.

I can't help you do this - my efforts to try and understand what needed doing failed, and since I set the needinfo a few months back, I've transferred to another department. So you'll need to work this out with whoever maintains the DBops team's Puppet code - and that is not the Systems team.

Comment 22

[Sheeri Cabral [:sheeri]]

In the interim, how we do replication has changed. Double-checked, and puppet has the appropriate configuration based on the current settings.