The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:
dropdb -U postgres mydb && \
pg_restore --create --dbname=postgres --username=postgres pg_backup.dump
The --clean
option in pg_restore doesn't look like much but actually raises non-trivial problems.
For versions up to 9.1
The combination of --create
and --clean
in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):
--clean
Clean (drop) database objects before recreating them
and
--create
Create the database before restoring into it.
Because what's the point of cleaning inside a brand-new database?
Starting from version 9.2
The combination is now accepted and the doc says this (quoting the 9.3 manpage):
--clean
Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)
--create
Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.
Now having both together leads to this kind of sequence during your restore:
DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...
There is no DROP
for each individual object, only a DROP DATABASE
at the beginning. If not using --create
this would be the opposite.
Anyway this sequence raises the error of public
schema already existing because creating mydb
from template0
has imported it already (which is normal, it's the point of a template database).
I'm not sure why this case is not handled automatically by pg_restore
. Maybe this would cause undesirable side-effects when an admin decides to customize template0
and/or change the purpose of public
, even if we're not supposed to do that.
It sounds like the code might be using SAVEPOINT
s to handle errors, and not releasing the savepoints before proceeding. That would explain the large number of virtual xid locks.
RELEASE SAVEPOINT
after you're done with a step.
You might also want to consider batching the work into smaller chunks, as the:
SAVEPOINT
- Try it
ROLLBACK TO SAVEPOINT
if it fails, RELEASE SAVEPOINT
if it succeeds
pattern works, but has some performance costs that scale with number of savepoints in a transaction.
This applies to PL/PgSQL BEGIN ... EXCEPTION
blocks too.
Best Answer
pg_dump
takes share locks on each table it dumps. So you need to configure Postgres to support that many locks It's unclear to me if you have 3 schemas with 1200 tables each - which would mean a total of 3600 tables, or if there are 1200 table distributed across 3 schemas.Quote from the manual
Assuming everything is configured as the defaults, you wind up with
64 * (100 + 0) = 6400
objects that can be locked. Ifpg_dump
is the only thing running on your database the default configuration should be fine.The fact that you only occasionally get that error, means that you have concurrent transactions that also use many object locks so that
pg_dump
can't allocate enough locks.You should increase that value nonetheless to avoid the "occasional" errors. I would start with increasing it to 128.