Обсуждение: 8.2.6 -> 8.1.11: syntax error at or near "OWNED BY"
Hello, I've pg_dump'ed a database from PostgreSQL 8.2.6 / openSUSE 10.3 and now trying to load it as a "postgres" user at 8.1.1 / CentOS 5.2 and get numerous errors like: CREATE TABLE ALTER TABLE CREATE SEQUENCE ALTER TABLE psql:denkwerk.sql:1156: ERROR: syntax error at or near "OWNED" at character 38 psql:denkwerk.sql:1156: LINE 1: ALTER SEQUENCE resource_types_id_seq OWNED BY resource_types... psql:denkwerk.sql:1156: ^ setval -------- 4 (1 row) The corresponding spot in the dump file is: CREATE TABLE resource_types ( id integer NOT NULL, name character varying(60) ); ALTER TABLE public.resource_types OWNER TO denkwerk; CREATE SEQUENCE resource_types_id_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; ALTER TABLE public.resource_types_id_seq OWNER TO denkwerk; ALTER SEQUENCE resource_types_id_seq OWNED BY resource_types.id; --line 1156 Does anybody please know what's wrong here (some new syntax introduced in 8.2.x)? And what could I do to workaround it (I have to use CentOS and would like to use its native PostgreSQL 8.1) Thank you for any hints Alex
On Wed, Dec 10, 2008 at 6:41 AM, Alexander Farber <alexander.farber@gmail.com> wrote: > Hello, > > I've pg_dump'ed a database from PostgreSQL 8.2.6 / openSUSE 10.3 > and now trying to load it as a "postgres" user at 8.1.1 / CentOS 5.2 > and get numerous errors like: That's not directly supported. The other direction is. You could try dumpig the 8.2 database with the 8.1 dump, but it's not guaranteed to work either. > Does anybody please know what's wrong here > (some new syntax introduced in 8.2.x)? Yep. > And what could I do to workaround it > (I have to use CentOS and would like to use its native PostgreSQL 8.1) Create the same basic thing by hand in 8.1 and dump it and see what the syntax looks like. Now write a script to transform your 8.2 dump into that format. OR upgrade your 8.1 machine to 8.2
In response to Alexander Farber : > Does anybody please know what's wrong here > (some new syntax introduced in 8.2.x)? Right. > And what could I do to workaround it Update your server or edit the dump. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
you should have dump it using pg_dump from 8.1.X. Plus, I don't think going back is a good idea. There is 8.3 available for centos, use it.
Thank you for replies, I've decided to upgrade by adding http://yum.pgsqlrpms.org/ to the yum config at my CentOS server
On Wed, Dec 10, 2008 at 7:21 AM, Alexander Farber <alexander.farber@gmail.com> wrote: > Thank you for replies, I've decided to upgrade by adding > http://yum.pgsqlrpms.org/ to the yum config at my CentOS server We run pgsql 8.3 on Centos 5.2 and are VERY happy with the PGDG rpms on it.
On Wed, Dec 10, 2008 at 2:21 PM, Alexander Farber <alexander.farber@gmail.com> wrote: > Thank you for replies, I've decided to upgrade by adding > http://yum.pgsqlrpms.org/ to the yum config at my CentOS server > way to go! -- GJ
Hello, On Wed, Dec 10, 2008 at 3:49 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > We run pgsql 8.3 on Centos 5.2 and are VERY happy with the PGDG rpms on it. > thanks for the confirmation Scott. I have installed PGDG with pgsql 8.2 at my CentOS 5.2 server and it seems to work now. I hope any updates will come timely over that PGDG too. (My target as a lazy sysadmin is to install everything and then to touch that server as seldom as possible) Greetings from Germany Alex
"Alexander Farber" <alexander.farber@gmail.com> writes: > I've pg_dump'ed a database from PostgreSQL 8.2.6 / openSUSE 10.3 > and now trying to load it as a "postgres" user at 8.1.1 / CentOS 5.2 > and get numerous errors like: In general there is no promise that you can load pg_dump output into previous server versions. You'll need to edit the file by hand if you want to do this. > (I have to use CentOS and would like to use its native PostgreSQL 8.1) Even if you have to use CentOS, using 8.1.1 is a horrid idea --- the current 8.1 release is 8.1.15. Try to get a more up-to-date copy of CentOS. However, there's really no very good reason not to install 8.2 or 8.3 from the RPMs that the Postgres project offers. regards, tom lane
Is there a limit to the number of entries I can pass in an IN clause as part of a SELECT statement? As in SELECT baz FROM foo where id in ( 1, 2,... ) ; Thanks, -Said
On Wed, Dec 10, 2008 at 11:08 AM, Said Ramirez <sramirez@vonage.com> wrote: > Is there a limit to the number of entries I can pass in an IN clause as part > of a SELECT statement? As in > SELECT baz FROM foo where id in ( 1, 2,... ) ; I think it's high enough you'd have performance problems before it would fail. If there even is one.
On Wed, Dec 10, 2008 at 11:28 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Wed, Dec 10, 2008 at 11:08 AM, Said Ramirez <sramirez@vonage.com> wrote: >> Is there a limit to the number of entries I can pass in an IN clause as part >> of a SELECT statement? As in >> SELECT baz FROM foo where id in ( 1, 2,... ) ; > > I think it's high enough you'd have performance problems before it > would fail. If there even is one. Just tested it with a subselect with 10M rows in an in clause. Not sure if that's the same as literal strings though.