Re: pg_upgrade help

Hi Fabio,
I think you have found the problem. Please find o/p below.

-bash-4.2$ ps -aef | grep postgres
postgres 478 1 0 13:40 ? 00:00:00 /usr/bin/postgres -p 50432
-D /var/ericsson/esm-data/postgresql-data/
postgres 490 478 0 13:40 ? 00:00:00 postgres: logger process
postgres 492 478 0 13:40 ? 00:00:00 postgres: writer process
postgres 493 478 0 13:40 ? 00:00:00 postgres: wal writer
process
postgres 494 478 0 13:40 ? 00:00:00 postgres: autovacuum
launcher process
postgres 495 478 0 13:40 ? 00:00:00 postgres: stats collector
process
postgres 528 1 0 13:40 ? 00:00:00
/opt/rh/rh-postgresql94/root/usr/bin/postgres -D
/var/ericsson/esm-data/postgresql-data-9.4/
postgres 529 528 0 13:40 ? 00:00:00 postgres: logger process
postgres 531 528 0 13:40 ? 00:00:00 postgres: checkpointer
process
postgres 532 528 0 13:40 ? 00:00:00 postgres: writer process
postgres 533 528 0 13:40 ? 00:00:00 postgres: wal writer
process
postgres 534 528 0 13:40 ? 00:00:00 postgres: autovacuum
launcher process
postgres 535 528 0 13:40 ? 00:00:00 postgres: stats collector
process
postgres 734 8647 0 13:50 pts/1 00:00:00 ps -aef
postgres 735 8647 0 13:50 pts/1 00:00:00 grep --color=auto postgres
root 8646 9365 0 Apr18 pts/1 00:00:00 su - postgres
postgres 8647 8646 0 Apr18 pts/1 00:00:00 -bash

9.4
===

-bash-4.2$ psql
psql (8.4.20, server 9.4.9)
WARNING: psql version 8.4, server version 9.4.
Some psql features might not work.
Type "help" for help.

postgres=#

-bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/psql
psql (9.4.9)
Type "help" for help.

postgres=#

8.4
====

-bash-4.2$ psql -p 50432
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"?

==========================================================================================================

After setting PGHOST, i can connect to PSQL

-bash-4.2$ echo $PGHOST
/var/run/postgresql
-bash-4.2$ psql -p 50432
psql (8.4.20)
Type "help" for help.

postgres=#

With Best Regards
Akshay
Ericsson OSS MON
Tata Consultancy Services
Mailto: akshay(dot)ballarpure(at)tcs(dot)com
Website: http://www.tcs.com
____________________________________________
Experience certainty. IT Services
Business Solutions
Consulting
____________________________________________

From: Fabio Pardi <f(dot)pardi(at)portavita(dot)eu>
To: Akshay Ballarpure <akshay(dot)ballarpure(at)tcs(dot)com>,
pgsql-general(at)lists(dot)postgresql(dot)org
Date: 04/19/2018 03:45 PM
Subject: Re: pg_upgrade help

Hi,

while trying to reproduce your problem, i noticed that on my Centos 6
installations Postgres 8.4 and Postgres 9.6 (I do not have 9.4 readily
available) store the socket in different places:

Postgres 9.6.6 uses /var/run/postgresql/

Postgres 8.4 uses /tmp/

therefore using default settings, i can connect to 9.6 but not 8.4 without
specifying where the socket is

Connect to 9.6

12:01 postgres(at)machine:~# psql
psql (8.4.20, server 9.6.6)
WARNING: psql version 8.4, server version 9.6.
Some psql features might not work.
Type "help" for help.

---------

Connect to 8.4

12:01 postgres(at)machine:~# psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?

12:04 postgres(at)machine:~# psql -h /tmp
psql (8.4.20)
Type "help" for help.

I think you might be incurring in the same problem.

Can you confirm it?

regards,

fabio pardi

