Re: Broken after upgrade

On Sat, Jul 9, 2016 at 5:08 PM, arnaud gaboury <arnaud.gaboury@gmail.com> 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).
>
>
>>
>> 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.
>>
UPDATE:

with the idea to start my database with postgres 9.4 then dump the
databse. I run:

$ /usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl -D /db/pgsql/data-9.4 start

but it returns this error:
FATAL:  58P01: could not access file "pg_stat_statements": No such
file or directory

$ ls -al data-9.4/pg_stat
.......
-rw------- 1 postgres postgres  905 Jul  9 15:36 pg_stat_statements.stat


Why do I have this error then ?

--

google.com/+arnaudgabourygabx