Migrating from SQL Server to PostgreSQL
- Stephen Fuqua
Overview
The Ed-Fi Alliance does not plan to publish migration support from a SQL Server to a PostgreSQL database. If anyone needs to perform such a migration, please note that it should only be done from an ODS/API 3.3 database (Data Standard 3.2). Trying to migrate from an older SQL Server to PostgreSQL would be error prone because of the data standard change.
Most table and column names are identical, but the mapping exercise using an ETL tool will require some manual work because of the long name problem: some SQL Server tables in the ODS have more than the allowed 63 characters in PostgreSQL. The following sections describe the differences in schema for each of the three databases.
EdFi_Admin
Tables
There are no differences in table names.
Columns
There is one difference in column names:
| Table | SQL Server | PostgreSQL |
|---|---|---|
| ApiClientApplicationEducationOrganizations | ApplicationEducationOrganization_ApplicationEducationOrganizationId | applicationedorg_applicationedorgid |
EdFi_ODS
Tables
| SQL Server | PostgreSQL |
|---|---|
| StaffDisciplineIncidentAssociationDisciplineIncidentParticipationCode | staffdisciplineincidentassociationdisciplineincidentpart_7fa4be |
| StudentEducationOrganizationAssociationProgramParticipationProgramCharacteristic | studenteducationorganizationassociationprogramparticipat_810575 |
| StudentEducationOrganizationAssociationStudentCharacteristicPeriod | studenteducationorganizationassociationstudentcharacteri_a18fcf |
| StudentEducationOrganizationAssociationStudentIdentificationCode | studenteducationorganizationassociationstudentidentifica_c15030 |
| StudentLanguageInstructionProgramAssociationEnglishLanguageProficiencyAssessment | studentlanguageinstructionprogramassociationenglishlangu_1ac620 |
| StudentLanguageInstructionProgramAssociationLanguageInstructionProgramService | studentlanguageinstructionprogramassociationlanguageinst_268e07 |
| StudentMigrantEducationProgramAssociationMigrantEducationProgramService | studentmigranteducationprogramassociationmigranteducatio_d9dcd7 |
| StudentNeglectedOrDelinquentProgramAssociationNeglectedOrDelinquentProgramService | studentneglectedordelinquentprogramassociationneglectedo_520251 |
| StudentSchoolFoodServiceProgramAssociationSchoolFoodServiceProgramService | studentschoolfoodserviceprogramassociationschoolfoodserv_85a0eb |
| StudentSpecialEducationProgramAssociationSpecialEducationProgramService | studentspecialeducationprogramassociationspecialeducatio_a51ff9 |
| StudentSpecialEducationProgramAssociationSpecialEducationProgramServiceProvider | studentspecialeducationprogramassociationspecialeducatio_bcba5c |
Columns
There are no column name differences.
EdFi_Security
No differences.