Project

General

Profile

Actions

Enhancement #9752

open

Document stopping process before Postgres restore maintenance to avoid primary key constrain get corrupted

Added by Nicolas CHARLES over 7 years ago. Updated 6 months ago.

Status:
New
Priority:
1
Category:
Web - Compliance & node report
UX impact:
Suggestion strength:
User visibility:
Infrequent - complex configurations | third party integrations
Effort required:
Very Small
Name check:
Fix check:
Regression:

Description

After some time, we understood that the problem seems to be that some primary key constraints get dropped during upgrade. So we can have a totally wrong schema!

Logs contains following error:

"[2016-11-30 13:59:04] ERROR com.normation.rudder.batch.AutomaticReportLogger$LAAutomaticReportLogger - could not fetch last id, don't log anything, wait next run, cause is Error when parsing property 'reportLoggerLastId'" 

and indeed, entry is duplicated in database:

select value from rudderproperties where name='reportLoggerLastId';
value
-----------
153729188
153729188

It occured during an upgrade, with the upgrade script that failed due to invalid db name used

Actions #1

Updated by François ARMAND over 7 years ago

How can it happens, the property name has an unicity constraint on it?

Actions #2

Updated by Alexandre Anriot over 7 years ago

I don't see any such constraint. I did this twice a few minutes ago:

rudder=# INSERT INTO rudderproperties VALUES ('reportLoggerLastId', 161072834);
INSERT 0 1

and got:

rudder=# select * from rudderproperties where name='reportLoggerLastId';
        name        |   value
--------------------+-----------
 reportLoggerLastId | 161072834
 reportLoggerLastId | 161072834
 reportLoggerLastId | 161072834
(3 rows)

Could it be a DB issue on our side?

François ARMAND wrote:

How can it happens, the property name has an unicity constraint on it?

Actions #3

Updated by Vincent MEMBRÉ over 7 years ago

  • Target version changed from 4.0.1 to 4.0.2
Actions #4

Updated by François ARMAND over 7 years ago

hum.
I just checked, and there is something strange.

On a fresh installation, I have

rudder=> \d rudderproperties;
Table "public.rudderproperties" 
 Column | Type | Modifiers
--------+------+-----------
 name   | text | not null
 value  | text |
Indexes:
    "rudderproperties_pkey" PRIMARY KEY, btree (name)

And trying to insert the same properties fails on pkey violation constraint.

But on the schema definition, we do have the primary key, and we have it since the addition of the table in ca2380af1529a0a717df88c6c57de54e78f31e4e

So I don't get it. You can correct the problem with the following table alteration:

ALTER TABLE RudderProperties ADD PRIMARY KEY (name);

But I don't understand why there is no pk declaration.

For information, could you provide the result of the following query, to see if other pk constraint are missing:

select constraint_name from information_schema.table_constraints where constraint_type = 'PRIMARY KEY');

Actions #5

Updated by Alexandre Anriot over 7 years ago

François,

It's probably a local issue, as I have 23 primary keys on the database of another Rudder instance, VS 21 pkeys on this database.

I just added the one you mentionned, as well as statusupdate_pkey.

I guess that you can now close the ticket.

Thanks a lot.

François ARMAND wrote:

hum.
I just checked, and there is something strange.

On a fresh installation, I have
[...]

And trying to insert the same properties fails on pkey violation constraint.

But on the schema definition, we do have the primary key, and we have it since the addition of the table in ca2380af1529a0a717df88c6c57de54e78f31e4e

So I don't get it. You can correct the problem with the following table alteration:

[...]

But I don't understand why there is no pk declaration.

For information, could you provide the result of the following query, to see if other pk constraint are missing:
[...]

Actions #6

Updated by François ARMAND over 7 years ago

  • Status changed from New to Discussion
  • Assignee set to Nicolas CHARLES

Wow, this is extremelly strange and worrying. I wasn't aware that postgres could create a schema with some missing constraints (with possibly grave consequences, as it is seen here).

Not sure if we should do something ? Nicolas, any insight on that?

Actions #7

Updated by Nicolas CHARLES over 7 years ago

This is weird indeed
my guess: table was restored or copied, and constraint was dropped during restore (possibly, concurrent writing of data prevented constraint to apply)

Actions #8

Updated by Alexandre Anriot over 7 years ago

Several changes have been done when the platforms were setup, it's indeed probably something like this.

I think that Rudder is not guilty.

Nicolas CHARLES wrote:

This is weird indeed
my guess: table was restored or copied, and constraint was dropped during restore (possibly, concurrent writing of data prevented constraint to apply)

Actions #9

Updated by François ARMAND over 7 years ago

  • Status changed from Discussion to Rejected

OK, thanks for the insight. So as asked, I'm closing the issue.

Actions #10

Updated by François ARMAND about 5 years ago

  • Status changed from Rejected to New
  • Target version changed from 4.0.2 to 5.0.9
  • Severity set to Major - prevents use of part of Rudder | no simple workaround
  • User visibility set to Operational - other Techniques | Rudder settings | Plugins
  • Priority set to 43

I'm reopening that issue because we get the same bug with:

- backup postgres
- upgrade Unbuntu 16.04 => 18.04
- upgrade postgres 9.6 => 10.5
- restore postgres backup
- upgrade Rudder.

Actions #11

Updated by Vincent MEMBRÉ about 5 years ago

  • Target version changed from 5.0.9 to 5.0.10
Actions #12

Updated by Nicolas CHARLES about 5 years ago

