I wanted to test a migration of Azure PostgreSQL from simple to flexible. I wanted a simple db to restore onto my single server. I may not have needed to do this, but I was then curious how one would go about dumping and restoring on Azure Postgres in general. I plan to use the Azure migration service, but since I started this dump/restore exercise, I thought I would document it here.
Before we look at your options, I cloned this db as a test: https://github.com/devrimgunduz/pagila
This way, I could restore a db and show you how to dump it. I will also use this restored db to test the Azure migration service (another post later).
Command line
You won’t be able to restore the pagila db from pgAdmin, as pgAdmin will throw an error.
You will need to install psql with Chocolatey (windows) or Homebrew (mac).
Restoring from the command line
Before restoring the pagila script, I had to change the owner to adminuser (bc that’s the name of my admin user on azure postgres). Also, I don’t like putting objects in public, so I created a schema pagila and added the objects to it instead. My updated script can be found here.
You need to restore the schema first:
export PGPASSWORD=YourP@ssword123
psql -h example-postgresql-server.postgres.database.azure.com -U adminuser@example-postgresql-server -d postgres -f /Users/josephineb/Documents/GitHub/pgdump/pagila-schema-azure-pg.sqlI also had to update the data file because I wanted the schema to be pagila – not public. I don’t like objects being placed in public.
export PGPASSWORD=YourP@ssword123
psql -h example-postgresql-server.postgres.database.azure.com -U adminuser@example-postgresql-server -d pagila -f /Users/josephineb/Documents/GitHub/pgdump/pagila-data-azure-pg.sqlSo it’s restored, as you can see in pgAdmin below.
Dumping from command line
Before you dump the schema and data, you will want to dump the roles. You can use the pg_dumpall utility to dump only the role definitions and not the data or schema.
pg_dumpall --roles-only -h example-postgresql-server.postgres.database.azure.com -U adminuser@example-postgresql-server -p 5432 > /Users/josephineb/Documents/GitHub/pgdump/roles_dump.sqlYou will need to restore this later before restoring the schema and data – depending on what roles are in use in your db. Mine doesn’t have any special as this is only a test, but environments in actual use may have roles associated with your db and they will need to be dumped and restored.
Then you can dump the schema and data as one depending on the size, or schema first, then data. In fact, if you have a large db, I suggest separating the indexes from the schema dump. You will dump the schema the same way and manually move the indexes out of the schema.sql file. This way, you restore the schema without the indexes, then the data, and then apply the indexes. It tends to be faster this way. I did this once with a VLDB, not required for smaller dbs.
Dump out the schema. -s flag tells it to get only the schema.
export PGPASSWORD=YourP@ssword123
pg_dump -h example-postgresql-server.postgres.database.azure.com -U adminuser@example-postgresql-server -d postgres -s -f /Users/josephineb/Documents/GitHub/pgdump/pagila-schema-azure-pg.sql
Dump out the data. -a flag tells it to get only the data.
export PGPASSWORD=YourP@ssword123
pg_dump -h example-postgresql-server.postgres.database.azure.com -U adminuser@example-postgresql-server -d postgres -a -f /Users/josephineb/Documents/GitHub/pgdump/pagila/pagila-data-azure-pg.sqlpgAdmin
If you aren’t into using the command line, you can use pgAdmin.
If you want to backup
You will need to back up the roles at the server level to support the use of any roles in the db.
It’s a SQL script so you can add .SQL to the file.
It will look something like this:
You can backup from pretty much any level in pgAdmin. That’s the nice thing about Postgres. You could backup the whole db or the schema or a table even, unlike SQL Server, where you can only backup the entire db.
This will bring up a dialog box where you can name the backup file and choose some other options.
Lots of options and here’s where you could get just schema or data.
And there are some more options you can choose.
I left the defaults, so in this case, it will do the schema and data in one file. After you click Backup, it will run.
When it’s done, you will receive a success message. If it fails, you will receive an error message. You can click More details… to see error messages.
If you want to restore
To restore that dump we just created, I created a new db named restoredpagila. Then I will right-click on that db to restore the dump to it.
I will choose the file and click Restore.
You will get a status box showing the state of your restore.
If you click on more details you will get a log of everything it did.
Once you know how to do it, backing up and restoring Postgres is not hard. You may be pleased to find that you can even back up one table and its data. The backups are far more flexible than SQL Server backups.
The post Dumping and Restoring Azure PostgreSQL Databases appeared first on sqlkitty.