I'm struggling with finding an appropriate backup / restore method for my postgres databases.
My requirements are these:
- DB owner is a non-superuser account. it only has "can login" and "inherit parent role rights" privileges.
- backup should be transferable among multiple DB hosts (e.g. development local copies)
- database restore should be a definitive operation: just make a running EXACT copy of the original
- Ideally (nice to have) the restore will be done by non-privileged user
So, I tried the following methods, with varying success:
use "plain" pg_dump
This one breaks while loading because it doesn't clear any existing data in the objects. If I add -c to pg_dump (drop existing objects before creating them), the resulting SQL statements don't account for possibility that the other database might still be empty or simply outdated with fewer objects. Drop statements thus fail.
Failing this method, I went to -Fc...
With custom format, pg_dump seems to only have one way of building the backup. This is good. So the variations will come with pg_restore. I start with -C -c (recreate database and objects):
This one seems to do nothing, ever run as postgres user. I get plenty of "object already exists" errors
Of course, not using the drop/create flags results in even worse failures.
Failing this, I went to a two-step process:
drop schema public cascade
This one works with -O --role=non-admin-role with one single statement failing. The one dealing with extensions.
It DOES NOT work when run by postgres and with restoring object ownership. It simply does not restore ownership. Everything in public schema is owned by postgres.
drop database / create database / restore with postgresql
This one alone seems to work as intended (when run by postgres, obviously), but it's extremely aggressive in what I have to do to get a simple backup / restore. Also, it doesn't satisfy the "restore should be done by non-privileged user" thingy.
I'm on pg 11, if it makes any difference.
So, what am I doing wrong? Do you have a simple two-command backup / restore example that actually works? I'd love to hear about it