Re: Broken after upgrade - Mailing list pgsql-general

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:

1) Did you run the Fedora postgresql-setup upgrade command at any time?

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