5

When I tried to run psql, I got

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

and when I checked Server.log, I saw:

FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 9.5,
        which is not compatible with this version 9.6.1.

I tried to follow this tutorial (from 9.4 to 9.5) for upgrading 9.6.1, but keep getting this error message

cannot write to log file pg_upgrade_internal.log
Failure, exiting

after running

$ pg_upgrade -v \
> -d /usr/local/var/postgres \
> -D /usr/local/var/postgres9.6.1 \
> -b /usr/local/Cellar/postgresql/9.5/bin/ \
> -B /usr/local/Cellar/postgresql/9.6.1/bin/

Does anyone have any idea what I am missing/doing wrong? Thanks!


System:

  • MacOS 10.12.2
  • PostgreSQL installed with Homebrew
  • 3
    Run the command while the working directory is one that postgres user has write access – ypercubeᵀᴹ Jan 15 '17 at 16:07
  • @ypercubeᵀᴹ Not sure how to do that. I tried running `sudo pg_upgrade -v ` but got the same error. – ogirginc Jan 16 '17 at 6:41
  • 1
    Create a directory, give permissions to user postgres on that directory (in Linux it would be with the chmod command, not sure if there are differences in MacOS). – ypercubeᵀᴹ Jan 16 '17 at 9:18
  • 2
    Do you actually want to upgrade to 9.6? Or are you just doing that because you think it will solve your start-up problem? Unless you actually want to upgrade, you can just change the init script to start the database using '/usr/local/Cellar/postgresql/9.5/bin/' software instead of the 9.6 software. – jjanes Jan 16 '17 at 18:53
  • 1
    @jjanes My reason is to learn how to do it because it seems like a very common problem. I want to understand the solution rather than applying a quick fix. – ogirginc Jan 18 '17 at 7:50
1

If your database size is not large, you can use pg_dump and pg_dumpall to take database structure and data dump in the form of sql queries from posgtres 9.5 and restore the same in postgres 9.6.1.

If the database size is too large, then go for pg_upgrade. By looking at the error, it seems the user does not have permissions on the directory and not able to write in the log file. Also to save time, i would suggest you use -k option of pg_upgrade for faster upgrade. Note that -k will create soft links of your old data in Newly installed data directory. So you will not be able to delete old data.

4

After 9 months, I updated to PostgreSQL 10 from 9.6.5 with pg_upgrade:

1) Stop postgresql:

brew services stop postgresql

2) Initialize postgresql10 database:

initdb /usr/local/var/postgres10.0 -E utf8

3) Double check the newly created db:

ls /usr/local/Cellar/postgresql/

4) Run pq_upgrade to move data to new database:

pg_upgrade \
  -d /usr/local/var/postgres \
  -D /usr/local/var/postgres10.0 \
  -b /usr/local/Cellar/postgresql/9.6.5/bin/ \
  -B /usr/local/Cellar/postgresql/10.0/bin/ \
  -v

5) Rename old database:

mv /usr/local/var/postgres /usr/local/var/postgres9.6.5

6) Rename new database to postgres:

mv /usr/local/var/postgres10.0 /usr/local/var/postgres

7) Restart postgresql:

brew services start postgresql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.