Heikki Linnakangas wrote:
Mario Weilguni wrote:
I cannot use "-1" for performance, because some gist stuff has changed
and the restore fails. But there seems to be no option for pg_restore to
use transactions for data restore, so it's very very slow (one million
records, each obviously in it's own transaction - because a separate
session "select count(1) from logins" shows a growing number).
By default, pg_dump/pg_restore uses a COPY command for each table, and
each COPY executes as a single transaction, so you shouldn't see the row
count growing like that. Is the dump file in --inserts format?
It would be nice to use transactions for the data stuff itself, but not
for schema changes or functions. I know I can use separate pg_restore
runs for schema and data, but it's complicated IMHO.
pg_restore -s foo
pg_restore -a -1 foo
doesn't seem too complicated to me. Am I missing something?
Doesn't pg_restore create the indices *after* loading the data if you let it
restore the schema *and* the data in one step? The above workaround would
disable that optimization, thereby making the data-restore phase much more costly.
Now that I think about it, I remember that I've often whished that we not only
had --schema-only and --data-only, but also --schema-unconstrained-only and
regards, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster