Обсуждение: Dump & restore in directory format and permissions are largely lost?
Source and target are 13.2. Source dump command:
time pg_dump \
--verbose \
--dbname=boxscore \
--blobs \
--jobs=${JOBS} \
--format=directory \
--file=db.full.dump > ~/db.full.dump.log 2>&1
--
--verbose \
--dbname=boxscore \
--blobs \
--jobs=${JOBS} \
--format=directory \
--file=db.full.dump > ~/db.full.dump.log 2>&1
Restore excludes mat views, so two steps:
time pg_restore \
--list \
--format=directory \
db.full.dump | sed '/MATERIALIZED VIEW DATA/d' > restore.lst
--list \
--format=directory \
db.full.dump | sed '/MATERIALIZED VIEW DATA/d' > restore.lst
And then
time pg_restore \
--verbose \
--host=${HOST} \
--user=postgres \
--format=directory \
--use-list restore.lst \
--dbname=boxscore \
--jobs 16 \
db.full.dump > ~/restore.log 2>&1
--verbose \
--host=${HOST} \
--user=postgres \
--format=directory \
--use-list restore.lst \
--dbname=boxscore \
--jobs 16 \
db.full.dump > ~/restore.log 2>&1
After creation, none of my schemes or relations really have the right permissions, they seem totally absent. The same exact roles exist in source and target.
Am I missing something obvious here?
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Re: Dump & restore in directory format and permissions are largely lost?
От
"David G. Johnston"
Дата:
On Tue, Jun 15, 2021 at 6:55 PM Wells Oliver <wells.oliver@gmail.com> wrote:
Am I missing something obvious here?
You specified a host in the restore command - since pg_dump doesn't dump global objects like roles are you sure the restore cluster has the necessary roles present?
David J.
Yes. The target and source servers have the same users and roles, which is why their permissions being absent from the same objects on the target where they exist in the source is very confusing to me.
I also do a pg_restore -l -Fd on the dump file, and I don't see any GRANT or REVOKE statements: should I expect that?
On Tue, Jun 15, 2021 at 5:58 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jun 15, 2021 at 6:55 PM Wells Oliver <wells.oliver@gmail.com> wrote:Am I missing something obvious here?You specified a host in the restore command - since pg_dump doesn't dump global objects like roles are you sure the restore cluster has the necessary roles present?David J.
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Wells Oliver <wells.oliver@gmail.com> writes:
> Yes. The target and source servers have the same users and roles, which is
> why their permissions being absent from the same objects on the target
> where they exist in the source is very confusing to me.
Did you look for errors in the log output of both pg_dump and pg_restore?
> I also do a pg_restore -l -Fd on the dump file, and I don't see any GRANT
> or REVOKE statements: should I expect that?
They'd show up in "-l" output as ACL items.
regards, tom lane
It looks like the dump, then is creating an insufficient number of ACL statements. Running
pg_restore -Fd -l db.full.dump/ | grep ACL | grep webaccess
Shows nothing, though I use this role on many schemas and relations.
Is there anything about this dump statement that would prevent some ACL lines from being generated?
time pg_dump \
--verbose \
--dbname=db \
--blobs \
--jobs=8 \
--format=directory \
--file=db.full.dump
--verbose \
--dbname=db \
--blobs \
--jobs=8 \
--format=directory \
--file=db.full.dump
Is there a way to run pg_dump to spit out all ACL lines w/o having to do the full dump?
On Tue, Jun 15, 2021 at 6:19 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> Yes. The target and source servers have the same users and roles, which is
> why their permissions being absent from the same objects on the target
> where they exist in the source is very confusing to me.
Did you look for errors in the log output of both pg_dump and pg_restore?
> I also do a pg_restore -l -Fd on the dump file, and I don't see any GRANT
> or REVOKE statements: should I expect that?
They'd show up in "-l" output as ACL items.
regards, tom lane
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Wells Oliver <wells.oliver@gmail.com> writes:
> pg_restore -Fd -l db.full.dump/ | grep ACL | grep webaccess
> Shows nothing, though I use this role on many schemas and relations.
What do you think you're filtering for there? The -l output for ACL entries
will show the names of the objects and their owners, but not the grantees.
> Is there anything about this dump statement that would prevent some ACL
> lines from being generated?
Not that I see.
regards, tom lane
Ah, I'm sorry, is there a way to see grants contained in a dump?
On Tue, Jun 15, 2021 at 6:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> pg_restore -Fd -l db.full.dump/ | grep ACL | grep webaccess
> Shows nothing, though I use this role on many schemas and relations.
What do you think you're filtering for there? The -l output for ACL entries
will show the names of the objects and their owners, but not the grantees.
> Is there anything about this dump statement that would prevent some ACL
> lines from being generated?
Not that I see.
regards, tom lane
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Wells Oliver <wells.oliver@gmail.com> writes:
> Ah, I'm sorry, is there a way to see grants contained in a dump?
I'd try "pg_restore -s -f - archive.file | grep GRANT" or so.
regards, tom lane
That worked, I piped all the GRANTs into a file and ran it against the DB and permissions have been restored, but it remains puzzling to me why they were absent after the restore.
On Tue, Jun 15, 2021 at 6:45 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> Ah, I'm sorry, is there a way to see grants contained in a dump?
I'd try "pg_restore -s -f - archive.file | grep GRANT" or so.
regards, tom lane
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com