Consider a situation in which the following are true:
- There exists a collection of databases which is a PostgreSQL database cluster, associated with PostgreSQL version 16. Here in this description, let's call that cluster "Pgdbc1".
- Attached to Pgdbc1, there exists a PostgreSQL user which is a superuser. Call that user "PostgresSuperUser".
- Attached to Pgdbc1, there exists a PostgreSQL user which is a user other than a superuser. Call that user "Pguser99".
- As a database in Pgdbc1, there exists a database which is owned by Pguser99. Call that database "Db55OwnedByPgUser99".
- Backups of databases in Pgdbc1, including Db55OwnedByPgUser99, are routinely done using pg_dump app, included with PostgreSQL version 16, and using the PostgresSuperUser.
- Those backup files are saved in archive format generated by pg_dump. So, in case of needing to restore a database, the pg_restore app, associated with PostgreSQL version 16 or higher, would be required.
- Let's use "Db55BackupArchive324" to refer to a collection of files/folders which consist of one particular instance of a backup of Db55OwnedByPgUser99, done using pg_dump, associated with PostgreSQL version 16, and using an archive format.
- Let's use "OperatingSystemUserDFG" to refer to a person (entity) which: □ Has access rights which allow execution of the pg_restore app. □ Has read access to Db55BackupArchive324.
- OperatingSystemUserDFG □ Does NOT have password of PostgresSuperUser or any superuser □ runs pg_restore, and when running pg_restore, ®Uses Pguser99 as the user, which is provided to pg_restore app. ®Uses the password which Pguser99 used on Pgdbc1. ®Restores Db55OwnedByPgUser99 on Pgdbc1.
- Given the fact that PostgresSuperUser did the instance of running pg_dump which led to the creation of database backup file named "Db55BackupArchive324", can someone use pg_restore to successfully restore Db55BackupArchive324 using the Pguser99 and password of Pguser99, rather than the PostgresSuperUser and password of PostgresSuperUser, and do the restore in cluster Pgdbc1?
Can every user restore a dump using pg_restore? is a similar question, but not really the same.
- Attached question. What about doing the restore to a different cluster, call it "Pgdbc2"? In order to achieve such a restore on Pgdbc2 using the Pguser99 and password of Pguser99, on Pgdbc2, would you do the following?
- Make sure there is a user named "Pguser99", and use the same character string for password of that Pguser99 as was used for Pguser99 on Pgdbc1.
- Make sure there is a database which is owned by Pguser99, and named "Db55OwnedByPgUser99", which is the same name as was used on Pgdbc1.