pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDump & restore in directory format and permissions are largely lost?
9+ messages / 3 participants
[nested] [flat]
* Dump & restore in directory format and permissions are largely lost?
@ 2021-06-16 00:54 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Wells Oliver @ 2021-06-16 00:54 UTC (permalink / raw)
To: pgsql-admin
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
Restore excludes mat views, so two steps:
time pg_restore \
--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
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Dump & restore in directory format and permissions are largely lost?
@ 2021-06-16 00:57 David G. Johnston <david.g.johnston@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2021-06-16 00:57 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Dump & restore in directory format and permissions are largely lost?
@ 2021-06-16 01:09 Wells Oliver <wells.oliver@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Wells Oliver @ 2021-06-16 01:09 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Dump & restore in directory format and permissions are largely lost?
@ 2021-06-16 01:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2021-06-16 01:19 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Dump & restore in directory format and permissions are largely lost?
@ 2021-06-16 01:23 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Wells Oliver @ 2021-06-16 01:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Dump & restore in directory format and permissions are largely lost?
@ 2021-06-16 01:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2021-06-16 01:30 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Dump & restore in directory format and permissions are largely lost?
@ 2021-06-16 01:32 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Wells Oliver @ 2021-06-16 01:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Dump & restore in directory format and permissions are largely lost?
@ 2021-06-16 01:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2021-06-16 01:45 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Dump & restore in directory format and permissions are largely lost?
@ 2021-06-16 01:52 Wells Oliver <wells.oliver@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Wells Oliver @ 2021-06-16 01:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-admin
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 <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2021-06-16 01:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-16 00:54 Dump & restore in directory format and permissions are largely lost? Wells Oliver <wells.oliver@gmail.com>
2021-06-16 00:57 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-16 01:09 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-16 01:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-16 01:23 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-16 01:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-16 01:32 ` Wells Oliver <wells.oliver@gmail.com>
2021-06-16 01:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-06-16 01:52 ` Wells Oliver <wells.oliver@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox