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

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\PostgreSQL\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 = UTF8
pg_dump: saving standard_conforming_strings = 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).

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

>>> John R Pierce <pierce(at)hogranch(dot)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
> object data managed by database's functions as lo(oid),
> lo_in(cstring), lo_oid(lo), lo_out(lo) and oid(lo) to manage ) running
> on PostgreSQL 8.0.13 and need to migrate it to most recently version
> as PostgreSQL 9.5.1.
>
> After install PostgreSQL 9.5.1, I running a simple command pg_dumb to
> 8.0.13 server with parameters required for generate dump file by blobs
> also be included then running psql with all required parameters to
> create those dump data to 9.5.1 server. However, almost tables are
> transferred to new server after the process completed except tables
> which contain my lo data that all those tables are missing from the
> database.
>
> Please advise to me, How could I migrate 8.0.13 database with large
> 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
dump just about any postgres database going back as far as 7.4 (?) in a
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
the new server...

1) if needed, configure the old server to allow the new one to log onto
the old postgres databases (pg_hba.conf, and possibly listen_address
and/or firewall rules), verify this works with 'psql -h oldserver
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
server using different ports and paths, then, as the postgres user...
$ /usr/pgsql-9.5/bin/pg_dump -p oldport -Ft olddatabase |
/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
these fail the same way, it would be useful to look at the postgres
logfile from when you ran the dump and restore jobs on both servers.

--
john r pierce, recycling bits in santa cruz