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

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