Re: could not migrate 8.0.13 database with large object data to 9.5.1 - Mailing list pgsql-bugs

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