MattMencel/azure_postgres_restore.md

## azure_postgres_restore.md

      
    Raw
  

              azure_postgres_restore.md
            
          
    Azure Postgres Database Restore

Steps

Remove the AAD Group from the original DB instance

There is a problem (bug?) where Azure won't let you do a point-in-time restore of a postgres server if an AAD account or group is assigned as an admin.
Do this from the portal.

Go to the postgres server in the portal
Select the Active Directory admin option under Settings
Record the name of the group
Remove admin and Save

Using AzureCLI

To avoid extra data transit charges in Azure, it is highly recommended you perform the dump/restore commands from a VM in Azure in the same region as the databases.
You may also use Azure Cloud Shell in the portal. However, you'll want to ensure your storage account backend is in the same region as the postgres servers you will be working with. See the Cloud Shell storage documentation for more details.
If using an Azure VM you may need to do an az login. If using Cloud Shell you can skip the login step.
Once logged in or connected to Cloud Shell, ensure you have the correct subscription set.
az account set --subscription SUBSCRIPTION_NAME
Perform a Point-in-Time Restore

Using AzureCLI:
az postgres server restore --resource-group RESOURCE_GROUP --source-server ORIGINAL_SERVER_NAME --name RESTORED_SERVER_NAME  --restore-point-in-time "2020-02-12T01:00:00Z"
Once the restore is complete, it may take a few minutes for the restored server to appear in the portal.  You can verify it exists with AzureCLI.
az postgres server list --resource-group RESOURCE_GROUP
Dump and Restore the Tables

Example assumes DB name is foo_db and DB username is foo_dbuser
The steps below dump tables from the restored database instance and then restores them to the original instance.
Get a list of the databases in the restored server if you want to validate DB names.
az postgres db list --resource-group RESOURCE_GROUP --server-name RESTORED_SERVER_NAME 
Dump and Restore the database.
### Force pg commands to use SSL
export PGSSLMODE=require

### Dump the restored database using the Azure super admin user - you will need to know the password.
pg_dump -v -h RESTORED_SERVER_NAME.postgres.database.azure.com -U AZURE_ADMIN_USER@RESTORED_SERVER_NAME -Fc -d foo_db -f foo_db.dump

### DROP and CREATE tables in the original DB using the Azure super admin user. Azure seems to also
### require you add your super admin user to the DB user role.
### On the CREATE statement, use the correct ENCODING, LC_COLLATE, and LC_CTYPE for your particular database.
psql -h ORIGINAL_SERVER_NAME.postgres.database.azure.com -U AZURE_ADMIN_USER@ORIGINAL_SERVER_NAME postgres << EOF
DROP DATABASE foo_db;
GRANT foo_dbuser TO AZURE_ADMIN_USER;
CREATE DATABASE foo_db WITH OWNER foo_dbuser ENCODING 'UTF-8' LC_COLLATE='en-US' LC_CTYPE='en-US' TEMPLATE='template0';
EOF

### The -j parameter in the pg_restore command is used to parallelize the restore and indicates the number of
### cores on the destination server. You can look in the portal or use this az command to retrieve the number 
### of cores. If this is a very large database restore, temporarily increasing the SKU to have 32 or 64 cores 
### can greatly decrease the restore time.
az postgres server show -g RESOURCE_GROUP -n ORIGINAL_SERVER_NAME -o json | jq '.sku.capacity'

### Restore the dump file you just created into the original DB instance using your DB user, not the Azure super admin user.
pg_restore -v --no-owner -h ORIGINAL_SERVER_NAME.postgres.database.azure.com -U foo_dbuser@ORIGINAL_SERVER_NAME -Fc -j 4 -d foo_db foo_db.dump
If the pg dump/restore commands show an error such as: pg_dump: error: connection to database "foo_db" failed: FATAL:  no pg_hba.conf entry for host "SOME_IP_ADDRESS", user "USERNAME", you need to create a temporary firewall exception on the specific DB instance you are targeting.
az postgres server firewall-rule create --resource-group RESOURCE_GROUP --server-name RESTORED_SERVER_NAME --name RULE_NAME --start-ip-address SOME_IP_ADDRESS --end-ip-address SOME_IP_ADDRESS
Completion/Cleanup

Remove any firewall rules you created on the original server.
az postgres server firewall-rule delete --resource-group RESOURCE_GROUP --server-name ORIGINAL_SERVER_NAME --name RULE_NAME
Delete the restored database instance.  VERIFY you are deleting the correct database before hitting enter.
az postgres server delete --resource-group RESOURCE_GROUP --name RESTORED_SERVER_NAME
Add the AAD Group back to the original DB instance

Do this from the portal.

Go to the postgres server in the portal
Select the Active Directory admin option under Settings
Add the admin back that you removed in the first step