I'm trying to upgrade PostgreSQL on Ubuntu Server from 9.6 to 10. This is the first time I'm doing this. The server doesn't contain any useful data yet but I want to make sure I can do this properly next time.

I followed the instructions in the manual. Here's what I did:

  • Installed the new packages for version 10
  • Stopped both versions on the server
  • Ran the pg_upgrade script:

    sudo -u postgres /usr/lib/postgresql/10/bin/pg_upgrade -b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/10/bin -d /var/lib/postgresql/9.6/main/ -D /var/lib/postgresql/10/main/

This is the console output:

Performing Consistency Checks
Checking cluster versions                                   ok

Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/lib/postgresql/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.6/main/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

This is the contents of pg_upgrade_server.log:

command: "/usr/lib/postgresql/9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.6/main/" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....postgres: could not access the server configuration file "/var/lib/postgresql/9.6/main/postgresql.conf": No such file or directory
 stopped waiting
pg_ctl: could not start server
Examine the log output.

I'm totally confused now. What should I do?


You know what they say about the word assume

I assumed the config dir and data dir was the same.

Answer 1:

Change your command to this and it will work.

sudo -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
-b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/10/bin \
-d /var/lib/postgresql/9.6/main/ -D /var/lib/postgresql/10/main/
-o 'config_file=/etc/postgresql/9.6/main/postgresql.conf' \
-O 'config_file=/etc/postgresql/10/main/postgresql.conf'

we can provide the special config file location as an option

Also, I believe you have identified a default install issue or the documentation needs to be updated

The default install should create the config files in the datadir

Answer 2:

Since this is play, try this too:

sudo -u postgres pg_dropcluster 10 main
sudo -u postgres pg_upgradecluster 9.6 main

Keeping this, since it might help someone else.

The empty databases seem to have not been created yet. Or are in a different directory. That's why postgresql.conf is not found in the error message.

ls -l /var/lib/postgresql/9.6/main/postgresql.conf /var/lib/postgresql/10/main/postgresql.conf

should show two files.

The equivalent initdb command for both versions should be run

pg_ctl -D /var/lib/postgresql/9.6/main initdb

pg_ctl -D /var/lib/postgresql/10/main initdb
  • The ls command shows no files. But both databases already exist. They were created by the package installer and I've already played a bit with the old (9.6) version. Both server instances were already running. Not sure what all the paths are about. The pg_upgrade documentation isn't very helpful with that. No examples.
    – ygoe
    Nov 9 '17 at 13:15
  • So the paths in the pg_upgrade command (-d -D) are the locations of where the data (and config files) are stored by postgres. Use the ps or find command to locate both config files and use those directories in the pg_upgrade command
    – amacvar
    Nov 9 '17 at 18:23
  • 1
    They're in different directories: /etc/postgresql/10/main/postgresql.conf for the config and /var/lib/postgresql/10/main/ for the data. That's how Postgres' original package for Ubuntu/Debian does it. Is that packaging incompatible with Postgres' own tools?
    – ygoe
    Nov 10 '17 at 8:03
  • 2
    So I was wrong to say that -d & -D are location of where the data and config files are. It's actually the config directory. Thanks - I learnt something today and the documentation needs to be updated too
    – amacvar
    Nov 10 '17 at 17:07
  • 6
    Thanks, I made some progress following this answer. However both the old and new options parameters should have -c before the config_file so -o '-c config_file=/path/to/postgresql.conf'. Also, I think the 3rd line in your Answer 1 is missing a backslash.
    – hsebastian
    Jan 30 '18 at 23:04

I've recently did DB upgrade from 8.4 to 11, on same machine, using pg_upgrade.

Issue was that in 8.4 instance, configuration file - postgresql.conf, unix_socket_directory parameter was empty.

Changes as below, in 8.4 postgresql.conf:

unix_socket_directory = '/var/run/postgresql'

solved the issue.

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.