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.von.postgresql.org@xxxxxxxxxxxxxxx> 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.
