Created
October 25, 2018 00:29
-
-
Save gionniboy/5fe44a04ad9d4cf38a4f27e868ecd613 to your computer and use it in GitHub Desktop.
This file contains hidden or 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
| #!/bin/bash | |
| set -o errexit | |
| set -o nounset | |
| echo "Configuring psql with improved performance..." | |
| sed -ri "s/^#*(fsync\s*=\s*)\S+/\1 off/" "$PGDATA"/postgresql.conf | |
| sed -ri "s/^#*(full_page_writes\s*=\s*)\S+/\1 off/" "$PGDATA"/postgresql.conf | |
| sed -ri "s/^#*(random_page_cost\s*=\s*)\S+/\1 2.0/" "$PGDATA"/postgresql.conf | |
| sed -ri "s/^#*(checkpoint_segments\s*=\s*)\S+/\1 64/" "$PGDATA"/postgresql.conf | |
| sed -ri "s/^#*(checkpoint_completion_target\s*=\s*)\S+/\1 0.9/" "$PGDATA"/postgresql.conf | |
| # Performance Tuning | |
| sed -i -e"s/^max_connections = 100.*$/max_connections = 1000/" "$PGDATA"/postgresql.conf | |
| sed -i -e"s/^shared_buffers =.*$/shared_buffers = 512MB/" "$PGDATA"/postgresql.conf | |
| sed -i -e"s/^#effective_cache_size = 128MB.*$/effective_cache_size = 1GB/" "$PGDATA"/postgresql.conf | |
| sed -i -e"s/^#work_mem = 4MB.*$/work_mem = 16MB/" "$PGDATA"/postgresql.conf | |
| # sed -i -e"s/^#checkpoint_segments = .*$/checkpoint_segments = 32/" "$PGDATA"/postgresql.conf | |
| # sed -i -e"s/^#checkpoint_completion_target = 0.5.*$/checkpoint_completion_target = 0.7/" "$PGDATA"/postgresql.conf | |
| sed -i -e"s/^#wal_buffers =.*$/wal_buffers = 16MB/" "$PGDATA"/postgresql.conf | |
| sed -i -e"s/^#default_statistics_target = 100.*$/default_statistics_target = 100/" "$PGDATA"/postgresql.conf | |
| grep fsync "$PGDATA"/postgresql.conf | |
| grep max_connections "$PGDATA"/postgresql.conf | |
| grep work_mem "$PGDATA"/postgresql.conf | |
| grep effective_cache_size "$PGDATA"/postgresql.conf | |
| echo "pgconf optimized" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment