Deron escribió: > I see a lot of suggestions to increase maintenance work mem if running > pg_restore. This is to help with the index creation and initial sort from > what I understand. A few tests I ran does show this helps. > Does anyone know if there is a way to only "temporarily" set this setting? > I have some DBs that are bloated and I have a script to run off hours (I > don't like working late). This uses pg_dump and pg_restore to recover the > disk space. I found that this is usually faster than 'vacuum full', and is > useful in some cases.
You can specify it via PGOPTIONS:
$ PGOPTIONS='-c maintenance_work_mem=1GB' psql Welcome to psql 8.2.5, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit
alvherre=# show maintenance_work_mem ; maintenance_work_mem ---------------------- 1GB (1 row)
alvherre=# \q
$ psql Welcome to psql 8.2.5, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit
alvherre=# show maintenance_work_mem ; maintenance_work_mem ---------------------- 16MB (1 row)