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.


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 -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 -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.


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 port=5432 dbname=postgres’ –username=pgadmin@server psql: FATAL: unrecognized configuration parameter ‘connection_Vnet’ This is a known issue:



Non-authoritative answer:





Non-authoritative answer:




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 -p 5432 -U pgadmin@restoretest1 -d mydatabase  "c:\Temp\mydatabase.dump"

pg_restore -v -a -t mytable -h -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