Обсуждение: Broken after upgrade
I am left today, after an upgrade I think (Fedora 23 --> 24) with a broken postgresql. It used to work very well. The service is started with the systemd unit file. I have now this error: ----------------- postgres@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. ----------------------- Googling it gave me some entries, but I couldn't find any clear explanation and how to solve. It seems I need to initiate a new database, but thereafter is not clear (how to use my old data and conf files pg_hba and pg_ident). Thank you for hints/links how to solve this issue. -- google.com/+arnaudgabourygabx
On 07/06/2016 11:13 AM, arnaud gaboury wrote: > I am left today, after an upgrade I think (Fedora 23 --> 24) with a > broken postgresql. > > It used to work very well. The service is started with the systemd > unit file. I have now this error: > > ----------------- > postgres@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. > ----------------------- > > Googling it gave me some entries, but I couldn't find any clear > explanation and how to solve. It seems I need to initiate a new > database, but thereafter is not clear (how to use my old data and conf > files pg_hba and pg_ident). > > Thank you for hints/links how to solve this issue. First, if possible copy your data dir and conf files somewhere for safety. From the looks of it Fedora upgraded your Postgres from 9.4.x to 9.5.3. That constitutes a major upgrade and as the error says the data directory is no longer compatible. What Postgres versions are installed? Or did Fedora get rid of the Postgres 9.4 entirely? I do not use Fedora so I cannot get any more specific but this might help( I would still backup up your data directory manually, even if the instructions below say that is done for you): https://fedoraproject.org/wiki/PostgreSQL in particular: https://fedoraproject.org/wiki/PostgreSQL#Upgrade > -- Adrian Klaver adrian.klaver@aklaver.com
On 7/6/2016 11:27 AM, Adrian Klaver wrote: > > First, if possible copy your data dir and conf files somewhere for > safety. +1 !!! must be first step. > > From the looks of it Fedora upgraded your Postgres from 9.4.x to > 9.5.3. That constitutes a major upgrade and as the error says the data > directory is no longer compatible. > > What Postgres versions are installed? > > Or did Fedora get rid of the Postgres 9.4 entirely? > > I do not use Fedora so I cannot get any more specific but this might > help( I would still backup up your data directory manually, even if > the instructions below say that is done for you): > > https://fedoraproject.org/wiki/PostgreSQL > > in particular: > > https://fedoraproject.org/wiki/PostgreSQL#Upgrade pretty much whatever you do, you'll need to install the 9.4 database server software temporarily in a separate directory, and you'll need to do the upgrade. if your data is reasonable sized (say, under a few dozen gigabytes?), the easiest upgrade is to fire up both servers (configure that 9.4 to use a different port, like 5434), where 9.4 is running with your existing data directory, and 9.5 is running a freshly initdb'd empty database cluster, then do something like pg_dumpall -p 5434 | psql -p 5432 if the data is larger, that could take a really long time, so you'll want to do a pg_upgrade, again with 9.4 configured for the existing database, and 9.5 running on an empty new cluster, then follow the pg_upgrade instructions on the website links above. this goes much faster if both 'data' directories are on the same file system, and you use the 'links' option... -- john r pierce, recycling bits in santa cruz
On Wed, Jul 6, 2016 at 8:56 PM, John R Pierce <pierce@hogranch.com> wrote: > On 7/6/2016 11:27 AM, Adrian Klaver wrote: >> >> >> First, if possible copy your data dir and conf files somewhere for safety. > > > +1 !!! must be first step. It is the first thing I did > >> >> From the looks of it Fedora upgraded your Postgres from 9.4.x to 9.5.3. >> That constitutes a major upgrade and as the error says the data directory is >> no longer compatible. I can read here[0] on postgresql website that 9.5 have major enhancements. >> >> What Postgres versions are installed? >> >> Or did Fedora get rid of the Postgres 9.4 entirely? Yes. I am only left with 9.5.3 >> >> I do not use Fedora so I cannot get any more specific but this might help( >> I would still backup up your data directory manually, even if the >> instructions below say that is done for you): >> >> https://fedoraproject.org/wiki/PostgreSQL >> >> in particular: >> >> https://fedoraproject.org/wiki/PostgreSQL#Upgrade > > > pretty much whatever you do, you'll need to install the 9.4 database server > software temporarily in a separate directory, and you'll need to do the > upgrade. So the idea is to downgrade first to 9.4.8 ? I will try this way > > if your data is reasonable sized (say, under a few dozen gigabytes?), the > easiest upgrade is to fire up both servers (configure that 9.4 to use a > different port, like 5434), where 9.4 is running with your existing data > directory, and 9.5 is running a freshly initdb'd empty database cluster, > then do something like pg_dumpall -p 5434 | psql -p 5432 My data are small hopefully. > > if the data is larger, that could take a really long time, so you'll want to > do a pg_upgrade, again with 9.4 configured for the existing database, and > 9.5 running on an empty new cluster, then follow the pg_upgrade instructions > on the website links above. this goes much faster if both 'data' > directories are on the same file system, and you use the 'links' option... > > [0]https://www.postgresql.org/docs/9.5/static/release-9-5.html -- google.com/+arnaudgabourygabx
On 7/6/2016 2:13 PM, arnaud gaboury wrote:
pretty much whatever you do, you'll need to install the 9.4 database server > software temporarily in a separate directory, and you'll need to do the > upgrade.So the idea is to downgrade first to 9.4.8 ? I will try this way
no, not downgrade, you need both sets of code. install 9.4 (from the PGDG yum repository) separately. the PGDG 9.4 code should go into /usr/pgsql/9.4 and it will by default look for its data in /var/lib/pgsql/9.4/data (so move your data directory to that...)
-- john r pierce, recycling bits in santa cruz
On 07/06/2016 02:13 PM, arnaud gaboury wrote: > On Wed, Jul 6, 2016 at 8:56 PM, John R Pierce <pierce@hogranch.com> wrote: >> On 7/6/2016 11:27 AM, Adrian Klaver wrote: >>> >>> >>> First, if possible copy your data dir and conf files somewhere for safety. >> >> >> +1 !!! must be first step. > > It is the first thing I did >> >>> >>> From the looks of it Fedora upgraded your Postgres from 9.4.x to 9.5.3. >>> That constitutes a major upgrade and as the error says the data directory is >>> no longer compatible. > > I can read here[0] on postgresql website that 9.5 have major enhancements. >>> >>> What Postgres versions are installed? >>> >>> Or did Fedora get rid of the Postgres 9.4 entirely? > > Yes. I am only left with 9.5.3 >>> >>> I do not use Fedora so I cannot get any more specific but this might help( >>> I would still backup up your data directory manually, even if the >>> instructions below say that is done for you): >>> >>> https://fedoraproject.org/wiki/PostgreSQL >>> >>> in particular: >>> >>> https://fedoraproject.org/wiki/PostgreSQL#Upgrade >> >> >> pretty much whatever you do, you'll need to install the 9.4 database server >> software temporarily in a separate directory, and you'll need to do the >> upgrade. > > So the idea is to downgrade first to 9.4.8 ? I will try this way Take a look at this link: https://fedoraproject.org/wiki/PostgreSQL#Upgrade It seems to offer a built in way to do this. >> >> if your data is reasonable sized (say, under a few dozen gigabytes?), the >> easiest upgrade is to fire up both servers (configure that 9.4 to use a >> different port, like 5434), where 9.4 is running with your existing data >> directory, and 9.5 is running a freshly initdb'd empty database cluster, >> then do something like pg_dumpall -p 5434 | psql -p 5432 > > My data are small hopefully. >> >> if the data is larger, that could take a really long time, so you'll want to >> do a pg_upgrade, again with 9.4 configured for the existing database, and >> 9.5 running on an empty new cluster, then follow the pg_upgrade instructions >> on the website links above. this goes much faster if both 'data' >> directories are on the same file system, and you use the 'links' option... >> >> > [0]https://www.postgresql.org/docs/9.5/static/release-9-5.html > > -- Adrian Klaver adrian.klaver@aklaver.com
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
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
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
On July 6, 2016 9:13:50 PM GMT+03:00, arnaud gaboury <arnaud.gaboury@gmail.com> wrote:
I am left today, after an upgrade I think (Fedora 23 --> 24) with a
broken postgresql.
It used to work very well. The service is started with the systemd
unit file. I have now this error:
-----------------
postgres@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.
-----------------------
Googling it gave me some entries, but I couldn't find any clear
explanation and how to solve. It seems I need to initiate a new
database, but thereafter is not clear (how to use my old data and conf
files pg_hba and pg_ident).
Thank you for hints/links how to solve this issue.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
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 Best
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.
>
> Best
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
google.com/+arnaudgabourygabx
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.
google.com/+arnaudgabourygabx
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 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 -- google.com/+arnaudgabourygabx
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
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
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
On 07/09/2016 08:59 AM, arnaud gaboury wrote: > 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 ? > Please let's not introduce new errors until we figure out the existing errors and setup. -- Adrian Klaver adrian.klaver@aklaver.com
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
On Sat, Jul 9, 2016 at 7:31 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > 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? % dnf info postgresql Last metadata expiration check: 2 days, 20:46:44 ago on Wed Jul 6 23:17:54 2016. Installed Packages Name : postgresql Arch : x86_64 Epoch : 0 Version : 9.5.3 Release : 1.fc24 > > > 3) List what is in the PG_VERSION file of each directory you are using. % cat data-9.4/PG_VERSION 9.4 % cat data-9.5/PG_VERSION 9.5 >>> >>> 1) Did you run the Fedora postgresql-setup upgrade command at any time? YES >> >> 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 -- google.com/+arnaudgabourygabx
On 07/09/2016 11:06 AM, arnaud gaboury wrote: > On Sat, Jul 9, 2016 at 7:31 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> On 07/09/2016 08:08 AM, arnaud gaboury wrote: >>> >>> 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? > % dnf info postgresql > Last metadata expiration check: 2 days, 20:46:44 ago on Wed Jul 6 > 23:17:54 2016. > Installed Packages > Name : postgresql > Arch : x86_64 > Epoch : 0 > Version : 9.5.3 > Release : 1.fc24 Have you installed Postgres from some other source then the package manager? Just trying to figure out where the 9.4 binaries are coming from? > >> >> >> 3) List what is in the PG_VERSION file of each directory you are using. > % cat data-9.4/PG_VERSION > 9.4 > % cat data-9.5/PG_VERSION > 9.5 So where are you getting the below directories from?: /db/pgsql/data /db/pgsql/data.old > >>>> >>>> 1) Did you run the Fedora postgresql-setup upgrade command at any time? > YES > > >>> >>> 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). >> >> >>> -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, Jul 9, 2016 at 8:22 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 07/09/2016 11:06 AM, arnaud gaboury wrote: >> >> On Sat, Jul 9, 2016 at 7:31 PM, Adrian Klaver <adrian.klaver@aklaver.com> >> wrote: >>> >>> On 07/09/2016 08:08 AM, arnaud gaboury wrote: > > >>>> >>>> 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? >> >> % dnf info postgresql >> Last metadata expiration check: 2 days, 20:46:44 ago on Wed Jul 6 >> 23:17:54 2016. >> Installed Packages >> Name : postgresql >> Arch : x86_64 >> Epoch : 0 >> Version : 9.5.3 >> Release : 1.fc24 > > > > Have you installed Postgres from some other source then the package manager? NO > > Just trying to figure out where the 9.4 binaries are coming from? postgresql-update Fedora package > >> >>> >>> >>> 3) List what is in the PG_VERSION file of each directory you are using. >> >> % cat data-9.4/PG_VERSION >> 9.4 >> % cat data-9.5/PG_VERSION >> 9.5 > > > So where are you getting the below directories from?: > > /db/pgsql/data > /db/pgsql/data.old I changed the name to 9-4 et 9-5 for convenience > > >> >>>>> >>>>> 1) Did you run the Fedora postgresql-setup upgrade command at any time? >> >> YES >> >> >>>> >>>> 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). >>> >>> >>> >>>> > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com -- google.com/+arnaudgabourygabx
On 07/09/2016 11:40 AM, arnaud gaboury wrote: > On Sat, Jul 9, 2016 at 8:22 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> On 07/09/2016 11:06 AM, arnaud gaboury wrote: >>> >>> On Sat, Jul 9, 2016 at 7:31 PM, Adrian Klaver <adrian.klaver@aklaver.com> >>> wrote: >>>> >>>> On 07/09/2016 08:08 AM, arnaud gaboury wrote: >> >> >>>>> >>>>> 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? >>> >>> % dnf info postgresql >>> Last metadata expiration check: 2 days, 20:46:44 ago on Wed Jul 6 >>> 23:17:54 2016. >>> Installed Packages >>> Name : postgresql >>> Arch : x86_64 >>> Epoch : 0 >>> Version : 9.5.3 >>> Release : 1.fc24 >> >> >> >> Have you installed Postgres from some other source then the package manager? > NO >> >> Just trying to figure out where the 9.4 binaries are coming from? > postgresql-update Fedora package > > >> >>> >>>> >>>> >>>> 3) List what is in the PG_VERSION file of each directory you are using. >>> >>> % cat data-9.4/PG_VERSION >>> 9.4 >>> % cat data-9.5/PG_VERSION >>> 9.5 >> >> >> So where are you getting the below directories from?: >> >> /db/pgsql/data >> /db/pgsql/data.old > > I changed the name to 9-4 et 9-5 for convenience Please don't do that, it just adds to the confusion. For instance I still do not know what data-* maps to /db/pgsql/data*. So please run the cat on the actual directories. >> >> >>> >>>>>> >>>>>> 1) Did you run the Fedora postgresql-setup upgrade command at any time? >>> >>> YES >>> >>> >>>>> >>>>> 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). >>>> >>>> >>>> >>>>> >> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, Jul 9, 2016 at 8:45 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 07/09/2016 11:40 AM, arnaud gaboury wrote: >> >> On Sat, Jul 9, 2016 at 8:22 PM, Adrian Klaver <adrian.klaver@aklaver.com> >> wrote: >>> >>> On 07/09/2016 11:06 AM, arnaud gaboury wrote: >>>> >>>> >>>> On Sat, Jul 9, 2016 at 7:31 PM, Adrian Klaver >>>> <adrian.klaver@aklaver.com> >>>> wrote: >>>>> >>>>> >>>>> On 07/09/2016 08:08 AM, arnaud gaboury wrote: >>> >>> >>> >>>>>> >>>>>> 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? >>>> >>>> >>>> % dnf info postgresql >>>> Last metadata expiration check: 2 days, 20:46:44 ago on Wed Jul 6 >>>> 23:17:54 2016. >>>> Installed Packages >>>> Name : postgresql >>>> Arch : x86_64 >>>> Epoch : 0 >>>> Version : 9.5.3 >>>> Release : 1.fc24 >>> >>> >>> >>> >>> Have you installed Postgres from some other source then the package >>> manager? >> >> NO >>> >>> >>> Just trying to figure out where the 9.4 binaries are coming from? >> >> postgresql-update Fedora package >> >> >>> >>>> >>>>> >>>>> >>>>> 3) List what is in the PG_VERSION file of each directory you are using. >>>> >>>> >>>> % cat data-9.4/PG_VERSION >>>> 9.4 >>>> % cat data-9.5/PG_VERSION >>>> 9.5 >>> >>> >>> >>> So where are you getting the below directories from?: >>> >>> /db/pgsql/data >>> /db/pgsql/data.old >> >> >> I changed the name to 9-4 et 9-5 for convenience > > > Please don't do that, it just adds to the confusion. For instance I still do > not know what data-* maps to /db/pgsql/data*. > > So please run the cat on the actual directories. Here is my /db/pgsql : data-9.4 (formerly data) data-9.5 (formerly data.old) > > >>> >>> >>>> >>>>>>> >>>>>>> 1) Did you run the Fedora postgresql-setup upgrade command at any >>>>>>> time? >>>> >>>> >>>> YES >>>> >>>> >>>>>> >>>>>> 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). >>>>> >>>>> >>>>> >>>>> >>>>>> >>> >>> >>> >>> -- >>> Adrian Klaver >>> adrian.klaver@aklaver.com >> >> >> >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com -- google.com/+arnaudgabourygabx
On 07/09/2016 12:09 PM, arnaud gaboury wrote: >>>>> >>>>> >>>>> % dnf info postgresql >>>>> Last metadata expiration check: 2 days, 20:46:44 ago on Wed Jul 6 >>>>> 23:17:54 2016. >>>>> Installed Packages >>>>> Name : postgresql >>>>> Arch : x86_64 >>>>> Epoch : 0 >>>>> Version : 9.5.3 >>>>> Release : 1.fc24 >>>> >>>> >>>> >>>> >>>> Have you installed Postgres from some other source then the package >>>> manager? >>> >>> NO >>>> >>>> >>>> Just trying to figure out where the 9.4 binaries are coming from? >>> >>> postgresql-update Fedora package >>> >>> >>>> >>>>> >>>>>> >>>>>> >>>>>> 3) List what is in the PG_VERSION file of each directory you are using. >>>>> >>>>> >>>>> % cat data-9.4/PG_VERSION >>>>> 9.4 >>>>> % cat data-9.5/PG_VERSION >>>>> 9.5 >>>> >>>> >>>> >>>> So where are you getting the below directories from?: >>>> >>>> /db/pgsql/data >>>> /db/pgsql/data.old >>> >>> >>> I changed the name to 9-4 et 9-5 for convenience >> >> >> Please don't do that, it just adds to the confusion. For instance I still do >> not know what data-* maps to /db/pgsql/data*. >> >> So please run the cat on the actual directories. > > Here is my /db/pgsql : > > data-9.4 (formerly data) > data-9.5 (formerly data.old) So back to one of your questions from upstream: "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 " https://www.postgresql.org/docs/9.5/static/pgupgrade.html -d datadir --old-datadir=datadir the old cluster data directory; environment variable PGDATAOLD -D datadir --new-datadir=datadir the new cluster data directory; environment variable PGDATANEW You have the directories swapped. There is still the issue of the 9.4 binaries and which version of Postgres you are running commands from at any given time. I would suggest using full paths to the commands until this is resolved. > >> >> >>>> >>>> >>>>> >>>>>>>> >>>>>>>> 1) Did you run the Fedora postgresql-setup upgrade command at any >>>>>>>> time? >>>>> >>>>> >>>>> YES >>>>> >>>>> >>>>>>> >>>>>>> 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). >>>>>> >>>>>> >>>>>> >>>>>> >>>>>>> >>>> >>>> >>>> >>>> -- >>>> Adrian Klaver >>>> adrian.klaver@aklaver.com >>> >>> >>> >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver wrote: > There is still the issue of the 9.4 binaries and which version of > Postgres you are running commands from at any given time. I would > suggest using full paths to the commands until this is resolved. It reads to me as if the OP's Fedora 23 -> Fedora 24 upgrade didn't install the new Postgres package(s) side-by-side with the old ones, it "upgraded" them, so the old Postgres binaries are no longer present. Rummaging in the Fedora package repository: http://dl.fedoraproject.org/pub/fedora/linux/releases/23/Server/x86_64/os/Packages/p/ http://dl.fedoraproject.org/pub/fedora/linux/releases/24/Server/x86_64/os/Packages/p/ would seem to agree; the postgresql-<version> packages list the exact same program binaries in the 9.4 (Fedora 23) and 9.5 (Fedora 24) packages. (Modulo one dropped binary and one new one.) The pathnames do not refer to the Postgres version, so you can't have both installed at the same time. I suspect the solution is going to be to revert Postgres and its dependencies to the packages from Fedora 23, pg_dump, reinstall the updated versions from Fedora 24, then restore the dump into the empty 9.5 install. Or, revert Postgres and its dependencies, and install 9.5 from the PGDG repo (which won't stomp on Fedora's 9.4 package), and use pg_upgrade. -kgd