Again, as the list software doesn't like "config" at the start of a line. Karsten On Fri, Jan 08, 2016 at 05:34:00PM +0100, Karsten Hilbert wrote: > > > (For what it's worth, I have also tried the --method=dump way > > > of using Debian's pg_upgradecluster which internally uses a > > > dump/restore cycle rather than calling pg_upgrade. That > > > failed due to ordering problems with table data vs table > > > constraints.) > > > > That seems like an independent bug. Can you provide specifics? > > Attached the log of > > pg_upgradecluster -v 9.5 9.4 main &> pg-upgrade-9_4-9_5-dump_restore.log > > and here is the function that leads to the schema having a > dependancy on table data: > > create or replace function gm.account_is_dbowner_or_staff(_account name) > returns boolean > language plpgsql > as ' > DECLARE > _is_owner boolean; > BEGIN > -- is _account member of current db group ? > -- PERFORM 1 FROM pg_auth_members > -- WHERE > -- roleid = (SELECT oid FROM pg_roles WHERE rolname = current_database()) > -- AND > -- member = (SELECT oid FROM pg_roles WHERE rolname = _account) > -- ; > -- IF FOUND THEN > -- -- should catch people on staff, gm-dbo, and postgres > -- RETURN TRUE; > -- END IF; > > -- postgres > IF _account = ''postgres'' THEN > RETURN TRUE; > END IF; > > -- on staff list > PERFORM 1 FROM dem.staff WHERE db_user = _account; > IF FOUND THEN > RETURN TRUE; > END IF; > > -- owner > SELECT pg_catalog.pg_get_userbyid(datdba) = _account INTO STRICT _is_owner FROM pg_catalog.pg_database WHERE datname = current_database(); > IF _is_owner IS TRUE THEN > RETURN TRUE; > END IF; > > -- neither > RAISE EXCEPTION > ''gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff'', _account > USING ERRCODE = ''integrity_constraint_violation'' > ; > RETURN FALSE; > END;'; > > The function is used on audit tables: > > alter table audit.audit_fields > drop constraint if exists > audit_audit_fields_sane_modified_by cascade; > > alter table audit.audit_fields > add constraint audit_audit_fields_sane_modified_by check > (gm.account_is_dbowner_or_staff(modified_by) IS TRUE) > ; > > Karsten > -- > GPG key ID E4071346 @ eu.pool.sks-keyservers.net > E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Stopping old cluster... Disabling connections to the old cluster during upgrade... Restarting old cluster with restricted connections... Creating new cluster 9.5/main ... _DEFANG_ config /etc/postgresql/9.5/main data /var/lib/postgresql/9.5/main locale de_DE.UTF-8 port 5433 Disabling connections to the new cluster during upgrade... Roles, databases, schemas, ACLs... WARNING: column "brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "atc_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "external_code_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "external_code_type_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "fake_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "pk_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "pk_data_source" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "pk_drug_component" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "src_table" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "atc_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "external_code_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "external_code_type_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "fake_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "pk_brand" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "pk_data_source" has type "unknown" DETAIL: Proceeding with relation creation anyway. WARNING: column "pk_drug_component" has type "unknown" DETAIL: Proceeding with relation creation anyway. Fixing hardcoded library paths for stored procedures... Upgrading database orthanc_db... Analyzing database orthanc_db... Fixing hardcoded library paths for stored procedures... Upgrading database gnumed_v21... pg_restore: [Archivierer (DB)] Fehler in Phase PROCESSING TOC: pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8619; 0 489459 TABLE DATA allergy_state gm-dbo pg_restore: [Archivierer (DB)] COPY fehlgeschlagen für Tabelle „allergy_state“: ERROR: gm.account_is_dbowner_or_staff(NAME): <any-doc> is neither database owner, nor <postgres>, nor on staff KONTEXT: COPY allergy_state, line 2: "13764 0 2015-11-21 18:51:18.75123+00 any-doc 2 \N \N \N 7" pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8601; 0 489164 TABLE DATA encounter gm-dbo pg_restore: [Archivierer (DB)] COPY fehlgeschlagen für Tabelle „encounter“: ERROR: gm.account_is_dbowner_or_staff(NAME): <any-doc> is neither database owner, nor <postgres>, nor on staff KONTEXT: COPY encounter, line 7: "13762 0 2015-11-21 18:51:17.663448+00 any-doc 7 15 13 9 01:00:00 \N \N 2015-11-21 18:51:17.663448+00..." pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8913; 0 513400 TABLE DATA patient gm-dbo pg_restore: [Archivierer (DB)] COPY fehlgeschlagen für Tabelle „patient“: ERROR: gm.account_is_dbowner_or_staff(NAME): <any-doc> is neither database owner, nor <postgres>, nor on staff KONTEXT: COPY patient, line 3: "13761 0 2015-11-21 18:51:17.456747+00 any-doc 3 15 \N" pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8801; 0 499101 TABLE DATA substance_intake gm-dbo pg_restore: [Archivierer (DB)] COPY fehlgeschlagen für Tabelle „substance_intake“: ERROR: null value in column "fk_episode" violates not-null constraint DETAIL: Failing row contains (13799, 0, 2015-12-30 15:58:30.455053+01, gm-dbo, 20, 2005-11-11 00:00:00+01, 4, null, enjoys an occasional pipe of Old Toby, p, 3, 8, tobacco, null, null, null, f, null, null, null, null, ?, 0). KONTEXT: COPY substance_intake, line 3: "13799 0 2015-12-30 14:58:30.455053+00 gm-dbo 20 2005-11-10 23:00:00+00 4 \N enjoys an occasional pip..." pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8553; 0 488437 TABLE DATA address gm-dbo pg_restore: [Archivierer (DB)] COPY fehlgeschlagen für Tabelle „address“: ERROR: gm.account_is_dbowner_or_staff(NAME): <any-doc> is neither database owner, nor <postgres>, nor on staff KONTEXT: COPY address, line 4: "13758 0 2015-11-21 18:51:16.565222+00 any-doc 4 11 \N asa \N \N \N" pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8563; 0 488518 TABLE DATA identity gm-dbo pg_restore: [Archivierer (DB)] COPY fehlgeschlagen für Tabelle „identity“: ERROR: gm.account_is_dbowner_or_staff(NAME): <any-doc> is neither database owner, nor <postgres>, nor on staff KONTEXT: COPY identity, line 14: "13757 1 2015-11-21 18:51:16.475927+00 any-doc 15 f m 2001-11-21 10:11:11.111+00 \N \N \N Lt. \N \N \..." pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8567; 0 488560 TABLE DATA lnk_identity2ext_id gm-dbo pg_restore: [Archivierer (DB)] COPY fehlgeschlagen für Tabelle „lnk_identity2ext_id“: ERROR: gm.account_is_dbowner_or_staff(NAME): <any-doc> is neither database owner, nor <postgres>, nor on staff KONTEXT: COPY lnk_identity2ext_id, line 2: "13759 0 2015-11-21 18:51:16.793088+00 any-doc 2 15 af 11 \N" pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 8581; 0 488713 TABLE DATA lnk_job2person gm-dbo pg_restore: [Archivierer (DB)] COPY fehlgeschlagen für Tabelle „lnk_job2person“: ERROR: gm.account_is_dbowner_or_staff(NAME): <any-doc> is neither database owner, nor <postgres>, nor on staff KONTEXT: COPY lnk_job2person, line 1: "13760 0 2015-11-21 18:51:16.874259+00 any-doc 1 15 9 \N" WARNUNG: bei Wiederherstellung ignorierte Fehler: 8 Analyzing database gnumed_v21... Fixing hardcoded library paths for stored procedures... Upgrading database gnumed_v20... Analyzing database gnumed_v20... Fixing hardcoded library paths for stored procedures... Upgrading database template1... Analyzing database template1... Fixing hardcoded library paths for stored procedures... Upgrading database postgres... Analyzing database postgres... Fixing hardcoded library paths for stored procedures... Upgrading database akonadi... Analyzing database akonadi... 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... Stopping old cluster... Disabling automatic startup of old cluster... Configuring old cluster to use a different port (5433)... Starting target cluster on the original port... Success. Please check that the upgraded cluster works. If it does, you can remove the old cluster with pg_dropcluster 9.4 main
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
