Обсуждение: Re: could not migrate 8.0.13 database with large object data to 9.5.1

Поиск
Список
Период
Сортировка

Re: could not migrate 8.0.13 database with large object data to 9.5.1

От
"Premsun Choltanwanich"
Дата:
Hi Joon,

I have no source code for this contrib/lo and dll/function seem already be stored with my 8.0.13 installation package.

Any suggestion?

Regards,

NETsolutions Asia Limited   +66 2 401 9250

NETsolutions Asia Limited
>>> John R Pierce <pierce@hogranch.com> 2016-03-03 09:05 >>>

On 3/2/2016 5:52 PM, Premsun Choltanwanich wrote:
>
> And, almost tables are transferred to new server except tables which
> contain lo data (all those tables are missing from the database) after
> running pg_dump and psql following as per your suggestion.
>
> The attachment is a log file created after all processes are completed.


your logfile shows a bunch of custom functions being defined which are
dependent on a missing binary library, apparently your previous install
of postgres has some customizations.

> 2016-03-02 18:06:25 ICT ERROR:  could not find function "lo_in" in file "C:/Program Files/PostgreSQL/9.5/lib/lo.dll"
> 2016-03-02 18:06:25 ICT STATEMENT:  CREATE FUNCTION lo_in(cstring) RETURNS lo
>         LANGUAGE c IMMUTABLE STRICT
>         AS '$libdir/lo', 'lo_in';

these custom functions are being used to define a custom data type.

> 2016-03-02 18:06:25 ICT ERROR:  function lo_in(cstring) does not exist
> 2016-03-02 18:06:25 ICT STATEMENT:  CREATE TYPE lo (
>         INTERNALLENGTH = 4,
>         INPUT = lo_in,
>         OUTPUT = lo_out,
>         ALIGNMENT = int4,
>         STORAGE = plain
>     );


and your tables are using this custom data type 'lo', which is invalid
since the code to implement it is missing.

> 2016-03-02 18:06:26 ICT ERROR:  type "lo" is only a shell at character 186
> 2016-03-02 18:06:26 ICT STATEMENT:  CREATE TABLE t_familypic (
>         sysid bigint DEFAULT nextval('public.t_familypic_sysid_seq'::text) NOT NULL,
>         mbrsysid bigint NOT NULL,
>         familysysid bigint NOT NULL,
>         familypic lo
>     );


do you have the source code to this lo.dll so you can be rebuild it for
9.5 ?





--
john r pierce, recycling bits in santa cruz


Вложения

Re: Re: could not migrate 8.0.13 database with large object data to 9.5.1

От
John R Pierce
Дата:
On 3/3/2016 3:53 AM, Premsun Choltanwanich wrote:
>
> I have no source code for this contrib/lo and dll/function seem
> already be stored with my 8.0.13 installation package.

upon looking at the current docs again in the light of day, I see that
there *IS* a contrib/lo module in all recent versions.

try, before doing the restore... as postgres with your empty database...

     psql mynewdatabase -c "create extension lo"

I would also consider doing the dump in two parts.   first pass use
--schema-only   to just dump the defninitions, and restore these, this
will create your database structures with no data.   you may need to
edit the generated .sql file to remove these redundant/erroneous
function definitions.     then once the schema is created cleanly, do
the same dump but with --data-only and to a different file, this will
dump just the actual database data, which you them 'restore' to the same
database you've already created the schema in...





--
john r pierce, recycling bits in santa cruz

Re: Re: could not migrate 8.0.13 database with large object data to 9.5.1

От
"Rader, David"
Дата:
Wow -- you have an old db version there! When doing a little research, I found that back in 2005 you actually had the same basic issue - that the way you were using the "lo" contrib module in 7.x and 8.0 was not supported in 8.1 anymore:
http://www.postgresql.org/message-id/439FFA3F.C5F7.004C.0@nsasia.co.th

My first approach would be to change your 8.0 database to use supported blob types and functions so that you can dump and restore. 


On Thu, Mar 3, 2016 at 6:53 AM, Premsun Choltanwanich <Premsun@nsasia.co.th> wrote:
Hi Joon,

I have no source code for this contrib/lo and dll/function seem already be stored with my 8.0.13 installation package.

Any suggestion?

Regards,

NETsolutions Asia Limited   +66 2 401 9250

NETsolutions Asia Limited
>>> John R Pierce <pierce@hogranch.com> 2016-03-03 09:05 >>>

On 3/2/2016 5:52 PM, Premsun Choltanwanich wrote:
>
> And, almost tables are transferred to new server except tables which
> contain lo data (all those tables are missing from the database) after
> running pg_dump and psql following as per your suggestion.
>
> The attachment is a log file created after all processes are completed.


your logfile shows a bunch of custom functions being defined which are
dependent on a missing binary library, apparently your previous install
of postgres has some customizations.

> 2016-03-02 18:06:25 ICT ERROR:  could not find function "lo_in" in file "C:/Program Files/PostgreSQL/9.5/lib/lo.dll"
> 2016-03-02 18:06:25 ICT STATEMENT:  CREATE FUNCTION lo_in(cstring) RETURNS lo
>         LANGUAGE c IMMUTABLE STRICT
>         AS '$libdir/lo', 'lo_in';

these custom functions are being used to define a custom data type.

> 2016-03-02 18:06:25 ICT ERROR:  function lo_in(cstring) does not exist
> 2016-03-02 18:06:25 ICT STATEMENT:  CREATE TYPE lo (
>         INTERNALLENGTH = 4,
>         INPUT = lo_in,
>         OUTPUT = lo_out,
>         ALIGNMENT = int4,
>         STORAGE = plain
>     );


and your tables are using this custom data type 'lo', which is invalid
since the code to implement it is missing.

> 2016-03-02 18:06:26 ICT ERROR:  type "lo" is only a shell at character 186
> 2016-03-02 18:06:26 ICT STATEMENT:  CREATE TABLE t_familypic (
>         sysid bigint DEFAULT nextval('public.t_familypic_sysid_seq'::text) NOT NULL,
>         mbrsysid bigint NOT NULL,
>         familysysid bigint NOT NULL,
>         familypic lo
>     );


do you have the source code to this lo.dll so you can be rebuild it for
9.5 ?





--
john r pierce, recycling bits in santa cruz



Вложения