PostgreSQL Hints

dump restore classic

# -Fc - `format - custom` for parallel restore; not sql commands; compression
> pg_dump -Fc db_name > name.dump 

# most of time - index recreation
> pg_restore --dbname=db_name name.dump
# can be parallel; jobs=num of cores +1
> pg_restore --jobs=2

pg_hba.conf

	# `role` like a group (hierarchical); role can own db objects
	# `user`=`role` + login priviledge
	
	# local - local sockets
	# host - tcp address

	# trust - permissive; for local; not for producation
	# peer - login sys user if name matches pg user
	# md5 - md5 passwd; use ssl, restrict addresses

postgresql.conf

	include = '/path/to/include.conf'

	# LOG
	log_destination = 'csvlog'  
	log_directory = 'pg_log'  # inside of `pg_data`. can be path /var/log/postrgesql/
	logging_collector = on
	log_filename = 'pg_%Y-%m-%d_%H%M%S'
	log_rotation_age = 1d
	log_rotation_size = 1GB
	log_min_duration_statement = 250ms  # cut off long queries
	log_checkpoints = on
	log_connections = on  # probably not good for django
	log_disconnections = on
	log_lock_waits = on
	log_temp_files = 0

	# MEMORY
	# -1st line of caching; <2GB=20%_of_mem; <32GB=25%_of_mem; >32GB=8GB; >64GB=16GB;
	shared_buffers = 
	# start from 32-64MB; look `temporary file` lines in logs, set 2-3x of the largest tmp file
	work_mem = 
	# 10% of mem, up to 1GB; or higher if VACUUM problems
	maintenance_work_mem = 
	# hint for planner; set to amount file system cache available; or set to 75% of sys mem
	effective_cache_size = 

	# PG 9.4
	# CHECKPOINTS
	wal_buffers = 16MB  # WAL - write ahead log
	checkpoint_completion_target = 0.9
	checkpoint_timeout = 10min # 10-30min - can up to that time to restore on restart
	checkpoint_segments = 32 # to start
	# Look at logs for `checkpoint` creation, if often than `checkpoint_timeout` double `checkpoint_segments` till it stops. WAL can take (3 x 16MB x `checkpoint_segments`) on disk.
	# PG 9.5
	# checkpoint_segments - gone
	min_wal_size = 512MB	
	max_wal_size = 2GB  # 3x of min_wal_size

	# PLANNER
	effective_io_concurrency = # to num of I/O channels; ignore otherwise
	random_page_cost = # 3.0 for RAID10, 2.0 for SAN, 1.1 Amazon EBS

	# DO NOT TOUCH
	fsync = on  # or db corrupted
	synchronous_commit = on  # potential data loss