Re: Broken after upgrade

On 07/09/2016 08:08 AM, arnaud gaboury wrote:
> On Sat, Jul 9, 2016 at 4:16 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> On 07/09/2016 03:09 AM, arnaud gaboury wrote:
>>>
>>> On Sat, Jul 9, 2016 at 11:54 AM, arnaud gaboury
>>> <arnaud.gaboury@gmail.com> wrote:
>>>>
>>>> On Thu, Jul 7, 2016 at 10:01 AM, arnaud gaboury
>>>> <arnaud.gaboury@gmail.com> wrote:
>>>>>
>>>>> On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
>>>>> <jtj@tssystems.net> wrote:
>>>>>>
>>>>>> El 7/7/16 a las 0:14, Devrim Gündüz escribió:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> Fedora packages have an executable (not sure about its name, but check
>>>>>>> a
>>>>>>> package called postgresql-upgrade or so). That will help you to finish
>>>>>>> the
>>>>>>> upgrade process.
>>>>>>>
>>>>>>> Regards, Devrim
>>>>>>>
>>>>>> Hello
>>>>>>
>>>>>> Yes, the package is called postgresql-upgrade, and you can read
>>>>>> detailed
>>>>>> instructions in /usr/share/doc/postgresql
>>>>>>
>>>>>> In Fedora 22 (the machine i can check now) the document is called
>>>>>> README.rpm-dist in that directory
>>>>>>
>>>>>> Hope helps you
>>>>>
>>>>>
>>>>> I effectively found the postgresql-update tool. But when running it, I
>>>>> get an error:
>>>>>
>>>>> # postgresql-setup --upgrade
>>>>>
>>>>>  * Upgrading database.
>>>>> ERROR: pg_upgrade tool failed
>>>>> ERROR: Upgrade failed.
>>>>>  * See /var/lib/pgsql/upgrade_postgresql.log for details.
>>>>>
>>>>>
>>>>> #  cat /var/lib/pgsql/upgrade_postgresql.log
>>>>> -----------------------------------------------------------------
>>>>> 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 "/home/postgres/.s.PGSQL.5432"?
>>>>>
>>>>>
>>>>> could not connect to old postmaster started with the command:
>>>>> "/usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl" -w -l
>>>>> "pg_upgrade_server.log" -D "/db/pgsql/data-old" -o "-p 5432 -b  -c
>>>>> listen_addresses='' -c unix_socket_permissions=0700 -c
>>>>> unix_socket_directories='/home/postgres'" start
>>>>> Failure, exiting
>>>>> --------------------------------------------------
>>>>>
>>>>> My data dir is in fact /db/pgsql/data. I do not know why it is looking
>>>>> for data-old.
>>>>> I will investigate this way.
>>>>>
>>>>
>>>> It took me some time to figure out the correct way for Fedora to
>>>> upgrade pgsql. Here is the command I am supposed to run as postgres
>>>> user, with postgresql.service stopped:
>>>>
>>>>
>>>> $ pg_upgrade -b /usr/lib64/pgsql/postgresql-9.4/bin/ -B /usr/bin -d
>>>> /db/pgsql/data.old -D /db/pgsql/data
>>>>
>>>> with data a new DB initialized with postgresql 9.5, data.old my
>>>> original DB, postgresql-9.4/bin old cluster executable.
>>>> NOTE: I didn't put my original config files pg_hba.conf, pg_ident.conf
>>>> and postgresql.conf in the new data directory. I left everything
>>>> untouched after the init command. Is it correct ?
>>>>
>>>> The above command returns an error:
>>>> -----------------------------------------------------------------------
>>>> *failure*
>>>> Consult the last few lines of "pg_upgrade_server.log" for
>>>> the probable cause of the failure.
>>>>
>>>> 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 "/db/pgsql/.s.PGSQL.50432"?
>>>>
>>>>
>>>> could not connect to old postmaster started with the command:
>>>> "/usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl" -w -l
>>>> "pg_upgrade_server.log" -D "/db/pgsql/data.old" -o "-p 50432 -b  -c
>>>> listen_addresses='' -c unix_socket_permissions=0700 -c
>>>> unix_socket_directories='/db/pgsql'" start
>>>> Failure, exiting
>>>> ---------------------------------------------------------------------
>>>>
>>>> My postgresql service is in fact started with its socket in
>>>> /run/postgresql directory, as indicated in my postgresql.conf. Now,
>>>> when running pg_upgrade, where is the socket ? I guess it shall be in
>>>> << unix_socket_directories='/db/pgsql' >> as indicated in the log file
>>>> above? But it isn't apparently as postgresql can find the socket. How
>>>> can I change this behavior? How can I tell pg_upgrade to place the
>>>> socket in /db/pgsql ?
>>>>
>>>> Thank you for help. This issue is getting me crazy and my mail server is
>>>> broken.
>>>
>>>
>>> UPDATE
>>>
>>> Running the following command (it indicates where to find the socket):
>>>
>>> $  /usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl -w -l
>>> "pg_upgrade_server.log" -D "/db/pgsql/data.old" -o "-p 50432 -b  -c
>>> listen_addresses='' -c unix_socket_permissions=0700 -c
>>> unix_socket_directories='/run/postgresql'" start >>
>>> "pg_upgrade_server.log"
>>>
>>>
>>> leave me this time with another error, in fact the original one:
>>>
>>> % cat pg_upgrade_server.log
>>> waiting for server to start....FATAL:  22023: database files are
>>> incompatible with server
>>> DETAIL:  The data directory was initialized by PostgreSQL version 9.5,
>>> which is not compatible with this version 9.4.8.
>>> LOCATION:  ValidatePgVersion, miscinit.c:1216
>>>  stopped waiting
>>
>>
>> Actually it is the opposite of your original message:
>>
>> "postgres(at)thetradinghall ➤➤ systemd/system %
>> /usr/libexec/postgresql-ctl start -D /db/pgsql/data -s -w -t 270
>> FATAL:  22023: database files are incompatible with server
>> DETAIL:  The data directory was initialized by PostgreSQL version 9.4,
>> which is not compatible with this version 9.5.3.
>>
>>>
>>> So aside this socket issue, it seems I am back again to the
>>> initialization issue. Is there something wrong in the command I use ?
>>>
>>> $ pg_upgrade -b /usr/lib64/pgsql/postgresql-9.4/bin/ -B /usr/bin -d
>>> /db/pgsql/data.old -D /db/pgsql/data
>>>
>>
>> Have no idea as I am confused as to your layout and what you have done, so:
>
> I understand you are confused, as I am too and tried too many things
> with no success.
>
> I have postgresql 9.5.3 installed, and the upgrade was from 9.4.8 to 9.5.3.
> I can find in /usr/lib64/pgsql/postgresql-9.4 some binaries from
> previous versions. Nothing in /opt/ as it can be sometimes indicated.
>
> I have my old database in /db/pgsql/data.old and the new empty
> initialized with 9.5 in /db/pgsql/data.old (here versions are given by
> PG_VERSION files).

