0

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:

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

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

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

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

0

You say "backup / restore" but I think you mean "distribute copies of".

If you mean "restore", then use the standard tools in the standard way. Anything else just increases the Risk of something going [badly] wrong when you can least afford it to. Yes, these tools require elevated permissions. That's just the Nature of the Beast.

To distribute copies of databases, "pg_dump -Fc" is the most portable option, even crossing operating system boundaries, but probably produces the largest dump file (but "disk space is cheap").
Yes, the recipients of those files will have to know what they're doing with their own databases. I see that as a Good Thing, because it gives them more of an appreciation of your role, as DBA.

If the intention is to provide copies of databases for others without them having the permissions to create databases for themselves, then you'll just have to do it for them. You create the databases, you copy and populate the tables, you grant relevant permissions on those tables and then they take it from there.

3
  • OK, if there is a difference, then yes, I mean "distribute copies of". As I have said in the initial OP, I was unable to get ANY restore working without actually doing additional steps like dropping schema or even entire database prior to restoring. Can you suggest what the "standard way" for the tools in question is? I mean such that I call pg_dump, then pg_restore and be done with it? Because none of the guides I found helped... – velis Mar 27 '20 at 11:50
  • @velis: There is no /easy/ way of "merging" one database with another. If you want to deliver a copy of your database to someone else, then they're going to get the whole thing in one go. The most sensible way to handle that is to throw away the database that they have and restore the new one in its place or to create a new database and restore the new copy into that. Either way, they have to be able to do 'database-y' things for themselves - and that means elevated permissions on their own databases. – Phill W. Mar 27 '20 at 12:30
  • Yes, forget trying to restore it with non-admin account. As you can see from OP, I failed to do it as postgres user. One thing or another went wrong. And I don't want to merge. Just get the database of my choosing to become an exact copy of the original database. Also, equally named users are present in the target database, so changing ownership should not be an issue. But for some reason it is - in some circumstances. – velis Mar 27 '20 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.