Migrate your database from PostgreSQL Single Server to PostgreSQL Flexible Server using Azure DMS

Published Jan 26 2021 09:20 AM 3,131 Views
Microsoft

Overview:

Azure Database for PostgreSQL recently announced Flexible Server,  Flexible Server is a new deployment option for Azure Database for PostgreSQL that gives you the control you need with multiple configuration parameters for fine-grained database tuning along with a simpler developer experience to accelerate end-to-end deployment. With Flexible Server, you will also have a new way to optimize cost with stop/start capabilities. 

 

In this article, we will provide a guideline on how to leverage Azure DMS for seamless and simplified migrations of your database from Azure PostgreSQL Single Server to Azure PostgreSQL Flexible Server with minimal downtime.

 

Important - Azure Database for PostgreSQL - Flexible Server is still in preview as of Jan 2021.

 

1 – Prepare the Source – Azure PostgreSQL - Single Server

 

You will need to set some Postgresql Parameters on the source as follows:

 

-- parameter wal_level should be logical, on Azure PgSQL this can be done from Azure Portal as shown blow:

Ahmed_S_Mahmoud_0-1611574758260.png

 

-- max_replication_slots = [number of slots], recommend setting to 5 slots
-- max_wal_senders =[number of concurrent tasks] - The max_wal_senders parameter sets the number of concurrent tasks that can run, recommend setting to 10 tasks

-- the user must have 'userepl' privilege, if replication not granted, you can grant it as follows:

select * from pg_roles;

ALTER USER <user-name> WITH REPLICATION;

 

2- Prepare the target for the migration - Flexible Server

 

You will need to migrate database schema from source to target using commands:

 

-- on the source, create a schema dump file for a database

 

 

 

 

 

pg_dump -o -h hostname -U db_username -d db_name -s > your_schema.sql

 

 

 

 

 

 

-- Import the schema into the target database

 

 

 

 

 

psql -h hostname -U db_username -d db_name < your_schema.sql

 

 

 

 

 

 

-- Remove foreign keys in schema at target Azure Database for PostgreSQL

 

 

 

 

SELECT Q.table_name
    ,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(DISTINCT CONCAT(' DROP CONSTRAINT ', foreignkey), ','), ';') as DropQuery
        ,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(DISTINCT CONCAT(' ADD CONSTRAINT ', foreignkey, ' FOREIGN KEY (', column_name, ')', ' REFERENCES ', foreign_table_schema, '.', foreign_table_name, '(', foreign_column_name, ')' ), ','), ';') as AddQuery
FROM
    (SELECT
    S.table_schema,
    S.foreignkey,
    S.table_name,
    STRING_AGG(DISTINCT S.column_name, ',') AS column_name,
    S.foreign_table_schema,
    S.foreign_table_name,
    STRING_AGG(DISTINCT S.foreign_column_name, ',') AS foreign_column_name
FROM
    (SELECT DISTINCT
    tc.table_schema,
    tc.constraint_name AS foreignkey,
    tc.table_name,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
    FROM information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE constraint_type = 'FOREIGN KEY'
    ) S
    GROUP BY S.table_schema, S.foreignkey, S.table_name, S.foreign_table_schema, S.foreign_table_name
    ) Q
    GROUP BY Q.table_schema, Q.table_name;

 

-- Disable triggers at target Azure Database for PostgreSQL

 

The migration service internally handles the enable/disable of foreign keys and triggers to ensure a reliable and robust data migration. As a result, you do not have to worry about making any modifications to the target database schema.

 

So you don't need to manually disable it.

 

SELECT DISTINCT CONCAT('ALTER TABLE ', event_object_schema, '.', event_object_table, ' DISABLE TRIGGER ', trigger_name, ';')
FROM information_schema.triggers

 

-- Provision Database Migration Service and create a migration task

More details in Tutorial: Migrate PostgreSQL to Azure Database for PostgreSQL online via the Azure CLI - Azure Datab...

 

