We have a web application that allows users to modify data through an API. In order to allow people to run tests, we provide a sandbox area called 'daily'. This sandbox gets refreshed every day from the 'live' database.
This works by dropping/creating a 'daily' schema then copying over a subset of tables from the 'live' schema. This is applied by running a simple SQL script via crontab every day (see below).
- when the web application is started after the daily refresh, clients can insert data into the 'daily' schema as expected
when the web application is started before the daily refresh, we encounter permission errors when trying to insert data, eg
permission denied for relation annotation
Note: restarting the web application allows everything to work fine, but I would prefer to avoid the downtime.
- is this behaviour expected (Pg 9.0.23)?
- is there anything I can do to avoid this problem?
The script we are using to provide the daily refresh:
BEGIN; drop schema if exists genome3d_data_daily CASCADE; create schema genome3d_data_daily AUTHORIZATION genome3ddaily; -- recreate sequence create sequence genome3d_data_daily.annotation_id_seq; select setval('genome3d_data_daily.annotation_id_seq', orig.last_value) from genome3d_data.annotation_id_seq orig; -- create new tables (including constraints, indexes, etc) create table genome3d_data_daily.annotation_type (like genome3d_data.annotation_type including all); create table genome3d_data_daily.annotation_segment (like genome3d_data.annotation_segment including all); create table genome3d_data_daily.annotation (like genome3d_data.annotation including all); grant select on all tables in schema genome3d_data_daily to PUBLIC; grant select on all sequences in schema genome3d_data_daily to PUBLIC; grant all privileges on all tables in schema genome3d_data_daily to genome3ddaily; grant all privileges on all sequences in schema genome3d_data_daily to genome3ddaily; -- by default this will point to the original sequence alter table genome3d_data_daily.annotation alter column id set default nextval('genome3d_data_daily.annotation_id_seq'); -- insert data insert into genome3d_data_daily.annotation_type select * from genome3d_data.annotation_type; insert into genome3d_data_daily.annotation_segment select * from genome3d_data.annotation_segment; insert into genome3d_data_daily.annotation select * from genome3d_data.annotation; END;