How to restore a pg_dumpall dump without CREATE INDEX?

I am attempting to migrate from Postgres 9.6 to 10.3 and during the restore each index is recreated one by one - this is a problem.

So far I thought pg_dumpall is a good option.

pg_dumpall -U postgres -h localhost -p 5432 --clean --file=dumpall_clean.sql


Once this is done the file is around 1.2TB in size and I can load it to the new 10.3 instance with

psql -U postgres -h localhost -p 5433 < dumpall_clean.sql

simple.

Problem

As I learned the indicies are not backed up like tables are, they are simply recreated, and that is my problem.

The cluster has thousands of partitions each with several million rows and two indices (one BTREE and one GIST). This takes days since each index is created one by one.

As I have enough resources and I know which indices have to be created, I would perfer to do this step after the dump has been restored. Initially I made 8 FOR loops (to run in parallel) to go through the partitions, and created an index by moving a partition to a faster tablespace (SSD), create the index, then move the table and the index back to the default tablespace. So far this has worked for me.

Question
How can I have the same result* of a pg_dumpall dump without recreating the indices when loading the dumpall_clean.sql file?
A pg_dumpall --without-index would be nice.


"This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole." - pg_dumpall manual

It's been a while since this was up, but we need do something like this in our restores. Finding this answer has actually made me realize I can use something just like this to speed up my current restore by cutting out the index creation :)

You can use the -l and -L flags to pg_restore to list actions and use a list of actions.

From my notes in our script:

# pg_restore -l gives a list of all operations that would be performed during the restore.
    # pg_restore -L accepts a list of operations from file to perform during the restore.

So you can use -l to dump the list of operations from an existing dump, filter it, and then run again w/ -L to accept that newly filtered list of operations.

In practice that looks something like:

${PGRESTORE} --dbname=db_restore -Fc -l dump_filename \
        | grep -v "public view_we_dont_want" \
        | grep -v "public postgres" >${tmpFile}
    ${PGRESTORE} --dbname=db_restore -Fc -L ${tmpFile} dump_filename

StackExchange Database Administrators Q#204490, answer score: 3