opentrials
opentrials copied to clipboard
"ERROR: function set_updated_at() does not exist" when restoring from last dump
The last dump on the website is from April 1 2018. When attempting to restore it via
pg_restore -e -v -O -x --no-owner --clean --if-exists --host=eldamar --dbname=opentrials ~/Downloads/opentrials-api-2018-04-01.dump
It spends some minutes "working" and creating various artifacts before choking on creating a trigger referencing a non-existing function set_updated_at():
...
pg_restore: creating INDEX "public.trialrecords_trial_id_index"
pg_restore: creating INDEX "public.trials_documents_document_id_index"
pg_restore: creating TRIGGER "public.conditions_set_updated_at"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3061; 2620 87835462 TRIGGER conditions_set_updated_at database
pg_restore: [archiver (db)] could not execute query: ERROR: function set_updated_at() does not exist
Command was: CREATE TRIGGER conditions_set_updated_at BEFORE UPDATE ON conditions FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
What needs to be done differently for this to work? I am on postgres 11.4
You just need to manually create the trigger https://github.com/opentrials/opentrials/blob/0eda7e551a186e20057f4621f06c293f03945fa9/migrations/20170123121324_add_trigger_for_updated_at.js
CREATE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$;