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.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/dataand 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.klaver@aklaver.com