Re: Broken after upgrade - Mailing list pgsql-general

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).


>
> 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



--

google.com/+arnaudgabourygabx