Re: pg_upgrade help - Mailing list pgsql-general

Hi,

while trying to reproduce your problem, i noticed that on my Centos 6 installations Postgres 8.4 and Postgres 9.6 (I do
nothave 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@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@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@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@ms-esmon root]# su - postgres -c "/usr/bin/postgres -p 50432 -D /var/ericsson/esm-data/postgresql-data/ 2>&1
&"
> [root@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@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@ms-esmon root]#
> [root@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@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.ballarpure@tcs.com
> Website: http://www.tcs.com <http://www.tcs.com/>
> ____________________________________________
> Experience certainty.        IT Services
>                        Business Solutions
>                        Consulting
> ____________________________________________
> 
> 
> 
> 
> From:        Fabio Pardi <f.pardi@portavita.eu>
> To:        Akshay Ballarpure <akshay.ballarpure@tcs.com>
> Cc:        pgsql-general@lists.postgresql.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.ballarpure@tcs.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.pardi@portavita.eu>
>> To:        Akshay Ballarpure <akshay.ballarpure@tcs.com>,
>> pgsql-general@lists.postgresql.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@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@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.pardi@portavita.eu>
>>> To:        Akshay Ballarpure <akshay.ballarpure@tcs.com>,
>>> pgsql-general@lists.postgresql.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
>>>>
>>>
>>
>