3 – Create Migration Project

You will need to use PostgreSQL as source server type and provide the single server endpoint and credentials.

 

Ahmed_S_Mahmoud_1-1611574758268.png

 

Note:- You will need to make sure the connectivity between the source single server and DMS service on one side and the DMS service and the target flexible server on the other side.

 

Details steps: Tutorial: Migrate PostgreSQL to Azure DB for PostgreSQL online via the Azure portal - Azure Database...

 

4- Monitoring the migration project progress and you can perform the migration once it’s ready for cutover

Ahmed_S_Mahmoud_2-1611574758296.png

Ahmed_S_Mahmoud_3-1611574758312.png

 

Note:- After migration is complete, you might need to re-enable the triggers and create foreign keys. 

 

I hope you find this article helpful. If you have any feedback please do not hesitate to provide it in the comment section below.

 

Ahmed S. Mazrouh

4 Comments
Senior Member

When will version 12 be available in PostgreSQL Single Server and any plan to implement storage auto growth in flexible server ?

Microsoft

Thanks @karthi natarajan for your interest in Azure Postgresql,  Postgresql version 12 is only available on Azure Flexible server Supported versions - Azure Database for PostgreSQL - Flexible Server | Microsoft Docs,  For other question, please contact the Ask Azure DB for PostgreSQL directly, Thanks!

Occasional Visitor

I am running Data Migration service from Azure Postgres Single node server to Azure Postgres Flexible Server. With below features :

 

max_replication_slots =5

max_wal_senders =10

size = 2TB

 

migration process has been initiated and passed with ‘backup’.

But from last 5+ days the status has been stuck at ‘Restore in progress’. Is this expected for 2TB of data or can you please suggest how do I check the migration status from backend?

Microsoft

@LokeshBabu Thanks for using Azure DMS for your migration, please contact Azure DMS Feedback <dmsfeedback@microsoft.com> alias directly for a response to your question. Thanks!

