Hello Nate,
When restoring, I generally use the --disable-triggers, which instructs Postgres to ignore foreign key constraints.
--nate
pg_dump: NOTICE: there are circular foreign-key constraints among these
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
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: table(s): 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