pg_upgrade error on FreeBSD (9.1 -> 9.5)

Started by Amitabh Kantabout 9 years ago3 messagesgeneral
Jump to latest
#1Amitabh Kant
amitabhkant@gmail.com

Hi

I am trying to upgrade an exisitng Postgresql cluster from 9.1 to 9.5 on
FreeBSD. pg_upgrade fails with the following error:
[Verbose Output: http://pastebin.com/YhR8vD03]
==
$ /usr/local/bin/pg_upgrade -k -d /usr/local/pgsql/data91 -D
/usr/local/pgsql/data --old-bindir=/var/tmp/pgbin.SPOsRj4D/bin
--new-bindir=/usr/local/bin --check

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

pg_ctl failed to start the old server, or connection failed
Failure, exiting

$ cat pg_upgrade_server.log

-----------------------------------------------------------------
pg_upgrade run on Sat Jan 21 04:08:13 2017
-----------------------------------------------------------------

command: "/var/tmp/pgbin.SPOsRj4D/bin/pg_ctl" -w -l "pg_upgrade_server.log"
-D "/usr/local/pgsql/data91" -o "-p 50432 -b -c listen_addresses='' -c
unix_socket_permissions=0700 -c unix_socket_directory='/usr/local/pgsql'"
start >> "pg_upgrade_server.log" 2>&1
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.
==

On checking the process list, the old postgres server seems to have
started, but I am unable to connect to it using psql.
==
$ ps auwwx |grep post
pgsql 26636 0.0 1.4 66960 14512 - Is 4:08AM 0:00.06
/var/tmp/pgbin.SPOsRj4D/bin/postgres -D /usr/local/pgsql/data91 -p 50432 -b
-c listen_addresses= -c unix_socket_permissions=0700 -c
unix_socket_directory=/usr/local/pgsql
pgsql 26638 0.0 1.4 66960 14516 - Ss 4:08AM 0:00.23 postgres:
writer process (postgres)
pgsql 26639 0.0 1.4 66960 14516 - Ss 4:08AM 0:00.15 postgres: wal
writer process (postgres)
pgsql 26640 0.0 0.9 26720 9568 - Ss 4:08AM 0:00.02 postgres:
stats collector process (postgres)

$ psql -p 50432 -d template1
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.50432"?
==

If I run the pg_ctl command directly with just the minimum parameters, it
still gives me an error, but seems to work correctly.
==
$ /var/tmp/pgbin.SPOsRj4D/bin/pg_ctl -o "-p 50432 " -w -l
"pg_upgrade_server.log" -D "/usr/local/pgsql/data91" start
waiting for server to start.... stopped waiting
pg_ctl: could not start server
Examine the log output.

$ cat pg_upgrade_server.log

$ psql -p 50432 -d template1
psql (9.5.5, server 9.1.20)
Type "help" for help.

template1=#
==

Where am I going wrong?

Amitabh

Detailed steps that I followed for upgrade:

Since postgresql installed on FreeBSD through its ports/packages doesn't
have version specific binaries or data directory till PG 9.5 (9.6 does
have version specific data directory), use of pg_upgrade requires a little
bit of work:

i) Stopped the current PG 9.1 instance. Renamed the data directory to
data91 (under /usr/local/pgsql).
ii) Installed PG 9.1 binaries in a different location
(/var/tmp/pgbin.SPOsRj4D/bin). Set owner and permission for pgsql
user(default superuser in FreeBSD)
iii) Removed PG 9.1 installation from server & installed PG 9.5 in it's
default location (under /usr/local/bin)
iv) Initiaized DB instance using initdb command (PG 9.5 data location is
/usr/local/pgsql/data)
v) Ran the following command for upgrade check:
/usr/local/bin/pg_upgrade -k -d /usr/local/pgsql/data91 -D
/usr/local/pgsql/data --old-bindir=/var/tmp/pgbin.SPOsRj4D/bin
--new-bindir=/usr/local/bin --check --verbose

Output: http://pastebin.com/YhR8vD03

OS: FreeBSD 10.3 (running as virtualbox guest OS, host is Mac OS)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Amitabh Kant (#1)
Re: pg_upgrade error on FreeBSD (9.1 -> 9.5)

Amitabh Kant <amitabhkant@gmail.com> writes:

command: "/var/tmp/pgbin.SPOsRj4D/bin/pg_ctl" -w -l "pg_upgrade_server.log"
-D "/usr/local/pgsql/data91" -o "-p 50432 -b -c listen_addresses='' -c
unix_socket_permissions=0700 -c unix_socket_directory='/usr/local/pgsql'"

Note the unix_socket_directory parameter, which is indeed being applied
because we can see it again in the ps output:

pgsql 26636 0.0 1.4 66960 14512 - Is 4:08AM 0:00.06
/var/tmp/pgbin.SPOsRj4D/bin/postgres -D /usr/local/pgsql/data91 -p 50432 -b
-c listen_addresses= -c unix_socket_permissions=0700 -c
unix_socket_directory=/usr/local/pgsql

However, your psql is looking for the socket in /tmp:

$ psql -p 50432 -d template1
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.50432"?

You could successfully connect to that server with
"psql -p 50432 -h /usr/local/pgsql ...", I expect.

The question is why pg_upgrade issued that option and then failed to
cope with the consequences. I suspect it has something to do with one
installation being configured with different default socket directory
than the other, but I don't have enough facts.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#3Amitabh Kant
amitabhkant@gmail.com
In reply to: Tom Lane (#2)
Re: pg_upgrade error on FreeBSD (9.1 -> 9.5)

On Sat, Jan 21, 2017 at 9:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Amitabh Kant <amitabhkant@gmail.com> writes:

command: "/var/tmp/pgbin.SPOsRj4D/bin/pg_ctl" -w -l

"pg_upgrade_server.log"

-D "/usr/local/pgsql/data91" -o "-p 50432 -b -c listen_addresses='' -c
unix_socket_permissions=0700 -c unix_socket_directory='/usr/

local/pgsql'"

Note the unix_socket_directory parameter, which is indeed being applied
because we can see it again in the ps output:

pgsql 26636 0.0 1.4 66960 14512 - Is 4:08AM 0:00.06
/var/tmp/pgbin.SPOsRj4D/bin/postgres -D /usr/local/pgsql/data91 -p

50432 -b

-c listen_addresses= -c unix_socket_permissions=0700 -c
unix_socket_directory=/usr/local/pgsql

However, your psql is looking for the socket in /tmp:

$ psql -p 50432 -d template1
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.50432"?

You could successfully connect to that server with
"psql -p 50432 -h /usr/local/pgsql ...", I expect.

The question is why pg_upgrade issued that option and then failed to
cope with the consequences. I suspect it has something to do with one
installation being configured with different default socket directory
than the other, but I don't have enough facts.

regards, tom lane

Yes, it does connect using Unix domain socket as you suggested. PG 9.5 is
the stock install as present on FreeBSD. I will have to check the script
that installs PG 9.1 in an alternate location for any changes from the
default.

regards

Amitabh