Last active
March 4, 2017 12:26
-
-
Save idlesign/5d0d915d50e474414075ae10b1487285 to your computer and use it in GitHub Desktop.
PostgreSQL Hints
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# -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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# `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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment