From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: pg_restore 12 "permission denied for schema" errors |
Date: | 2022-10-20 17:25:51 |
Message-ID: | c558ca71-5602-7c1f-902e-59b3f8840f73@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/20/22 10:02, Adrian Klaver wrote:
> On 10/20/22 06:20, Ron wrote:
>> On 10/20/22 00:12, Tom Lane wrote:
>>> Frank Gard <frank(dot)von(dot)postgresql(dot)org(at)familie-gard(dot)de> writes:
>>>> Am 20.10.22 um 02:58 schrieb Ron:
>>>>> pg_restore -v --create --clean --jobs=`nproc` -Fd --dbname=template1 $DB
>>>> What database do you want to restore your data into? As far as I know
>>>> your pg_restore command would import the data into template1, right?
>>> No --- my ears went up about that too, but he's using --create.
>>> So template1 is just the springboard for the initial connection
>>> in which CREATE DATABASE will be issued.
>>>
>>> My guess is that there's something inconsistent between source
>>> and target installations about roles and role-privilege grants.
>>> We don't have enough details to do more than guess, though.
>>
>> I ran "pg_dumpall --globals-only --no-role-passwords" on the source
>> instance, and applied it to the new instance before doing the pg_restore.
>> If I hadn't done that, pg_restore would have thrown errors on all the
>> GRANT and ALTER TABLE ... OWNER TO statements embedded in the backup.
>>
>
> Some questions:
>
> 1) The backup was from a Postgres 12.x database using a version 12 or
> higher instance of pg_backup?
The data is in an RDS Postgresql 12.11 instance.
pg_dump on the "client" Linux VM is v12.12 or 12.11 from RHEL8 packages.
(Don't remember the exact version ATM.)
> 2) What does nproc --jobs=`nproc` resolve to?
Two CPUs on the VM.
> 3) What if you run without --jobs?
I can test that, but will be under serious time constraints during the
maintenance window. Heavily parallel dump and restore are imperative.
> 4) What user are you running the pg_restore as?
PGUSER=postgres
> 5) Why the --no-role-passwords in the pg_dump?
That's a requirement for AWS RDS. User postgres does not have privs to dump
the password hash from pg_authid.
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2022-10-20 17:29:21 | Re: Does standalone postfrsql have autogrowth/ manual growth on table space? |
Previous Message | Vince McMahon | 2022-10-20 17:19:38 | Does standalone postfrsql have autogrowth/ manual growth on table space? |