14 Ocak 2025 Salı

postgresql.conf Dosyası - Log Ayarları

log_min_duration_statement
Açıklaması şöyle
log_min_duration_statement: just logging the statements whose duration is more than this value in milliseconds. This is very useful for detecting slow queries.
Açıklaması şöyle
It sets the minimum execution time in milliseconds (ms) above which all statements will be logged.

The default value for the log_min_duration_statement parameter is -1, which disables logging statements.

Setting the PostgreSQL parameter log_min_duration_statement to 0 will print all statements' durations.
Örnek
Şöyle yaparız
# set log_min_duration_statement=1;
SET # show log_min_duration_statement; log_min_duration_statement ---------------------------- 1ms (1 row)
PostgreSQL log dosyalarındaki statement log cümleleri şöyledir
LOG: duration: 5.477 ms statement: insert into abc values(1); LOG: duration: 8.656 ms statement: insert into abc values(2);
log_statement
ALL, MOD,DDL değerlerini alabilir

Açıklaması şöyle
Most fintech companies, who are required to log all user and application activities, set the most verbose option log_statement=all. This is the easiest way to bloat the log files and invite storage issues if storage consumption is not monitored. Pg_audit can be a smarter way of logging user activities, where you can specify which class of activities you want to log, such as READ, WRITE, DDL, or FUNCTION

log_statement_sample_rate
Açıklama yaz

log_destination
Bu alanlar için Audit Trails yazısına bakabilirsiniz

log_destination Alanı
PostgreSQL 15 için açıklama şöyle
PostgreSQL supports several methods for logging server messages, including stderr (default), csvlog, and syslog. With this release, jsonlog is also supported which is convenient for exporting logs and debugging. In order to enable this, add the jsonlog under the log_destination inside the postgresql.conf file.

This will be a nice feature to have with exports to logging and monitoring tools like HoneyComb and DataDog etc.

Historical Table

Giriş
Yedek yani backup amaçlı değildir. Sadece eski veriyi farklı bir tabloya taşır

Örnek
Şöyle yaparız
> create table main_table_historical_data
as select * from main_table where create_date < '01-Jan-2020';
> delete from main_table where create_date < '01-Jan-2020';

31 Aralık 2024 Salı

pg_createsubscriber

Giriş
Açıklaması şöyle
The pg_createsubscriber command allows for easy transformation of physical replicas into logical replicas, making high-availability setups more manageable.

Best Practice: For large retail applications, using pg_createsubscriber helps in maintaining fail-safe backups without overhauling the infrastructure, providing continuous service even during maintenance.

24 Aralık 2024 Salı

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.
Örnek
Şöyle yaparız
SELECT cron.schedule('0 0 * * *', $$DELETE 

FROM cron.job_run_details 

WHERE end_time < now() - interval '10 days'$$);

15 Aralık 2024 Pazar

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. */

9 Aralık 2024 Pazartesi

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