On 04/19/2018 09:37 AM, Akshay Ballarpure wrote:
> Hi Fabio,
> Yes i ran initdb on new database and able to start as below.
>
> [root(at)ms-esmon root]# su - postgres -c "/usr/bin/postgres -p 50432 -D
/var/ericsson/esm-data/postgresql-data/ 2>&1 &"
> [root(at)ms-esmon root]# su - postgres -c
"/opt/rh/rh-postgresql94/root/usr/bin/postgres -D
/var/ericsson/esm-data/postgresql-data-9.4/ 2>&1 &"
> [root(at)ms-esmon root]# 2018-04-19 08:17:53.553 IST LOG: redirecting log
output to logging collector process
> 2018-04-19 08:17:53.553 IST HINT: Future log output will appear in
directory "pg_log".
>
> [root(at)ms-esmon root]#
> [root(at)ms-esmon root]# ps -eaf | grep postgre
> sroot 8646 9365 0 Apr18 pts/1 00:00:00 su - postgres
> postgres 8647 8646 0 Apr18 pts/1 00:00:00 -bash
> postgres 28009 1 2 08:17 ? 00:00:00 /usr/bin/postgres -p
50432 -D /var/ericsson/esm-data/postgresql-data/ *--8.4*
> postgres 28010 28009 0 08:17 ? 00:00:00 postgres: logger process
> postgres 28012 28009 0 08:17 ? 00:00:00 postgres: writer process
> postgres 28013 28009 0 08:17 ? 00:00:00 postgres: wal writer
process
> postgres 28014 28009 0 08:17 ? 00:00:00 postgres: autovacuum
launcher process
> postgres 28015 28009 0 08:17 ? 00:00:00 postgres: stats
collector process
> postgres 28048 1 0 08:17 ? 00:00:00
/opt/rh/rh-postgresql94/root/usr/bin/postgres -D
/var/ericsson/esm-data/postgresql-data-9.4/
> postgres 28049 28048 0 08:17 ? 00:00:00 postgres: logger process
> postgres 28051 28048 0 08:17 ? 00:00:00 postgres: checkpointer
process
> postgres 28052 28048 0 08:17 ? 00:00:00 postgres: writer process
> postgres 28053 28048 0 08:17 ? 00:00:00 postgres: wal writer
process
> postgres 28054 28048 0 08:17 ? 00:00:00 postgres: autovacuum
launcher process
> postgres 28055 28048 0 08:17 ? 00:00:00 postgres: stats
collector process
> root 28057 2884 0 08:17 pts/0 00:00:00 grep --color=auto
postgre
>
>
> Also i am able to start db with the command provided by you and run
psql.
>
> /opt/rh/rh-postgresql94/root/usr/bin/pg_ctl start -o "-p 50432 -c
listen_addresses='' -c unix_socket_permissions=0700" -D
/var/ericsson/esm-data/postgresql-data-9.4/
> pg_ctl: another server might be running; trying to start server anyway
> server starting
> -bash-4.2$ 2018-04-19 08:22:46.527 IST LOG: redirecting log output to
logging collector process
> 2018-04-19 08:22:46.527 IST HINT: Future log output will appear in
directory "pg_log".
>
> -bash-4.2$ ps -eaf | grep postg
> root 8646 9365 0 Apr18 pts/1 00:00:00 su - postgres
> postgres 8647 8646 0 Apr18 pts/1 00:00:00 -bash
> postgres 28174 1 0 08:22 pts/1 00:00:00
/opt/rh/rh-postgresql94/root/usr/bin/postgres -D
/var/ericsson/esm-data/postgresql-data-9.4 -p 50432 -c listen_addresses=
-c unix_socket_permissions=0700
> postgres 28175 28174 0 08:22 ? 00:00:00 postgres: logger process
> postgres 28177 28174 0 08:22 ? 00:00:00 postgres: checkpointer
process
> postgres 28178 28174 0 08:22 ? 00:00:00 postgres: writer process
> postgres 28179 28174 0 08:22 ? 00:00:00 postgres: wal writer
process
> postgres 28180 28174 0 08:22 ? 00:00:00 postgres: autovacuum
launcher process
> postgres 28181 28174 0 08:22 ? 00:00:00 postgres: stats
collector process
> postgres 28182 8647 0 08:22 pts/1 00:00:00 ps -eaf
> postgres 28183 8647 0 08:22 pts/1 00:00:00 grep --color=auto postg
>
> -bash-4.2$ psql -p 50432 -h /var/run/postgresql -U rhqadmin -d rhq
> psql (8.4.20, server 9.4.9)
> WARNING: psql version 8.4, server version 9.4.
> Some psql features might not work.
> Type "help" for help.
>
> rhq=>
>
>
> Still its failing...
>
> -bash-4.2$ ps -efa | grep postgre
> root 8646 9365 0 Apr18 pts/1 00:00:00 su - postgres
> postgres 8647 8646 0 Apr18 pts/1 00:00:00 -bash
> postgres 28349 8647 0 08:34 pts/1 00:00:00 ps -efa
> postgres 28350 8647 0 08:34 pts/1 00:00:00 grep --color=auto
postgre
>
> -bash-4.2$ echo $OLDCLUSTER
> /usr/bin/postgres
> -bash-4.2$ echo $NEWCLUSTER
> /opt/rh/rh-postgresql94/
>
> [root(at)ms-esmon rh-postgresql94]#
/opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade --old-bindir=/usr/bin
--new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
--old-datadir=/var/ericsson/esm-data/postgresql-data
--new-datadir=/var/ericsson/esm-data/postgresql-data-9.4
>
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions ok
>
> 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/run/postgresql/.s.PGSQL.50432"?
>
>
> could not connect to old postmaster started with the command:
> "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
"/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off -c
autovacuum_freeze_max_age=2000000000 -c listen_addresses='' -c
unix_socket_permissions=0700" start
> Failure, exiting
>
> With Best Regards
> Akshay
> Ericsson OSS MON
> Tata Consultancy Services
> Mailto: akshay(dot)ballarpure(at)tcs(dot)com
> Website: http://www.tcs.com <http://www.tcs.com/>
> ____________________________________________
> Experience certainty. IT Services
> Business Solutions
> Consulting
> ____________________________________________
>
>
>
>
> From: Fabio Pardi <f(dot)pardi(at)portavita(dot)eu>
> To: Akshay Ballarpure <akshay(dot)ballarpure(at)tcs(dot)com>
> Cc: pgsql-general(at)lists(dot)postgresql(dot)org
> Date: 04/18/2018 06:17 PM
> Subject: Re: pg_upgrade help
>
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
>
> did you run initdb on the new db?
>
> what happens if you manually start the new db?
>
> /opt/rh/rh-postgresql94/root/usr/bin/pg_ctl start -o "-p 50432 -c
> listen_addresses='' -c unix_socket_permissions=0700" -D $NEWCLUSTER
>
> after starting it, can you connect to it using psql?
>
> psql -p 50432 -h /var/run/postgresql -U your_user _db_
>
>
>
> regards,
>
> fabio pardi
>
>
> On 04/18/2018 02:02 PM, Akshay Ballarpure wrote:
>> Hi Fabio,
>> sorry to bother you again, its still failing with stopping both server
>> (8.4 and 9.4)
>>
>> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
>> --old-bindir=/usr/bin --new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
>> --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER
>>
>> 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/run/postgresql/.s.PGSQL.50432"?
>>
>>
>> could not connect to old postmaster started with the command:
>> "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
>> "/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c autovacuum=off
>> -c autovacuum_freeze_max_age=2000000000 -c listen_addresses='' -c
>> unix_socket_permissions=0700" start
>> Failure, exiting
>>
>>
>> With Best Regards
>> Akshay
>> Ericsson OSS MON
>> Tata Consultancy Services
>> Mailto: akshay(dot)ballarpure(at)tcs(dot)com
>> Website: http://www.tcs.com <http://www.tcs.com/><http://www.tcs.com/>
>> ____________________________________________
>> Experience certainty. IT Services
>> Business Solutions
>> Consulting
>> ____________________________________________
>>
>>
>>
>>
>> From: Fabio Pardi <f(dot)pardi(at)portavita(dot)eu>
>> To: Akshay Ballarpure <akshay(dot)ballarpure(at)tcs(dot)com>,
>> pgsql-general(at)lists(dot)postgresql(dot)org
>> Date: 04/18/2018 02:35 PM
>> Subject: Re: pg_upgrade help
>>
------------------------------------------------------------------------
>>
>>
>>
>> Hi,
>>
>> i was too fast in reply (and perhaps i should drink my morning coffee
>> before replying), I will try to be more detailed:
>>
>> both servers should be able to run at the moment you run pg_upgrade,
>> that means the 2 servers should have been correctly stopped in advance,
>> should have their configuration files, and new cluster initialized too.
>>
>> Then, as Sergei highlights here below, pg_upgrade will take care of the
>> upgrade process, starting the servers.
>>
>>
>> Here there is a step by step guide, i considered my best ally when it
>> was time to upgrade:
>>
>> https://www.postgresql.org/docs/9.4/static/pgupgrade.html
>>
>> note point 7:
>>
>> 'stop both servers'
>>
>>
>> About the port the servers will run on, at point 9 there is some
>> clarification:
>>
>> ' pg_upgrade defaults to running servers on port 50432 to avoid
>> unintended client connections. You can use the same port number for
both
>> clusters when doing an upgrade because the old and new clusters will
not
>> be running at the same time. However, when checking an old running
>> server, the old and new port numbers must be different.'
>>
>> Hope it helps,
>>
>> Fabio Pardi
>>
>>
>> On 04/18/2018 10:34 AM, Akshay Ballarpure wrote:
>>> Thanks Fabio for instant reply.
>>>
>>> I now started 8.4 with 50432 and 9.4 with default port but still its
>>> failing ...Can you please suggest what is wrong ?
>>>
>>> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
>>> --old-bindir=/usr/bin
--new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
>>> --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER
>>>
>>> *failure*
>>> Consult the last few lines of "pg_upgrade_server.log" for
>>> the probable cause of the failure.
>>>
>>> There seems to be a postmaster servicing the old cluster.
>>> Please shutdown that postmaster and try again.
>>> Failure, exiting
>>> -bash-4.2$ ps -eaf | grep postgres
>>> root 8646 9365 0 08:07 pts/1 00:00:00 su - postgres
>>> postgres 8647 8646 0 08:07 pts/1 00:00:00 -bash
>>> postgres 9778 1 0 09:17 ? 00:00:00 /usr/bin/postgres -p
>>> 50432 -D /var/ericsson/esm-data/postgresql-data/
>>> postgres 9779 9778 0 09:17 ? 00:00:00 postgres: logger
process
>>> postgres 9781 9778 0 09:17 ? 00:00:00 postgres: writer
process
>>> postgres 9782 9778 0 09:17 ? 00:00:00 postgres: wal writer
>>> process
>>> postgres 9783 9778 0 09:17 ? 00:00:00 postgres: autovacuum
>>> launcher process
>>> postgres 9784 9778 0 09:17 ? 00:00:00 postgres: stats
>>> collector process
>>> postgres 9900 1 0 09:20 ? 00:00:00
>>> /opt/rh/rh-postgresql94/root/usr/bin/postgres -D
>>> /var/ericsson/esm-data/postgresql-data-9.4/
>>> postgres 9901 9900 0 09:20 ? 00:00:00 postgres: logger
process
>>> postgres 9903 9900 0 09:20 ? 00:00:00 postgres: checkpointer
>>> process
>>> postgres 9904 9900 0 09:20 ? 00:00:00 postgres: writer
process
>>> postgres 9905 9900 0 09:20 ? 00:00:00 postgres: wal writer
>>> process
>>> postgres 9906 9900 0 09:20 ? 00:00:00 postgres: autovacuum
>>> launcher process
>>> postgres 9907 9900 0 09:20 ? 00:00:00 postgres: stats
>>> collector process
>>> postgres 9926 8647 0 09:21 pts/1 00:00:00 ps -eaf
>>> postgres 9927 8647 0 09:21 pts/1 00:00:00 grep --color=auto
postgres
>>>
>>>
>>> -bash-4.2$ netstat -antp | grep 50432
>>> (Not all processes could be identified, non-owned process info
>>> will not be shown, you would have to be root to see it all.)
>>> tcp 0 0 127.0.0.1:50432 0.0.0.0:*
>>> LISTEN 9778/postgres
>>> tcp6 0 0 ::1:50432 :::*
>>> LISTEN 9778/postgres
>>> -bash-4.2$ netstat -antp | grep 5432
>>> (Not all processes could be identified, non-owned process info
>>> will not be shown, you would have to be root to see it all.)
>>> tcp 0 0 127.0.0.1:5432 0.0.0.0:*
>>> LISTEN 9900/postgres
>>> tcp6 0 0 ::1:5432 :::*
>>> LISTEN 9900/postgres
>>>
>>> -----------------------------------------------------------------
>>> pg_upgrade run on Wed Apr 18 09:24:47 2018
>>> -----------------------------------------------------------------
>>>
>>> command: "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
>>> "/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c
autovacuum=off
>>> -c autovacuum_freeze_max_age=2000000000 -c listen_addresses='' -c
>>> unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1
>>> pg_ctl: another server might be running; trying to start server anyway
>>> FATAL: lock file "postmaster.pid" already exists
>>> HINT: Is another postmaster (PID 9778) running in data directory
>>> "/var/ericsson/esm-data/postgresql-data"?
>>> pg_ctl: could not start server
>>> Examine the log output.
>>>
>>>
>>> [root(at)ms-esmon /]# cat
>>> ./var/ericsson/esm-data/postgresql-data-9.4/postmaster.pid
>>> 9900
>>> /var/ericsson/esm-data/postgresql-data-9.4
>>> 1524039630
>>> 5432
>>> /var/run/postgresql
>>> localhost
>>> 5432001 2031616
>>>
>>>
>>> [root(at)ms-esmon /]# cat
>>> ./var/ericsson/esm-data/postgresql-data/postmaster.pid
>>> 9778
>>> /var/ericsson/esm-data/postgresql-data
>>> 50432001 1998850
>>>
>>>
>>>
>>>
>>> With Best Regards
>>> Akshay
>>>
>>>
>>>
>>>
>>>
>>> From: Fabio Pardi <f(dot)pardi(at)portavita(dot)eu>
>>> To: Akshay Ballarpure <akshay(dot)ballarpure(at)tcs(dot)com>,
>>> pgsql-general(at)lists(dot)postgresql(dot)org
>>> Date: 04/18/2018 01:06 PM
>>> Subject: Re: pg_upgrade help
>>>
------------------------------------------------------------------------
>>>
>>>
>>>
>>> Hi,
>>>
>>> please avoid crossposting to multiple mailing lists.
>>>
>>>
>>> You need to run both versions of the database, the old and the new.
>>>
>>> They need to run on different ports (note that it is impossible to run
2
>>> different processes on the same port, that's not a postgresql thing)
>>>
>>>
>>>
>>> On 04/18/2018 09:30 AM, Akshay Ballarpure wrote:
>>>> Hi all,
>>>> I need help on pg_upgrade from 8.4 to 9.4 version. Appreciate urgent
>>>> response.
>>>> Installed both version and stopped it. Do i need to run both version
or
>>>> only one 8.4 or 9.4 . Both should run on 50432 ?
>>>>
>>>>
>>>> -bash-4.2$ id
>>>> uid=26(postgres) gid=26(postgres) groups=26(postgres)
>>>> context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
>>>>
>>>> -bash-4.2$ export OLDCLUSTER=/var/ericsson/esm-data/postgresql-data

>>>> -- 8.4 data
>>>> -bash-4.2$ export
NEWCLUSTER=/var/ericsson/esm-data/postgresql-data-9.4
>>>> -- 9.4 data
>>>>
>>>>
>>>> -bash-4.2$ /opt/rh/rh-postgresql94/root/usr/bin/pg_upgrade
>>>> --old-bindir=/usr/bin
--new-bindir=/opt/rh/rh-postgresql94/root/usr/bin
>>>> --old-datadir=$OLDCLUSTER --new-datadir=$NEWCLUSTER
>>>>
>>>> *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/run/postgresql/.s.PGSQL.50432"?
>>>>
>>>>
>>>> could not connect to old postmaster started with the command:
>>>> "/usr/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
>>>> "/var/ericsson/esm-data/postgresql-data" -o "-p 50432 -c
autovacuum=off
>>>> -c autovacuum_freeze_max_age=2000000000 -c listen_addresses='' -c
>>>> unix_socket_permissions=0700" start
>>>> Failure, exiting
>>>>
>>>>
>>>>
>>>>
>>>> With Best Regards
>>>> Akshay
>>>>
>>>> =====-----=====-----=====
>>>> Notice: The information contained in this e-mail
>>>> message and/or attachments to it may contain
>>>> confidential or privileged information. If you are
>>>> not the intended recipient, any dissemination, use,
>>>> review, distribution, printing or copying of the
>>>> information contained in this e-mail message
>>>> and/or attachments to it are strictly prohibited. If
>>>> you have received this communication in error,
>>>> please notify us by reply e-mail or telephone and
>>>> immediately and permanently delete the message
>>>> and any attachments. Thank you
>>>>
>>>
>>
>