pg_upgrade schlägt fehlt (pg_clog/pg_multixact)

Fragen und Probleme zu Verwaltungstools.
phppgadmin und pgadmin bitte in die entsprechenden Foren!
Antworten
devnull
Neuer Benutzer
Beiträge: 4
Registriert: Mo 24. Okt 2016, 07:20

pg_upgrade schlägt fehlt (pg_clog/pg_multixact)

Beitrag von devnull » Mo 24. Okt 2016, 07:34

Hallo zusammen,

das Upgrade von Version 9.5 auf 9.6 schlägt fehl, mit der Begründung die Dateien 'pg_clog/0000' und 'pg/multixact/offsets/0000' würden fehlen.
Sobald beide Versionen frisch installiert sind, existieren beide Dateien noch.

Während der Ausführung von pg_upgrade verschwinden beide Dateien und pg_upgrade beschwert sich:

Code: Alles auswählen

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for roles starting with 'pg_'                      ok
Creating dump of global objects                             ok
Creating dump of database schemas
  SomeDatabase
  postgres
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok

*failure*
Consult the last few lines of "pg_upgrade_server_start.log" or "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 50432?
could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 50432?

could not connect to new postmaster started with the command:
"C:\Program Files\PostgreSQL\9.6\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.6\data" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off " start
In der pg_upgrade_server bzw. pg_upgrade_server_start.log Datei steht dann die Fehlermeldung, dass 'pg_clog/0000' bzw. 'pg_multixact/offsets/0000' nicht gefunden wurden.

Das pg_upgrade wurde mit folgenden Parameter ausgeführt:

Code: Alles auswählen

"C:\Program Files\PostgreSQL\9.6\bin\pg_upgrade.exe" -U postgres -d "C:\Program Files\PostgreSQL\9.5\data" -D "C:\Program Files\PostgreSQL\9.6\data" -b "C:\Program Files\PostgreSQL\9.5\bin" -B "C:\Program Files\PostgreSQL\9.6\bin"
Deshalb wundere ich mich jetzt, wo der Fehler liegt. Müssen noch weitere Dinge beachtet werden?

Grüße,
devnull

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: pg_upgrade schlägt fehlt (pg_clog/pg_multixact)

Beitrag von castorp » Mo 24. Okt 2016, 07:59

Das hier:
connection to database failed: could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 50432?
could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 50432?
lässt eher darauf schliessen, dass mit der Konfiguration von pg_hba.conf etwas nicht stimmt.

Ich finde es am einfachsten für die Dauer des pg_upgrade Verbindungen von localhost einfach auf "trust" zu setzen (für beide Instanzen: die alte und die neue), dann kann sich pg_upgrade ohne Probleme verbinden.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

devnull
Neuer Benutzer
Beiträge: 4
Registriert: Mo 24. Okt 2016, 07:20

Re: pg_upgrade schlägt fehlt (pg_clog/pg_multixact)

Beitrag von devnull » Mo 24. Okt 2016, 08:20

In beiden Konfigurationen (pg_hba.conf) werden folgende Zeilen an den Anfang der Datei geschrieben, um die Verbindung auf "trust" zu setzen:

Code: Alles auswählen

host      all     all     127.0.0.1/32    trust
host      all     all     ::1/128         trust
In der Firewall werden ebenfalls folgende Ports freigeschaltet:

Code: Alles auswählen

netsh.sh advfirewall firewall add rule name="pg_in" dir=in action=allow protocol=TCP localport=50432
netsh.sh advfirewall firewall add rule name="pg_out" dir=out action=allow protocol=TCP localport=50432

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: pg_upgrade schlägt fehlt (pg_clog/pg_multixact)

Beitrag von castorp » Mo 24. Okt 2016, 09:32

Hmm, komisch.

Kannst Du mal den exakten Inhalt der "pg_upgrade_server_start.log" hier anfügen?

Ich hatte mit pg_upgrade ein Problem mit den .fsm Dateien unter Windows (war ein Bug der in 9.6.1 behoben sein wird) - aber das scheint was anderes zu sein.

Ich denke, am schnellsten kriegst Du eine Antwort wenn Du das auch nochmal auf der -general Mailing Liste fragst: https://www.postgresql.org/list/
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

