Postgres could not connect to server after migrating data from a Time Machine Backup (Apple Silicon)

Question

When I got my M1 computer last year I installed postgres using Homebrew. I took my computer to the Genius Bar a few days ago because of some problems I was having. It was wiped clean and Big Sur was reinstalled. I got it back yesterday and migrated my data from my latest Time Machine backup.

This morning I tried to work on one of my Rails applications and got this error:

connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?

I thought that maybe I needed to update some software. I executed the following commands.

brew doctor
brew update

This updated my postgres. I wasn't thinking about postgres. Sigh.

==> Upgrading postgresql
  13.3 -> 14.2 

==> Pouring postgresql--14.2.arm64_big_sur.bottle.tar.gz
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /opt/homebrew/var/postgres
For more details, read:
  https://www.postgresql.org/docs/14/app-initdb.html

To restart postgresql after an upgrade:
  brew services restart postgresql
Or, if you don't want/need a background service you can just run:
  /opt/homebrew/opt/postgresql/bin/postgres -D /opt/homebrew/var/postgres
==> Summary
  /opt/homebrew/Cellar/postgresql/14.2: 3,305 files, 46.2MB
==> Running `brew cleanup postgresql`...
Removing: /opt/homebrew/Cellar/postgresql/13.3... (3,225 files, 43.5MB)

Homebrew suggested that I run this Apple command so I did. It downloaded the latest version of Big Sur and Safari.

softwareupdate --all --install --force

Here are the contents in ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist that was created.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>KeepAlive</key>
    <true/>
    <key>Label</key>
    <string>homebrew.mxcl.postgresql</string>
    <key>ProgramArguments</key>
    <array>
        <string>/opt/homebrew/opt/postgresql/bin/postgres</string>
        <string>-D</string>
        <string>/opt/homebrew/var/postgres</string>
    </array>
    <key>RunAtLoad</key>
    <true/>
    <key>StandardErrorPath</key>
    <string>/opt/homebrew/var/log/postgres.log</string>
    <key>StandardOutPath</key>
    <string>/opt/homebrew/var/log/postgres.log</string>
    <key>WorkingDirectory</key>
    <string>/opt/homebrew</string>
</dict>
</plist>

I have this statement in ~/.bashrc which was working before.

export PGDATA='/opt/homebrew/var/postgres'

This is what happened when I checked which psql.

[my_rails_folder] (master) which psql
/opt/homebrew/bin/psql

I get no output when I execute lsof -i:5432.

I've executed the following commands. All of them said they executed successfully.

brew services stop postgres
brew services start postgres

brew services restart postgres

This link gave several suggestions about removing postmaster.pid from different directories. There is not a postmaster.pid anywhere on my computer.

Postgres could not connect to server

I created the folder in question.

sudo mkdir /var/pgsql_socket/
sudo ln -s /private/tmp/.s.PGSQL.5432 /var/pgsql_socket/

I executed brew postgresql-upgrade-database but it failed to convert my data.

[myrailsfolder] (master) brew postgresql-upgrade-database
==> brew install postgresql@13
==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/13/manifests/13.6
######################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/postgresql/13/blobs/sha256:3d2c9b85a099c55dbdb605cae
==> Downloading from https://pkg-containers.githubusercontent.com/ghcr1/blobs/sha256:3d2c9b85a099c55d
######################################################################## 100.0%
==> Pouring postgresql@13--13.6.arm64_big_sur.bottle.tar.gz
==> /opt/homebrew/Cellar/postgresql@13/13.6/bin/initdb --locale=C -E UTF-8 /opt/homebrew/var/postgres
==> Caveats
Previous versions of this formula used the same data directory as
the regular PostgreSQL formula. This causes a conflict if you
try to use both at the same time.

In order to avoid this conflict, you should make sure that the
postgresql@13 data directory is located at:
  /opt/homebrew/var/postgresql@13

This formula has created a default database cluster with:
  initdb --locale=C -E UTF-8 /opt/homebrew/var/postgresql@13
For more details, read:
  https://www.postgresql.org/docs/13/app-initdb.html

postgresql@13 is keg-only, which means it was not symlinked into /opt/homebrew,
because this is an alternate version of another formula.

If you need to have postgresql@13 first in your PATH, run:
  echo 'export PATH="/opt/homebrew/opt/postgresql@13/bin:$PATH"' >> /Users/mymacuserid/.bash_profile

For compilers to find postgresql@13 you may need to set:
  export LDFLAGS="-L/opt/homebrew/opt/postgresql@13/lib"
  export CPPFLAGS="-I/opt/homebrew/opt/postgresql@13/include"

