Re: pg_upgrade from V9.3.3 to V9.4.0

On Sun, Jan 11, 2015 at 6:25 AM, John Scalia <jayknowsunix(at)gmail(dot)com> wrote:

>
> On 1/10/2015 3:32 PM, Bruce Momjian wrote:
>
>> On Sat, Jan 10, 2015 at 07:13:00PM +0530, Harshad Adalkonda wrote:
>>
>>> On Sat, Jan 10, 2015 at 3:57 AM, John Scalia <jayknowsunix(at)gmail(dot)com>
>>> wrote:
>>>
>>> Hi all,
>>>
>>> While we're not planning to migrate to the new version, I'm trying
>>> to gain
>>> some experience as the streaming replication has really changed in
>>> this
>>> version and indicates future directions for it. My environment is
>>> virtualized CentO/S 6.5, and this particular server is the current
>>> primary,
>>> not a standby. My problem is that the pg_upgrade is just hanging -
>>> for over
>>> an hour, before I killed it. I only see a "Verifying Cluster"
>>> message, and
>>> that's it. Even the 3 logs written by pg_upgrade, don't really tell
>>> me any
>>> more. Am I trying to go too far with this upgrade? Do I need to
>>> bring it
>>> V9.3.4 first, or what? The behavior just seems a bit odd. I've only
>>> run
>>> this prior going from 9.2.2 to 9.3.3 and that had no issues.
>>>
>>> Hi John Scalia,
>>>
>>> I have tried pg_upgrade from 9.3.3 to 9.4.0 and i have done with the
>>> following
>>> procedure which will solve your problem.
>>>
>>> Create .pgpass file in users home directory ~/.pgpass and add both
>>> server with
>>> their respective user & password
>>>
>> Oh, so you are thinking it is waiting for a password? Wouldn't it say
>> "Password:"?
>>
>> I saw nothing on the terminal, nor anything in the logfiles indicating a
> password request.
>

If you don't set security *trust* in pg_hba.conf OR .pgpass file it will
give you error.

It will not ask for password if you have mdf in pg_hba.conf OR no .pgpass
entry about the server.

it will directly give you following error.

-bash-4.1$ /opt/PostgreSQL/9.4/bin/pg_upgrade -c -v -d
/tmp/PostgreSQL/9.3.3/data/ -D /tmp/PostgreSQL/9.4.0/data/ -b
/opt/PostgreSQL/9.3.3/bin/ -B /opt/PostgreSQL/9.4/bin/ -p 5888 -P 5999 -U
postgres

Running in verbose
mode

Performing Consistency
Checks

-----------------------------

Checking cluster versions
ok

pg_control
values:

First log segment after reset: 000000010000000000000079
pg_control version number: 937
Catalog version number: 201306121
Database system identifier: 6102580828849880319
Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1816
Latest checkpoint's NextOID: 16396
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1800
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 2000
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by reference
Data page checksum version: 0
Current pg_control values:

pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6102580804446358846
Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1812
Latest checkpoint's NextOID: 16393
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1801
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 2000
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by reference
Data page checksum version: 0

Values to be changed:

First log segment after reset: 000000010000000000000002
"/opt/PostgreSQL/9.3.3/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
"/tmp/PostgreSQL/9.3.3/data/" -o "-p 5888 -b -c listen_addresses='' -c
unix_socket_permissions=0700 -c
unix_socket_directories='/tmp/PostgreSQL/9.3'" start >>
"pg_upgrade_server.log"
2>&1

connection to database failed: fe_sendauth: no password supplied

could not connect to old postmaster started with the command:
"/opt/PostgreSQL/9.3.3/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D
"/tmp/PostgreSQL/9.3.3/data/" -o "-p 5888 -b -c listen_addresses='' -c
unix_socket_permissions=0700 -c
unix_socket_directories='/tmp/PostgreSQL/9.3'"
start

Failure,
exiting

"/opt/PostgreSQL/9.3.3/bin/pg_ctl" -w -D "/tmp/PostgreSQL/9.3.3/data/" -o
"" -m fast stop >> "pg_upgrade_server.log"
2>&1

--
Thanks & Regards,
*Harshad Adalkonda*
Database Administrator
http://www.shreeyansh.com