setting Postgres options during a pg_restore

Question

I am finding it difficult to find the best way to set Postgres options for a pg_restore session only. It would be nice to be able to do this in the environment in which I run pg_restore and not change global sessions or have to initiate an interactive psql session. E.g. I would like to set these values during the restore only.

 work_mem = 32MB
 shared_buffers = 4GB
 maintenance_work_mem = 2GB
 full_page_writes = off
 autovacuum = off
 wal_buffers = -1

I did try using export PGOPTIONS for the values, but I am not sure it actually worked or whether the format for setting them was correct, e.g. export PGOPTIONS='-c work_mem=32MB -c shared_buffers=4GB' etc.

Answer 1

Most of the parameters you want to set don't make any sense. Here is my recommendation:

• increase max_wal_size a lot

• increase maintenance_work_mem a lot

• set wal_level = minimal and use --single-transaction with pg_restore, so that PostgreSQL can skip writing WAL

The first two options can be set via PGOPTIONS like you show in your question, the third requires changing parameters in the configuration file or on the server command line and a restart.

To see if PGOPTIONS works, try

PGOPTIONS="-c work_mem=123MB" psql -c "SHOW work_mem"

Answer 2

If you can restart Postgresql (I didn't see you mention one way or another), you can pass ad hoc parameters there.

For example:

pg_ctl restart -wt9999 -mfast \
                -o "-c hba_file=/var/lib/pgsql/14/data/pg_hba_maintmode.conf" \
                -o "-c fsync=off" \
                -o "-c log_statement=none" \
                -o "-c log_temp_files=100kB" \
                -o "-c log_checkpoints=off" \
                -o "-c log_min_duration_statement=120000" \
                -o "-c maintenance_work_mem=2GB" \
                -o "-c synchronous_commit=off" \
                -o "-c archive_mode=off" \
                -o "-c full_page_writes=off" \
                -o "-c checkpoint_timeout=30min" \
                -o "-c max_wal_size=36GB" \
                -o "-c wal_level=minimal" \
                -o "-c max_wal_senders=0" \
                -o "-c wal_buffers=64MB" \
                -o "-c autovacuum=off" 

Then do the pg_restore, vacuumdb --analyze-only and lastly pg_ctl stop -wt9999 -mfast && pg_ctl start -wt9999.

(The explicit stop/start are required because pg_ctl restart retains the passed parameters, while stop/start wipes them out.)