CODEWITHSUNDEEP
sqlpostgresqlforeign-keysmigration
alukach
alukach

Reputation: 6288

Postgres: Migrate data with FKs to multi-database

I currently have a single DB with data from a number of clients. I have been tasked with writing a script that splits the data up into separate DBs per client (each DB will have the same schema).

For the purpose of example, let's say the DB is filled with Artists, Songs, Videos, Reviews, and Locations. I've created separate DBs for each Client and I have a table that states which Artist belongs to which Client.

ArtistClient
  - FK to Artist
  - ClientDbName

Location
  - ...

Artist
  - FK to Location
  - ...

Song
  - FK to Artist
  - ...

Video
  - FK to Artist
  - ...

Review
  - FK to Song
  - ...

In reality, there are many more relations than this, but this illustrates the issue.

The problem I'm having is following all of the FK relations amongst the tables. Is there any elegant way to iterate through all rows in Artist, get the ClientDbName from the ArtistClient table, and then insert all data related to the Artist (ascertained from analyzing FKs) into the proper DB?

Upvotes: 0

Views: 44

Answers (1)

Marsh
Marsh

Reputation: 8145

Start with all the tables which have no foreign keys. Copy over all their values for one client.

Next find all the tables with a foreign key to your starting tables. Copy over those values.

Repeat this process until you have all tables copied over.

There is no built-in way in postgresql to do this, but you could write a script that parses a text representation of the database schema and determines the best order of tables, possibly even generating the SQL statements in the process. Whether it's worth writing a script depends on how much work it would be to just do this process manually.

Alternately, you could disable foreign key constraint checks, copy over all the data, and then re-enable the checks.

Upvotes: 1

Related Questions