Re: pg_upgrade of 11 -> 13: free(): invalid pointer
> On Nov 17, 2020, at 12:18 PM, Adrian Klaver wrote: > > On 11/17/20 8:59 AM, Jeremy Wilson wrote: > > Was this after a clean install of the corrected RPM’s? Yes, this is a fresh install of CentOS 8 and installed using the updated repo and RPMs.
pg_upgrade of 11 -> 13: free(): invalid pointer
I’m continuing my upgrade journey, this time from 11 to 13, and the process is
dying in the copy phase, always on the same DB:
—
Performing Upgrade
--
Analyzing all rows in the new cluster ok
Freezing all rows in the new clusterok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsetsok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/membersok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new clusterok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
messages
*failure*
Consult the last few lines of "pg_upgrade_dump_16387.log" for
the probable cause of the failure.
Failure, exiting
—
The log contains (which is different each time):
—
pg_restore: WARNING: terminating connection because of crash of another server
process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited abnormally
and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat
your command.
pg_restore: creating COMMENT "public.FUNCTION "st_isempty"("rast"
"public"."raster")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 5338; 0 0 COMMENT FUNCTION "st_isempty"("rast"
"public"."raster") postgres
pg_restore: error: could not execute query: server closed the connection
unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Command was: COMMENT ON FUNCTION "public"."st_isempty"("rast"
"public"."raster") IS 'args: rast - Returns true if the raster is empty (width
= 0 and height = 0). Otherwise, returns false.’;
—
And the pgsql13 server log contains:
—
2020-11-17 11:51:40.953 EST [96545] LOG: database system is ready to accept
connections
free(): invalid pointer
2020-11-17 11:51:42.880 EST [96545] LOG: server process (PID 96575) was
terminated by signal 6: Aborted
2020-11-17 11:51:42.880 EST [96545] LOG: terminating any other active server
processes
2020-11-17 11:51:42.880 EST [96582] WARNING: terminating connection because of
crash of another server process
2020-11-17 11:51:42.880 EST [96582] DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2020-11-17 11:51:42.880 EST [96582] HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2020-11-17 11:51:42.884 EST [96545] LOG: all server processes terminated;
reinitializing
2020-11-17 11:51:42.904 EST [96545] LOG: received fast shutdown request
2020-11-17 11:51:42.905 EST [96585] LOG: database system was interrupted; last
known up at 2020-11-17 11:51:42 EST
2020-11-17 11:51:42.906 EST [96585] LOG: database system was not properly shut
down; automatic recovery in progress
2020-11-17 11:51:42.906 EST [96585] LOG: redo starts at E0/DB6B2960
2020-11-17 11:51:42.907 EST [96545] LOG: abnormal database system shutdown
2020-11-17 11:51:42.909 EST [96545] LOG: database system is shut down
—
So I’m assuming it’s that free() call. Servers have PostGIS 3.0 on them, all
installed from repo, and running CentOS 8.
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 1:10 PM, Magnus Hagander wrote: > > The problem is that postgis, through gdal, ended up being linked to two > different versions of proj at the same time. > > You can check it by doing: > ldd /usr/pgsql-13/lib/postgis_raster-3.so | grep proj > > If that shows up two different "proj" libraries, then you have that same > problem. Just wanted to reply that this was indeed an issue. After removing the proj RPMs and reinstalling, the upgrade seems to be running now. On a side note, you need to enable the PowerTools repo on CentOS to install PostGIS, otherwise it complains about the gdal libraries being missing. If I run into another failure I’ll update the list - thanks!
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 12:06 PM, Adrian Klaver wrote: > > Hmm. You can still connect if you use?: > > /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start Same result. bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start waiting for server to start done server started bash-4.4$ psql postgres psql: error: could not connect to server: FATAL: database "postgres" does not exist
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 12:00 PM, Tom Lane wrote: > > Are you by any chance trying to preload any of the postgis-related > extensions? If so, try not doing that. Not sure what you mean by this - I’ve installed the postgis packages for 9.5 and 13 and the extensions are installed and working in 9.5, but I’m not doing anything but initdb and then pg_upgrade for 13.
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 11:39 AM, Adrian Klaver wrote: > > This does not show trying to connect to a database. It would help to list the > commands run and then the corresponding log portions. bash-4.4$ "/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 waiting for server to start done server started bash-4.4$ psql postgres psql: error: could not connect to server: FATAL: database "postgres" does not exist --- 2020-11-13 11:39:38.378 EST [205747] LOG: starting PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit 2020-11-13 11:39:38.378 EST [205747] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-11-13 11:39:38.382 EST [205749] LOG: database system was shut down at 2020-11-13 11:37:34 EST 2020-11-13 11:39:38.388 EST [205747] LOG: database system is ready to accept connections 2020-11-13 11:39:45.750 EST [205756] FATAL: database "postgres" does not exist
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 11:35 AM, Adrian Klaver wrote: > > On 11/13/20 8:31 AM, Jeremy Wilson wrote: >>> On Nov 13, 2020, at 11:26 AM, Adrian Klaver >>> wrote: >>> >>> What shows up in the log file in "log" directory when you try to connect to >>> a database? >> I did a completely fresh initdb to get clean logs: > > Was the below from starting using the pg_upgrade version of pg_ctl start? No, strictly from running pg_upgrade. Here’s the additional logs from manually running it: 2020-11-13 11:36:35.976 EST [205733] LOG: starting PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit 2020-11-13 11:36:35.976 EST [205733] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-11-13 11:36:35.981 EST [205735] LOG: database system was shut down at 2020-11-13 11:30:06 EST 2020-11-13 11:36:35.989 EST [205733] LOG: database system is ready to accept connections
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 11:26 AM, Adrian Klaver wrote: > > What shows up in the log file in "log" directory when you try to connect to a > database? I did a completely fresh initdb to get clean logs: bash-4.4$ cat 13/data/log/postgresql-Fri.log 2020-11-13 11:29:44.744 EST [205647] LOG: starting PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit 2020-11-13 11:29:44.744 EST [205647] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-11-13 11:29:44.748 EST [205649] LOG: database system was shut down at 2020-11-13 11:29:19 EST 2020-11-13 11:29:44.755 EST [205647] LOG: database system is ready to accept connections free(): invalid pointer 2020-11-13 11:29:45.771 EST [205647] LOG: received smart shutdown request 2020-11-13 11:30:05.292 EST [205647] LOG: server process (PID 205659) was terminated by signal 6: Aborted 2020-11-13 11:30:05.292 EST [205647] LOG: terminating any other active server processes 2020-11-13 11:30:05.295 EST [205647] LOG: abnormal database system shutdown 2020-11-13 11:30:05.306 EST [205647] LOG: database system is shut down 2020-11-13 11:30:06.136 EST [205704] LOG: starting PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit 2020-11-13 11:30:06.136 EST [205704] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-11-13 11:30:06.141 EST [205706] LOG: database system was shut down at 2020-11-13 11:30:05 EST 2020-11-13 11:30:06.148 EST [205704] LOG: database system is ready to accept connections 2020-11-13 11:30:06.195 EST [205712] FATAL: database "template1" does not exist 2020-11-13 11:30:06.200 EST [205704] LOG: received fast shutdown request 2020-11-13 11:30:06.201 EST [205704] LOG: aborting any active transactions 2020-11-13 11:30:06.204 EST [205704] LOG: background worker "logical replication launcher" (PID 205711) exited with exit code 1 2020-11-13 11:30:06.204 EST [205707] LOG: shutting down 2020-11-13 11:30:06.219 EST [205704] LOG: database system is shut down
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 11:26 AM, Adrian Klaver wrote: > > What shows up in the log file in "log" directory when you try to connect to a > database? 2020-11-13 10:14:35.821 EST [204797] LOG: starting PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit 2020-11-13 10:14:35.822 EST [204797] LOG: listening on IPv4 address "127.0.0.1", port 5432 2020-11-13 10:14:35.824 EST [204797] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-11-13 10:14:35.828 EST [204797] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2020-11-13 10:14:35.833 EST [204799] LOG: database system was shut down at 2020-11-13 10:12:15 EST 2020-11-13 10:14:35.845 EST [204797] LOG: database system is ready to accept connections 2020-11-13 10:14:45.416 EST [204797] LOG: received fast shutdown request 2020-11-13 10:14:45.418 EST [204797] LOG: aborting any active transactions 2020-11-13 10:14:45.421 EST [204797] LOG: background worker "logical replication launcher" (PID 204805) exited with exit code 1 2020-11-13 10:14:45.421 EST [204800] LOG: shutting down 2020-11-13 10:14:45.442 EST [204797] LOG: database system is shut down
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 11:06 AM, Adrian Klaver wrote: > > When you manually run the pg_upgrade pg_ctl script the server starts but you > cannot connect to any database in it correct? Yes. > What does pg_upgrade_server.log show when you do above? - pg_upgrade run on Fri Nov 13 10:11:45 2020 - command: "/usr/pgsql-9.5/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/9.5/data" -o "-p 54320 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/run/postgresql'" start >> "pg_upgrade_server.log" 2>&1 waiting for server to start2020-11-13 10:11:46 EST [204620]: [1-1] user=,db=,app=,client= LOG: ending log output to stderr 2020-11-13 10:11:46 EST [204620]: [2-1] user=,db=,app=,client= HINT: Future log output will go to log destination "syslog". done server started command: "/usr/pgsql-9.5/bin/pg_ctl" -w -D "/var/lib/pgsql/9.5/data" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1 waiting for server to shut down done server stopped 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 >> "pg_upgrade_server.log" 2>&1 waiting for server to start2020-11-13 10:11:54.944 EST [204724] LOG: redirecting log output to logging collector process 2020-11-13 10:11:54.944 EST [204724] HINT: Future log output will appear in directory "log". done server started command: "/usr/pgsql-13/bin/pg_ctl" -w -D "/var/lib/pgsql/13/data" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1 waiting for server to shut down... done server stopped 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 >> "pg_upgrade_server.log" 2>&1 waiting for server to start2020-11-13 10:12:15.902 EST [204782] LOG: redirecting log output to logging collector process 2020-11-13 10:12:15.902 EST [204782] HINT: Future log output will appear in directory "log". done server started command: "/usr/pgsql-13/bin/pg_ctl" -w -D "/var/lib/pgsql/13/data" -o "" -m fast stop >> "pg_upgrade_server.log" 2>&1 waiting for server to shut down done server stopped
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 10:46 AM, Tom Lane wrote: > > Hmph. We know that 9.5 -> 13 pg_upgrade works in simple scenarios, > because the buildfarm tests that every day. So there has to be > something out of the ordinary about your setup. Any unusual > extensions, pg_hba.conf configuration, etc? We have postgis installed, here’s a list of most of the extensions: master=# \dx List of installed extensions Name| Version | Schema | Description +-++- btree_gin | 1.0 | public | support for indexing common datatypes in GIN cube | 1.0 | public | data type for multidimensional cubes dblink | 1.1 | public | connect to other PostgreSQL databases from within a database earthdistance | 1.0 | public | calculate great-circle distances on the surface of the Earth pageinspect| 1.3 | public | inspect the contents of database pages at a low level pg_buffercache | 1.1 | public | examine the shared buffer cache pg_stat_statements | 1.3 | public | track execution statistics of all SQL statements executed pg_trgm| 1.1 | public | text similarity measurement and index searching based on trigrams plpgsql| 1.0 | pg_catalog | PL/pgSQL procedural language postgis| 3.0.2 | public | PostGIS geometry, geography, and raster spatial types and functions postgis_raster | 3.0.2 | public | PostGIS raster types and functions postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers unaccent | 1.0 | public | text search dictionary that removes accents (13 rows) I did have to upgrade all the postgis from 2.2 to 3.0 which went smoothly.
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 10:23 AM, Tom Lane wrote: > > Unless ... could it be that there is another PG server active on the > machine, whose cluster lacks a "template1" database? Seems unlikely, > but you might try confirming with "ps auxww | grep post" or the like. This is a test environment so only the two databases are on it and neither is running. pg_upgrade checks if either is running. > I also wonder if the cluster you're trying to upgrade from has a > "template1" database. Not sure if anyone has ever tested whether > pg_upgrade can cope with the lack of one. It’s there: $ psql -p 54320 template1 psql (13.0, server 9.5.23) Type "help" for help. template1=#
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 10:09 AM, Adrian Klaver wrote: > > In your previous post you had --socketdir=/var/run/postgresql/. Did you > change that or is it missing? Sorry, here it is with the socket directory specified. bash-4.4$ rm -r 13/data/* bash-4.4$ bash-4.4$ /usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ --locale=en_US.UTF-8 The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/pgsql/13/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... America/Toronto creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start bash-4.4$ bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start waiting for server to start done server started bash-4.4$ psql template1 psql (13.0) Type "help" for help. template1=# \q bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile stop waiting for server to shut down done server stopped 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 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 in the new clusterok Deleting files from new pg_xact 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/offsetsok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/membersok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new clusterok Resetting WAL archives 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
Re: Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
> On Nov 13, 2020, at 9:58 AM, Adrian Klaver wrote: > > 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. here’s the complete process: bash-4.4$ /usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data/ --locale=en_US.UTF-8 The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/pgsql/13/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... America/Toronto creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile start waiting for server to start done server started bash-4.4$ psql postgres psql (13.0) Type "help" for help. postgres=# \c template1 You are now connected to database "template1" as user "postgres". template1=# \q bash-4.4$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data/ -l logfile stop waiting for server to shut down done server stopped 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 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 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 in the new clusterok Deleting files from new pg_xact 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/offsetsok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/membersok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new clusterok Resetting WAL archives 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/lib/pgsql'" start Failure, exiting
Issue upgrading from 9.5 to 13 with pg_upgrade: "connection to database failed: FATAL: database "template1" does not exist"
I’m running CentOS 8 on an EC2 instance and attempting to upgrade a 9.5 database to 13 using pg_upgrade. Both are running on 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 complains it doesn’t exist. I can’t use the dump/restore method to upgrade this server as the data in question is fairly massive and in my testing it took 45 hours to complete. Any help appreciated.
