Re: pg_restore -L reordering of the statements does not work

Thanks a lot Tom for the detailed info. Few queries -

1. Is there any way I can create my own list file?
2. For Alter table <<table name>> owner to <<role name>> statement, what is
the equivalent line in the list file?
3. Was using List file to achieve the following, i.e. executing the
following lines in this order -
* Restoring the dump file using list file with role <<user1>>
* create table schema1.table1
* grant all on schema1 to user2
* alter table schema1.table1 to user2

Is the above possible using list file, if not what is the best alternative
as we would like to automate.

PS: Don't have super user privileges on the target.

Regards,
Aditya D

On Sat, 28 Oct 2023 at 00:04, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Aditya D <dsaditya91(at)gmail(dot)com> writes:
> > Problem Statement - The ACL command i.e. The grant statement gets called
> > out at the end while performing pg_restore and the use case is to call it
> > at the beginning. I believe even though the re-ordering is done the
> > statements are executed using the "Internal Archive Id" while referring
> > to the dump file.
>
> Yeah, see the comments for RestorePass:
>
> * For historical reasons, ACL items are interspersed with everything else
> in
> * a dump file's TOC; typically they're right after the object they're for.
> * However, we need to restore data before ACLs, as otherwise a read-only
> * table (ie one where the owner has revoked her own INSERT privilege)
> causes
> * data restore failures. On the other hand, matview REFRESH commands
> should
> * come out after ACLs, as otherwise non-superuser-owned matviews might not
> * be able to execute. (If the permissions at the time of dumping would
> not
> * allow a REFRESH, too bad; we won't fix that for you.) We also want
> event
> * triggers to be restored after ACLs, so that they can't mess those up.
> *
> * These considerations force us to make three passes over the TOC,
> * restoring the appropriate subset of items in each pass. We assume that
> * the dependency sort resulted in an appropriate ordering of items within
> * each subset.
>
> Use of an -L switch overrides the dependency sort, but not this
> pass mechanism.
>
> Whatever you're hoping to do by overriding that is most likely
> just going to replace one kind of breakage by another.
>
> regards, tom lane
>