pg_cron Extension

Giriş

Açıklaması şöyle

pg_cron is a PostgreSQL extension that allows scheduling of SQL queries to run at specific times, similar to cron jobs in Linux.

pg_combinebackup komutu - Incremental Backup İçindir

Örnek

Önce full backup yapılır. Şöyle yaparız

pg_combinebackup /backups/fullbackup/ /backups/incr_backup1/ -o /combinebackup/

Açıklaması şöyle

In this command:

- /backups/fullbackup/ is the directory containing the full backup.

- /backups/incr_backup1/ is the first incremental backup directory.

- -o specifies the output directory where the combined backup will be stored.

Daha sonra PostgreSQL Service durdurulur. Şöyle yaparız

/usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/17/data/ -l logfile stop

Dizine okuma ve yazma hakkı veriririz. Şöyle yaparız

chmod 700 /combinebackup

PostgreSQL Service tekrar başlatılır. Şöyle yaparız

/usr/pgsql-17/bin/pg_ctl -D /combinebackup/ -l /combinebackup/logfile start

PostgreSQL Service tarafından kullanılan dizini kontrol etmek için şöyle yaparız

SHOW data_directory;
 data_directory
----------------
 /combinebackup
(1 row)

pg_basebackup komutu - Incremental Backup İçindir

Örnek

Önce full backup yapılır. Şöyle yaparız

/usr/pgsql-17/bin/pg_basebackup -D /backups/fullbackup

/* 
The `-D` flag specifies the destination directory for the backup. 
Ensure that the directory exists and has the necessary permissions.
*/

Sonra incremental backup yapılır. Şöyle yaparız

pg_basebackup - incremental=/backups/fullbackup/backup_manifest -D /backups/incr_backup1/

/*
The ` - incremental` flag specifies the location of the full backup's 
manifest file, and `-D` is again used to specify the directory for the 
incremental backup.
*/

PostgreSQL Shared Buffer

Giriş

Açıklaması şöyle

When you are sending a read or write request to Postgres, you are never interacting with the files directly. In order to read something, first those pages need to be loaded in shared_buffers! 

Proper Amount for Shared Buffer

Açıklaması şöyle

It is recommended in the docs: If (RAM > 1GB) shared_buffers = 25%

Official Docs: Values larger than 40% of RAM might NOT Help

Açıklaması şöyle

Pro tip: If you are setting a PostgreSQL or MySQL, don't use the default database settings because those are meant for personal computers or notebooks.

One example is the Buffer Pool size:

- For MySQL, increase the innodb_buffer_pool_size

- For PostgreSQL, increase shared_buffers and effective_cache_size to match your OS cache size

Örnek

postgresql.conf dosyasında şöyle yaparız

shared_buffers = 256MB
effective_cache_size = 1GB

PG_BUFFERCACHE Extension

Shared Buffer kullanımını görmek için kullanılabilir