Postgresql – Trying to upgrade PostgreSQL 9.1 to 9.4 on Debian

postgresqlpostgresql-9.4upgrade

After upgrading my Debian development box I have both PostgreSQL 9.1 and 9.4 on my system. I want to have 9.4 serve my data and get rid of the outdated version.

AFAICS, it is important to use a working directory where the postgres user can write, and configure the old and new services in a way which allows them to run simultaneously (using different ports). Thus, this is what I tried:

# cd /tmp/
# sudo -u postgres /usr/lib/postgresql/9.4/bin/pg_upgrade \
--check \
--retain \
--old-bindir=/usr/lib/postgresql/9.1/bin/ \
--new-bindir=/usr/lib/postgresql/9.4/bin/ \
--old-datadir=/var/lib/postgresql/9.1/main \
--new-datadir=/var/lib/postgresql/9.4/main \
--old-options /etc/postgresql/9.1/main/postgresql.conf \
--new-options=/etc/postgresql/9.4/main/postgresql.conf \
--old-port=5433 \
--new-port=5432

This failed (because of postmasters serving the running services).

The --old-port and --new-port options mirror the settings in the configuration files; thus, both services are able to run at the same time. I stopped them both and tried again:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
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 "/tmp/.s.PGSQL.5433"?


could not connect to old postmaster started with the command:
"/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.1/main" -o "-p 5433 -b /etc/postgresql/9.1/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/tmp'" start
Failure, exiting

The log file pg_upgrade_server.log contains:

-----------------------------------------------------------------
  pg_upgrade run on Mon Jul 18 11:46:33 2016
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.1/main" -o "-p 5433 -b /etc/postgresql/9.1/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....postgres: invalid argument: "/etc/postgresql/9.1/main/postgresql.conf"
Try "postgres --help" for more information.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

As far as pg_ctl --help tells me, there is no -b option; the file /etc/postgresql/9.1/main/postgresql.conf does exist.

What is going on here, and how can I get the upgrade done?

Best Answer

For the records, I gave up trying this method and did it via pg_dumpall instead.

Here is my earlier question in the unix area (with some more detail), and of course the PostgreSQL docs about upgrading where this is still the first method described (and obviously prefered).

Related Question