Hypothesis is that Rudder restarted during the restore (stopping rudder doesn't disable it - rudder agent check is still run every 5 minutes), causing Rudder to compute the compliance and writing in database
once data got inserted by restore, constraint is broken

Actions #13

Updated by Vincent MEMBRÉ almost 5 years ago

  • Target version changed from 5.0.10 to 5.0.11
Actions #14

Updated by Vincent MEMBRÉ almost 5 years ago

  • Target version changed from 5.0.11 to 5.0.12
Actions #15

Updated by Vincent MEMBRÉ almost 5 years ago

  • Target version changed from 5.0.12 to 5.0.13
Actions #16

Updated by Vincent MEMBRÉ over 4 years ago

  • Target version changed from 5.0.13 to 5.0.14
Actions #17

Updated by Vincent MEMBRÉ over 4 years ago

  • Target version changed from 5.0.14 to 5.0.15
Actions #18

Updated by François ARMAND over 4 years ago

  • Subject changed from Compliance not computed because of duplicate entry in rudderproperties to Postegres primary key constrain get corrupted during rudder upgrade
  • Description updated (diff)
Actions #19

Updated by Vincent MEMBRÉ over 4 years ago

  • Target version changed from 5.0.15 to 5.0.16
Actions #20

Updated by Alexis Mousset about 4 years ago

  • Target version changed from 5.0.16 to 5.0.17
  • Priority changed from 43 to 44
Actions #21

Updated by Vincent MEMBRÉ almost 4 years ago

  • Target version changed from 5.0.17 to 5.0.18
  • Priority changed from 44 to 45
Actions #22

Updated by Vincent MEMBRÉ over 3 years ago

  • Target version changed from 5.0.18 to 5.0.19
  • Priority changed from 45 to 46
Actions #23

Updated by Vincent MEMBRÉ over 3 years ago

  • Target version changed from 5.0.19 to 5.0.20
Actions #24

Updated by Vincent MEMBRÉ over 3 years ago

  • Target version changed from 5.0.20 to 797
  • Priority changed from 46 to 47
Actions #25

Updated by Alexis Mousset over 3 years ago

  • Subject changed from Postegres primary key constrain get corrupted during rudder upgrade to Postgres primary key constrain get corrupted during rudder upgrade
  • Priority changed from 47 to 48
Actions #26

Updated by Benoît PECCATTE almost 3 years ago

  • Target version changed from 797 to 6.1.14
  • Priority changed from 48 to 50
Actions #27

Updated by Vincent MEMBRÉ almost 3 years ago

  • Target version changed from 6.1.14 to 6.1.15
Actions #28

Updated by Vincent MEMBRÉ over 2 years ago

  • Target version changed from 6.1.15 to 6.1.16
Actions #29

Updated by Vincent MEMBRÉ over 2 years ago

  • Target version changed from 6.1.16 to 6.1.17
  • Priority changed from 50 to 51
Actions #30

Updated by Vincent MEMBRÉ over 2 years ago

  • Target version changed from 6.1.17 to 6.1.18
  • Priority changed from 51 to 52
Actions #31

Updated by Vincent MEMBRÉ over 2 years ago

  • Target version changed from 6.1.18 to 6.1.19
Actions #32

Updated by François ARMAND about 2 years ago

  • Subject changed from Postgres primary key constrain get corrupted during rudder upgrade to Document stopping process before Postgres restore maintenance to avoir primary key constrain get corrupted
  • User visibility changed from Operational - other Techniques | Rudder settings | Plugins to Infrequent - complex configurations | third party integrations
  • Effort required set to Very Small
  • Priority changed from 52 to 70

So, we believe that upgrade are now correctly done, but we need to document the fact that if you want to restore things in postgres, all services that may interact with it (webapp, relayd, and rudder agent that restarts them) need to be disabled.

Actions #33

Updated by François ARMAND about 2 years ago

  • Tracker changed from Bug to Enhancement
  • Subject changed from Document stopping process before Postgres restore maintenance to avoir primary key constrain get corrupted to Document stopping process before Postgres restore maintenance to avoid primary key constrain get corrupted
  • Severity deleted (Major - prevents use of part of Rudder | no simple workaround)
  • Priority deleted (70)
Actions #34

Updated by Vincent MEMBRÉ about 2 years ago

  • Target version changed from 6.1.19 to 6.1.20
Actions #35

Updated by Vincent MEMBRÉ almost 2 years ago

  • Target version changed from 6.1.20 to 6.1.21
Actions #36

Updated by Vincent MEMBRÉ almost 2 years ago

  • Target version changed from 6.1.21 to old 6.1 issues to relocate
Actions #37

Updated by Alexis Mousset about 1 year ago

  • Target version changed from old 6.1 issues to relocate to 7.2.5
Actions #38

Updated by Vincent MEMBRÉ about 1 year ago

  • Target version changed from 7.2.5 to 7.2.6
Actions #39

Updated by Vincent MEMBRÉ 12 months ago

  • Target version changed from 7.2.6 to 7.2.7
Actions #40

Updated by Vincent MEMBRÉ 11 months ago

  • Target version changed from 7.2.7 to 7.2.8
Actions #41

Updated by Vincent MEMBRÉ 10 months ago

  • Target version changed from 7.2.8 to 7.2.9
Actions #42

Updated by Vincent MEMBRÉ 9 months ago

  • Target version changed from 7.2.9 to 7.2.10
Actions #43

Updated by Alexis Mousset 8 months ago

  • Target version changed from 7.2.10 to 7.2.11
Actions #44

Updated by Vincent MEMBRÉ 7 months ago

  • Target version changed from 7.2.11 to 1046
Actions #45

Updated by Alexis Mousset 6 months ago

  • Target version changed from 1046 to Ideas (not version specific)
Actions

Also available in: Atom PDF