Обсуждение: 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 John,

There are about 400GB of disk space on the machine that running PostgreSQL 9.5.1.

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.

Please advise.

Regards,

NETsolutions Asia Limited   +66 2 401 9250

NETsolutions Asia Limited
>>> John R Pierce <pierce@hogranch.com> 2016-03-02 11:59 >>>

(thread moved from pg_bugs)
(upgrading a 8.0.13 database on Windows XP 32bit to 9.5.1 on Windows 8
64 bit.....)

On 3/1/2016 8:05 PM, Premsun Choltanwanich wrote:
> Modified command by remove -Ft flag as per you suggestion:
> pg_dump -v -h 192.168.200.75 -U clubadmin -d clubprogram | psql -U
> clubadmin -d clubprogram
>
> Result (got same message even with parameter -b or not):
> pg_dump: reading rewrite rules
> pg_dump: reading policies
> pg_dump: reading large objects
> ...
> pg_dump: creating FUNCTION "public.plpgsql_call_handler()"
> pg_dump: creating FUNCTION "public.plpgsql_validator(oid)"
> ...
> pg_dump: creating FUNCTION "public.pg_file_write(text, text, boolean)"

those all sound like standard postgres functions, its not clear to me
why pg_dump is generating the CREATE FUNCTION code for them.

> pg_dump: [archiver] could not write to output file: Invalid argument
>

ok, presumably your new box has plenty of disk space?  try this on the
new 9.5 system...

     "C:\Program Files\PostgreSQL\9.5\bin\pg_dump" -v -h 192.168.200.75
-U clubadmin -d clubprogram -f "D:\Backups\clubprogram-2016-03-01.sql"

(replace D:\Backups\  with wherever you want to put this potentially
rather large backup file)

if that works, then ...

         "C:\Program Files\PostgreSQL\9.5\bin\psql" -U clubadmin -d
clubprogram -f "D:\Backups\clubprogram-2016-03-01.sql"

should load the dumped data onto the new system.


--
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/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