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

Hi John,

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):=20
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding =3D UTF8
pg_dump: saving standard_conforming_strings =3D off
pg_dump: saving database definition
pg_dump: creating SCHEMA "public"
pg_dump: creating COMMENT "SCHEMA public"
pg_dump: creating FUNCTION "public.plpgsql_call_handler()"
pg_dump: creating FUNCTION "public.plpgsql_validator(oid)"
pg_dump: creating PROCEDURAL LANGUAGE "public.plpgsql"
pg_dump: creating SHELL TYPE "public.lo"
pg_dump: creating FUNCTION "public.lo_in(cstring)"
pg_dump: creating FUNCTION "public.lo_out(lo)"
pg_dump: creating TYPE "public.lo"
pg_dump: creating FUNCTION "public.chk_exist(character varying)"
pg_dump: creating FUNCTION "public.chk_exist_rec(character varying, =
numeric, bigint)"
pg_dump: creating FUNCTION "public.database_size(name)"
pg_dump: creating FUNCTION "public.get_cat_id(bigint)"
pg_dump: creating FUNCTION "public.get_subcat_id(bigint)"
pg_dump: pcg_restore: [archiver] reating FUiNCTION "publincp.lo(oid)"ut =
file does not appear to be a valid archive
pg_dump: creating FUNCTION "public.lo_manage()"
pg_dump: creating FUNCTION "public.lo_oid(lo)"
pg_dump: creating FUNCTION "public.oid(lo)"
pg_dump: creating FUNCTION "public.pg_database_size(oid)"
pg_dump: creating FUNCTION "public.pg_dir_ls(text, boolean)"
pg_dump: creating FUNCTION "public.pg_file_length(text)"
pg_dump: creating FUNCTION "public.pg_file_read(text, bigint, bigint)"
pg_dump: creating FUNCTION "public.pg_file_rename(text, text)"
pg_dump: creating FUNCTION "public.pg_file_rename(text, text, text)"
pg_dump: creating FUNCTION "public.pg_file_stat(text)"
pg_dump: creating FUNCTION "public.pg_file_unlink(text)"
pg_dump: creating FUNCTION "public.pg_file_write(text, text, boolean)"
pg_dump: [archiver] could not write to output file: Invalid argument

Please advise.

Regards,




NETsolutions Asia Limited
http://www.nsasia.co.th

>>> Premsun Choltanwanich 2016-03-02 09:04 >>>
Hi John,

Following your instruction by only modify some parameters to match with my =
system, below is a command I just running under "C:\Program Files\PostgreSQ=
L\9.5\bin" on my PostgreSQL 9.5.1 after trying that "psql -h 192.168.200.75=
 clubprogram clubadmin" is worked fine.

My Command:
pg_dump -Ft -v -b -h 192.168.200.75 -U clubadmin -d clubprogram | =
pg_restore -U clubadmin -d clubprogram

Almost Last Result Message:
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding =3D UTF8
pg_dump: saving standard_conforming_strings =3D off
pg_dump: saving database definition
pg_restore: [tar archiver] corrupt tar header found in TION (expected 0, =
computed 37579) file position 512
pg_dump: [tar archiver] could not write to output file: Broken pipe

LOG Message:
No log is created after running above command.


Do you have any other suggestion? Or, May I need to modify some parameters =
on my command?




NOTE: My PostgreSQL 9.5.1 is running on Windows 8 (64bit) and PostgreSQL =
8.0.13 is running on Windows XP (32bit).



>>> John R Pierce <pierce@hogranch.com> 2016-03-01 12:33 >>>
On 2/29/2016 6:08 PM, Premsun Choltanwanich wrote:
> I have very old project database which also contain lo data (large=20
> object data managed by database's functions as lo(oid),=20
> lo_in(cstring), lo_oid(lo), lo_out(lo) and oid(lo) to manage ) running=20=

> on PostgreSQL 8.0.13 and need to migrate it to most recently version=20
> as PostgreSQL 9.5.1.
>
> After install PostgreSQL 9.5.1, I running a simple command pg_dumb to=20
> 8.0.13 server with parameters required for generate dump file by =
blobs=20
> also be included then running psql with all required parameters to=20
> create those dump data to 9.5.1 server. However, almost tables are=20
> transferred to new server after the process completed except tables=20
> which contain my lo data that all those tables are missing from the=20
> database.
>
> Please advise to me, How could I migrate 8.0.13 database with large=20
> object data to be working on 9.5.1?

phew, 8.0 has been unsupported now for several years.

that said, the latest pg_dump and pg_dumpall are supposed to be able to=20
dump just about any postgres database going back as far as 7.4 (?) in a=20
format that can be restored on the same version as the pg_dump software.

if you have an old and new server running side by side, I'd try, from=20
the new server...

1) if needed, configure the old server to allow the new one to log onto=20
the old postgres databases (pg_hba.conf, and possibly listen_address=20
and/or firewall rules), verify this works with 'psql -h oldserver=20
dbname' as the postgres user on the new server.

2) from the /new/ 9.5 server, as the postgres user,
       $  pg_dump -Ft -h oldserver olddatabase | pg_restore -d =
newdatabase
(where newdatabase is a fresh empty database on the new server)


if you have the two database servers running side by side on the same=20
server using different ports and paths, then, as the postgres user...
     $ /usr/pgsql-9.5/bin/pg_dump -p oldport -Ft olddatabase |=20
/usr/pgsql-9.5/bin/pg_restore -p newport -Fc newdatabase

if you've already done pretty much the same thing as this, and/or if=20
these fail the same way, it would be useful to look at the postgres=20
logfile from when you ran the dump and restore jobs on both servers.




--=20
john r pierce, recycling bits in santa cruz

On 02/03/16 17:05, Premsun Choltanwanich wrote:
> Hi John,
>
> 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):

(snippage)

> pg_dump: creating FUNCTION "public.pg_database_size(oid)"
> pg_dump: creating FUNCTION "public.pg_dir_ls(text, boolean)"
> pg_dump: creating FUNCTION "public.pg_file_length(text)"
> pg_dump: creating FUNCTION "public.pg_file_read(text, bigint, bigint)"
> pg_dump: creating FUNCTION "public.pg_file_rename(text, text)"
> pg_dump: creating FUNCTION "public.pg_file_rename(text, text, text)"
> pg_dump: creating FUNCTION "public.pg_file_stat(text)"
> pg_dump: creating FUNCTION "public.pg_file_unlink(text)"
> pg_dump: creating FUNCTION "public.pg_file_write(text, text, boolean)"
> pg_dump: [archiver] could not write to output file: Invalid argument
>
> Please advise.
>

I'd guess you are running into something like

http://stackoverflow.com/questions/15672651/failure-using-heroku-pgtransfer

i.e old functions that cannot be recreated in 9,5, maybe check the logs
on the 9.5 server for the reason you are seeing 'invalid argument'.

You may need to drop those pg_file_* and pg_database_size functions
(they are not native to 8.0...I checked...had to compile a 8.0.26, which
was interesting).

regards

Mark

On 02/03/16 18:21, Mark Kirkwood wrote:
> On 02/03/16 17:05, Premsun Choltanwanich wrote:
>> Hi John,
>>
>> 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):
>
> (snippage)
>
>> pg_dump: creating FUNCTION "public.pg_database_size(oid)"
>> pg_dump: creating FUNCTION "public.pg_dir_ls(text, boolean)"
>> pg_dump: creating FUNCTION "public.pg_file_length(text)"
>> pg_dump: creating FUNCTION "public.pg_file_read(text, bigint, bigint)"
>> pg_dump: creating FUNCTION "public.pg_file_rename(text, text)"
>> pg_dump: creating FUNCTION "public.pg_file_rename(text, text, text)"
>> pg_dump: creating FUNCTION "public.pg_file_stat(text)"
>> pg_dump: creating FUNCTION "public.pg_file_unlink(text)"
>> pg_dump: creating FUNCTION "public.pg_file_write(text, text, boolean)"
>> pg_dump: [archiver] could not write to output file: Invalid argument
>>
>> Please advise.
>>
>
> I'd guess you are running into something like
>
> http://stackoverflow.com/questions/15672651/failure-using-heroku-pgtransfer
>
> i.e old functions that cannot be recreated in 9,5, maybe check the logs
> on the 9.5 server for the reason you are seeing 'invalid argument'.
>
> You may need to drop those pg_file_* and pg_database_size functions
> (they are not native to 8.0...I checked...had to compile a 8.0.26, which
> was interesting).
>

I tested that idea out (adding in the dbsize contrib for 8.0 and doing
dump/restore to 9.5) and while I see errors for *those* functions,
everything else (i.e tables etc) are loaded into 9.5 ok.

So something else is tripping you up - have a look at the postgres logs
for the 8.0 and 9.5 systems.

Cheers

Mark