Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"

On 11/13/20 6:32 AM, Jeremy Wilson wrote:
> I’m running CentOS 8 on an EC2 instance and attempting to upgrade a 9.5 database to 13 using pg_upgrade.  Both are
runningon the same box and pass initial tests but it fails during the later part of the process.
 
> 
> ---
> 
> bash-4.4$ /usr/pgsql-13/bin/pg_upgrade --old-bindir /usr/pgsql-9.5/bin --new-bindir /usr/pgsql-13/bin --old-datadir
/var/lib/pgsql/9.5/data--new-datadir /var/lib/pgsql/13/data --old-port=54320 --new-port=5432
--socketdir=/var/run/postgresql/
> 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* data types in user tables                 ok
> Checking for contrib/isn with bigint-passing mismatch       ok
> Checking for tables WITH OIDS                               ok
> Checking for invalid "sql_identifier" user columns          ok
> Checking for invalid "unknown" user columns                 ok
> Checking for roles starting with "pg_"                      ok
> Creating dump of global objects                             ok
> Creating dump of database schemas
>                                                              ok
> 
> connection to database failed: FATAL:  database "template1" does not exist
> 
> could not connect to target postmaster started with the command:
> "/usr/pgsql-13/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/13/data" -o "-p 5432 -b -c
synchronous_commit=off-c fsync=off -c full_page_writes=off -c vacuum_defer_cleanup_age=0  -c listen_addresses='' -c
unix_socket_permissions=0700-c unix_socket_directories='/var/run/postgresql'" start
 
> Failure, exiting
> 
> ---
> 
> When I manually run that command it works, although when I attempt to connect to the ‘postgres’ database on it, it
complainsit doesn’t exist.
 

To me it seems the initdb for the 13 instance did not complete 
successfully. Have you tried clearing /var/lib/pgsql/13/data and doing 
the init over again? If you do try it monitor the output carefully.

> 
> I can’t use the dump/restore method to upgrade this server as the data in question is fairly massive and in my
testingit took 45 hours to complete.  Any help appreciated.
 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com