# Migrate PostgreSQL to Azure Database for PostgreSQL online using DMS
## Introduction
[**Azure DMS (Database Migration Service)**](https://docs.microsoft.com/en-us/azure/dms/dms-overview) is a fully managed service designed to enable seamless migrations from multiple database sources to Azure data platforms with minimal downtime.
## Scenario
In this lab, you will use Azure DMS (Database Migration Service) to migrate the databases from an on-premises PostgreSQL instance to [**Azure Database for PostgreSQL**](https://docs.microsoft.com/en-us/azure/postgresql/quickstart-create-server-database-portal) with minimal downtime. In other words, migration can be achieved with minimum downtime to the application.
## Prerequisites
* An Azure account with Azure Subscription
## Step by step
### Step 1: Create and Connect to VM
1. Create a virtual machine
* Sign in to the [**Azure portal**](https://portal.azure.com/) if you haven't already.
* Type **virtual machines** in the search.
* Under **Services**, select **Virtual machines**.
* In the **Virtual machines** page, select **Add**. The **Create a virtual machine** page opens.
* In the **Basics** tab, under **Project details**, make sure the correct subscription is selected and then choose to **Create new** resource group. Type *myResourceGroup* for the name.
* Under **Instance details**, type *myVM* for the **Virtual machine name**, choose *(US) West US 2* for your **Region**, and choose Ubuntu 18.04 LTS for your **Image**. Leave the other defaults.
* Under **Administrator account**, select **Password**.
* Under **Inbound port rules** > **Public inbound ports**, choose **Allow selected ports** and then select **SSH (22)** and **HTTP (80)** and **SSH (443)** from the drop-down.
* Leave the remaining defaults and then select the **Review + create** button at the bottom of the page.
* On the **Create a virtual machine page**, you can see the details about the VM you are about to create. When you are ready, select **Create**.
* When the deployment is finished, select **Go to resource**.
* On the page for your new VM, select the public IP address and copy it to your clipboard.
2. Connect to virtual machine
* Create an SSH connection with the VM.
### Step 2: Install PostgreSql in VM
1. Install PostgreSql
```
sudo apt-get install postgresql
```
### Step 3: Register the Microsoft.DataMigration resource provider
1. Sign in to the Azure portal, select All services, and then select Subscriptions.
2. Select the subscription in which you want to create the instance of Azure Database Migration Service, and then select **Resource providers**.
3. Search for migration, and then to the right of **Microsoft.DataMigration**, select **Register**.
### Step 4: Create a Database Migration Service instance
1. In the Azure portal, select + **Create a resource**, search for Azure Database Migration Service, and then select **Azure Database Migration Service** from the drop-down list.
2. On the **Azure Database Migration Service** screen, select **Create**.
3. On the **Create Migration Service** screen, specify a name for the service, the subscription, and a new or existing resource group.
4. Select a pricing tier and move to the networking screen. Offline migration capability is available in both the Standard and Premium pricing tier.
5. Select an existing virtual network from the list or provide the name of the new virtual network to be created. Move to the **review + create** screen. Optionally you can add tags to the service using the **tags** screen.
The virtual network provides Azure Database Migration Service with access to the source PostgreSQL Server and the target Azure PostgreSQL Database instance.
6. Review the configurations and select **Create** to create the service.
### Step 5: Create a migration project
>After the service is created, locate it within the Azure portal, open it, and then create a new migration project.
1. In the Azure portal, select **All services**, search for Azure Database Migration Service, and then select **Azure Database Migration Services**.
2. Select your migration service instance from the search results and select + **New Migration Project**.
3. On the **New migration project** screen, specify a name for the project, in the **Source server type** selection box, select **MySQL**, in the **Target server type** selection box, select **Azure Database For MySQL** and in the **Migration activity type** selection box, select **Online data migration**. Select **Create and run activity**.
### Step 6: Create **Azure Database for PostgreSQL servers**
1. In the Azure portal, select **All services**, search *Azure Database for PostgreSQL servers*.
2. select + **New**.
3. In the **Basics** tab, under **Project details**, make sure the correct subscription is selected and then choose the resource group.
4. Leave the remaining defaults and then select the **Review + create** button at the bottom of the page.
5. On the **Create a PostgreSQLServer page**, you can see the details about the PostgreSQLServer you are about to create. When you are ready, select **Create**.
### Step 7: Login VM and finish configuration
1. PostgreSQL configuration
* Modify the login password of PostgreSQL
>After installation, a database named *Postgres* will be created by default, and a login account named *Postgres* will be created by default, with a random password. We don't know what the password is, so it needs to be changed manually.
* Log in to Postgres through the following command
```
sudo -u Postgres psql
```
* Use the **ALTER** command to change the password of the default account Postgres to [Your password]
```
ALTER USER postgres WITH PASSWORD '[Your password]';
```
* Configure remote access permissions
>After Postgre is installed, it only supports local access by default. If you want to connect to it through other hosts, you need to modify some configuration files.
2. Modify postgresql. conf
```
sudo vim /etc/postgresql/10/main/postgresql.conf
```
* Add at the end of the file
```
listen_addresses = '*'
```
3. Modify pg_hba.conf
```
sudo vim /etc/postgresql/10/main/pg_hba.conf
```
* Add at the end of the file
```
host all all 0.0.0.0/0 md5
```
4. Turn on port 5432 on NSG
:::info
:::
5. Restart PostgreSQL
```
systemctl restart postgresql
```
### Step 8: Create database and table after login to PostgreSQL
1. Go to Postgres Database
```
\c postgres
```
2. Create database and table
* Create Company Database
```
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL,
JOIN_DATE DATE
);
```
3. Insert a data into PostgreSQL
* Insert a row into the COMPANY table in the following example
```
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00 ,'2001-07-13');
```
* Use flowing instruction can check the table
```
SELECT * FROM COMPANY;
```
### Step 9: Migrate the sample schema
* Export database structure
```
pg_dump -o -h hostname -U db_username -d db_name -s > your_schema.sql
```
* Import database structure
```
psql -h hostname -U db_username -d db_name < your_schema.sql
```
### Step 10: Configure migration project
1. On the **Select source** screen, specify the connection details for the source MySQL instance, and select **Next: Select target>>**
2. On the **Select target** screen, specify the connection details for the target Azure Database for MySQL instance, and select **Next: Select databases>>**
3. On the **Select databases** screen, map the source and the target database for migration, and select **Next: Select tables>>**.
4. On the **Select tables** screen, make sure the table has been selected, and select **Next: Configure migration settings>>**.
5. On the **Configure migration settings** screen, accept the default values on the "Migration Settings" screen., and select **Next: Summary>>**.
6. On the Summary screen, in the Activity name text box, specify a name for the migration activity and review the summary to ensure that the source and target details match what you previously specified.
### Step 11: Monitor the migration
1. On the migration activity screen, select Refresh to update the display until the Status of the migration shows as Complete.
2. Under **Database Name**, select the specific database to get to the migration status for **Full data load** and **Incremental data sync operations**.
Full data load will show the initial load migration status while Incremental data sync will show change data capture (CDC) status.
### Step 12: Perform migration cutover
* After the initial Full load is completed, the databases are marked **Ready to cutover**.
1. When you're ready to complete the database migration, select **Start Cutover**.
2. Make sure to stop all the incoming transactions to the source database; wait until the **Pending changes** counter shows **0**.
3. Select **Confirm**, and select **Apply**.
## Cleanup resources
* You can clean up the resources created in this quickstart by deleting the Azure resource group. To delete the resource group, navigate to the instance of the Azure Database Migration Service that you created. Select the Resource group name, and then select Delete resource group. This action deletes all assets in the resource group as well as the group itself.
## Conclusion
* Azure Database Migration Service integrates some of the functionality of our existing tools and services. It provides customers with a comprehensive, highly available solution.
### Reference
* [**Tutorial: Migrate PostgreSQL to Azure DB for PostgreSQL online using DMS via the Azure portal**](https://docs.microsoft.com/en-us/azure/dms/tutorial-postgresql-azure-postgresql-online-portal)
Sign in with Wallet
Connect another wallet