PSQL has some awesome tools pg_dump and pg_restore that can assist and cut down restore times in the event of a recovery event.
Scenario
User error – If a table was accidentally emptied at noon today:
• Restore point in time just before noon and retrieve the missing table and data from that new copy of the server (Azure Portal or CLI) , Powershell not supported yet.
• Update firewall rules to allow traffic internally on the new SQL Instance
Then log onto a JumpBox in the Azure cloud.
• Dump the restored database to file (pg_dump)
pg_dump -Fc -v -t mytable -h Source.postgres.database.azure.com -U pgadmin@source -d mydatabase > c:\Temp\mydatabase .dump
The switch -Fc – gives us flexibility with pg_restore later (Table Level Restore)
• Copy the table back to the original server using pg_restore
You may need to truncate the target table before, as we doing data only restore (-a)
Table Only (not schema):
pg_restore -v -A -t mytable -h Target.postgres.database.azure.com -p 5432 -U pgadmin@target -d mydatabase "c:\Temp\mydatabase .dump"
note: Use a VM in the Azure cloud that has access to SQL via VNET Rules or SQL Firewalls.
We currently use PSQL 10. There is a known issue with VNet Rules. Just contact Microsoft if you need them, else use firewall rules for now.
Issues
VNET Rules and PostgreSQL 10
Issue Definition: If using VET rules to control access to the PostgreSQL 10.0 server, customer gets the following error: psql ‘sslmode=require host=server .postgres.database.azure.com port=5432 dbname=postgres’ –username=pgadmin@server psql: FATAL: unrecognized configuration parameter ‘connection_Vnet’ This is a known issue: https://social.msdn.microsoft.com/Forums/azure/en-US/0e99fb68-47fd-4053-a8be-5f8b87b3a660/azure-database-for-postgresql-vnet-service-endpoints-not-working?forum=AzureDatabaseforPostgreSQL
DNS CNAME
nslookup restoretest2.postgres.database.azure.com
Non-authoritative answer:
Name: cr1.westus1-a.control.database.windows.net
Address: 23.9.34.71
Aliases: restoretest2.postgres.database.azure.com
nslookup restoretest1.postgres.database.azure.com
Non-authoritative answer:
Name: cr1.westus1-a.control.database.windows.net
Address: 23.9.34.71
Aliases: restoretest1.postgres.database.azure.com
The Host is the same server. The way the connection string works is on the USERNAME.
These two command will connect to the SAME Server instance
pg_restore -v -a -t mytable -h restoretest2.postgres.database.azure.com -p 5432 -U pgadmin@restoretest1 -d mydatabase "c:\Temp\mydatabase.dump"
pg_restore -v -a -t mytable -h restoretest1.postgres.database.azure.com -p 5432 -U pgadmin@restoretest1 -d mydatabase "c:\Temp\mydatabase.dump"
The hostname just gets us to the Microsoft PSQL server, it is the username that points us to the correct instance. This is extremely important when updating connection strings on clients!
Happy Life – Happy Wife
Romiko Derbynew 