Discussion:
[GENERAL] Broken after upgrade
(too old to reply)
arnaud gaboury
2016-07-06 18:13:50 UTC
Permalink
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:

-----------------
***@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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Adrian Klaver
2016-07-06 18:27:46 UTC
Permalink
Post by arnaud gaboury
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
-----------------
/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
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
John R Pierce
2016-07-06 18:56:51 UTC
Permalink
Post by Adrian Klaver
First, if possible copy your data dir and conf files somewhere for
safety.
+1 !!! must be first step.
Post by Adrian Klaver
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
https://fedoraproject.org/wiki/PostgreSQL
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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
arnaud gaboury
2016-07-06 21:13:40 UTC
Permalink
Post by John R Pierce
Post by Adrian Klaver
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
Post by John R Pierce
Post by Adrian Klaver
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.
Post by John R Pierce
Post by Adrian Klaver
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
Post by John R Pierce
Post by Adrian Klaver
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
https://fedoraproject.org/wiki/PostgreSQL
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
Post by John R Pierce
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.
Post by John R Pierce
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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
John R Pierce
2016-07-06 21:31:09 UTC
Permalink
Post by arnaud gaboury
Post by John R Pierce
pretty much whatever you do, you'll need to install the 9.4 database server
Post by John R Pierce
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
Adrian Klaver
2016-07-06 21:49:29 UTC
Permalink
Post by arnaud gaboury
Post by John R Pierce
Post by Adrian Klaver
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
Post by John R Pierce
Post by Adrian Klaver
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.
Post by John R Pierce
Post by Adrian Klaver
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
Post by John R Pierce
Post by Adrian Klaver
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
https://fedoraproject.org/wiki/PostgreSQL
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.
Post by arnaud gaboury
Post by John R Pierce
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.
Post by John R Pierce
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
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Devrim Gündüz
2016-07-06 22:14:28 UTC
Permalink
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
Post by arnaud gaboury
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
-----------------
/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
--
http://www.postgresql.org/mailpref/pgsql-general
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
José María Terry Jiménez
2016-07-06 23:00:55 UTC
Permalink
Post by Devrim Gündüz
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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
arnaud gaboury
2016-07-07 08:01:41 UTC
Permalink
On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
Post by José María Terry Jiménez
Post by Devrim Gündüz
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.
Post by José María Terry Jiménez
Best
--
http://www.postgresql.org/mailpref/pgsql-general
--
google.com/+arnaudgabourygabx
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
arnaud gaboury
2016-07-09 10:09:15 UTC
Permalink
On Sat, Jul 9, 2016 at 11:54 AM, arnaud gaboury
On Thu, Jul 7, 2016 at 10:01 AM, arnaud gaboury
Post by arnaud gaboury
On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
Post by José María Terry Jiménez
Post by Devrim Gündüz
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
# 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"?
"/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
$ 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 ?
-----------------------------------------------------------------------
*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"?
"/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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
arnaud gaboury
2016-07-09 15:08:46 UTC
Permalink
Post by arnaud gaboury
On Sat, Jul 9, 2016 at 11:54 AM, arnaud gaboury
On Thu, Jul 7, 2016 at 10:01 AM, arnaud gaboury
Post by arnaud gaboury
On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
Post by José María Terry Jiménez
Post by Devrim Gündüz
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
# 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"?
"/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
$ 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 ?
-----------------------------------------------------------------------
*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"?
"/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
$ /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"
% 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
"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.
Post by arnaud gaboury
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
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
--
google.com/+arnaudgabourygabx
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
arnaud gaboury
2016-07-09 18:40:56 UTC
Permalink
Post by arnaud gaboury
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
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
/db/pgsql/data
/db/pgsql/data.old
I changed the name to 9-4 et 9-5 for convenience
Post by arnaud gaboury
1) Did you run the Fedora postgresql-setup upgrade command at any time?
YES
Post by arnaud gaboury
Yes I did many times against a /db/pgsql/data with my 9.4 database (If
Cannot upgrade because the database in /db/pgsql/data is of
version 9.5 but it should be 9.4).
--
Adrian Klaver
--
google.com/+arnaudgabourygabx
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
arnaud gaboury
2016-07-09 19:09:02 UTC
Permalink
Post by arnaud gaboury
On Sat, Jul 9, 2016 at 7:31 PM, Adrian Klaver
Post by arnaud gaboury
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
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
/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)
Post by arnaud gaboury
Post by arnaud gaboury
1) Did you run the Fedora postgresql-setup upgrade command at any
time?
YES
Post by arnaud gaboury
Yes I did many times against a /db/pgsql/data with my 9.4 database (If
Cannot upgrade because the database in /db/pgsql/data is of
version 9.5 but it should be 9.4).
--
Adrian Klaver
--
Adrian Klaver
--
google.com/+arnaudgabourygabx
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Adrian Klaver
2016-07-09 18:45:34 UTC
Permalink
Post by arnaud gaboury
Post by arnaud gaboury
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
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
/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.
Post by arnaud gaboury
Post by arnaud gaboury
1) Did you run the Fedora postgresql-setup upgrade command at any time?
YES
Post by arnaud gaboury
Yes I did many times against a /db/pgsql/data with my 9.4 database (If
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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
arnaud gaboury
2016-07-09 09:54:58 UTC
Permalink
On Thu, Jul 7, 2016 at 10:01 AM, arnaud gaboury
Post by arnaud gaboury
On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
Post by José María Terry Jiménez
Post by Devrim Gündüz
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
# 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"?
"/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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Adrian Klaver
2016-07-09 14:16:21 UTC
Permalink
Post by arnaud gaboury
On Sat, Jul 9, 2016 at 11:54 AM, arnaud gaboury
On Thu, Jul 7, 2016 at 10:01 AM, arnaud gaboury
Post by arnaud gaboury
On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
Post by José María Terry Jiménez
Post by Devrim Gündüz
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
# 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"?
"/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
$ 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 ?
-----------------------------------------------------------------------
*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"?
"/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
$ /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"
% 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.
Post by arnaud gaboury
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
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
arnaud gaboury
2016-07-09 15:59:35 UTC
Permalink
Post by arnaud gaboury
Post by arnaud gaboury
On Sat, Jul 9, 2016 at 11:54 AM, arnaud gaboury
On Thu, Jul 7, 2016 at 10:01 AM, arnaud gaboury
Post by arnaud gaboury
On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
Post by José María Terry Jiménez
Post by Devrim Gündüz
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
# 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"?
"/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
$ 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 ?
-----------------------------------------------------------------------
*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"?
"/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
$ /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"
% 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
"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.
Post by arnaud gaboury
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
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
Cannot upgrade because the database in /db/pgsql/data is of
version 9.5 but it should be 9.4).
--------------------------
*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"?
"/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
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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Adrian Klaver
2016-07-09 17:25:53 UTC
Permalink
Post by arnaud gaboury
Post by arnaud gaboury
Post by arnaud gaboury
On Sat, Jul 9, 2016 at 11:54 AM, arnaud gaboury
On Thu, Jul 7, 2016 at 10:01 AM, arnaud gaboury
Post by arnaud gaboury
On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
Post by José María Terry Jiménez
Post by Devrim Gündüz
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
# 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"?
"/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
$ 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 ?
-----------------------------------------------------------------------
*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"?
"/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
$ /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"
% 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
"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.
Post by arnaud gaboury
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
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
Cannot upgrade because the database in /db/pgsql/data is of
version 9.5 but it should be 9.4).
--------------------------
*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"?
"/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
unix_socket_directories = '/home/postgres,/run/postgresql'
I would like now to try the above command when avoiding this socket story issue.
with the idea to start my database with postgres 9.4 then dump the
$ /usr/lib64/pgsql/postgresql-9.4/bin/pg_ctl -D /db/pgsql/data-9.4 start
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
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Adrian Klaver
2016-07-09 17:31:42 UTC
Permalink
Post by arnaud gaboury
Post by arnaud gaboury
On Sat, Jul 9, 2016 at 11:54 AM, arnaud gaboury
On Thu, Jul 7, 2016 at 10:01 AM, arnaud gaboury
Post by arnaud gaboury
On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
Post by José María Terry Jiménez
Post by Devrim Gündüz
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
# 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"?
"/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
$ 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 ?
-----------------------------------------------------------------------
*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"?
"/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
$ /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"
% 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
"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.
Post by arnaud gaboury
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
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.
Post by arnaud gaboury
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
Cannot upgrade because the database in /db/pgsql/data is of
version 9.5 but it should be 9.4).
--------------------------
*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"?
"/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
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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
arnaud gaboury
2016-07-09 18:06:30 UTC
Permalink
Post by arnaud gaboury
Post by arnaud gaboury
On Sat, Jul 9, 2016 at 11:54 AM, arnaud gaboury
On Thu, Jul 7, 2016 at 10:01 AM, arnaud gaboury
Post by arnaud gaboury
On Thu, Jul 7, 2016 at 1:00 AM, José María Terry Jiménez
Post by José María Terry Jiménez
Post by Devrim Gündüz
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
# 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"?
"/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
$ 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 ?
-----------------------------------------------------------------------
*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"?
"/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
$ /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"
% 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
"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.
Post by arnaud gaboury
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,
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
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
Post by arnaud gaboury
1) Did you run the Fedora postgresql-setup upgrade command at any time?
YES
Post by arnaud gaboury
Yes I did many times against a /db/pgsql/data with my 9.4 database (If
Cannot upgrade because the database in /db/pgsql/data is of
version 9.5 but it should be 9.4).
--------------------------
*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"?
"/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
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
--
google.com/+arnaudgabourygabx
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Adrian Klaver
2016-07-09 18:22:08 UTC
Permalink
Post by arnaud gaboury
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
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
Post by arnaud gaboury
1) Did you run the Fedora postgresql-setup upgrade command at any time?
YES
Post by arnaud gaboury
Yes I did many times against a /db/pgsql/data with my 9.4 database (If
Cannot upgrade because the database in /db/pgsql/data is of
version 9.5 but it should be 9.4).
--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Adrian Klaver
2016-07-09 20:30:23 UTC
Permalink
Post by arnaud gaboury
Post by arnaud gaboury
% 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
/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.
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.
Post by arnaud gaboury
Post by arnaud gaboury
Post by arnaud gaboury
1) Did you run the Fedora postgresql-setup upgrade command at any
time?
YES
Post by arnaud gaboury
Yes I did many times against a /db/pgsql/data with my 9.4 database (If
Cannot upgrade because the database in /db/pgsql/data is of
version 9.5 but it should be 9.4).
--
Adrian Klaver
--
Adrian Klaver
--
Adrian Klaver
***@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Kris Deugau
2016-07-11 14:30:11 UTC
Permalink
Post by Adrian Klaver
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
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...