We have a server that's disconnected from the world. It's a high end system with 48GB memory and 500GB SSD hard disk, 16 core CPUs. We're trying to do a
pg_restore of a simple database dump of less than 10 tables, no binary data or blobs, just simple text (a comments system). But one table has about 200GB of data, so it's large.
There's no other jobs for this DB. Only this maintenance task. What are the best settings for this purpose given the config above? PGSQL's documentation does not help me too much. My specific question is around wal settings.
If we're OK with using the whole of this server just to do a
pg_restore, and nothing else other than PG is on this server, what settings should we use? This is what we have now:
maintenance_work_mem = 1500MB fsync = off synchronous_commit = off wal_level = minimal full_page_writes = off wal_buffers = 64MB #----- checkpoint_segments = 512 #----- max_wal_size = (3 * checkpoint_segments) * 16MB #-- min_wal_size = 100MB # 80MB is the default max_wal_size = 24576MB # based on 512 checkpoint_segments max_wal_senders = 0 wal_keep_segments = 0 archive_mode = off autovacuum = off
Note that using top, we find that memory is not the issue. The CPU cores are spiking to 100 or so, and then dipping. This is an intensive write process, so that makes sense. Welcome any simple-to-understand guidance on how the
min_wal_size should be set -- note that it's commented for us now.