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
# 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
postgres=# \d usersTable "public.users"Column | Type | Collation | Nullable | Default--------------+--------------------------------+-----------+----------+---------firstname | character varying(300) | | |lastname | character varying(300) | | |name | character varying(300) | | |createdat | timestamp(6) without time zone | | |emailaddress | character varying(300) | | |lockout | boolean | | |status | numeric(30,0) | | |lockout_date | timestamp(6) without time zone | | |Indexes:"users_at_idx_1" btree (lockout, status, createdat DESC NULLS LAST)
SELECT
*
FROM
users
WHERE
lockout IN (true)
AND status IN (3, 4)
AND createdat IN ('2023-05-18 00:00:00', '2023-06-19 00:00:00');Index Scan using users_at_idx_1 on public.users (cost=0.56..24879.59 rows=7255 width=72) (actual time=0.132..33.704 rows=3686 loops=1)
Output: firstname, lastname, name, createdat, emailaddress, lockout, status, lockout_date
Index Cond: ((users.lockout = true) AND (users.status = ANY ('{3,4}'::numeric[])) AND (users.createdat = ANY ('{"2023-05-18 00:00:00","2023-06-19 00:00:00"}'::timestamp without time zone[])))
Buffers: shared hit=48 read=3653
Planning:
Buffers: shared hit=53
Planning Time: 0.584 ms
Execution Time: 34.102 msA set of GIN-specific operators that can be used in a query depends on the operator class specified at index creation. For JSONB, supported classes are:jsonb_ops — the default operator class, which provides two categories of operators: checking the existence of keys and checking the existence of values based on JSONPath expressions or key-value pairs.jsonb_path_ops — provides only the latter category and offers better performance for these operators.
Create a GIN index on the JSONB column using the jsonb_path_ops operator class. This limits the operator to only @>, but that operator allows equality comparisons on any number of properties located at different levels within the JSONB document in a single WHERE criteria.
CREATE TABLE sample_jsonb (id serial NOT NULL PRIMARY KEY, data jsonb);
INSERT INTO sample_jsonb (data) VALUES
('{"name":"First", "count":12, "orderDate":"2022-03-03T12:14:01", "extra":"some text"}');
INSERT INTO sample_jsonb (data) VALUES
('{"name": "Second", "count": 23, "orderDate": "2022-04-03T15:17:01"}');
CREATE INDEX sample_jsonb_path_ops_idx
ON sample_jsonb USING GIN (data jsonb_path_ops);
-- select all rows where the name property is equal to "First" a
SELECT * FROM sample_jsonb WHERE data @> '{"name":"First"}'::jsonb
-- select all rows where count is greater than 15
SELECT * FROM sample_jsonb WHERE data @@ '$.count > 15'CREATE INDEX gin_data ON books_data using gin(data);
EXPLAIN ANALYZE SELECT * FROM books_data WHERE data @> '{"author" : "Mark Figueroa"}';