Vipin TS wrote:
Sorry for the relayed response from my side.
As you suggested I tried with --disable-triggers option for restoring the database from the dump file, Apparently this returns some notice message associated with circular foreign-key constraints for few tables. Here are some notice messages:
pg_dump: NOTICE: there are circular foreign-key constraints among these table(s): pg_dump: stored_workflow pg_dump: workflow pg_dump: You may not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints. pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
and finally ended up with:
pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem. pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3075; 0 16650 TABLE DATA history_dataset_association galaxy pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xb9 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY history_dataset_association, line 8149 pg_restore: [archiver (db)] Error from TOC entry 3064; 0 16435 TABLE DATA job galaxy pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xb9 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY job, line 16578 WARNING: errors ignored on restore: 2
Was your original dump from a database using an encoding other than UTF-8? --nate
Based on the suggestions from the error and notice message first one seems to be not much harm to the database contents. I believe the second one points to some naming related thing, other than that I don't have much idea. According to the above suggestions I tried to load the dump files directly and it also ended up with error message. from the man page I understood that pg_restore will use the option --disable-triggers with --data-only dump.
My issue is not yet solved, that is the 'job' table seems to be empty. Please let me know if I am doing something wrong. Here is my inputs,
dumping table schema
pg_dump --schema --create -F t --host MAN | pg_restore --dbname=galaxy -F t
dumping table contents
pg_dump --data-only --create -F t --host MAN | pg_restore --dbname=galaxy -F t --disable-triggers
Many thanks for your suggestions, Vipin