Part 16 - PostgreSQL : What is write ahead logging ( WAL ). скачать в хорошем качестве

Part 16 - PostgreSQL : What is write ahead logging ( WAL ).

WHAT IS A WAL WAL is short for Write Ahead Log. Transaction performed on the database is first written out as a WAL file.T Then applied to the actual on-disk table data files. WAL files are strictly sequential "replay log" of changes. So are they actual files/location? Located under data_directory as pg_wal /var/lib/pgsql/12/data/pg_wal How to control WAL? wal_keep_segments (Specifies the minimum number of past log file segments kept in the pg_xlog directory) max_wal_size (Maximum size to let the WAL grow as soft limit ) ARCHIVING WALS copying out generated WAL files is called archiving. THE ARCHIVE COMMAND The script is set using the archive_command configuration setting Copy the file to a safe location (like a mounted NFS volume) archive_command = 'cp %p /mnt/nfs/%f' Not overwriting files is a good practice archive_command = 'test ! -f /mnt/nfs/%f && cp %p /mnt/nfs/%f' Copy to S3 bucket archive_command = 's3cmd put %p s3://BUCKET/path/%f' Copy to Google Cloud bucket archive_command = 'gsutil cp %p gs://BUCKET/path/%f' An external script archive_command = '/opt/scripts/archive_wal %p' THE WAL LEVEL minimal WAL level. information needed to recover from a crash or immediate shutdown archive (or replica in version 9.6 and above) enough information to allow the archival (and restoration) of WAL files. replica information required to run read-only queries on a standby server. logical extract logical change sets from WAL. The WAL level must be archive or higher. wal_level = archive ARCHIVE TIMEOUT no activity then generate a wal after mentioned interval of time. archive_timeout = 1h How to CONTROL size and number of THE WAL FILES This is a soft upper limit on the total size of WAL files. max_wal_size = 1GB Keep around at least these many WAL files (aka segments). wal_keep_segments = 10 THE ARCHIVAL SETTINGS The WAL level must be archive or higher. wal_level = replica This is a soft upper limit on the total size of WAL files. max_wal_size = 1GB alter system set max_wal_size = '1 GB'; Keep around at least these many WAL files (aka segments). wal_keep_segments = 10 alter system set wal_keep_segments = 10; The archive_mode must be set to on for archiving to happen. archive_mode = on alter system set archive_mode = on; This is the command to invoke for each WAL file to be archived. archive_command = '/postgres/logs/archive_wal %p' alter system set archive_command = '/postgres/logs/archive_wal%f'; alter system set archive_command = 'cp %p /postgres/logs/archive_wal%f'; 'cp %p /Archive/Location/%f' Ensure there is at least one WAL file for each "archive_timeout" duration. archive_timeout = 1h alter system set archive_timeout= '1 h'; sudo systemctl restart postgresql-12 Verification. show wal_level; show max_wal_size; show wal_keep_segments; show archive_mode; show archive_command; show archive_timeout;