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.
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 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
okconnection 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.
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 testing it took 45 hours to complete. Any help appreciated.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Nov 13, 2020, at 9:58 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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 cluster ok
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/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
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
On 11/13/20 7:02 AM, Jeremy Wilson wrote:
On Nov 13, 2020, at 9:58 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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 ... okinitdb: 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
Well that blows the initdb theory out of the water.
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
In your previous post you had --socketdir=/var/run/postgresql/. Did you
change that or is it missing?
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
Where is Postgres putting its sockets?
Failure, exiting
--
Adrian Klaver
adrian.klaver@aklaver.com
On Nov 13, 2020, at 10:09 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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 cluster ok
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/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
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
Jeremy Wilson <jwilson@clover.co> writes:
On Nov 13, 2020, at 10:09 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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.
An incorrect socket-directory setting would lead to something like
"could not connect to server: No such file or directory", not to a
specific complaint about a database not being present.
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.
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.
regards, tom lane
On Nov 13, 2020, at 10:23 AM, Tom Lane <tgl@sss.pgh.pa.us> 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=#
Jeremy Wilson <jwilson@clover.co> writes:
[ no soap on either of my theories ]
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?
regards, tom lane
On 11/13/20 7:28 AM, Jeremy Wilson wrote:
On Nov 13, 2020, at 10:23 AM, Tom Lane <tgl@sss.pgh.pa.us> 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.
When you manually run the pg_upgrade pg_ctl script the server starts but
you cannot connect to any database in it correct?
What does pg_upgrade_server.log show when you do above?
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=#
--
Adrian Klaver
adrian.klaver@aklaver.com
On Nov 13, 2020, at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> 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.
On Nov 13, 2020, at 11:06 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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 start....2020-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 start....2020-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 start....2020-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
On 11/13/20 8:19 AM, Jeremy Wilson wrote:
On Nov 13, 2020, at 11:06 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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-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 start....2020-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
What shows up in the log file in "log" directory when you try to connect
to a database?
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
--
Adrian Klaver
adrian.klaver@aklaver.com
On Nov 13, 2020, at 11:26 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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
On Nov 13, 2020, at 11:26 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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
On 11/13/20 8:27 AM, Jeremy Wilson wrote:
On Nov 13, 2020, at 11:26 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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"
I'm pretty sure the above, two sockets, should not happen. Have to think
on how that could come to pass.
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
--
Adrian Klaver
adrian.klaver@aklaver.com
On 11/13/20 8:31 AM, Jeremy Wilson wrote:
On Nov 13, 2020, at 11:26 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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?
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
--
Adrian Klaver
adrian.klaver@aklaver.com
On Nov 13, 2020, at 11:35 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/13/20 8:31 AM, Jeremy Wilson wrote:
On Nov 13, 2020, at 11:26 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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
On 11/13/20 8:37 AM, Jeremy Wilson wrote:
On Nov 13, 2020, at 11:35 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/13/20 8:31 AM, Jeremy Wilson wrote:
On Nov 13, 2020, at 11:26 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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
This does not show trying to connect to a database. It would help to
list the commands run and then the corresponding log portions.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Nov 13, 2020, at 11:39 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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
Jeremy Wilson <jwilson@clover.co> writes:
I did a completely fresh initdb to get clean logs:
...
free(): invalid pointer
2020-11-13 11:30:05.292 EST [205647] LOG: server process (PID 205659) was terminated by signal 6: Aborted
This is highly significant. It suggests that you're getting bit by the
postgis crash-in-atexit problem that a couple of people have reported
(and which, unfortunately, we don't yet know the exact cause of).
I now suspect that something similar is happening earlier in the process
and preventing template1 from getting created, or maybe it's created but
then we can't access it.
Are you by any chance trying to preload any of the postgis-related
extensions? If so, try not doing that.
regards, tom lane