devnull
Neuer Benutzer
Beiträge: 4
Registriert: Mo 24. Okt 2016, 07:20

Re: pg_upgrade schlägt fehlt (pg_clog/pg_multixact)

Beitrag von devnull » Mo 24. Okt 2016, 14:35

Hier die Logausgaben

'pg_upgrade_server_start.log':

Code: Alles auswählen

-----------------------------------------------------------------
  pg_upgrade run on Mon Oct 24 14:23:27 2016
-----------------------------------------------------------------
-----------------------------------------------------------------
  pg_upgrade run on Mon Oct 24 14:23:27 2016
-----------------------------------------------------------------
command: "C:\Program Files\PostgreSQL\9.5\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.5\data" -o "-p 50432 -b " start >> "pg_upgrade_server_start.log" 2>&1
warte auf Start des Servers.... fertig
Server gestartet

command: "C:\Program Files\PostgreSQL\9.6\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.6\data" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off " start >> "pg_upgrade_server_start.log" 2>&1
warte auf Start des Servers.... fertig
Server gestartet

command: "C:\Program Files\PostgreSQL\9.6\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.6\data" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off " start >> "pg_upgrade_server_start.log" 2>&1
warte auf Start des Servers.... Warten beendet
pg_ctl: konnte Server nicht starten
Prüfen Sie die Logausgabe.
'pg_upgrade_server.log':

Code: Alles auswählen

-----------------------------------------------------------------
  pg_upgrade run on Mon Oct 24 14:23:27 2016
-----------------------------------------------------------------
-----------------------------------------------------------------
  pg_upgrade run on Mon Oct 24 14:23:27 2016
-----------------------------------------------------------------

2016-10-24 14:23:27 CEST [4276]: [1-1] user=,db=,app=,client= LOG:  redirecting log output to logging collector process
2016-10-24 14:23:27 CEST [4276]: [2-1] user=,db=,app=,client= HINT:  Future log output will appear in directory "pg_log".
        1 Datei(en) kopiert.
        1 Datei(en) kopiert.
2016-10-24 14:23:34 CEST LOG:  Logausgabe wird an Logsammelprozess umgeleitet
2016-10-24 14:23:34 CEST TIPP:  Die weitere Logausgabe wird im Verzeichnis »pg_log« erscheinen.
2016-10-24 14:23:39 CEST LOG:  Logausgabe wird an Logsammelprozess umgeleitet
2016-10-24 14:23:39 CEST TIPP:  Die weitere Logausgabe wird im Verzeichnis »pg_log« erscheinen.
'postgresql-2016-10-24_142339.log':

Code: Alles auswählen

2016-10-24 14:23:39 CEST LOG:  Datenbanksystem wurde am 2016-10-24 14:23:39 CEST heruntergefahren
2016-10-24 14:23:39 CEST FATAL:  konnte auf den Status von Transaktion 0 nicht zugreifen
2016-10-24 14:23:39 CEST DETAIL:  Konnte Datei »pg_multixact/offsets/0000« nicht öffnen: No such file or directory.
2016-10-24 14:23:39 CEST LOG:  Startprozess (PID 2140) beendete mit Status 1
2016-10-24 14:23:39 CEST LOG:  Serverstart abgebrochen wegen Startprozessfehler
2016-10-24 14:23:39 CEST LOG:  Datenbanksystem ist heruntergefahren
'pg_upgrade_internal.log':

Code: Alles auswählen

Running in verbose mode

-----------------------------------------------------------------
  pg_upgrade run on Mon Oct 24 14:23:27 2016
-----------------------------------------------------------------

Running in verbose mode

-----------------------------------------------------------------
  pg_upgrade run on Mon Oct 24 14:23:27 2016
-----------------------------------------------------------------

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Current pg_control values:

pg_control version number:            942
Catalog version number:               201510051
Database system identifier:           6239923215899011444
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/2752
Latest checkpoint's NextOID:          22252
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        609
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0


Values to be changed:

First log segment after reset:        000000010000000000000003
Current pg_control values:

