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 7:02 AM, Jeremy Wilson wrote:
>
>
>> On Nov 13, 2020, at 9:58 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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

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(dot)klaver(at)aklaver(dot)com