[Pkg-postgresql-public] Bug#844053: postgresql-common: pg_upgradecluster fails with NOT VALID constraints

[Liam K Morland]

Package: postgresql-common
Version: 177
Severity: important

Dear Maintainer,

I attempted to use pg_upgradecluster to update 9.5 to 9.6. One of my 
database tables contains this Check constraint:

    "role_id_not_null" CHECK (role_id IS NOT NULL) NOT VALID

(A NOT VALID constraint is one is will be enforced for INSERT and 
UPDATE, but existing data may not meet the constraint.)

During the upgrade, I got this error message:

pg_restore: [archiver (db)] COPY failed for table "scout_registration": ERROR:  new row for relation "scout_registration" violates check constraint "role_id_not_null"

After the upgrade, the table in question was empty. The error message 
was in the middle of the output. A novice user might not notice it and 
lose data because it is not obvious that the upgrade failed.

I suppose that the problem is that pg_restore enforces the constraint. 
To make NOT VALID constraints work, there needs to be an import method 
that does not enforce the constraint.

The command and its output:

$ sudo pg_upgradecluster 9.5 main
Stopping old cluster...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for stop operation
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Redirecting start request to systemctl
Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_CA.UTF-8
  socket /var/run/postgresql
  port   5433
Disabling connections to the new cluster during upgrade...
Redirecting start request to systemctl
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database drupal8...
Analyzing database drupal8...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Fixing hardcoded library paths for stored procedures...
Upgrading database lkmorlan...
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2288; 0 17795 TABLE DATA scout_registration lkmorlan
pg_restore: [archiver (db)] COPY failed for table "scout_registration": ERROR:  new row for relation "scout_registration" violates check constraint "role_id_not_null"
DETAIL:  Failing row contains (94, 2004, null, 21W-T).
CONTEXT:  COPY scout_registration, line 16: "94 2004    \N      21W-T"
WARNING: errors ignored on restore: 1
Analyzing database lkmorlan...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Stopping target cluster...
Redirecting stop request to systemctl
Stopping old cluster...
Redirecting stop request to systemctl
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Redirecting start request to systemctl
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

  pg_dropcluster 9.5 main


-- System Information:
Debian Release: stretch/sid
  APT prefers testing
  APT policy: (500, 'testing'), (500, 'stable')
Architecture: i386 (i686)

Kernel: Linux 4.7.0-1-686-pae (SMP w/4 CPU cores)
Locale: LANG=en_CA.UTF-8, LC_CTYPE=en_CA.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: systemd (via /run/systemd/system)

Versions of packages postgresql-common depends on:
ii  adduser                   3.115
ii  debconf [debconf-2.0]     1.5.59
ii  init-system-helpers       1.45
ii  lsb-base                  9.20161101
ii  postgresql-client-common  177
ii  procps                    2:3.3.12-2
ii  ssl-cert                  1.0.38
ii  ucf                       3.0036

Versions of packages postgresql-common recommends:
ii  logrotate  3.8.7-2

postgresql-common suggests no packages.

-- Configuration Files:
/etc/postgresql-common/createcluster.conf changed:
ssl = on
cluster_name = '%v/%c'
stats_temp_directory = '/var/run/postgresql/%v-%c.pg_stat_tmp'
log_line_prefix = '%%t [%%p-%%l] %%q%%u@%%d '


-- debconf information:
* postgresql-common/ssl: true
* postgresql-common/obsolete-major:
  postgresql-common/catversion-bump: