Обсуждение: Errors regarding transporting database using pg_dump

I want to transport the database server to a new machine.

The OS and the PostgreSQL version of the old machine: FreeBSD 6.1-RELEASE & psql 8.1.3
And that of the new machine: FreeBSD 7.2-RELEASE & psql 8.4.0
(Using  "uname -a"  "psql -U pgsql" to check the version)

I used pg_dump to dump the old database out and psql to store the database into the new server, but the following two errors occurred:

ERROR:  incompatible library "/lib/libc.so.6": missing magic block
HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.
STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
            AS '/lib/libc.so.7', 'system'
            LANGUAGE c STRICT;
ERROR:  function public.system(cstring) does not exist
STATEMENT:  ALTER FUNCTION public."system"(cstring) OWNER TO pgsql;
ERROR:  value too long for type character varying(12)
CONTEXT:  COPY stulist, line 46803, column STU_CNAME: "鷓埥(9debacdd)砆"
STATEMENT:  COPY stulist ("COURSE_SN", "S_YEAR", "S_TERM", "COU_CODE", "CLASS", "REG_NO", "DPT_CODE", "DPT_SCNAME", "YEAR", "CREDIT", "COU_CNAME", "TEA_CODE", "TEA_CNAME", "STU_CNAME", "SCORE", "SCORE_A", "SERNO", "PANOPA", "UNIT", "TEMP") FROM stdin;

The previous error was it could not find /lib/libc.so.6, so I installed the port misc/compat6x and copy the libc.so.6 to /lib. If I replaced libc.so.6 with libc.so.7, the same error occured.

I've did some googling. Should I insert the line

PG_MODULE_MAGIC;

into the libc source code and recompile it? But I don't know where the code resides and what the command is.....

The above is about the first error, and for the second one I totally have no idea. Could somebody please help me out?

Thanks in advance.

On Mon, Oct 05, 2009 at 06:07:44PM +0800, Chun-fan Ivan Liao wrote:
> I used pg_dump to dump the old database out and psql to store the database
> into the new server, but the following two errors occurred:
>
> ERROR:  incompatible library "/lib/libc.so.6": missing magic block
> HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.
> STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
>             AS '/lib/libc.so.7', 'system'
>             LANGUAGE c STRICT;

Ok, here someone was cheating by invoking a system library function
directly. That's no longer supported. If you really want this you need
to make a small library with PG_MODULE_MAGIC; and a pg_system() function
that redirects to the real system().

That said, why are you doint this anyway. A better solution may be to
install a trusted language (like plperlu or plpython) and do the system
call from there.

> ERROR:  value too long for type character varying(12)
> CONTEXT:  COPY stulist, line 46803, column STU_CNAME: "??????(9debacdd)???"
> STATEMENT:  COPY stulist ("COURSE_SN", "S_YEAR", "S_TERM", "COU_CODE",
> "CLASS", "REG_NO", "DPT_CODE", "DPT_SCNAME", "YEAR", "CREDIT", "COU_CNAME",
> "TEA_CODE", "TEA_CNAME", "STU_CNAME", "SCORE", "SCORE_A", "SERNO", "PANOPA",
> "UNIT", "TEMP") FROM stdin;

Here the column is defined as 12 characters and you're trying to
inserts a 19 character string, which is bad. How you got that out of
pg_dump though I don't know.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Chun-fan Ivan Liao <ivan@ivangelion.tw> writes:
> STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
>             AS '/lib/libc.so.7', 'system'
>             LANGUAGE c STRICT;

This hack doesn't work any more --- not that it was ever considered
supported or recommended.  If you really need such a thing, I'd
suggest writing a plperlu or pltclu or plpythonu (according to taste)
wrapper around system().  But do you really need it, or is this just
cruft left over in your database from some playing around?  If the
latter, just ignore this error.

> ERROR:  value too long for type character varying(12)
> CONTEXT:  COPY stulist, line 46803, column STU_CNAME: "鷓埥(9debacdd)砆"

This one looks like it might be an encoding issue.  What database
encoding did you have in the old server, and did you reproduce it
in the new one?

[ ... counts characters ... ]  although frankly that looks like it'd be
more than 12 characters by *anyone's* accounting.  Peculiar.  Maybe the
dump file got mangled while being copied over?

            regards, tom lane

Thank you, Oosterhout and Lane.

The code is not written by me. The previous DB admin is not reachable.
I was just the present-stage DB (newbie) admin, and I never touched
PostgreSQL before.... :(

On Mon, Oct 5, 2009 at 10:34 PM, Martijn van Oosterhout
<kleptog@svana.org> wrote:
>
> On Mon, Oct 05, 2009 at 06:07:44PM +0800, Chun-fan Ivan Liao wrote:
> > I used pg_dump to dump the old database out and psql to store the database
> > into the new server, but the following two errors occurred:
> >
> > ERROR:  incompatible library "/lib/libc.so.6": missing magic block
> > HINT:  Extension libraries are required to use the PG_MODULE_MAGIC macro.
> > STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
> >             AS '/lib/libc.so.7', 'system'
> >             LANGUAGE c STRICT;
>
> Ok, here someone was cheating by invoking a system library function
> directly. That's no longer supported. If you really want this you need
> to make a small library with PG_MODULE_MAGIC; and a pg_system() function
> that redirects to the real system().
>
> That said, why are you doint this anyway. A better solution may be to
> install a trusted language (like plperlu or plpython) and do the system
> call from there.

Could you tell me the exact command I should use? Replace the error
command in the dumped file?

>
> > ERROR:  value too long for type character varying(12)
> > CONTEXT:  COPY stulist, line 46803, column STU_CNAME: "??????(9debacdd)???"
> > STATEMENT:  COPY stulist ("COURSE_SN", "S_YEAR", "S_TERM", "COU_CODE",
> > "CLASS", "REG_NO", "DPT_CODE", "DPT_SCNAME", "YEAR", "CREDIT", "COU_CNAME",
> > "TEA_CODE", "TEA_CNAME", "STU_CNAME", "SCORE", "SCORE_A", "SERNO", "PANOPA",
> > "UNIT", "TEMP") FROM stdin;
>
> Here the column is defined as 12 characters and you're trying to
> inserts a 19 character string, which is bad. How you got that out of
> pg_dump though I don't know.
>

I used pg_dump to dump the database again, and the statement and error
was the same also. No idea what to do.

On Monday 05 October 2009 10:36:04 am Chun-fan Ivan Liao wrote:
> Thank you, Oosterhout and Lane.
>
> The code is not written by me. The previous DB admin is not reachable.
> I was just the present-stage DB (newbie) admin, and I never touched
> PostgreSQL before.... :(
>
> On Mon, Oct 5, 2009 at 10:34 PM, Martijn van Oosterhout
>
> <kleptog@svana.org> wrote:
> > On Mon, Oct 05, 2009 at 06:07:44PM +0800, Chun-fan Ivan Liao wrote:
> > > I used pg_dump to dump the old database out and psql to store the
> > > database into the new server, but the following two errors occurred:
> > >
> > > ERROR:  incompatible library "/lib/libc.so.6": missing magic block
> > > HINT:  Extension libraries are required to use the PG_MODULE_MAGIC
> > > macro. STATEMENT:  CREATE FUNCTION "system"(cstring) RETURNS integer
> > >             AS '/lib/libc.so.7', 'system'
> > >             LANGUAGE c STRICT;
> >
> > Ok, here someone was cheating by invoking a system library function
> > directly. That's no longer supported. If you really want this you need
> > to make a small library with PG_MODULE_MAGIC; and a pg_system() function
> > that redirects to the real system().
> >
> > That said, why are you doint this anyway. A better solution may be to
> > install a trusted language (like plperlu or plpython) and do the system
> > call from there.
>
> Could you tell me the exact command I should use? Replace the error
> command in the dumped file?
>
> > > ERROR:  value too long for type character varying(12)
> > > CONTEXT:  COPY stulist, line 46803, column STU_CNAME:
> > > "??????(9debacdd)???" STATEMENT:  COPY stulist ("COURSE_SN", "S_YEAR",
> > > "S_TERM", "COU_CODE", "CLASS", "REG_NO", "DPT_CODE", "DPT_SCNAME",
> > > "YEAR", "CREDIT", "COU_CNAME", "TEA_CODE", "TEA_CNAME", "STU_CNAME",
> > > "SCORE", "SCORE_A", "SERNO", "PANOPA", "UNIT", "TEMP") FROM stdin;
> >
> > Here the column is defined as 12 characters and you're trying to
> > inserts a 19 character string, which is bad. How you got that out of
> > pg_dump though I don't know.
>
> I used pg_dump to dump the database again, and the statement and error
> was the same also. No idea what to do.

First are you using the 8.1 or 8.3 version of pg_dump? It is generally
recommended to use the new version to dump the old version database. One
potential solution is to alter the varchar length in the old database before
you dump so the field will copy over.

--
Adrian Klaver
aklaver@comcast.net

On Mon, Oct 5, 2009 at 7:34 AM, Martijn van Oosterhout
<kleptog@svana.org> wrote:
> That said, why are you doint this anyway. A better solution may be to
> install a trusted language (like plperlu or plpython) and do the system
> call from there.
>

If you just want system(3) you might as well use plsh...

--
greg