1

I wanted to ugrade my postgresql database from v10 to v14

I have run these commands in windows command prompt:

C:\Users\mahajan>CD C:\Program Files\PostgreSQL\14\bin
C:\Program Files\PostgreSQL\14\bin>SET PGPASSWORD =1234
pg_upgrade -d "c:\Program Files\PostgreSQL\10\data" -D"c:\Program Files\PostgreSQL\14\data" -b "c:\Program Files\PostgreSQL\10\bin" -B "c:\Program Files\PostgreSQL\14\bin" -U Postgres

could not open log file "pg_upgrade_internal.log": Permission denied
Failure, exiting

C:\Program Files\PostgreSQL\14\bin>cd "C:\Users\santo\Downloads\Temp"

C:\Users\mahajan\Downloads\Temp>SET PGPASSWORD=1234

C:\Users\mahajan\Downloads\Temp>"c:\Program Files\PostgreSQL\14\bin\pg_upgrade.exe" -d "c:\Program Files\PostgreSQL\10\data" -D"c:\Program Files\PostgreSQL\14\data" -b "c:\Program Files\PostgreSQL\10\bin" -B "c:\Program Files\PostgreSQL\14\bin" -U postgres

I have got ERROR like thos How to resolve this error?

pg_upgrade_internal.log:

-----------------------------------------------------------------
  pg_upgrade run on Wed Apr  6 11:06:21 2022
-----------------------------------------------------------------


-----------------------------------------------------------------
  pg_upgrade run on Wed Apr  6 11:06:21 2022
-----------------------------------------------------------------


connection to server at "localhost" (::1), port 50432 failed: FATAL:  password authentication failed for user "Postgres"

could not connect to target postmaster started with the command:

"c:/Program Files/PostgreSQL/14/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "c:/Program Files/PostgreSQL/14/data" -o "-p 50432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000 -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0 " start

ERROR:

Microsoft Windows [Version 10.0.22000.556]
(c) Microsoft Corporation. All rights reserved.

C:\Windows\system32>SET PGPASSWORD =1234

C:\Windows\system32>pg_upgrade -d "c:\Program Files\PostgreSQL\10\data" -D"c:\Program Files\PostgreSQL\14\data" -b "c:\Program Files\PostgreSQL\10\bin" -B "c:\Program Files\PostgreSQL\14\bin" -U Postgres

'pg_upgrade' is not recognized as an internal or external command,
operable program or batch file.
mahajan
  • 11
  • 2
  • 2
    Putting the data directory below `c:\Program Files` is a really bad idea to begin with. That directory is protected for a reason. Regular users have no privilege to write there and `pg_upgrade` is run with the credentials of the current user. If you really want to keep the data directory there, you need to run `pg_upgrade` as `Administrator`, e.g. by starting `cmd.exe` as `Administrator` –  Apr 06 '22 at 05:27
  • I have run command prompt as administrator, but still error araised – mahajan Apr 06 '22 at 05:38
  • so what does `pg_upgrade_internal.log` contain? –  Apr 06 '22 at 05:39
  • I have updated question with contents of `pg_upgrade_internal.log` – mahajan Apr 06 '22 at 05:43
  • @mahajan did you solve this? Am running into the same issue and would appreciate guidance if you have any. – Jay J Nov 06 '22 at 19:28

1 Answers1

0

Omit the cd in the beginning and stay in a directory where you have write permissions. Then pg_upgrade can write the log files that contain the actual problem.

The error message that you then get is clear:

connection to server at "localhost" (::1), port 50432 failed:
   FATAL:  password authentication failed for user "Postgres"

You will have to edit pg_hba.conf on the server and allow user postgres to establish a connection on the loopback interface without a password. The first line should read:

host  all  postgres  ::1/128  trust
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263