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.
pg_cron is a PostgreSQL extension that allows scheduling of SQL queries to run at specific times, similar to cron jobs in Linux.
Açıklaması şöylepg_combinebackup /backups/fullbackup/ /backups/incr_backup1/ -o /combinebackup/
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.
/usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/17/data/ -l logfile stop
chmod 700 /combinebackup
/usr/pgsql-17/bin/pg_ctl -D /combinebackup/ -l /combinebackup/logfile start
SHOW data_directory; data_directory ---------------- /combinebackup (1 row)
/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.*/
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. */
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!
It is recommended in the docs: If (RAM > 1GB) shared_buffers = 25%Official Docs: Values larger than 40% of RAM might NOT Help
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
shared_buffers = 256MB effective_cache_size = 1GB
# Memory settingsshared_buffers = 16GBwork_mem = 64MBmaintenance_work_mem = 2GBeffective_cache_size = 48GB# Connection settingsmax_connections = 500# Checkpoint settingscheckpoint_segments = 32checkpoint_completion_target = 0.9# WAL settingswal_buffers = 16MBmax_wal_size = 4GB# Logging settingslog_min_duration_statement = 5000 # Log queries taking longer than 5 seconds
