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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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@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
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

On 3/1/2016 6:04 PM, Premsun Choltanwanich wrote:

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).

try the dump without the -Ft flag (tar format) and piped into psql...
like...

c:
cd \Program Files\PostgreSQL\9.5\bin
pg_dump -h 192.168.200.75 -U clubadmin -d clubprogram | psql -U
clubadmin -d clubprogram

(after first dropping and recreating the new database)

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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):
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_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\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).

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
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

(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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi John,

There are about 400GB of disk space on the machine that running PostgreSQL 9.5.1.

And, almost tables are transferred to new server except tables which contain lo data (all those tables are missing from the database) after running pg_dump and psql following as per your suggestion.

The attachment is a log file created after all processes are completed.

Please advise.

Regards,

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

John R Pierce <pierce@hogranch.com> 2016-03-02 11:59 >>>

(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

Hi Magnus,

My database size is about 1.5 GB by most of them are lo (large object) data.

Regards,

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

Magnus Hagander <magnus@hagander.net> 2016-03-02 15:29 >>>

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 5:52 PM, Premsun Choltanwanich wrote:

And, almost tables are transferred to new server except tables which
contain lo data (all those tables are missing from the database) after
running pg_dump and psql following as per your suggestion.

The attachment is a log file created after all processes are completed.

your logfile shows a bunch of custom functions being defined which are
dependent on a missing binary library, apparently your previous install
of postgres has some customizations.

2016-03-02 18:06:25 ICT ERROR: could not find function "lo_in" in file "C:/Program Files/PostgreSQL/9.5/lib/lo.dll"
2016-03-02 18:06:25 ICT STATEMENT: CREATE FUNCTION lo_in(cstring) RETURNS lo
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/lo', 'lo_in';

these custom functions are being used to define a custom data type.

2016-03-02 18:06:25 ICT ERROR: function lo_in(cstring) does not exist
2016-03-02 18:06:25 ICT STATEMENT: CREATE TYPE lo (
INTERNALLENGTH = 4,
INPUT = lo_in,
OUTPUT = lo_out,
ALIGNMENT = int4,
STORAGE = plain
);

and your tables are using this custom data type 'lo', which is invalid
since the code to implement it is missing.

2016-03-02 18:06:26 ICT ERROR: type "lo" is only a shell at character 186
2016-03-02 18:06:26 ICT STATEMENT: CREATE TABLE t_familypic (
sysid bigint DEFAULT nextval('public.t_familypic_sysid_seq'::text) NOT NULL,
mbrsysid bigint NOT NULL,
familysysid bigint NOT NULL,
familypic lo
);

do you have the source code to this lo.dll so you can be rebuild it for
9.5 ?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Joon,

I have no source code for this contrib/lo and dll/function seem already be stored with my 8.0.13 installation package.

Any suggestion?

Regards,

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

John R Pierce <pierce@hogranch.com> 2016-03-03 09:05 >>>

On 3/2/2016 5:52 PM, Premsun Choltanwanich wrote:

And, almost tables are transferred to new server except tables which
contain lo data (all those tables are missing from the database) after
running pg_dump and psql following as per your suggestion.

The attachment is a log file created after all processes are completed.

your logfile shows a bunch of custom functions being defined which are
dependent on a missing binary library, apparently your previous install
of postgres has some customizations.

2016-03-02 18:06:25 ICT ERROR: could not find function "lo_in" in file "C:/Program Files/PostgreSQL/9.5/lib/lo.dll"
2016-03-02 18:06:25 ICT STATEMENT: CREATE FUNCTION lo_in(cstring) RETURNS lo
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/lo', 'lo_in';

these custom functions are being used to define a custom data type.

2016-03-02 18:06:25 ICT ERROR: function lo_in(cstring) does not exist
2016-03-02 18:06:25 ICT STATEMENT: CREATE TYPE lo (
INTERNALLENGTH = 4,
INPUT = lo_in,
OUTPUT = lo_out,
ALIGNMENT = int4,
STORAGE = plain
);

and your tables are using this custom data type 'lo', which is invalid
since the code to implement it is missing.

2016-03-02 18:06:26 ICT ERROR: type "lo" is only a shell at character 186
2016-03-02 18:06:26 ICT STATEMENT: CREATE TABLE t_familypic (
sysid bigint DEFAULT nextval('public.t_familypic_sysid_seq'::text) NOT NULL,
mbrsysid bigint NOT NULL,
familysysid bigint NOT NULL,
familypic lo
);

do you have the source code to this lo.dll so you can be rebuild it for
9.5 ?

--
john r pierce, recycling bits in santa cruz

Wow -- you have an old db version there! When doing a little research, I
found that back in 2005 you actually had the same basic issue - that the
way you were using the "lo" contrib module in 7.x and 8.0 was not supported
in 8.1 anymore:
/messages/by-id/439FFA3F.C5F7.004C.0@nsasia.co.th

My first approach would be to change your 8.0 database to use supported
blob types and functions so that you can dump and restore.

On Thu, Mar 3, 2016 at 6:53 AM, Premsun Choltanwanich <Premsun@nsasia.co.th>
wrote:

On 3/3/2016 3:53 AM, Premsun Choltanwanich wrote:

I have no source code for this contrib/lo and dll/function seem
already be stored with my 8.0.13 installation package.

upon looking at the current docs again in the light of day, I see that
there *IS* a contrib/lo module in all recent versions.

try, before doing the restore... as postgres with your empty database...

psql mynewdatabase -c "create extension lo"

I would also consider doing the dump in two parts. first pass use
--schema-only to just dump the defninitions, and restore these, this
will create your database structures with no data. you may need to
edit the generated .sql file to remove these redundant/erroneous
function definitions. then once the schema is created cleanly, do
the same dump but with --data-only and to a different file, this will
dump just the actual database data, which you them 'restore' to the same
database you've already created the schema in...

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi John,

Database seem now completely transferred after "create extension lo" on my new database then restore all data from the backup file. (tested by my application to call/save all information data and picture with database)

Thank you for your kindly help.

Regards,

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

John R Pierce <pierce@hogranch.com> 2016-03-04 01:37 >>>

On 3/3/2016 3:53 AM, Premsun Choltanwanich wrote:

I have no source code for this contrib/lo and dll/function seem
already be stored with my 8.0.13 installation package.

upon looking at the current docs again in the light of day, I see that
there *IS* a contrib/lo module in all recent versions.

try, before doing the restore... as postgres with your empty database...

psql mynewdatabase -c "create extension lo"

I would also consider doing the dump in two parts. first pass use
--schema-only to just dump the defninitions, and restore these, this
will create your database structures with no data. you may need to
edit the generated .sql file to remove these redundant/erroneous
function definitions. then once the schema is created cleanly, do
the same dump but with --data-only and to a different file, this will
dump just the actual database data, which you them 'restore' to the same
database you've already created the schema in...

--
john r pierce, recycling bits in santa cruz