pg_control version number:            960
Catalog version number:               201608131
Database system identifier:           6345005527417059008
Latest checkpoint's TimeLineID:       1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:544
Latest checkpoint's NextOID:          16393
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        536
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0


Values to be changed:

First log segment after reset:        000000010000000000000002
"C:\Program Files\PostgreSQL\9.5\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.5\data" -o "-p 50432 -b " start >> "pg_upgrade_server_start.log" 2>&1
executing: SELECT d.oid, d.datname, d.encoding, d.datcollate, d.datctype, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM pg_catalog.pg_database d  LEFT OUTER JOIN pg_catalog.pg_tablespace t  ON d.dattablespace = t.oid WHERE d.datallowconn = true ORDER BY 2
executing: WITH regular_heap (reloid, indtable, toastheap) AS (   SELECT c.oid, 0::oid, 0::oid   FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n          ON c.relnamespace = n.oid   WHERE relkind IN ('r', 'm', 'S') AND     ((n.nspname !~ '^pg_temp_' AND       n.nspname !~ '^pg_toast_temp_' AND       n.nspname NOT IN ('pg_catalog', 'information_schema',                         'binary_upgrade', 'pg_toast') AND       c.oid >= 16384::pg_catalog.oid) OR      (n.nspname = 'pg_catalog' AND       relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))),   toast_heap (reloid, indtable, toastheap) AS (   SELECT c.reltoastrelid, 0::oid, c.oid   FROM regular_heap JOIN pg_catalog.pg_class c       ON regular_heap.reloid = c.oid   WHERE c.reltoastrelid != 0),   all_index (reloid, indtable, toastheap) AS (   SELECT indexrelid, indrelid, 0::oid   FROM pg_catalog.pg_index   WHERE indisvalid AND indisready     AND indrelid IN         (SELECT reloid FROM regular_heap          UNION ALL          SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname,   c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap       UNION ALL       SELECT * FROM toast_heap       UNION ALL       SELECT * FROM all_index) all_rels   JOIN pg_catalog.pg_class c       ON all_rels.reloid = c.oid   JOIN pg_catalog.pg_namespace n      ON c.relnamespace = n.oid   LEFT OUTER JOIN pg_catalog.pg_tablespace t      ON c.reltablespace = t.oid ORDER BY 1;
executing: WITH regular_heap (reloid, indtable, toastheap) AS (   SELECT c.oid, 0::oid, 0::oid   FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n          ON c.relnamespace = n.oid   WHERE relkind IN ('r', 'm', 'S') AND     ((n.nspname !~ '^pg_temp_' AND       n.nspname !~ '^pg_toast_temp_' AND       n.nspname NOT IN ('pg_catalog', 'information_schema',                         'binary_upgrade', 'pg_toast') AND       c.oid >= 16384::pg_catalog.oid) OR      (n.nspname = 'pg_catalog' AND       relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))),   toast_heap (reloid, indtable, toastheap) AS (   SELECT c.reltoastrelid, 0::oid, c.oid   FROM regular_heap JOIN pg_catalog.pg_class c       ON regular_heap.reloid = c.oid   WHERE c.reltoastrelid != 0),   all_index (reloid, indtable, toastheap) AS (   SELECT indexrelid, indrelid, 0::oid   FROM pg_catalog.pg_index   WHERE indisvalid AND indisready     AND indrelid IN         (SELECT reloid FROM regular_heap          UNION ALL          SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname,   c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap       UNION ALL       SELECT * FROM toast_heap       UNION ALL       SELECT * FROM all_index) all_rels   JOIN pg_catalog.pg_class c       ON all_rels.reloid = c.oid   JOIN pg_catalog.pg_namespace n      ON c.relnamespace = n.oid   LEFT OUTER JOIN pg_catalog.pg_tablespace t      ON c.reltablespace = t.oid ORDER BY 1;
executing: WITH regular_heap (reloid, indtable, toastheap) AS (   SELECT c.oid, 0::oid, 0::oid   FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n          ON c.relnamespace = n.oid   WHERE relkind IN ('r', 'm', 'S') AND     ((n.nspname !~ '^pg_temp_' AND       n.nspname !~ '^pg_toast_temp_' AND       n.nspname NOT IN ('pg_catalog', 'information_schema',                         'binary_upgrade', 'pg_toast') AND       c.oid >= 16384::pg_catalog.oid) OR      (n.nspname = 'pg_catalog' AND       relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))),   toast_heap (reloid, indtable, toastheap) AS (   SELECT c.reltoastrelid, 0::oid, c.oid   FROM regular_heap JOIN pg_catalog.pg_class c       ON regular_heap.reloid = c.oid   WHERE c.reltoastrelid != 0),   all_index (reloid, indtable, toastheap) AS (   SELECT indexrelid, indrelid, 0::oid   FROM pg_catalog.pg_index   WHERE indisvalid AND indisready     AND indrelid IN         (SELECT reloid FROM regular_heap          UNION ALL          SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname,   c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap       UNION ALL       SELECT * FROM toast_heap       UNION ALL       SELECT * FROM all_index) all_rels   JOIN pg_catalog.pg_class c       ON all_rels.reloid = c.oid   JOIN pg_catalog.pg_namespace n      ON c.relnamespace = n.oid   LEFT OUTER JOIN pg_catalog.pg_tablespace t      ON c.reltablespace = t.oid ORDER BY 1;

old databases:
Database: SomeDatabase
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace: 
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace: 
relname: SomeSchema.SomeTable: reloid: 16403 reltblspace: 
...


Database: postgres
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace: 
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace: 


Database: template1
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace: 
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace: 


executing: SELECT	pg_catalog.pg_tablespace_location(oid) AS spclocation FROM	pg_catalog.pg_tablespace WHERE	spcname != 'pg_default' AND 		spcname != 'pg_global'
executing: SELECT DISTINCT probin FROM	pg_catalog.pg_proc WHERE	prolang = 13 /* C */ AND probin IS NOT NULL AND oid >= 16384;
executing: SELECT DISTINCT probin FROM	pg_catalog.pg_proc WHERE	prolang = 13 /* C */ AND probin IS NOT NULL AND oid >= 16384;
executing: SELECT DISTINCT probin FROM	pg_catalog.pg_proc WHERE	prolang = 13 /* C */ AND probin IS NOT NULL AND oid >= 16384;
Checking database user is the install user                  executing: SELECT rolsuper, oid FROM pg_catalog.pg_roles WHERE rolname = current_user AND rolname !~ '^pg_'
executing: SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'
ok
Checking database connection settings                       executing: SELECT	datname, datallowconn FROM	pg_catalog.pg_database
ok
Checking for prepared transactions                          executing: SELECT * FROM pg_catalog.pg_prepared_xacts
ok
Checking for reg* system OID user data types                executing: SELECT n.nspname, c.relname, a.attname FROM	pg_catalog.pg_class c, 		pg_catalog.pg_namespace n, 		pg_catalog.pg_attribute a WHERE	c.oid = a.attrelid AND 		NOT a.attisdropped AND 		a.atttypid IN ( 			'pg_catalog.regproc'::pg_catalog.regtype, 			'pg_catalog.regprocedure'::pg_catalog.regtype, 			'pg_catalog.regoper'::pg_catalog.regtype, 			'pg_catalog.regoperator'::pg_catalog.regtype, 			'pg_catalog.regconfig'::pg_catalog.regtype, 			'pg_catalog.regdictionary'::pg_catalog.regtype) AND 		c.relnamespace = n.oid AND 		n.nspname NOT IN ('pg_catalog', 'information_schema')
executing: SELECT n.nspname, c.relname, a.attname FROM	pg_catalog.pg_class c, 		pg_catalog.pg_namespace n, 		pg_catalog.pg_attribute a WHERE	c.oid = a.attrelid AND 		NOT a.attisdropped AND 		a.atttypid IN ( 			'pg_catalog.regproc'::pg_catalog.regtype, 			'pg_catalog.regprocedure'::pg_catalog.regtype, 			'pg_catalog.regoper'::pg_catalog.regtype, 			'pg_catalog.regoperator'::pg_catalog.regtype, 			'pg_catalog.regconfig'::pg_catalog.regtype, 			'pg_catalog.regdictionary'::pg_catalog.regtype) AND 		c.relnamespace = n.oid AND 		n.nspname NOT IN ('pg_catalog', 'information_schema')
executing: SELECT n.nspname, c.relname, a.attname FROM	pg_catalog.pg_class c, 		pg_catalog.pg_namespace n, 		pg_catalog.pg_attribute a WHERE	c.oid = a.attrelid AND 		NOT a.attisdropped AND 		a.atttypid IN ( 			'pg_catalog.regproc'::pg_catalog.regtype, 			'pg_catalog.regprocedure'::pg_catalog.regtype, 			'pg_catalog.regoper'::pg_catalog.regtype, 			'pg_catalog.regoperator'::pg_catalog.regtype, 			'pg_catalog.regconfig'::pg_catalog.regtype, 			'pg_catalog.regdictionary'::pg_catalog.regtype) AND 		c.relnamespace = n.oid AND 		n.nspname NOT IN ('pg_catalog', 'information_schema')
ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for roles starting with 'pg_'                      executing: SELECT * FROM pg_catalog.pg_roles WHERE rolname ~ '^pg_'
ok
Creating dump of global objects                             "C:\Program Files\PostgreSQL\9.6\bin/pg_dumpall" --port 50432 --username ^"postgres^" --globals-only --quote-all-identifiers --binary-upgrade --verbose -f pg_upgrade_dump_globals.sql >> "pg_upgrade_utility.log" 2>&1
ok
Creating dump of database schemas
  SomeDatabase
"C:\Program Files\PostgreSQL\9.6\bin/pg_dump" --port 50432 --username ^"postgres^" --schema-only --quote-all-identifiers --binary-upgrade --format=custom --verbose --file="pg_upgrade_dump_16393.custom" ^"dbname^=SomeDatabase^" >> "pg_upgrade_dump_16393.log" 2>&1
  postgres
"C:\Program Files\PostgreSQL\9.6\bin/pg_dump" --port 50432 --username ^"postgres^" --schema-only --quote-all-identifiers --binary-upgrade --format=custom --verbose --file="pg_upgrade_dump_12373.custom" ^"dbname^=postgres^" >> "pg_upgrade_dump_12373.log" 2>&1
  template1
"C:\Program Files\PostgreSQL\9.6\bin/pg_dump" --port 50432 --username ^"postgres^" --schema-only --quote-all-identifiers --binary-upgrade --format=custom --verbose --file="pg_upgrade_dump_1.custom" ^"dbname^=template1^" >> "pg_upgrade_dump_1.log" 2>&1
                                                            ok
"C:\Program Files\PostgreSQL\9.5\bin/pg_ctl" -w -D "C:\Program Files\PostgreSQL\9.5\data" -o ""  stop >> "pg_upgrade_utility.log" 2>&1
"C:\Program Files\PostgreSQL\9.6\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.6\data" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off " start >> "pg_upgrade_server_start.log" 2>&1
executing: SELECT d.oid, d.datname, d.encoding, d.datcollate, d.datctype, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM pg_catalog.pg_database d  LEFT OUTER JOIN pg_catalog.pg_tablespace t  ON d.dattablespace = t.oid WHERE d.datallowconn = true ORDER BY 2
executing: WITH regular_heap (reloid, indtable, toastheap) AS (   SELECT c.oid, 0::oid, 0::oid   FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n          ON c.relnamespace = n.oid   WHERE relkind IN ('r', 'm', 'S') AND     ((n.nspname !~ '^pg_temp_' AND       n.nspname !~ '^pg_toast_temp_' AND       n.nspname NOT IN ('pg_catalog', 'information_schema',                         'binary_upgrade', 'pg_toast') AND       c.oid >= 16384::pg_catalog.oid) OR      (n.nspname = 'pg_catalog' AND       relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))),   toast_heap (reloid, indtable, toastheap) AS (   SELECT c.reltoastrelid, 0::oid, c.oid   FROM regular_heap JOIN pg_catalog.pg_class c       ON regular_heap.reloid = c.oid   WHERE c.reltoastrelid != 0),   all_index (reloid, indtable, toastheap) AS (   SELECT indexrelid, indrelid, 0::oid   FROM pg_catalog.pg_index   WHERE indisvalid AND indisready     AND indrelid IN         (SELECT reloid FROM regular_heap          UNION ALL          SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname,   c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap       UNION ALL       SELECT * FROM toast_heap       UNION ALL       SELECT * FROM all_index) all_rels   JOIN pg_catalog.pg_class c       ON all_rels.reloid = c.oid   JOIN pg_catalog.pg_namespace n      ON c.relnamespace = n.oid   LEFT OUTER JOIN pg_catalog.pg_tablespace t      ON c.reltablespace = t.oid ORDER BY 1;
executing: WITH regular_heap (reloid, indtable, toastheap) AS (   SELECT c.oid, 0::oid, 0::oid   FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n          ON c.relnamespace = n.oid   WHERE relkind IN ('r', 'm', 'S') AND     ((n.nspname !~ '^pg_temp_' AND       n.nspname !~ '^pg_toast_temp_' AND       n.nspname NOT IN ('pg_catalog', 'information_schema',                         'binary_upgrade', 'pg_toast') AND       c.oid >= 16384::pg_catalog.oid) OR      (n.nspname = 'pg_catalog' AND       relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))),   toast_heap (reloid, indtable, toastheap) AS (   SELECT c.reltoastrelid, 0::oid, c.oid   FROM regular_heap JOIN pg_catalog.pg_class c       ON regular_heap.reloid = c.oid   WHERE c.reltoastrelid != 0),   all_index (reloid, indtable, toastheap) AS (   SELECT indexrelid, indrelid, 0::oid   FROM pg_catalog.pg_index   WHERE indisvalid AND indisready     AND indrelid IN         (SELECT reloid FROM regular_heap          UNION ALL          SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname,   c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap       UNION ALL       SELECT * FROM toast_heap       UNION ALL       SELECT * FROM all_index) all_rels   JOIN pg_catalog.pg_class c       ON all_rels.reloid = c.oid   JOIN pg_catalog.pg_namespace n      ON c.relnamespace = n.oid   LEFT OUTER JOIN pg_catalog.pg_tablespace t      ON c.reltablespace = t.oid ORDER BY 1;

new databases:
Database: postgres
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace: 
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace: 


Database: template1
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace: 
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace: 
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace: 


Checking for presence of required libraries                 ok
Checking database user is the install user                  executing: SELECT rolsuper, oid FROM pg_catalog.pg_roles WHERE rolname = current_user AND rolname !~ '^pg_'
executing: SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'
ok
Checking for prepared transactions                          executing: SELECT * FROM pg_catalog.pg_prepared_xacts
ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       "C:\Program Files\PostgreSQL\9.6\bin/vacuumdb" --port 50432 --username ^"postgres^" --all --analyze --verbose >> "pg_upgrade_utility.log" 2>&1
ok
Freezing all rows on the new cluster                        "C:\Program Files\PostgreSQL\9.6\bin/vacuumdb" --port 50432 --username ^"postgres^" --all --freeze --verbose >> "pg_upgrade_utility.log" 2>&1
ok
"C:\Program Files\PostgreSQL\9.6\bin/pg_ctl" -w -D "C:\Program Files\PostgreSQL\9.6\data" -o ""  stop >> "pg_upgrade_utility.log" 2>&1
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           xcopy /e /y /q /r "C:\Program Files\PostgreSQL\9.5\data/pg_clog" "C:\Program Files\PostgreSQL\9.6\data/pg_clog\" >> "pg_upgrade_utility.log" 2>&1
ok
Setting next transaction ID and epoch for new cluster       "C:\Program Files\PostgreSQL\9.6\bin/pg_resetxlog" -f -x 2752 "C:\Program Files\PostgreSQL\9.6\data" >> "pg_upgrade_utility.log" 2>&1
"C:\Program Files\PostgreSQL\9.6\bin/pg_resetxlog" -f -e 0 "C:\Program Files\PostgreSQL\9.6\data" >> "pg_upgrade_utility.log" 2>&1
"C:\Program Files\PostgreSQL\9.6\bin/pg_resetxlog" -f -c 2752,2752 "C:\Program Files\PostgreSQL\9.6\data" >> "pg_upgrade_utility.log" 2>&1
ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              xcopy /e /y /q /r "C:\Program Files\PostgreSQL\9.5\data/pg_multixact/offsets" "C:\Program Files\PostgreSQL\9.6\data/pg_multixact/offsets\" >> "pg_upgrade_utility.log" 2>&1
ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              xcopy /e /y /q /r "C:\Program Files\PostgreSQL\9.5\data/pg_multixact/members" "C:\Program Files\PostgreSQL\9.6\data/pg_multixact/members\" >> "pg_upgrade_utility.log" 2>&1
ok
Setting next multixact ID and offset for new cluster        "C:\Program Files\PostgreSQL\9.6\bin/pg_resetxlog" -O 0 -m 1,1 "C:\Program Files\PostgreSQL\9.6\data" >> "pg_upgrade_utility.log" 2>&1
ok
Resetting WAL archives                                      "C:\Program Files\PostgreSQL\9.6\bin/pg_resetxlog" -l 000000010000000000000003 "C:\Program Files\PostgreSQL\9.6\data" >> "pg_upgrade_utility.log" 2>&1
ok
"C:\Program Files\PostgreSQL\9.6\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.6\data" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off " start >> "pg_upgrade_server_start.log" 2>&1

*failure*
There were problems executing ""C:\Program Files\PostgreSQL\9.6\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.6\data" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off " start >> "pg_upgrade_server_start.log" 2>&1"
Consult the last few lines of "pg_upgrade_server_start.log" or "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: Connection refused (0x0000274D/10061)
	Is the server running on host "localhost" (::1) and accepting
	TCP/IP connections on port 50432?
could not connect to server: Connection refused (0x0000274D/10061)
	Is the server running on host "localhost" (127.0.0.1) and accepting
	TCP/IP connections on port 50432?

could not connect to new postmaster started with the command:
"C:\Program Files\PostgreSQL\9.6\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "C:\Program Files\PostgreSQL\9.6\data" -o "-p 50432 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off " start

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: pg_upgrade schlägt fehlt (pg_clog/pg_multixact)

Beitrag von castorp » Mo 24. Okt 2016, 15:01

Das einzige was mir noch einfällt: Virenscanner oder fehlerhafte Berechtigungen auf den Datenverzeichnissen.

Grundsätzlich ist es eine sehr, sehr schlechte Idee das Datenverzeichnis nach "c:\Program Files" zu legen da dieses Verzeichnis sehr restriktive Berechtigungen hat - das gilt für alle Anwendungen, nicht nur Postgres.

Klappt es wenn Du die Daten woanders ablegst? Und dort auch sicherstellst, dass die Berechtigungen alle korrekt sind (vom Root-Verzeichnis bis in das eigentliche Datenverzeichnis)
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

devnull
Neuer Benutzer
Beiträge: 4
Registriert: Mo 24. Okt 2016, 07:20

Re: pg_upgrade schlägt fehlt (pg_clog/pg_multixact)

Beitrag von devnull » Mo 24. Okt 2016, 15:04

Die Installation wird als Administrator (mit UAC-Bestätigung) gestartet und auf dem System (virtuelle Maschine mit Windows 7) ist kein Virenscanner installiert.

castorp
Erfahrener Benutzer
Beiträge: 1070
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: pg_upgrade schlägt fehlt (pg_clog/pg_multixact)

Beitrag von castorp » Mo 24. Okt 2016, 15:19

devnull hat geschrieben:Die Installation wird als Administrator (mit UAC-Bestätigung) gestartet
Das ist unerheblich.

Postgres startet sich selbst aus Sicherheitsgründen grundsätzlich ohne Administrator Privilegien.

Das kann also sehr wohl ein Privilegien-Problem sein. Versuch es einfach mal mit einem Datenverzeichnis außerhalb von "c:\Program Files"

Ich kann nur raten, niemals irgendwelche Programm-Daten unter "c:\Program Files" zu speichern - weder für Postgres noch für irgendein anderes Programm. Das im letzten Jahrhundert noch akzeptabel gewesen sein, aber spätestens seit Windows 2000 ist das eine sehr schlechte Idee.
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Antworten