For pkg-config to find postgresql@13 you may need to set:
  export PKG_CONFIG_PATH="/opt/homebrew/opt/postgresql@13/lib/pkgconfig"


To restart postgresql@13 after an upgrade:
  brew services restart postgresql@13
Or, if you don't want/need a background service you can just run:
  /opt/homebrew/opt/postgresql@13/bin/postgres -D /opt/homebrew/var/postgresql@13
==> Summary
  /opt/homebrew/Cellar/postgresql@13/13.6: 3,234 files, 44MB
==> Running `brew cleanup postgresql@13`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).
==> Upgrading postgresql data from 13 to 14...
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
waiting for server to start....2022-02-26 15:50:19.490 CST [37355] LOG:  starting PostgreSQL 13.6 on arm-apple-darwin20.6.0, compiled by Apple clang version 13.0.0 (clang-1300.0.29.30), 64-bit
2022-02-26 15:50:19.492 CST [37355] LOG:  listening on IPv6 address "::1", port 5432
2022-02-26 15:50:19.492 CST [37355] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-02-26 15:50:19.492 CST [37355] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-02-26 15:50:19.502 CST [37356] LOG:  database system was interrupted; last known up at 2022-02-21 00:01:32 CST
2022-02-26 15:50:19.637 CST [37356] LOG:  invalid primary checkpoint record
2022-02-26 15:50:19.637 CST [37356] PANIC:  could not locate a valid checkpoint record
2022-02-26 15:50:19.637 CST [37355] LOG:  startup process (PID 37356) was terminated by signal 6: Abort trap: 6
2022-02-26 15:50:19.637 CST [37355] LOG:  aborting startup due to startup process failure
2022-02-26 15:50:19.638 CST [37355] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.
==> Moving postgresql data from /opt/homebrew/var/postgres to /opt/homebrew/var/postgres.old...
==> Creating database...
The files belonging to this database system will be owned by user "mymacuserid".
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 /opt/homebrew/var/postgres ... 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/Chicago
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:

    /opt/homebrew/opt/postgresql/bin/pg_ctl -D /opt/homebrew/var/postgres -l logfile start

==> Migrating and upgrading data...
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

The source cluster was not shut down cleanly.
Failure, exiting
Error: Upgrading postgresql data from 13 to 14 failed!
==> Removing empty postgresql initdb database...
==> Moving postgresql data back from /opt/homebrew/var/postgres.old to /opt/homebrew/var/postgres...
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
Error: Failure while executing; `/opt/homebrew/opt/postgresql/bin/pg_upgrade -r -b /opt/homebrew/Cellar/postgresql@13/13.6/bin -B /opt/homebrew/opt/postgresql/bin -d /opt/homebrew/var/postgres.old -D /opt/homebrew/var/postgres -j 8` exited with 1.

I even restarted my computer. I still get the error.

I would like to keep my data. However I'm willing to start over since I can rebuild my data from the databases in Heroku.

Where do I go from here?

Answer 1

Is TCP enabled? Can you connect to postgres using psql -h host|ip ?

What is the socket directory defined as in postgresql.conf (should look something like this:

unix_socket_directories = '/var/run/postgresql' # comma-separated list of direct

You may have to set it to your homebrew path, ie, "/opt" or wherever brew has placed your pgsql var directory.

You can also enable TCP using:

listen_addresses = '127.0.0.1' and

port = 5432

and then connect using "psql -h 127.0.0.1".

You may want to consider not using packages for databases. I have found that when you do updates, databases easily break. You may want to compile it yourself and place the contents in your preferred directory for macOS.

Steps for checking:

1. Make sure your PATH and environment variables are correct. I see "/opt/homewbrew/opt", which I assume is how your brew installs its packages. You can try things like:

locate psql or locate pg_ctl, and make sure you are using the right ones. If you don't have locate, try:

find / -name psql -print or find / -name program_name -print

to get the proper file path.

2. Make sure your unix socket directory path is not commented out and set to the proper path (perhaps "/tmp" as that is where it is where psql is looking for it? Also, make sure "psql" is the version you installed is the correct one. It may be using the wrong version.

3. Enable TCP, but you will need to look at pg_hba.conf and control your access, so that only you are able to connect.

4. You can restart the changes with:

brew services restart postgresql

5. Check your postgresql log file for errors if it is not running. Look for the directory in postgresql.conf called "log_directory" under REPORTING & LOGGING. If there are startup errors, they would be in the log directory. It could also be commented out, and you may need to define a path.

6. If all else fails, try:

pg_ctl -D /path/to/postgres/database -l /tmp/server.log &

Then have a look at /tmp/server.log and see what's going on. Feel free to paste it if you have troubles and I'll look further.