Azure Database for PostgreSQL – Backup/Restore

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

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s