---
title: Enabling database migrations with cross-database PostgreSQL triggers
description: Redesigning a backend system can be challenging. Our senior software engineer Erry explains how to make it simpler with cross-database PostgreSQL triggers.
image: https://blog.lantum.com/hubfs/Blog%20and%20social/dev-blog.png
---

[![Lantum](https://blog.lantum.com/hubfs/Lantum%20March2018/Images/logo-header.svg)](https://us.lantum.com)

for Locums

- [Locums](https://lantum.com/)
- [Practices](https://lantum.com/p/practices/)
- [Hubs](https://lantum.com/p/hubs/)
- [Federations](https://lantum.com/p/nhs-federation/)

[Dev team](https://blog.lantum.com/tag/dev-team), [Life at Lantum](https://blog.lantum.com/tag/life-at-lantum)

07 Oct 2020

[![Devblog: enabling database migrations with cross-database PostgreSQL triggers](https://blog.lantum.com/hubfs/Blog%20and%20social/dev-blog.png)](https://blog.lantum.com/enabling-database-migrations-with-cross-database-postgresql-triggers)

# Enabling database migrations with cross-database PostgreSQL triggers

 0 Comments <https://blog.lantum.com/enabling-database-migrations-with-cross-database-postgresql-triggers#comments-listing>

- [Tweet](https://twitter.com/share)

As part of my role at Lantum, I work with the team responsible for writing a new implementation of the part of the platform that allows workers to submit their timesheets and seek payment for their work.

Within the Lantum product, a worker, after completing a shift at a hospital, has a record of how many hours they worked and thus how much money they will receive for that work: their timesheet. 

The system allows workers to make changes to the timesheet before they submit it to the hospital that they worked for (for example, stating that they started work earlier or finished later), and the hospital has the ability to approve or reject those changes before paying the worker.

The old backend system was badly designed with a lot of duplication in the data structure and data going out of sync, hence we decided to re-design the database in a more atomic, normalised way.

To complete that work, we realised pretty early on that the best way to change from one system to the other with minimal disruption was to write the new API, complete with a new, better designed and more normalised database schema. Then we would migrate the data over and switch which service is used.

So we could perform the migration early and minimise disruptions for the users, we decided on the following pattern:

- Update the code that writes to the database to use the new service
- Keep data replicated to the old database, so any services that need read access can still use the old service temporarily
- Migrate the code that reads from the database to read from the new service

This was because there were fewer places performing writes than performing reads, and those were the more important places to update, so we could prioritise updating those early. 

## **How we planned to replicate the data**

Since the two database schemas were different, I proposed that we use [PostgreSQL triggers](https://www.postgresql.org/docs/9.1/sql-createtrigger.html) to keep the data replicated to the old database. 

Triggers are a PostgreSQL feature that allow you to run queries before or after data is inserted to, updated, or deleted from a table, or instead of an insert, update, or delete query that’s run against a view. For our use case, this would allow us to know when data is inserted to the new database tables, and replicate it to the old tables using the old format.

This was a very interesting challenge, and I wanted to share the procedure I followed for anybody else wanting to do something similar. For obvious reasons I have changed the names of columns, tables, and databases, but the procedure should be the same.

## **The database schemas that we’re working with**

For the purpose of this blog post, consider that this is the schema as it is in the new database on the top, and as it was in the old database on the bottom:

![The schema for the old database, with timesheet_new and a separate database for timesheet_options_id. Plus the schema for timesheet_old, which housed everything.](https://lh4.googleusercontent.com/ea2szeIR3nABcTS16lvd95tOeESUI5Hti_YWLmCcLI_Ta_Z2LtNHyoBSJTjP3O3r6-YN7EnVyX0vRl4WUP1yGOCkG6XzOESBOryyPvfnQDOjSSywa4Ar5uhggy-MN9EWIwo3PuLM)

As you can see the column `hospital_id` was moved to the `timesheet_options` table (there are sensible reasons for this move that I won’t bore you with), and the `is_approved` column was removed, as it’s just an indication of `status == approved`

## **Creating a cross-database connection**

This migration is happening across two different databases, let’s say from `timesheet_old` to `timesheet_new`. We need to set up a connection from `timesheet_new` to `timesheet_old` using the [PostgreSQL Foreign Data Wrapper (FDW),](https://www.postgresql.org/docs/9.5/postgres-fdw.html) which was introduced in version 9.5. This will allow us to run queries on the `timesheet_old` database when records are inserted in the `timesheet_new` database.

First enable the FDW extension:

| `CREATE EXTENSION IF NOT EXISTS postgres_fdw;` |
| --- |

Then we create a reference to the server and database that we want to connect to. In this case, it’s a different database on the same server, so we use `localhost`, but it could be on a completely different server.

We also create a user mapping, which specifies the username and password we use to connect to the foreign database.

````

| `CREATE SERVER timesheet_old_server` `FOREIGN DATA WRAPPER postgres_fdw` `OPTIONS (host 'localhost', dbname 'timesheet_old');`   `# This specifies which username and password to use.` `CREATE USER MAPPING FOR CURRENT_USER` `SERVER timesheet_old_server` `OPTIONS (user 'username', password 'password');` |
| --- |

 

Even though we created a reference to the foreign server, we have no visibility of the tables available on that server at the time being. We could manually specify all the foreign tables using [CREATE FOREIGN TABLE](https://www.postgresql.org/docs/9.5/sql-createforeigntable.html), or just import the whole schema right away. In this case, we are opting for the latter. 

Note that this is just a representation of the foreign schema at the point in time, and not a real-time reference to that schema. This means that it does not automatically update. There is also no requirement for the foreign schema to match the actual physical schema on the foreign server, although if the schemas do not match queries may not succeed. There are, however, cases, where you may not want to copy certain columns to the foreign schema, which I will get to later.

In order to create a schema for our foreign database and import the schema from the remote server, we run the following:

| `CREATE SCHEMA timesheet_old_schema;` `IMPORT FOREIGN SCHEMA public` `FROM SERVER timesheet_old_server` `INTO timesheet_old_schema;` |
| --- |

 

````

Now if you check the tables in `timesheet_old_schema` you should be able to see all tables that exist in the remote database. You can run any SQL queries on them as normal. For example, the following command should return data that exists in one of the foreign tables:

| `SELECT * FROM timesheet_old_schema.timesheet_old LIMIT 1;` |
| --- |

 

## **Creating the database triggers**

Now that we have the foreign server defined, the schema imported and have verified that the connection works, we can actually create the database triggers that will keep our old database up to date when records are inserted into our new database.

In order to create the triggers, we first need to define a function that will run when the record is inserted. Because this function is run as part of a trigger, it has access to two special variables, `OLD` and `NEW. OLD` is only available when running an `UPDATE` query and has the values of the old record. `NEW` is available for inserts and updates and refers to the new record.

| `CREATE OR REPLACE FUNCTION new_records_inserted()` `RETURNS TRIGGER` `AS $$` `BEGIN` `  INSERT INTO timesheet_old_schema.timesheet_old` `  (id, worker_id, hospital_id, status, is_approved)` `  VALUES (` `   NEW.id,` `   NEW.worker_id, ` `   (SELECT tso.hospital_id FROM timesheet_options tso` `    WHERE tso.id=NEW.timesheet_options_id),` `   NEW.status,` `   (CASE WHEN NEW.status='approved' THEN true ELSE false END) --- is_approved` `  );` `RETURN NEW;` `END;` `$$ LANGUAGE PLPGSQL;` |
| --- |

 

````

As you can see, the syntax is mostly the same as a normal SQL query, although because we are using PL/pgSQL as a language, we could use some additional features (such as declaring variables) if we needed to. This function basically takes a record (`NEW`) , and inserts the same data into the old schema after converting it to the correct format.

Finally, in order to actually run this function when a record is inserted to our new table, we create the trigger:

| `CREATE CONSTRAINT TRIGGER do_dreplicate_to_old_table` `-- When to run the trigger; this can be AFTER or BEFORE INSERT or UPDATE. In this case we want to run the trigger after an INSERT statement is ran` `AFTER INSERT ON timesheet_new` `-- By making the trigger deferrable and initially deferred, it will only run at the end of any transactions that the query may be ran as part of.` `DEFERRABLE INITIALLY DEFERRED` `-- for each row or for each statement; if we went with for each statement our function would need to be able to handle more than one insert at a time, as it's possible to insert multiple rows in a single statement. We don't bulk-create these records, so "for each row" is efficient enough.` `FOR EACH ROW` `-- Which function to call, this is the function that was defined earlier.` `EXECUTE PROCEDURE new_records_inserted();` |
| --- |

````

 

After running this query, we now have a trigger running every time a new record is inserted into the new table! Let’s try it out:

| `INSERT INTO timesheet_options (hospital_id) VALUES (15);` `INSERT INTO timesheet_new (worker_id, status, timesheet_options_id) VALUES (33, 'approved', 14);` |
| --- |

````

 

This should have created the following record in the old table:

| `SELECT * FROM timesheet_old_schema.timesheet_old;` |
| --- |

| id | worker\_id | hospital\_id | status | is\_approved |
| --- | --- | --- | --- | --- |
| 40 | 33 | 15 | approved | true |

As you can see, the data is converted into the format of the old table and inserted into that table as well. Clients can now continue reading from the old database until the relevant code has been rewritten.

Performing this migration and keeping the records in sync was a challenge I really enjoyed. I hope my write up of how I achieved this with the Postgres FDW and `AFTER INSERT/UPDATE` triggers will help someone else.

![Erry Kostala](https://blog.lantum.com/hubfs/People/erry-bw-circle-clear-01.png)

### Author

[Erry Kostala](https://blog.lantum.com/author/erry-kostala)

Erry is a Senior Software Engineer at Lantum. You can check out their Github here: https://github.com/errietta

### Subscribe to the newsletter

### Subscribe to the newsletter

### About Lantum

Lantum is a workforce platform that uses technology to simplify all aspects of healthcare staffing.

Our easy-to-use tools empower healthcare organisations to fill their shifts and professionals to fill their diaries, without the need for agencies. And they dramatically reduce time spent on rostering admin, compliance, and invoice chasing.

### Categories

- [NHS (44)](https://blog.lantum.com/tag/nhs)
- [Mindfulness and burnout (35)](https://blog.lantum.com/tag/mindfulness-and-burnout)
- [Opinion (35)](https://blog.lantum.com/tag/opinion)
- [Clinical (33)](https://blog.lantum.com/tag/clinical)
- [Primary Care (30)](https://blog.lantum.com/tag/primary-care)
- [Getting work (29)](https://blog.lantum.com/tag/getting-work)
- [Managing finances (27)](https://blog.lantum.com/tag/managing-finances)
- [Doctors in the spotlight (22)](https://blog.lantum.com/tag/doctors-in-the-spotlight)
- [Limited company vs sole trader (21)](https://blog.lantum.com/tag/limited-company-vs-sole-trader)
- [Advice Guide (20)](https://blog.lantum.com/tag/advice-guide)
- [covid-19, coronavirus (20)](https://blog.lantum.com/tag/covid-19-coronavirus)
- [GP locum (19)](https://blog.lantum.com/tag/gp-locum)
- [Case Study (17)](https://blog.lantum.com/tag/case-study)
- [work life balance (17)](https://blog.lantum.com/tag/work-life-balance)
- [Workforce Planning (16)](https://blog.lantum.com/tag/workforce-planning)
- [Five Year Forward View (15)](https://blog.lantum.com/tag/five-year-forward-view)
- [GP locum work (15)](https://blog.lantum.com/tag/gp-locum-work)
- [Primary Care Networks (14)](https://blog.lantum.com/tag/primary-care-networks)
- [portfolio GP (14)](https://blog.lantum.com/tag/portfolio-gp)
- [NHS pension (12)](https://blog.lantum.com/tag/nhs-pension)
- [Overseas coming and going (11)](https://blog.lantum.com/tag/overseas-coming-and-going)
- [Working as a GP locum (11)](https://blog.lantum.com/tag/working-as-a-gp-locum)
- [Appraisal (10)](https://blog.lantum.com/tag/appraisal)
- [Continuous Professional Development (10)](https://blog.lantum.com/tag/continuous-professional-development)
- [General Practice (10)](https://blog.lantum.com/tag/general-practice)
- [Out of Hours (10)](https://blog.lantum.com/tag/out-of-hours)
- [ics (10)](https://blog.lantum.com/tag/ics)
- [self-employment (10)](https://blog.lantum.com/tag/self-employment)
- [COVID-19 GP diary (9)](https://blog.lantum.com/tag/covid-19-gp-diary)
- [Education and CPD (9)](https://blog.lantum.com/tag/education-and-cpd)
- [GP parent (9)](https://blog.lantum.com/tag/gp-parent)
- [Staff banks (9)](https://blog.lantum.com/tag/staff-banks)
- [financial advice (9)](https://blog.lantum.com/tag/financial-advice)
- [Extended Access Hubs (8)](https://blog.lantum.com/tag/extended-access-hubs)
- [Overseas (8)](https://blog.lantum.com/tag/overseas)
- [Lantum news (7)](https://blog.lantum.com/tag/lantum-news)
- [Life at Lantum (7)](https://blog.lantum.com/tag/life-at-lantum)
- [CPD (6)](https://blog.lantum.com/tag/cpd)
- [GP recruitment (6)](https://blog.lantum.com/tag/gp-recruitment)
- [NHS long term plan (6)](https://blog.lantum.com/tag/nhs-long-term-plan)
- [Working at Scale (6)](https://blog.lantum.com/tag/working-at-scale)
- [diagnosis (6)](https://blog.lantum.com/tag/diagnosis)
- [GP Wellbeing (5)](https://blog.lantum.com/tag/gp-wellbeing)
- [GP practice managers (5)](https://blog.lantum.com/tag/gp-practice-managers)
- [Hubs (5)](https://blog.lantum.com/tag/hubs)
- [Other (5)](https://blog.lantum.com/tag/other)
- [Urgent Care (5)](https://blog.lantum.com/tag/urgent-care)
- [tax (5)](https://blog.lantum.com/tag/tax)
- [volunteering abroad (5)](https://blog.lantum.com/tag/volunteering-abroad)
- [CCG (4)](https://blog.lantum.com/tag/ccg)
- [Dev team (4)](https://blog.lantum.com/tag/dev-team)
- [Federation (4)](https://blog.lantum.com/tag/federation)
- [Flexible staff pools (4)](https://blog.lantum.com/tag/flexible-staff-pools)
- [GP indemnity and medicolegal (4)](https://blog.lantum.com/tag/gp-indemnity-and-medicolegal)
- [Winter Pressures (4)](https://blog.lantum.com/tag/winter-pressures)
- [gp profile (4)](https://blog.lantum.com/tag/gp-profile)
- [humanitarian work (4)](https://blog.lantum.com/tag/humanitarian-work)
- [indemnity (4)](https://blog.lantum.com/tag/indemnity)
- [21st Century GP (3)](https://blog.lantum.com/tag/21st-century-gp)
- [Communication (3)](https://blog.lantum.com/tag/communication)
- [Early career GP (3)](https://blog.lantum.com/tag/early-career-gp)
- [Enhanced Access (3)](https://blog.lantum.com/tag/enhanced-access)
- [GP Commissioning (3)](https://blog.lantum.com/tag/gp-commissioning)
- [Health systems innovation (3)](https://blog.lantum.com/tag/health-systems-innovation)
- [Hussain Gandhi (3)](https://blog.lantum.com/tag/hussain-gandhi)
- [Product updates (3)](https://blog.lantum.com/tag/product-updates)
- [vaccinations (3)](https://blog.lantum.com/tag/vaccinations)
- [BMA (2)](https://blog.lantum.com/tag/bma)
- [Finance (2)](https://blog.lantum.com/tag/finance)
- [GMC (2)](https://blog.lantum.com/tag/gmc)
- [GP Practice Hours (2)](https://blog.lantum.com/tag/gp-practice-hours)
- [GP partner (2)](https://blog.lantum.com/tag/gp-partner)
- [GP training (2)](https://blog.lantum.com/tag/gp-training)
- [Integrated Neighbourhood Teams (2)](https://blog.lantum.com/tag/integrated-neighbourhood-teams)
- [Matt Hancock (2)](https://blog.lantum.com/tag/matt-hancock)
- [NHS Funding (2)](https://blog.lantum.com/tag/nhs-funding)
- [Next generation GP (2)](https://blog.lantum.com/tag/next-generation-gp)
- [RCGP (2)](https://blog.lantum.com/tag/rcgp)
- [Simon Horton (2)](https://blog.lantum.com/tag/simon-horton)
- [australia (2)](https://blog.lantum.com/tag/australia)
- [in the spotlight (2)](https://blog.lantum.com/tag/in-the-spotlight-2)
- [mental health (2)](https://blog.lantum.com/tag/mental-health)
- [pension (2)](https://blog.lantum.com/tag/pension)
- [taylorcocks (2)](https://blog.lantum.com/tag/taylorcocks)
- [AHSN (1)](https://blog.lantum.com/tag/ahsn)
- [ARRS (1)](https://blog.lantum.com/tag/arrs)
- [Awards (1)](https://blog.lantum.com/tag/awards)
- [CNSGP (1)](https://blog.lantum.com/tag/cnsgp)
- [Clinical Negligence (1)](https://blog.lantum.com/tag/clinical-negligence)
- [Dr Nav Chana (1)](https://blog.lantum.com/tag/dr-nav-chana)
- [Dr Nikki Kanani (1)](https://blog.lantum.com/tag/dr-nikki-kanani)
- [Fuller approach (1)](https://blog.lantum.com/tag/fuller-approach)
- [Future Liabilities Scheme (1)](https://blog.lantum.com/tag/future-liabilities-scheme)
- [GP Contract (1)](https://blog.lantum.com/tag/gp-contract)
- [Integrated Care (1)](https://blog.lantum.com/tag/integrated-care)
- [NHS rota solutions (1)](https://blog.lantum.com/tag/nhs-rota-solutions)
- [NIA (1)](https://blog.lantum.com/tag/nia)
- [Neighbourhood Health (1)](https://blog.lantum.com/tag/neighbourhood-health)
- [Nurses (1)](https://blog.lantum.com/tag/nurses)
- [Oldenburg Burnout Inventory (1)](https://blog.lantum.com/tag/oldenburg-burnout-inventory)
- [Personal Data (1)](https://blog.lantum.com/tag/personal-data)
- [Privacy Policy (1)](https://blog.lantum.com/tag/privacy-policy)
- [Productivity (1)](https://blog.lantum.com/tag/productivity)
- [Summer Tips (1)](https://blog.lantum.com/tag/summer-tips)
- [Winter Access Fund (1)](https://blog.lantum.com/tag/winter-access-fund)
- [Women's Health Hubs (1)](https://blog.lantum.com/tag/womens-health-hubs)
- [accounting (1)](https://blog.lantum.com/tag/accounting)
- [digital staff banks (1)](https://blog.lantum.com/tag/digital-staff-banks)

see all

### Lantum on Twitter

[Tweets by WeAreLantum](https://twitter.com/WeAreLantum?ref_src=twsrc%5Etfw)

Any questions?

### Get in touch with the Lantum Team

0203 771 8411

[support@lantum.com](mailto:support@lantum.com)

![Get in touch](https://blog.lantum.com/hubfs/Lantum%20March2018/Images/footer-image.png)

[Lantum](https://lantum.com)

4th Floor,  
 15 Bonhill Street,  
 London, EC2A 4DN

0203 771 8411

- About us
- [Who we are](https://lantum.com/p/about/)
- [Join the team](https://lantum.com/p/careers/)
- [Blog](https://lantum.com/p/blog/)
- [Testimonials](https://lantum.com/p/testimonials/)
- [Community](https://lantum.com/p/community/)
- [Events](https://lantum.com/p/community/events/)
- [GP Trainee Hub](https://lantum.com/p/community/gp-trainee-hub/)
- [Press](https://lantum.com/p/press/)
- [Contact us](https://lantum.com/p/contact/)

- Locums
- [Locum FAQs](https://support.lantum.com/hc/en-us/categories/200833715-GP-Locums)
- [Locum sign up](https://lantum.com/account/locum/signup)
- [Locum Ts & Cs](https://lantum.com/p/locums/terms-and-conditions/)
- [Privacy Policy](https://lantum.com/p/locums/privacy-policy/)
- [Case studies](https://lantum.com/p/case-studies/)

- Practices
- [Practice FAQs](https://support.lantum.com/hc/en-us/categories/200809429-Practice-Managers)
- [Practice Sign up](https://lantum.com/register/practice)
- [Practice Ts & Cs](https://lantum.com/p/practices/terms-and-conditions/)
- [Privacy Policy](https://lantum.com/p/practices/privacy-policy/)
- [Case studies](https://lantum.com/p/case-studies/)

© 2018 Lantum LTD, formerly Network Locum. All Rights Reserved.

![](https://px.ads.linkedin.com/collect/?pid=3686322&fmt=gif)