Page History

...

• Dump the database to an SQL file, and create a copy too:

  Code Block
  pg_dump terena > terena.sql cp terena.sql backup.sql

• Now edit the SQL file, by locating the view definition, and change it to what it should be.

• Create a database patch from the changes: 

  Code Block
  diff -Nru backup.sql terena.sql > terena.diff

  An example of such a patch, that adds an extra field to the vw_events view: 

  Code Block
  language diff
  --- terena.sql 2012-08-20 12:52:47.000000000 +0200 +++ backup.sql 2012-08-20 12:58:39.000000000 +0200 @@ -1935,7 +1935,7 @@ -- CREATE VIEW vw_events AS - SELECT event_status.status, event_types.name AS event_type, events.event_id, events.gcal_event_id, events.owner_email, events.event_type_id, events.name, events.description, events.starts, events.ends, date_part('day'::text, events.starts) AS start_day, date_part('month'::text, events.starts) AS start_month, date_part('year'::text, events.starts) AS start_year, date_part('day'::text, events.ends) AS end_day, date_part('month'::text, events.ends) AS end_month, date_part('year'::text, events.ends) AS end_year, events.venue_name, events.address, events.city, events.country, events.country_code, events.project_url, events.direct_link, events.agenda_url, events.show, events.show_homepage, events.event_status_id, events.reg_deadline, events.capacity, events.customfield1_label, events.inserted, events.modified, events.group_id FROM ((event_types JOIN events ON ((event_types.event_type_id = events.event_type_id))) JOIN event_status ON ((events.event_status_id = event_status.event_status_id))); + SELECT event_status.status, event_types.name AS event_type, events.event_id, events.gcal_event_id, events.owner_email, events.event_type_id, events.name, events.description, events.starts, events.ends, date_part('day'::text, events.starts) AS start_day, date_part('month'::text, events.starts) AS start_month, date_part('year'::text, events.starts) AS start_year, date_part('day'::text, events.ends) AS end_day, date_part('month'::text, events.ends) AS end_month, date_part('year'::text, events.ends) AS end_year, events.venue_name, events.address, events.city, events.country, events.country_code, events.project_url, events.direct_link, events.agenda_url, events.show, events.show_homepage, events.event_status_id, events.reg_deadline, events.capacity, events.customfield1_label, events.inserted, events.modified, events.group_id, events.show_reglink FROM ((event_types JOIN events ON ((event_types.event_type_id = events.event_type_id))) JOIN event_status ON ((events.event_status_id = event_status.event_status_id))); ALTER TABLE public.vw_events OWNER TO postgres;

• Now make sure the database has no users. In our case this means shutting down the apache web server. Then dump the database, drop it, patch the dump, and restore the patched dump: 

  Code Block
  sudo apache2ctl stop pg_dump terena > terena.latest.sql patch terena.latest.sql < terena.diff dropdb terena createdb -T template0 psql terena < terena.latest.diffsql sudo apache2ctl start

You should test this of course by restoring to another database to make sure the patch is working OK. If you feel comfortable you could script everything together to minimise downtime.

...