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

(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

On Mar 2, 2016 06:01, "John R Pierce" <pierce@hogranch.com> wrote:
>
> (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...
>

How large is the total database? The earliest versions of pg on Windows had bugs in pg_dump for files larger than 2GB. I don't recall exactly when they were fixed, but this was a long time ago.. Through if my memory is correct the actual bugs were in pg_dump itself, so using a new pg_dump against the old server should be safe.

/Magnus

On 3/2/2016 12:29 AM, Magnus Hagander wrote:
> How large is the total database? The earliest versions of pg on
> Windows had bugs in pg_dump for files larger than 2GB. I don't recall
> exactly when they were fixed, but this was a long time ago.. Through
> if my memory is correct the actual bugs were in pg_dump itself, so
> using a new pg_dump against the old server should be safe.

yeah, following my direction, he's running pg_dump on the new 9.5
server.   the old 8.0 database is on a different PC, an old XP system.



--
john r pierce, recycling bits in santa cruz