When restoring, I generally use the --disable-triggers, which instructs
Postgres to ignore foreign key constraints.
--nate
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
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