%3CLINGO-SUB%20id%3D%22lingo-sub-2109220%22%20slang%3D%22en-US%22%3ERe%3A%20Migrate%20your%20database%20from%20PostgreSQL%20Single%20Server%20to%20PostgreSQL%20Flexible%20Server%20using%20Azure%20DM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2109220%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20will%20version%2012%20be%20available%20in%3CSPAN%3E%26nbsp%3BPostgreSQL%20Single%20Server%20and%20any%20plan%20to%20implement%20storage%20auto%20growth%20in%20flexible%20server%20%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2111283%22%20slang%3D%22en-US%22%3ERe%3A%20Migrate%20your%20database%20from%20PostgreSQL%20Single%20Server%20to%20PostgreSQL%20Flexible%20Server%20using%20Azure%20DM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2111283%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EThanks%20%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F143130%22%20target%3D%22_blank%22%3E%40karthi%20natarajan%3C%2FA%3E%26nbsp%3Bfor%20your%20interest%20in%20Azure%20Postgresql%3CSPAN%3E%2C%26nbsp%3B%20Postgresql%20version%2012%20is%20only%20available%20on%20Azure%20Flexible%20server%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fpostgresql%2Fflexible-server%2Fconcepts-supported-versions%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESupported%20versions%20-%20Azure%20Database%20for%20PostgreSQL%20-%20Flexible%20Server%20%7C%20Microsoft%20Docs%2C%26nbsp%3B%3C%2FA%3E%26nbsp%3BFor%20other%20question%2C%20please%20contact%20the%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22mailto%3AAskAzureDBforPostgreSQL%40service.microsoft.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EAsk%20Azure%20DB%20for%20PostgreSQL%3C%2FA%3E%3CSPAN%3E%26nbsp%3Bdirectly%2C%20Thanks!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2339631%22%20slang%3D%22en-US%22%3ERe%3A%20Migrate%20your%20database%20from%20PostgreSQL%20Single%20Server%20to%20PostgreSQL%20Flexible%20Server%20using%20Azure%20DM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2339631%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20running%20Data%20Migration%20service%20from%20Azure%20Postgres%20Single%20node%20server%20to%20Azure%20Postgres%20Flexible%20Server.%20With%20below%20features%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emax_replication_slots%20%3D5%3C%2FP%3E%3CP%3Emax_wal_senders%20%3D10%3C%2FP%3E%3CP%3Esize%20%3D%202TB%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emigration%20process%20has%20been%20initiated%20and%20passed%20with%20%E2%80%98backup%E2%80%99.%3C%2FP%3E%3CP%3EBut%20from%20last%205%2B%20days%20the%20status%20has%20been%20stuck%20at%20%E2%80%98Restore%20in%20progress%E2%80%99.%20Is%20this%20expected%20for%202TB%20of%20data%20or%20can%20you%20please%20suggest%20how%20do%20I%20check%20the%20migration%20status%20from%20backend%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2360402%22%20slang%3D%22en-US%22%3ERe%3A%20Migrate%20your%20database%20from%20PostgreSQL%20Single%20Server%20to%20PostgreSQL%20Flexible%20Server%20using%20Azure%20DM%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1049373%22%20target%3D%22_blank%22%3E%40LokeshBabu%3C%2FA%3E%26nbsp%3BThanks%20for%20using%20Azure%20DMS%20for%20your%20migration%2C%20please%20contact%26nbsp%3BAzure%20DMS%20Feedback%20%3CDMSFEEDBACK%3E%26nbsp%3B%3CSPAN%3Ealias%20directly%20for%20a%20response%20to%20your%20question.%20Thanks!%3C%2FSPAN%3E%3C%2FDMSFEEDBACK%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2093660%22%20slang%3D%22en-US%22%3EMigrate%20your%20database%20from%20PostgreSQL%20Single%20Server%20to%20PostgreSQL%20Flexible%20Server%20using%20Azure%20DMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2093660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EOverview%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EAzure%20Database%20for%20PostgreSQL%20recently%20%3CA%20title%3D%22introducing%20flexible-server%20for%20azure%20database%20for%20PostgreSQL%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-for-postgresql%2Fintroducing-flexible-server-for-azure-database-for-postgresql%2Fba-p%2F1686616%22%20target%3D%22_blank%22%3Eannounced%26nbsp%3B%3C%2FA%3E%3CSPAN%3E%3CA%20title%3D%22Introducing%20flexible-server%20for%20azure%20database%20for%20PostgreSQL.%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-for-postgresql%2Fintroducing-flexible-server-for-azure-database-for-postgresql%2Fba-p%2F1686616%22%20target%3D%22_blank%22%3EFlexible%20Server%2C%3C%2FA%3E%26nbsp%3B%20Flexible%20Server%20is%20a%20new%20deployment%20option%20for%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fservices%2Fpostgresql%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Database%20for%20PostgreSQL%3C%2FA%3E%3CSPAN%3E%26nbsp%3Bthat%20gives%20you%20the%20control%20you%20need%20with%20multiple%20configuration%20parameters%20for%20fine-grained%20database%20tuning%20along%20with%20a%20simpler%20developer%20experience%20to%20accelerate%20end-to-end%20deployment.%20With%20Flexible%20Server%2C%20you%20will%20also%20have%20a%20new%20way%20to%20optimize%20cost%20with%20stop%2Fstart%20capabilities.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIn%20this%20article%2C%20we%20will%20provide%20a%20guideline%26nbsp%3Bon%20how%20to%20leverage%20Azure%20DMS%20for%20seamless%20and%20simplified%20migrations%20of%20your%20database%20from%20Azure%20PostgreSQL%20Single%20Server%20to%20Azure%20PostgreSQL%20Flexible%20Server%20%3C%2FSPAN%3Ewith%20minimal%20downtime.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22alert-title%22%3E%3CSTRONG%3EImportant%3C%2FSTRONG%3E%20-%26nbsp%3BAzure%20Database%20for%20PostgreSQL%20-%20Flexible%20Server%20is%20still%20in%20preview%20as%20of%20Jan%202021.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E1%20%E2%80%93%20Prepare%20the%20Source%20%E2%80%93%20Azure%20PostgreSQL%20-%20Single%20Server%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20will%20need%20to%20set%20some%20Postgresql%20Parameters%20on%20the%20source%20as%20follows%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--%20parameter%20wal_level%20should%20be%20logical%2C%20on%20Azure%20PgSQL%20this%20can%20be%20done%20from%20Azure%20Portal%20as%20shown%20blow%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ahmed_S_Mahmoud_0-1611574758260.png%22%20style%3D%22width%3A%20703px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249582iF081644C2C1E81BF%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Ahmed_S_Mahmoud_0-1611574758260.png%22%20alt%3D%22Ahmed_S_Mahmoud_0-1611574758260.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--%20max_replication_slots%20%3D%20%5Bnumber%20of%20slots%5D%2C%20recommend%20setting%20to%205%20slots%3CBR%20%2F%3E--%20max_wal_senders%20%3D%5Bnumber%20of%20concurrent%20tasks%5D%20-%20The%20max_wal_senders%20parameter%20sets%20the%20number%20of%20concurrent%20tasks%20that%20can%20run%2C%20recommend%20setting%20to%2010%20tasks%3C%2FP%3E%0A%3CP%3E--%20the%20user%20must%20have%20'userepl'%20privilege%2C%26nbsp%3Bif%20replication%20not%20granted%2C%20you%20can%20grant%20it%20as%20follows%3A%3C%2FP%3E%0A%3CP%3Eselect%20*%20from%20pg_roles%3B%3C%2FP%3E%0A%3CP%3EALTER%20USER%20%3CUSER-NAME%3E%20WITH%20REPLICATION%3B%3C%2FUSER-NAME%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E2-%20%3C%2FSTRONG%3E%3CSTRONG%3EPrepare%20the%20target%20for%20the%20migration%20-%20Flexible%20Server%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EYou%20will%20need%20to%20migrate%20database%20schema%20from%20source%20to%20target%20using%20commands%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--%20on%20the%20source%2C%26nbsp%3B%3CSPAN%3Ecreate%20a%20schema%20dump%20file%20for%20a%20database%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3Epg_dump%20-o%20-h%20hostname%20-U%20db_username%20-d%20db_name%20-s%20%26gt%3B%20your_schema.sql%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--%20Import%26nbsp%3Bthe%20schema%20into%20the%20target%20database%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3Epsql%20-h%20hostname%20-U%20db_username%20-d%20db_name%20%26lt%3B%20your_schema.sql%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--%20Remove%20foreign%20keys%20in%20schema%20at%20target%20Azure%20Database%20for%20PostgreSQL%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ESELECT%20Q.table_name%0A%20%20%20%20%2CCONCAT('ALTER%20TABLE%20'%2C%20table_schema%2C%20'.'%2C%20table_name%2C%20STRING_AGG(DISTINCT%20CONCAT('%20DROP%20CONSTRAINT%20'%2C%20foreignkey)%2C%20'%2C')%2C%20'%3B')%20as%20DropQuery%0A%20%20%20%20%20%20%20%20%2CCONCAT('ALTER%20TABLE%20'%2C%20table_schema%2C%20'.'%2C%20table_name%2C%20STRING_AGG(DISTINCT%20CONCAT('%20ADD%20CONSTRAINT%20'%2C%20foreignkey%2C%20'%20FOREIGN%20KEY%20('%2C%20column_name%2C%20')'%2C%20'%20REFERENCES%20'%2C%20foreign_table_schema%2C%20'.'%2C%20foreign_table_name%2C%20'('%2C%20foreign_column_name%2C%20')'%20)%2C%20'%2C')%2C%20'%3B')%20as%20AddQuery%0AFROM%0A%20%20%20%20(SELECT%0A%20%20%20%20S.table_schema%2C%0A%20%20%20%20S.foreignkey%2C%0A%20%20%20%20S.table_name%2C%0A%20%20%20%20STRING_AGG(DISTINCT%20S.column_name%2C%20'%2C')%20AS%20column_name%2C%0A%20%20%20%20S.foreign_table_schema%2C%0A%20%20%20%20S.foreign_table_name%2C%0A%20%20%20%20STRING_AGG(DISTINCT%20S.foreign_column_name%2C%20'%2C')%20AS%20foreign_column_name%0AFROM%0A%20%20%20%20(SELECT%20DISTINCT%0A%20%20%20%20tc.table_schema%2C%0A%20%20%20%20tc.constraint_name%20AS%20foreignkey%2C%0A%20%20%20%20tc.table_name%2C%0A%20%20%20%20kcu.column_name%2C%0A%20%20%20%20ccu.table_schema%20AS%20foreign_table_schema%2C%0A%20%20%20%20ccu.table_name%20AS%20foreign_table_name%2C%0A%20%20%20%20ccu.column_name%20AS%20foreign_column_name%0A%20%20%20%20FROM%20information_schema.table_constraints%20AS%20tc%0A%20%20%20%20JOIN%20information_schema.key_column_usage%20AS%20kcu%20ON%20tc.constraint_name%20%3D%20kcu.constraint_name%20AND%20tc.table_schema%20%3D%20kcu.table_schema%0A%20%20%20%20JOIN%20information_schema.constraint_column_usage%20AS%20ccu%20ON%20ccu.constraint_name%20%3D%20tc.constraint_name%20AND%20ccu.table_schema%20%3D%20tc.table_schema%0AWHERE%20constraint_type%20%3D%20'FOREIGN%20KEY'%0A%20%20%20%20)%20S%0A%20%20%20%20GROUP%20BY%20S.table_schema%2C%20S.foreignkey%2C%20S.table_name%2C%20S.foreign_table_schema%2C%20S.foreign_table_name%0A%20%20%20%20)%20Q%0A%20%20%20%20GROUP%20BY%20Q.table_schema%2C%20Q.table_name%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--%20Disable%20triggers%20at%20target%20Azure%20Database%20for%20PostgreSQL%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThe%20migration%20service%20internally%20handles%20the%20enable%2Fdisable%20of%20foreign%20keys%20and%20triggers%20to%20ensure%20a%20reliable%20and%20robust%20data%20migration.%20As%20a%20result%2C%20you%20do%20not%20have%20to%20worry%20about%20making%20any%20modifications%20to%20the%20target%20database%20schema.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20you%20don't%20need%20to%20manually%20disable%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESELECT%20DISTINCT%20CONCAT('ALTER%20TABLE%20'%2C%20event_object_schema%2C%20'.'%2C%20event_object_table%2C%20'%20DISABLE%20TRIGGER%20'%2C%20trigger_name%2C%20'%3B')%0AFROM%20information_schema.triggers%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--%20Provision%20Database%20Migration%20Service%20and%20create%20a%20migration%20task%3C%2FP%3E%0A%3CP%3EMore%20details%20in%20%3CA%20href%3D%22https%3A%2F%2Fnam06.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fdocs.microsoft.com%252Fen-us%252Fazure%252Fdms%252Ftutorial-postgresql-azure-postgresql-online%253FWT.mc_id%253DPortal-Microsoft_Azure_DMS%2523migrate-the-sample-schema%26amp%3Bdata%3D04%257C01%257CAhmed.Mahmoud%2540microsoft.com%257C20a989dcac4b486b1d0808d8bec0b6ee%257C72f988bf86f141af91ab2d7cd011db47%257C1%257C0%257C637469082234583067%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26amp%3Bsdata%3DYTJgoHut%252FYzlUiwq47SJfD8bXqYU7K6K0csyNgAt8p0%253D%26amp%3Breserved%3D0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ETutorial%3A%20Migrate%20PostgreSQL%20to%20Azure%20Database%20for%20PostgreSQL%20online%20via%20the%20Azure%20CLI%20-%20Azure%20Database%20Migration%20Service%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E3%20%E2%80%93%20Create%20Migration%20Project%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EYou%20will%20need%20to%20use%20PostgreSQL%20as%20source%20server%20type%20and%20provide%20the%20single%20server%20endpoint%20and%20credentials.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ahmed_S_Mahmoud_1-1611574758268.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249097i31AAEEC7A10E9789%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Ahmed_S_Mahmoud_1-1611574758268.png%22%20alt%3D%22Ahmed_S_Mahmoud_1-1611574758268.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ENote%3A-%20%3C%2FSTRONG%3EYou%20will%20need%20to%20make%20sure%20the%20connectivity%20between%20the%20source%20single%20server%20and%20DMS%20service%20on%20one%20side%20and%20the%20DMS%20service%20and%20the%20target%20flexible%20server%20on%20the%20other%20side.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDetails%20steps%3A%20%3CA%20href%3D%22https%3A%2F%2Fnam06.safelinks.protection.outlook.com%2F%3Furl%3Dhttps%253A%252F%252Fdocs.microsoft.com%252Fen-us%252Fazure%252Fdms%252Ftutorial-postgresql-azure-postgresql-online-portal%2523create-a-migration-project%26amp%3Bdata%3D04%257C01%257CAhmed.Mahmoud%2540microsoft.com%257C20a989dcac4b486b1d0808d8bec0b6ee%257C72f988bf86f141af91ab2d7cd011db47%257C1%257C0%257C637469082234593061%257CUnknown%257CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%253D%257C1000%26amp%3Bsdata%3DZIotfjYKSr3%252FRt5MIS8MKEeWgbTIW3%252Fcw3tmVTJ4xRs%253D%26amp%3Breserved%3D0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3ETutorial%3A%20Migrate%20PostgreSQL%20to%20Azure%20DB%20for%20PostgreSQL%20online%20via%20the%20Azure%20portal%20-%20Azure%20Database%20Migration%20Service%20%7C%20Microsoft%20Docs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E4-%20Monitoring%20the%20migration%20project%20progress%20and%20you%20can%20perform%20the%20migration%20once%20it%E2%80%99s%20ready%20for%20cutover%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ahmed_S_Mahmoud_2-1611574758296.png%22%20style%3D%22width%3A%20800px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249584iDD93ED50A67B6BF3%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Ahmed_S_Mahmoud_2-1611574758296.png%22%20alt%3D%22Ahmed_S_Mahmoud_2-1611574758296.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ahmed_S_Mahmoud_3-1611574758312.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249586i92CACB27ED29CD32%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Ahmed_S_Mahmoud_3-1611574758312.png%22%20alt%3D%22Ahmed_S_Mahmoud_3-1611574758312.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ENote%3A-%20%3C%2FSTRONG%3EAfter%20migration%20is%20complete%2C%20you%20might%20need%20to%26nbsp%3Bre-enable%20the%20triggers%20and%20create%20foreign%20keys.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20you%20find%20this%20article%20helpful.%20If%20you%20have%20any%20feedback%20please%20do%20not%20hesitate%20to%20provide%20it%20in%20the%20comment%20section%20below.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAhmed%20S.%20Mazrouh%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2093660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EIn%20this%20article%2C%20we%20will%20provide%20a%20guideline%26nbsp%3Bon%20how%20to%20migrate%20your%20database%20from%20PostgreSQL%20Single%20Server%20to%20PostgreSQL%20Flexible%20Server%20using%20Azure%20DMS.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Co-Authors
Version history
Last update:
‎Jul 28 2021 05:17 AM
Updated by: