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.
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
What's the worst PostgreSQL configuration mistake you've seen in production?I'll start: shared_buffers = 128 MB on a 64 GB server.Default PostgreSQL configuration. Running in production. For two years. On a database serving 50,000 queries per second.The database was using 128 MB of its own cache and relying entirely on the operating system's page cache for everything else. It worked — PostgreSQL is remarkably resilient — but it was leaving enormous performance on the table.Changed shared_buffers to 16 GB (25% of RAM), adjusted effective_cache_size to 48 GB, and query response times dropped 40% overnight.Other configuration horrors I've seen:• 𝗺𝗮𝘅_𝗰𝗼𝗻𝗻𝗲𝗰𝘁𝗶𝗼𝗻𝘀 = 𝟭𝟬𝟬𝟬 with no connection pooler. Each connection using 10 MB of RAM. Server running out of memory under load.• 𝗿𝗮𝗻𝗱𝗼𝗺_𝗽𝗮𝗴𝗲_𝗰𝗼𝘀𝘁 = 𝟰.𝟬 on an NVMe SSD. The planner thought random reads were 4x more expensive than sequential reads, avoiding index scans that would have been faster. Should have been 1.1.• 𝘄𝗼𝗿𝗸_𝗺𝗲𝗺 = 𝟰 𝗠𝗕 on an analytics workload. Every complex query spilling to disk for sorts and hash joins. Changed to 256 MB and query times dropped from minutes to seconds.• 𝗮𝘂𝘁𝗼𝘃𝗮𝗰𝘂𝘂𝗺 = 𝗼𝗳𝗳. Yes, someone turned it off. The table bloat was spectacular.The PostgreSQL defaults are intentionally conservative. They're designed to run on a Raspberry Pi, not to perform well on your production server. Five settings — shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, random_page_cost — capture 80% of the performance gains.
