Bu yazıyı okumadan önce, ssh ayarlarınızı yaptığınıza emin olun
Bu yazı PostgreSQL 11 ve PgBackRest 2.15 için yazılmıştır
Öncelikle aşağıdaki komut ile pgbackrest kuralım:
sudo apt-get install pgbackrest -y
# https://stackoverflow.com/questions/39928401/recover-db-password-stored-in-my-dbeaver-connection | |
# requires pycryptodome lib (pip install pycryptodome) | |
import sys | |
import base64 | |
import os | |
import json | |
from Crypto.Cipher import AES |
import subprocess | |
def get_master_volume(): | |
proc = subprocess.Popen('/usr/bin/amixer sget Master', shell=True, stdout=subprocess.PIPE) | |
amixer_stdout = proc.communicate()[0].split('\n')[4] | |
proc.wait() | |
find_start = amixer_stdout.find('[') + 1 | |
find_end = amixer_stdout.find('%]', find_start) | |
return float(amixer_stdout[find_start:find_end]) |
Edit /var/lib/postgres/data/postgresql.conf: | |
# change IP on subscriber | |
listen_addresses = '*' | |
wal_level = logical | |
shared_preload_libraries = 'pglogical' | |
max_worker_processes = 16 | |
max_wal_senders = 16 | |
max_replication_slots = 16 | |
track_commit_timestamp = on |
SELECT table, | |
formatReadableSize(sum(bytes)) as size, | |
min(min_date) as min_date, | |
max(max_date) as max_date | |
FROM system.parts | |
WHERE active | |
GROUP BY table |
# Use envFrom to load Secrets and ConfigMaps into environment variables | |
apiVersion: apps/v1beta2 | |
kind: Deployment | |
metadata: | |
name: mans-not-hot | |
labels: | |
app: mans-not-hot | |
spec: | |
replicas: 1 |
--- PSQL queries which also duplicated from https://github.com/anvk/AwesomePSQLList/blob/master/README.md | |
--- some of them taken from https://www.slideshare.net/alexeylesovsky/deep-dive-into-postgresql-statistics-54594192 | |
-- I'm not an expert in PSQL. Just a developer who is trying to accumulate useful stat queries which could potentially explain problems in your Postgres DB. | |
------------ | |
-- Basics -- | |
------------ | |
-- Get indexes of tables |
# Sample haproxy postgresql master check | |
# | |
# haproxy listen: 5431 | |
# pg, instance #1 listen: 5432 (master node) | |
# pg, instance #2 listen: 5433 (replica node) | |
# external failover, promoting replica to master in case of failure | |
# passwordless auth for user web | |
# template1 database is accessible by user web | |
# | |
# haproxy will pass connection to postgresql master node: |
String.prototype.turkishtoEnglish = function () { | |
return this.replace('Ğ','g') | |
.replace('Ü','u') | |
.replace('Ş','s') | |
.replace('I','i') | |
.replace('İ','i') | |
.replace('Ö','o') | |
.replace('Ç','c') | |
.replace('ğ','g') | |
.replace('ü','u') |
# See https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian for more information and warning! | |
# Check first | |
/usr/lib/postgresql/11/bin/pg_upgrade \ | |
-b /usr/lib/postgresql/9.6/bin \ | |
-B /usr/lib/postgresql/11/bin \ | |
-d /var/lib/postgresql/9.6/main \ | |
-D /var/lib/postgresql/11/main \ | |
-o ' -c config_file=/etc/postgresql/9.6/main/postgresql.conf' \ | |
-O ' -c config_file=/etc/postgresql/11/main/postgresql.conf' \ |