dincosman’s gists

dincosman / enable_tls_on_pgbackrest

Last active April 28, 2024 21:51

Configure TLS on pgbackrest repo server and postgresql databases

	[root@posbckp01 ~]# cd /etc/ssl/
	[root@posbckp01 ssl]# mkdir pgbackrest
	[root@posbckp01 ssl]# cd pgbackrest/
	[root@posbckp01 ~]# cat csr_details.txt
	[ req ]
	default_bits = 2048
	default_md = sha256
	req_extensions = v3_req
	distinguished_name = dn
	prompt = no

dincosman / etcd_config_with_ssl.txt

Last active April 28, 2024 21:50

Configure etcd to use SSL

dincosman / haproxy.cfg

Last active April 28, 2024 21:36

Sample HAProxy Configuration communicating with Patroni REST APIs over SSL

	[root@etcd02 ~]# cat /etc/ssl/etcd/server.crt /etc/ssl/etcd/server.key >> /etc/ssl/etcd/haproxy_combined.crt

	[root@etcd02 ~]# vi /etc/haproxy/haproxy.cfg
	global
	maxconn 4096

	defaults
	log global
	mode tcp
	timeout client 30m

dincosman / patroni_local_parameters_for_RCE.txt

Last active April 27, 2024 16:56

Parameters should be set in the local configuration section of the Patroni configuration against RCE

	[postgres@posvt01 ~]$ vi /etc/patroni.yml
	...
	postgresql
	...
	connect_address: posvt01.localdomain:3535
	...
	parameters:
	unix_socket_directories: '/var/run/postgresql'
	archive_command: "pgbackrest --stanza=pg-cluster1 archive-push %p"
	restore_command: "pgbackrest --stanza=pg-cluster1 archive-get %f %p"

dincosman / configure_patroni_ssl.txt

Last active May 1, 2024 21:26

Configure Patroni REST APIS for SSL encryption

	#-- Some security related parameters -- set on all postgresql servers
	[postgres@posvt01 ~]$ vi /etc/patroni.yml
	...
	restapi:
	listen: posvt01.localdomain:3308
	connect_address: posvt01.localdomain:3308
	certfile: /etc/ssl/postgres/server.crt
	keyfile: /etc/ssl/postgres/server.key
	cafile: /etc/ssl/postgres/root.crt
	authentication:

dincosman / configure_ssl_for_postgresql.txt

Last active April 29, 2024 20:29

Enable TLS/SSL for your PostgreSQL database

	[postgres@posvt01 ~]# vi csr_details.txt
	[ req ]
	default_bits = 2048
	default_md = sha256
	req_extensions = v3_req
	distinguished_name = dn
	prompt = no

	[ dn ]
	C = TR

dincosman / set_parameter_spatial_accelerate.sql

Last active April 27, 2024 19:10

Set SPATIAL_VECTOR_ACCELERATION parameter

SQL> ALTER SYSTEM SET SPATIAL_VECTOR_ACCELERATION = TRUE;

dincosman / query_performance_beforeafter.sql

Last active April 26, 2024 23:58

Analyze performance of tracked queries before and after

	SQL> WITH queries_to_measure AS (
	SELECT DISTINCT
	sql_id
	FROM
	gv$sql
	WHERE
	parsing_schema_name = :app_user
	AND upper(sql_fulltext) LIKE '%SDO\_%' ESCAPE '\'
	), before_performance AS (
	SELECT

dincosman / reinit_patroni_after_upgrade.txt

Last active April 20, 2024 22:32

Reinitialize patroni cluster on other servers after upgrade and configure pgbackrest


	[postgres@posvt01 ~]$ patronictl -c /etc/patroni.yml remove pgcluster
	+ Cluster: pgcluster (7099446795009447890) -----+
	\| Member \| Host \| Role \| State \| TL \| Lag in MB \|
	+--------+------+------+-------+----+-----------+
	+--------+------+------+-------+----+-----------+
	Please confirm the cluster name to remove: pgcluster
	You are about to remove all information in DCS for pgcluster, please type: "Yes I am aware": Yes I am aware

	#-- Environment variables provided below are updated

dincosman / Upgrade_postgresql_database.txt

Last active April 20, 2024 22:30

Upgrade of Postgresql database using pg_upgrade

	[postgres@posvt01 pg_cron-main]# mkdir -p /mnt/postgres/pgdata/16
	[postgres@posvt01 pg_cron-main]# mkdir -p /mnt/postgres/pg_wal/16

	[root@posvt01 ~]# systemctl stop patroni
	[postgres@posvt01 ~]$ /usr/pgsql-16/bin/initdb -U postgres --data-checksums -E 'UTF8' -W --wal-segsize='32' \
	--waldir='/mnt/postgres/pg_wal/16' --lc-ctype='tr_TR.UTF-8' --lc-collate='tr_TR.UTF-8' --pgdata='/mnt/postgres/pgdata/16'

	The files belonging to this database system will be owned by user "postgres".
	This user must also own the server process.

Osman DINC dincosman