pg_upgrade: when the --old-bindir requires "additional" $libdir/ plugins?

On 04/18/2018 07:07 AM, Pavel Raiskup wrote:

Hi all,

with a huge delay, I've seen this question [1].

. and it seems like the hstore.so was somewhat intimately integrated into
OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
--format=custom' called through 'pg_upgrade' failed with:

pg_dump: [archiver (db)] query failed: ERROR: could not access file
"$libdir/hstore": No such file or directory

From [1] it seems there is confusion in the system about where $libdir
is. Did anyone run pg_config to try to track this down?

Which means that the dump from old datadir, with old server (without
hstore.so packaged) failed. But playing with hstore.so a bit, the upgrade
always worked smoothly for me even without the "old" hstore.so

I am not following the above. If you played with hstore.so how could it
not be around? Or does this have something to do with 'old'?

Googling around, I only found that there were some incompatible changes
historically in hstore.so causing pg_upgrade failure; but this is
different issue -- in the issue I talk about now the module was not found
at all, even though it is usually even not searched for.

So the question is what circumstances could cause this issue in
particular? Is it expected in general that, to make the pg_upgrade run
smooth, all the "loaded" server modules are built against and available
for the --old-bindir server? Don't we have some reproducer for this
kind of issues?

[1] https://ask.fedoraproject.org/en/question/69316/postgresql-doesnt-work-after-upgrade-to-f22/
[2] https://bugzilla.redhat.com/show_bug.cgi?id=1557490

Thank for hints,
Pavel

--
Adrian Klaver
adrian.klaver@aklaver.com

Pavel Raiskup <praiskup@redhat.com> writes:

. and it seems like the hstore.so was somewhat intimately integrated into
OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
--format=custom' called through 'pg_upgrade' failed with:
pg_dump: [archiver (db)] query failed: ERROR: could not access file
"$libdir/hstore": No such file or directory
Which means that the dump from old datadir, with old server (without
hstore.so packaged) failed. But playing with hstore.so a bit, the upgrade
always worked smoothly for me even without the "old" hstore.so

Hi Pavel,

There are certainly plenty of reasons why extension .so's might be needed
during pg_dump, even in a binary-upgrade situation. The first example
that comes to mind is that an hstore-type constant appearing in a view
definition would require hstore_out() to be invoked while dumping the view
definition.

I don't remember anymore whether I'd set up the postgresql-update package
to include the contrib modules for the old server version. If I didn't,
it was an oversight :-(.

regards, tom lane

On 04/18/2018 07:22 AM, Tom Lane wrote:

Pavel Raiskup <praiskup@redhat.com> writes:

. and it seems like the hstore.so was somewhat intimately integrated into
OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
--format=custom' called through 'pg_upgrade' failed with:
pg_dump: [archiver (db)] query failed: ERROR: could not access file
"$libdir/hstore": No such file or directory
Which means that the dump from old datadir, with old server (without
hstore.so packaged) failed. But playing with hstore.so a bit, the upgrade
always worked smoothly for me even without the "old" hstore.so

Hi Pavel,

There are certainly plenty of reasons why extension .so's might be needed
during pg_dump, even in a binary-upgrade situation. The first example
that comes to mind is that an hstore-type constant appearing in a view
definition would require hstore_out() to be invoked while dumping the view
definition.

I am obviously missing something. If the old server was using hstore in
a database how could hstore.so could be accessible to it but not pg_dump?

I don't remember anymore whether I'd set up the postgresql-update package
to include the contrib modules for the old server version. If I didn't,
it was an oversight :-(.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wednesday, April 18, 2018 4:22:23 PM CEST Tom Lane wrote:

Pavel Raiskup <praiskup@redhat.com> writes:

[2] https://bugzilla.redhat.com/show_bug.cgi?id=1557490

There are certainly plenty of reasons why extension .so's might be needed
during pg_dump, even in a binary-upgrade situation. The first example
that comes to mind is that an hstore-type constant appearing in a view
definition would require hstore_out() to be invoked while dumping the view
definition.

Thanks for the example, Tom. I'll resolve the bug [2] then.

Pavel

On Wednesday, April 18, 2018 4:43:01 PM CEST Adrian Klaver wrote:

On 04/18/2018 07:22 AM, Tom Lane wrote:

Pavel Raiskup <praiskup@redhat.com> writes:

. and it seems like the hstore.so was somewhat intimately integrated into
OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
--format=custom' called through 'pg_upgrade' failed with:
pg_dump: [archiver (db)] query failed: ERROR: could not access file
"$libdir/hstore": No such file or directory
Which means that the dump from old datadir, with old server (without
hstore.so packaged) failed. But playing with hstore.so a bit, the upgrade
always worked smoothly for me even without the "old" hstore.so

Hi Pavel,

There are certainly plenty of reasons why extension .so's might be needed
during pg_dump, even in a binary-upgrade situation. The first example
that comes to mind is that an hstore-type constant appearing in a view
definition would require hstore_out() to be invoked while dumping the view
definition.

I am obviously missing something. If the old server was using hstore in
a database how could hstore.so could be accessible to it but not pg_dump?

Because on Fedora we usually run pg_upgrade after distribution upgrade
(e.g. for Fedora 27 => Fedora 28 upgrade it means also upgrade from PostgreSQL
9.6 to 10), and then we provide the old server in different package
(postgresql-upgrade) which has limited feature set (including the missing
hstore.so module).

Pavel

On 04/18/2018 08:04 AM, Pavel Raiskup wrote:

On Wednesday, April 18, 2018 4:43:01 PM CEST Adrian Klaver wrote:

I am obviously missing something. If the old server was using hstore in
a database how could hstore.so could be accessible to it but not pg_dump?

Because on Fedora we usually run pg_upgrade after distribution upgrade
(e.g. for Fedora 27 => Fedora 28 upgrade it means also upgrade from PostgreSQL
9.6 to 10), and then we provide the old server in different package
(postgresql-upgrade) which has limited feature set (including the missing
hstore.so module).

Thanks, now it makes sense.

Pavel

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 04/18/2018 07:22 AM, Tom Lane wrote:

Pavel Raiskup <praiskup@redhat.com> writes:

. and it seems like the hstore.so was somewhat intimately integrated into
OP's database so the '/usr/bin/pg_dump --schema-only --binary-upgrade
--format=custom' called through 'pg_upgrade' failed with:
pg_dump: [archiver (db)] query failed: ERROR: could not access file
"$libdir/hstore": No such file or directory
Which means that the dump from old datadir, with old server (without
hstore.so packaged) failed. But playing with hstore.so a bit, the upgrade
always worked smoothly for me even without the "old" hstore.so

Hi Pavel,

There are certainly plenty of reasons why extension .so's might be needed
during pg_dump, even in a binary-upgrade situation. The first example
that comes to mind is that an hstore-type constant appearing in a view
definition would require hstore_out() to be invoked while dumping the view
definition.

I am obviously missing something. If the old server was using hstore
in a database how could hstore.so could be accessible to it but not
pg_dump?

I presume because something stole the depended upon libs but went
unnoticed due to the referring objs being generally unused.

Along comes pg_upgrade and the requisite dump... BOOM!

I don't remember anymore whether I'd set up the postgresql-update package
to include the contrib modules for the old server version. If I didn't,
it was an oversight :-(.

regards, tom lane

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800