pg_restore ignores -j parameter

Question

I have quite a large database (15G+ compressed) that was created like this:

pg_dump wrwks_prod -Fc -f /path/to/file.dump

and restored to a remote host like this:

pg_restore --no-owner --clean --dbname=dbname --username=user --host=remote_host --port=port  -j 3 --v /path/to/file.dump

But when I checked the remote machine, there was only one process using one core for index creation, thus ignoring the jobs parameter.

What could be the reason for this?

Both the local and the remote machine run with Postgres 9.2.

Answer 1

I would assume you have one huge table with one huge index on it, so the parallel jobs restored all the rest small relations very fast. The index build can't start before the table is fully restored, so it waits for it. And as soon as a table is restored one huge index gets created... Of course this is an assumtion... try running smth like:

SELECT oid, row_estimate, total_bytes
    ,pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS tbl
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a
order by "total_bytes" desc
limit 10
;

to confirm or neglect the assumption