Re: [Gnumed-devel] Solved: Upgrade Postgresql for GNUmed on Windows

[Busser, Jim]

On 2013-11-19, at 9:59 AM, Karsten Hilbert <address@hidden> wrote:

> On Tue, Nov 19, 2013 at 05:56:55PM +0000, Jim Busser wrote:
> 
>>>>>>> 8.) type: pg_dump -p 5432 -Fc -f gnumedv18.backup gnumed_v18
>>>>>>> 
>>>>>>> 9.) type: pg_dumpall --globals-only > globals.sql
>>>>>> 
>>>>>> I think the above needs to be
>>>>>> 
>>>>>>  pg_dumpall -p 5432 --globals-only > globals.sql
>>>>> 
>>>>> That's safer but not required as 5432 is the default
>>>>> port unless reconfigured.
>>>> 
>>>> As would have been achieved from steps 2-5 taken earlier in the sequence:
>>>> 
>>>> - in Step 2, self-installation of the new postgres on port 5433 and
>>>> 
>>>> - in Step 6, SET PATH=%PATH%;C:\Programme\PostgreSQL\9.3\bin
>>> 
>>> Neither step reconfigures the default port 5432 of
>>> PG 8.4 as far as I can see.
>> 
>> My impression had been that in the process of upgrading, one is supposed to 
>> use the new binaries in place of the old ones.
>> 
>> This seems the case not just for pg_upgrade, but also for pg_dump and 
>> pg_dumpall
> 
> That's correct. And it's got nothing to do with
> the ports in use.

pg_dump under PostgreSQL 9.3 (running on port 5433) would not work for me 
without the 

        -p 5433

despite that 5433 is set in

        /Library/PostgreSQL/9.3/data/postgresql.conf(see below)

even after rebooting my computer. It kept complaining

        bash-3.2$ pg_dump -Fc -f gnumedv18.backup gnumed_v18
        pg_dump: [archiver (db)] connection to database "gnumed_v18" 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.5432"?

seemingly needing a PGPORT environmental variable to repoint to 5433:

        echo $PGPORT

        (nothing)

        export PGPORT=5433
        pg_dump -Fc -f gnumedv18.backup gnumed_v18
        pg_dumpall --globals-only > globals.sql

problem solved! Credit to the following for helping me:

        http://dba.stackexchange.com/a/24169
        http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/
        http://www.postgresql.org/docs/current/static/libpq-envars.html

after the various checks below had not answered my question.

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

Checks which did not explain my problem:

        whoami
        postgres

        which pg_dump
        /Library/PostgreSQL/9.3/bin/pg_dump 

        echo $PATH
        /Library/PostgreSQL/9.3/bin:/opt/local/bin…

        cat /Library/PostgreSQL/9.3/data/postgresql.conf | grep port
        port = 5433                             # (change requires restart) 
<--- but this was not a "change", per se

and running

        pg_ctl status  -D /Library/PostgreSQL/9.3/data/
        pg_ctl: server is running (PID: 49752)
        /Library/PostgreSQL/9.3/bin/postgres "-D/Library/PostgreSQL/9.3/data"

with root verifying /tmp/.s.PGSQL.5433 as listed among open files

        # lsof | grep PGSQL
        postgres  49752       postgres    6u     unix 0xa2482cb43f90706b        
0t0                     /tmp/.s.PGSQL.5433
        postgres  52118       postgres   12u     unix 0xa2482cb43bc27c83        
0t0                     /tmp/.s.PGSQL.5433
        postgres  52119       postgres   12u     unix 0xa2482cb43bc26ce3        
0t0                     /tmp/.s.PGSQL.5433

and even after restarting the computer, I still got the following on trying 
pg_dump without specifying the port

        pg_dump -Fc -f gnumedv18.backup gnumed_v18
        pg_dump: [archiver (db)] connection to database "gnumed_v18" 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.5432"?

is there some explanation for this not accounted for by what is in

        /Library/PostgreSQL/9.3/data/postgresql.conf

??

--> yes, answered above wrt PGPORT

-- Jim