Обсуждение: pg_dump PostgreSQL 8.4
hi!.
i am trying to migrate my database from 8.3.7 to 8.4 (both versions running on the same server windows 2003, hp ml350 quad core xeon) using pg_dump and restore using pgAdmin III R 1.10.0, this i was able to do. but i cannot seem to make a backup on 8.4. i keep getting the following error message:
pg_dump: reading user-defined tables
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: column "reltriggers" does not exist
LINE 1: ...oles WHERE oid = relowner) as rolname, relchecks, reltrigger...
^
pg_dump: The command was: SELECT c.tableoid, c.oid, relname, relacl, relkind, relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = relowner) as rolname, relchecks, reltriggers, relhasindex, relhasrules, relhasoids, d.refobjid as owning_tab, d.refobjsubid as owning_col, (SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, array_to_string(c.reloptions, ', ') as reloptions from pg_class c left join pg_depend d on (c.relkind = 'S' and d.classid = c.tableoid and d.objid = c.oid and d.objsubid = 0 and d.refclassid = c.tableoid and d.deptype = 'a') where relkind in ('r', 'S', 'v', 'c') order by c.oid
pg_dump: *** aborted because of error
Process returned exit code 1.
is there any configuration setting i need to do. any help would be appreciated.
regards
raffy segador
destiny cable inc
ncr, philipines
What version of pg_dump and pgAdmin are you using? In PostgreSQL version 8.3 it's reltriggers, in version 8.4 it's relhastriggers. Just run the next query to see the difference: SELECT * FROM pg_class; Using pgAdmin 1.10, I don't have any problemes with 8.3 nor 8.4 Regards, Frank Op 9 jul 2009, om 10:24 heeft serafin g.segador het volgende geschreven: > hi!. > > i am trying to migrate my database from 8.3.7 to 8.4 (both versions > running on the same server windows 2003, hp ml350 quad core xeon) > using pg_dump and restore using pgAdmin III R 1.10.0, this i was > able to do. but i cannot seem to make a backup on 8.4. i keep > getting the following error message: > > pg_dump: reading user-defined tables > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: column "reltriggers" > does not exist > LINE 1: ...oles WHERE oid = relowner) as rolname, relchecks, > reltrigger... > ^ > pg_dump: The command was: SELECT c.tableoid, c.oid, relname, relacl, > relkind, relnamespace, (SELECT rolname FROM pg_catalog.pg_roles > WHERE oid = relowner) as rolname, relchecks, reltriggers, > relhasindex, relhasrules, relhasoids, d.refobjid as owning_tab, > d.refobjsubid as owning_col, (SELECT spcname FROM pg_tablespace t > WHERE t.oid = c.reltablespace) AS reltablespace, > array_to_string(c.reloptions, ', ') as reloptions from pg_class c > left join pg_depend d on (c.relkind = 'S' and d.classid = c.tableoid > and d.objid = c.oid and d.objsubid = 0 and d.refclassid = c.tableoid > and d.deptype = 'a') where relkind in ('r', 'S', 'v', 'c') order by > c.oid > pg_dump: *** aborted because of error > Process returned exit code 1. > is there any configuration setting i need to do. any help would be > appreciated. > > regards > > raffy segador > destiny cable inc > ncr, philipines Frank Heikens frankheikens@mac.com
i found where the error is. thanks for the tip. i have versions 8.3.7 and 8.4 of PostgreSQL running on the same server, as well as versions 1.8 and 1.10 of pgAdmin. although i run the backup routine for pg8.4 from pgAdmin 1.10, the pdAdmin uses PostgreSQL\8.3\bin\pg_dump as default. i now run the backup routine from the command line utility pointing to the correct version. thank you. regards, raffy On Thu, 09 Jul 2009 17:03:04 +0800, Frank Heikens <frankheikens@mac.com> wrote: > What version of pg_dump and pgAdmin are you using? > > In PostgreSQL version 8.3 it's reltriggers, in version 8.4 it's > relhastriggers. Just run the next query to see the difference: > > SELECT * FROM pg_class; > > Using pgAdmin 1.10, I don't have any problemes with 8.3 nor 8.4 > > Regards, > Frank > > Op 9 jul 2009, om 10:24 heeft serafin g.segador het volgende geschreven: > >> hi!. >> >> i am trying to migrate my database from 8.3.7 to 8.4 (both versions >> running on the same server windows 2003, hp ml350 quad core xeon) using >> pg_dump and restore using pgAdmin III R 1.10.0, this i was able to do. >> but i cannot seem to make a backup on 8.4. i keep getting the >> following error message: >> >> pg_dump: reading user-defined tables >> pg_dump: SQL command failed >> pg_dump: Error message from server: ERROR: column "reltriggers" does >> not exist >> LINE 1: ...oles WHERE oid = relowner) as rolname, relchecks, >> reltrigger... >> ^ >> pg_dump: The command was: SELECT c.tableoid, c.oid, relname, relacl, >> relkind, relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE >> oid = relowner) as rolname, relchecks, reltriggers, relhasindex, >> relhasrules, relhasoids, d.refobjid as owning_tab, d.refobjsubid as >> owning_col, (SELECT spcname FROM pg_tablespace t WHERE t.oid = >> c.reltablespace) AS reltablespace, array_to_string(c.reloptions, ', ') >> as reloptions from pg_class c left join pg_depend d on (c.relkind = 'S' >> and d.classid = c.tableoid and d.objid = c.oid and d.objsubid = 0 and >> d.refclassid = c.tableoid and d.deptype = 'a') where relkind in ('r', >> 'S', 'v', 'c') order by c.oid >> pg_dump: *** aborted because of error >> Process returned exit code 1. >> is there any configuration setting i need to do. any help would be >> appreciated. >> >> regards >> >> raffy segador >> destiny cable inc >> ncr, philipines > > Frank Heikens > frankheikens@mac.com > > > -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
On Thu, Jul 9, 2009 at 11:06 AM, serafin segador<rsegador@mydestiny.net> wrote: > i found where the error is. thanks for the tip. > > i have versions 8.3.7 and 8.4 of PostgreSQL running on the same server, as > well as versions 1.8 and 1.10 of pgAdmin. although i run the backup routine > for pg8.4 from pgAdmin 1.10, the pdAdmin uses PostgreSQL\8.3\bin\pg_dump as > default. i now run the backup routine from the command line utility > pointing to the correct version. Check the PG bin path option and File -> Options. It also sounds like you disabled the version match check on that dialogue, otherwise pg_dump 8.3 should have complained about be used with 8.4. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
thank you all for your assistance. i tried not to be adventurous tweaking the configuration of a production system except for system upgrade which has not failed on me before. burned my fingers once. i need to visit the documentation and study more the admin side of the system. thanks all. raffy segador On Thu, 09 Jul 2009 18:27:35 +0800, Dave Page <dpage@pgadmin.org> wrote: > On Thu, Jul 9, 2009 at 11:06 AM, serafin segador<rsegador@mydestiny.net> > wrote: >> i found where the error is. thanks for the tip. >> >> i have versions 8.3.7 and 8.4 of PostgreSQL running on the same server, >> as >> well as versions 1.8 and 1.10 of pgAdmin. although i run the backup >> routine >> for pg8.4 from pgAdmin 1.10, the pdAdmin uses >> PostgreSQL\8.3\bin\pg_dump as >> default. i now run the backup routine from the command line utility >> pointing to the correct version. > > Check the PG bin path option and File -> Options. > > It also sounds like you disabled the version match check on that > dialogue, otherwise pg_dump 8.3 should have complained about be used > with 8.4. > -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/