pg_dump problems: [archiver (db)] query failed: ERROR: relation "pg_opfamily" does not exist
Hi,
While trying to move an old 8.1 Postgres install to a new server with
postgres 10.10, I get an error, listed hereunder:
I am trying to get a mydump_x_x_x.sql file to use to reinstall on the
target machine.
From my new machine I connect to my old machine like this:
erwin@ubuntu:~$ pg_dump -f "/home/erwin/mydump_$(date +%d_%m_%Y).sql"
-C -h somehost.com -p 5433 -U myvalidusername -d myremotedb
Password:
pg_dump: [archiver (db)] query failed: ERROR: relation "pg_opfamily"
does not exist
pg_dump: [archiver (db)] query was: SELECT classid, objid, refclassid,
refobjid, deptype FROM pg_depend WHERE deptype != 'p' AND deptype != 'e'
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amopfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amop o WHERE deptype
NOT IN ('p', 'e', 'i') AND classid = 'pg_amop'::regclass AND objid =
o.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND amopfamily =
refobjid)
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amprocfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amproc p WHERE
deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amproc'::regclass AND
objid = p.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND
amprocfamily = refobjid)
ORDER BY 1,2
And no archive is created.
Does any of you kind people have advice? I am sure this worked on postgres9.
Regards,
Erwin Moller
On 8/20/19 7:12 PM, Erwin Moller wrote:
Hi,
While trying to move an old 8.1 Postgres install to a new server with postgres 10.10, I get an error, listed hereunder:
I am trying to get a mydump_x_x_x.sql file to use to reinstall on the target machine.From my new machine I connect to my old machine like this:
erwin@ubuntu:~$ pg_dump -f "/home/erwin/mydump_$(date +%d_%m_%Y).sql" -C -h somehost.com -p 5433 -U myvalidusername -d myremotedb
Password:
pg_dump: [archiver (db)] query failed: ERROR: relation "pg_opfamily" does not exist
pg_dump: [archiver (db)] query was: SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' AND deptype != 'e'
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amopfamily AS objid, refclassid, refobjid, deptype FROM pg_depend d, pg_amop o WHERE deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amop'::regclass AND objid = o.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND amopfamily = refobjid)
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amprocfamily AS objid, refclassid, refobjid, deptype FROM pg_depend d, pg_amproc p WHERE deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amproc'::regclass AND objid = p.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND amprocfamily = refobjid)
ORDER BY 1,2And no archive is created.
Does any of you kind people have advice? I am sure this worked on postgres9.
It looks like this was broken just before 10.10 was released, and has since been fixed;
see:
Your options are:
- find a 10.9 or earlier package and use the pg_dump from that
- build pg_dump yourself
- wait for 10.11 to come out (though I imagine that won't be for a month or two)
Regards
Ian Barwick
--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Op 8/20/2019 om 2:07 PM schreef Ian Barwick:
On 8/20/19 7:12 PM, Erwin Moller wrote:
Hi,
While trying to move an old 8.1 Postgres install to a new server with
postgres 10.10, I get an error, listed hereunder:
I am trying to get a mydump_x_x_x.sql file to use to reinstall on the
target machine.From my new machine I connect to my old machine like this:
erwin@ubuntu:~$ pg_dump -f "/home/erwin/mydump_$(date
+%d_%m_%Y).sql" -C -h somehost.com -p 5433 -U myvalidusername -d
myremotedb
Password:
pg_dump: [archiver (db)] query failed: ERROR: relation "pg_opfamily"
does not exist
pg_dump: [archiver (db)] query was: SELECT classid, objid,
refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' AND
deptype != 'e'
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amopfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amop o WHERE
deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amop'::regclass AND
objid = o.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND
amopfamily = refobjid)
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amprocfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amproc p WHERE
deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amproc'::regclass
AND objid = p.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND
amprocfamily = refobjid)
ORDER BY 1,2And no archive is created.
Does any of you kind people have advice? I am sure this worked on
postgres9.It looks like this was broken just before 10.10 was released, and has
since been fixed;
see:Your options are:
- find a 10.9 or earlier package and use the pg_dump from that
- build pg_dump yourself
- wait for 10.11 to come out (though I imagine that won't be for a
month or two)Regards
Ian Barwick
Thanks a lot, Ian!
That saved me an upcoming headache. I searched a lot on Google but
couldn't find any relevant information.
Will installing Postgres 11 also work? (Not the Ubuntu18.04 default for
Postgres, but doable).
Or does those pg_dump versions also expect pg_opfamily to exist?
Thanks for your time!
Regards,
Erwin Moller
On 8/20/19 6:18 AM, Erwin Moller wrote:
Op 8/20/2019 om 2:07 PM schreef Ian Barwick:
On 8/20/19 7:12 PM, Erwin Moller wrote:
Hi,
While trying to move an old 8.1 Postgres install to a new server with
postgres 10.10, I get an error, listed hereunder:
I am trying to get a mydump_x_x_x.sql file to use to reinstall on the
target machine.From my new machine I connect to my old machine like this:
erwin@ubuntu:~$ pg_dump -f "/home/erwin/mydump_$(date
+%d_%m_%Y).sql" -C -h somehost.com -p 5433 -U myvalidusername -d
myremotedb
Password:
pg_dump: [archiver (db)] query failed: ERROR: relation "pg_opfamily"
does not exist
pg_dump: [archiver (db)] query was: SELECT classid, objid,
refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' AND
deptype != 'e'
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amopfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amop o WHERE
deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amop'::regclass AND
objid = o.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND
amopfamily = refobjid)
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amprocfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amproc p WHERE
deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amproc'::regclass
AND objid = p.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND
amprocfamily = refobjid)
ORDER BY 1,2And no archive is created.
Does any of you kind people have advice? I am sure this worked on
postgres9.It looks like this was broken just before 10.10 was released, and has
since been fixed;
see:Your options are:
- find a 10.9 or earlier package and use the pg_dump from that
- build pg_dump yourself
- wait for 10.11 to come out (though I imagine that won't be for a
month or two)Regards
Ian Barwick
Thanks a lot, Ian!
That saved me an upcoming headache. I searched a lot on Google but
couldn't find any relevant information.Will installing Postgres 11 also work? (Not the Ubuntu18.04 default for
Postgres, but doable).
Or does those pg_dump versions also expect pg_opfamily to exist?
From the bug that introduced the issue:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=07b39083c
"Per bug #15934 from Tom Gottfried. Back-patch to all supported branches."
So I'm guessing it is in the most recent release of all current versions.
Thanks for your time!
Regards,
Erwin Moller
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
On 8/20/19 6:18 AM, Erwin Moller wrote:
Will installing Postgres 11 also work? (Not the Ubuntu18.04 default for
Postgres, but doable).
Or does those pg_dump versions also expect pg_opfamily to exist?
From the bug that introduced the issue:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=07b39083c
"Per bug #15934 from Tom Gottfried. Back-patch to all supported branches."
So I'm guessing it is in the most recent release of all current versions.
Yeah, it's broken in all the August minor releases. Sorry about that :-(
regards, tom lane
Op 8/20/2019 om 3:32 PM schreef Adrian Klaver:
On 8/20/19 6:18 AM, Erwin Moller wrote:
Op 8/20/2019 om 2:07 PM schreef Ian Barwick:
On 8/20/19 7:12 PM, Erwin Moller wrote:
Hi,
While trying to move an old 8.1 Postgres install to a new server
with postgres 10.10, I get an error, listed hereunder:
I am trying to get a mydump_x_x_x.sql file to use to reinstall on
the target machine.From my new machine I connect to my old machine like this:
erwin@ubuntu:~$ pg_dump -f "/home/erwin/mydump_$(date
+%d_%m_%Y).sql" -C -h somehost.com -p 5433 -U myvalidusername -d
myremotedb
Password:
pg_dump: [archiver (db)] query failed: ERROR: relation
"pg_opfamily" does not exist
pg_dump: [archiver (db)] query was: SELECT classid, objid,
refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p'
AND deptype != 'e'
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amopfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amop o WHERE
deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amop'::regclass
AND objid = o.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND
amopfamily = refobjid)
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amprocfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amproc p WHERE
deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amproc'::regclass
AND objid = p.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND
amprocfamily = refobjid)
ORDER BY 1,2And no archive is created.
Does any of you kind people have advice? I am sure this worked on
postgres9.It looks like this was broken just before 10.10 was released, and
has since been fixed;
see:Your options are:
- find a 10.9 or earlier package and use the pg_dump from that
- build pg_dump yourself
- wait for 10.11 to come out (though I imagine that won't be for a
month or two)Regards
Ian Barwick
Thanks a lot, Ian!
That saved me an upcoming headache. I searched a lot on Google but
couldn't find any relevant information.Will installing Postgres 11 also work? (Not the Ubuntu18.04 default
for Postgres, but doable).
Or does those pg_dump versions also expect pg_opfamily to exist?From the bug that introduced the issue:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=07b39083c
"Per bug #15934 from Tom Gottfried. Back-patch to all supported
branches."So I'm guessing it is in the most recent release of all current versions.
Thank you, Adrian.
That is a bummer.
Since I have no idea how to safely install an older version of pg_dump
alongside my current 10.10 install, I will wait for the patch.
Thank you for your responses!
Regards,
Erwin Moller
On 8/20/19 7:37 AM, Erwin Moller wrote:
Op 8/20/2019 om 3:32 PM schreef Adrian Klaver:
On 8/20/19 6:18 AM, Erwin Moller wrote:
Op 8/20/2019 om 2:07 PM schreef Ian Barwick:
On 8/20/19 7:12 PM, Erwin Moller wrote:
Hi,
While trying to move an old 8.1 Postgres install to a new server
with postgres 10.10, I get an error, listed hereunder:
I am trying to get a mydump_x_x_x.sql file to use to reinstall on
the target machine.From my new machine I connect to my old machine like this:
erwin@ubuntu:~$ pg_dump -f "/home/erwin/mydump_$(date
+%d_%m_%Y).sql" -C -h somehost.com -p 5433 -U myvalidusername -d
myremotedb
Password:
pg_dump: [archiver (db)] query failed: ERROR: relation
"pg_opfamily" does not exist
pg_dump: [archiver (db)] query was: SELECT classid, objid,
refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p'
AND deptype != 'e'
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amopfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amop o WHERE
deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amop'::regclass
AND objid = o.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND
amopfamily = refobjid)
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amprocfamily AS objid,
refclassid, refobjid, deptype FROM pg_depend d, pg_amproc p WHERE
deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amproc'::regclass
AND objid = p.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND
amprocfamily = refobjid)
ORDER BY 1,2And no archive is created.
Does any of you kind people have advice? I am sure this worked on
postgres9.It looks like this was broken just before 10.10 was released, and
has since been fixed;
see:Your options are:
- find a 10.9 or earlier package and use the pg_dump from that
- build pg_dump yourself
- wait for 10.11 to come out (though I imagine that won't be for a
month or two)Regards
Ian Barwick
Thanks a lot, Ian!
That saved me an upcoming headache. I searched a lot on Google but
couldn't find any relevant information.Will installing Postgres 11 also work? (Not the Ubuntu18.04 default
for Postgres, but doable).
Or does those pg_dump versions also expect pg_opfamily to exist?From the bug that introduced the issue:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=07b39083c
"Per bug #15934 from Tom Gottfried. Back-patch to all supported
branches."So I'm guessing it is in the most recent release of all current versions.
Thank you, Adrian.
That is a bummer.
Since I have no idea how to safely install an older version of pg_dump
alongside my current 10.10 install, I will wait for the patch.
A quick test showed that:
1) Go here:
https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-11/
3) Using Ark extract pg_dump from data.tar.xz at path
/usr/lib/postgresql/11/bin
./pg_dump -V
pg_dump (PostgreSQL) 11.4 (Ubuntu 11.4-1.pgdg18.10+1)
I do not have a 8.1 instance to try against, so I could not test that part.
Thank you for your responses!
Regards,
Erwin Moller
--
Adrian Klaver
adrian.klaver@aklaver.com
On 8/20/19 8:02 AM, Adrian Klaver wrote:
On 8/20/19 7:37 AM, Erwin Moller wrote:
That is a bummer.
Since I have no idea how to safely install an older version of pg_dump
alongside my current 10.10 install, I will wait for the patch.A quick test showed that:
1) Go here:
https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-11/3) Using Ark extract pg_dump from data.tar.xz at path
/usr/lib/postgresql/11/bin
Just to be clear I did the above in my home directory so as not to
overwrite the installed programs.
Also probably not a bad idea to rename the below to something like
pg_dump_11_4 to keep things straight.
./pg_dump -V
pg_dump (PostgreSQL) 11.4 (Ubuntu 11.4-1.pgdg18.10+1)
I do not have a 8.1 instance to try against, so I could not test that part.
Thank you for your responses!
Regards,
Erwin Moller
--
Adrian Klaver
adrian.klaver@aklaver.com
On 8/20/2019 5:11 PM, Adrian Klaver wrote:
On 8/20/19 8:02 AM, Adrian Klaver wrote:
On 8/20/19 7:37 AM, Erwin Moller wrote:
That is a bummer.
Since I have no idea how to safely install an older version of
pg_dump alongside my current 10.10 install, I will wait for the patch.A quick test showed that:
1) Go here:
https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-11/3) Using Ark extract pg_dump from data.tar.xz at path
/usr/lib/postgresql/11/binJust to be clear I did the above in my home directory so as not to
overwrite the installed programs.Also probably not a bad idea to rename the below to something like
pg_dump_11_4 to keep things straight../pg_dump -V
pg_dump (PostgreSQL) 11.4 (Ubuntu 11.4-1.pgdg18.10+1)I do not have a 8.1 instance to try against, so I could not test that
part
Thanks Adrian,
It took some time for me to get back to this, but thanks to your
suggestions I got it working.
Since I am not too familiar with the inner workings of Ubuntu, some of
your suggestions took me a while longer to parse, but with succes. ;-)
In case some other unskilled soul like me happens on this problem, here
is what I did in babysteps:
I didn't want to grap the version you suggested (11.4), since I run
10.10, so I wanted to be safe and use 10.9 client tools:
Found it here:
https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-10/
postgresql-client-10_10.9-1.pgdg18.10+1_amd64.deb
<https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-10/postgresql-client-10_10.9-1.pgdg18.10%2b1_amd64.deb>
I created a new directory in my home:
mkdir pg_dump_10.9
cd pg_dump_10.9
wget
https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-10/postgresql-client-10_10.9-1.pgdg18.10+1_amd64.deb
now how to extract my pg_dump? After a short Google I did the following:
dpkg-deb -R ./postgresql-client-10_10.9-1.pgdg18.10+1_amd64.deb tmp
then walked to the right (new) directory:
cd tmp/usr/lib/postgresql/10/bin
There it was.: pg_dump from 10.9. Yes!
I moved it to my home-dir, and renamed it as you suggested (to avoid
confusion).
Work right from there. :-)
I was expecting all kind of dependencies I had to solve, but it worked
right out of the box.
Thanks Adrian.
Regards,
Erwin Moller