The above does not make sense, they both cannot be in the same data
directory. So to repeat, please answer each of the below after the
question and in detail eg. showing the actual commands you ran and the
results:



2) What versions of Postgres do you have installed and/or running?
Please verify from package manager and/or ps.

3) List what is in the PG_VERSION file of each directory you are using.


>
>
>>
>> 1) Did you run the Fedora postgresql-setup upgrade command at any time?
> Yes I did many times against a /db/pgsql/data with my 9.4 database (If
> I run the command with the 9.5 initialized data folder, I get ERROR:
> Cannot upgrade because the database in /db/pgsql/data is of
>        version 9.5 but it should be 9.4).

>
> The command failed with this error:
>
> --------------------------
> *failure*
> Consult the last few lines of "pg_upgrade_server.log" for
> the probable cause of the failure.
>
> 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 "/home/postgres/.s.PGSQL.5432"?
> could not connect to old postmaster started with the command:
> "/usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl" -w -l
> "pg_upgrade_server.log" -D "/db/pgsql/data-old" -o "-p 5432 -b  -c
> listen_addresses='' -c unix_socket_permissions=0700 -c
> unix_socket_directories='/home/postgres'" start
> ----------------------------
>
> As already mentioned, postgresql put its socket in /run/postgresql.
> Btw, both directories (/run/postgresql and /home/postgresql) are
> indicated in postgresql.conf:
>  unix_socket_directories = '/home/postgres,/run/postgresql'
>
> I would like now to try the above command when avoiding this socket story issue.
>>
>> 2) What versions of Postgres do you have installed and/or running?
>> Please verify from package manager and/or ps.
>>
>> 3) List what is in the PG_VERSION file of each directory you are using.
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com