Restoring galaxy database return few empty tables.
Dear Galaxy Team, I am switching our Galaxy production service to a new machine, We are using Postgres as database server. The existing database belongs to postgres version 8.3 and I took my instance's dump using postgres version 8.4. I am able to restore my database on postgres 8.4, Except few tables were empty and with few strange error message left on log file. I am not sure how many tables are empty, but I found table called 'job' returns 0 rows. Original job table returns me 40733 rows. excerpts from error log: ERROR: insert or update on table "history_dataset_association_annotation_association" violates foreign key constraint "history_dataset_association_a_history_dataset_association__fkey" DETAIL: Key (history_dataset_association_id)=(284985) is not present in table "history_dataset_association". STATEMENT: ALTER TABLE ONLY history_dataset_association_annotation_association ADD CONSTRAINT history_dataset_association_a_history_dataset_association__fkey FOREIGN KEY (history_dataset_association_id) REFERENCES history_dataset_association(id); ERROR: insert or update on table "history_dataset_association_tag_association" violates foreign key constraint "history_dataset_association_t_history_dataset_association__fkey" DETAIL: Key (history_dataset_association_id)=(387932) is not present in table "history_dataset_association". STATEMENT: ALTER TABLE ONLY history_dataset_association_tag_association In my knowledge we didn't do any manual transactions on Galaxy database and Could anyone let me know something wrong at my end. I'm sure this has been covered somewhere but I haven't been able to find it on the Wiki or by searching the archives. Thanks in advance, Vipin Friedrich Miescher Laboratory of the Max Planck Society Spemannstrasse 39, 72076 Tuebingen, Germany
Vipin TS wrote:
Dear Galaxy Team,
I am switching our Galaxy production service to a new machine, We are using Postgres as database server. The existing database belongs to postgres version 8.3 and I took my instance's dump using postgres version 8.4. I am able to restore my database on postgres 8.4, Except few tables were empty and with few strange error message left on log file. I am not sure how many tables are empty, but I found table called 'job' returns 0 rows. Original job table returns me 40733 rows.
excerpts from error log: ERROR: insert or update on table "history_dataset_association_annotation_association" violates foreign key constraint "history_dataset_association_a_history_dataset_association__fkey" DETAIL: Key (history_dataset_association_id)=(284985) is not present in table "history_dataset_association". STATEMENT: ALTER TABLE ONLY history_dataset_association_annotation_association ADD CONSTRAINT history_dataset_association_a_history_dataset_association__fkey FOREIGN KEY (history_dataset_association_id) REFERENCES history_dataset_association(id); ERROR: insert or update on table "history_dataset_association_tag_association" violates foreign key constraint "history_dataset_association_t_history_dataset_association__fkey" DETAIL: Key (history_dataset_association_id)=(387932) is not present in table "history_dataset_association". STATEMENT: ALTER TABLE ONLY history_dataset_association_tag_association
In my knowledge we didn't do any manual transactions on Galaxy database and Could anyone let me know something wrong at my end. I'm sure this has been covered somewhere but I haven't been able to find it on the Wiki or by searching the archives.
Hi Vipin, When restoring, I generally use the --disable-triggers, which instructs Postgres to ignore foreign key constraints. --nate
Thanks in advance, Vipin Friedrich Miescher Laboratory of the Max Planck Society Spemannstrasse 39, 72076 Tuebingen, Germany
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at:
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
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
Vipin TS wrote:
Hello Nate,
Was your original dump from a database using an encoding other than
UTF-8?
--nate
My database was in postgres 8.3 and I took the database dump using postgres 8.4. I did not understand your question at right way.
Can you make sure that your dump was created with '-E UTF-8' flag? Also, in your old database server, use psql's '\l' command to show the encoding of your databases. --nate
Vipin
Hello Nate, Can you make sure that your dump was created with '-E UTF-8' flag?
I didn't use any customized encoding for my dump, took the default one, I believe which is the database encoding.
Also, in your old database server, use psql's '\l' command to show the encoding of your databases.
My database encoding is SQL_ASCII thanks, Vipin
Vipin TS wrote:
Hello Nate,
Can you make sure that your dump was created with '-E UTF-8' flag?
I didn't use any customized encoding for my dump, took the default one, I believe which is the database encoding.
Also, in your old database server, use psql's '\l' command to show the encoding of your databases.
My database encoding is SQL_ASCII
Is your new database UTF-8? If so, you'll want to drop it and recreate in the same encoding as your source database. --nate
thanks, Vipin
participants (2)
-
Nate Coraor -
Vipin TS