Your message dated Thu, 24 Nov 2016 15:15:12 +0100
with message-id <[email protected]>
and subject line Re: Bug#844053: postgresql-common: pg_upgradecluster fails
with NOT VALID constraints
has caused the Debian Bug report #844053,
regarding postgresql-common: pg_upgradecluster fails with NOT VALID constraints
to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what this
message is talking about, this may indicate a serious mail system
misconfiguration somewhere. Please contact [email protected]
immediately.)
--
844053: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=844053
Debian Bug Tracking System
Contact [email protected] with problems
--- Begin Message ---
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:
--- End Message ---
--- Begin Message ---
Re: Liam K Morland 2016-11-12
<147891405788.2881.1770045320766184695.reportbug@localhost.localdomain>
> 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.
Hi,
thanks for the detailed report, I appreciate that.
Unfortunately I have to disappoint you with respect to the problem
itself. NOT VALID constraints are meant to be used temporarily while
cleaning up old data that doesn't fit the constraint yet, they are not
meant to be present indefinitely. That's why pg_dump (which is also
used from inside pg_upgrade) doesn't dump them. Your database as it
looks now is not only not upgrade-able, it's also not backup-able by
using pg_dump. Simply said, you are running out-of-spec here.
Christoph
signature.asc
Description: PGP signature
--- End Message ---
_______________________________________________
Pkg-postgresql-public mailing list
[email protected]
http://lists.alioth.debian.org/cgi-bin/mailman/listinfo/pkg-postgresql-public
