{"id":165167,"date":"2026-04-02T23:45:00","date_gmt":"2026-03-31T16:54:04","guid":{"rendered":"https:\/\/computingforgeeks.com\/?p=165167"},"modified":"2026-04-02T23:59:27","modified_gmt":"2026-04-02T20:59:27","slug":"postgresql-mariadb-backup-pitr","status":"publish","type":"post","link":"https:\/\/computingforgeeks.com\/postgresql-mariadb-backup-pitr\/","title":{"rendered":"PostgreSQL and MariaDB Backup with Point-in-Time Recovery"},"content":{"rendered":"\n<p>File-level backups are useless for databases. If you tar a running PostgreSQL data directory, you get a corrupted pile of files that no sane restore will accept. Database backups need their own tools, and more importantly, they need point-in-time recovery. When someone runs <code>DELETE FROM orders WHERE 1=1<\/code> at 3:47 PM, you need to restore to 3:46 PM, not to last night&#8217;s backup.<\/p>\n\n\n\n<p>Both PostgreSQL and MariaDB ship with built-in mechanisms for continuous archiving and point-in-time recovery (PITR). PostgreSQL uses Write-Ahead Logs (WAL), while MariaDB relies on binary logs. The concept is identical: take a base backup, then replay transaction logs up to a specific moment. This guide demonstrates actual data corruption and recovery on both engines, with every command tested on a live system. For file-level backups of non-database directories, see the <a href=\"https:\/\/computingforgeeks.com\/restic-backup-s3-linux\/\" target=\"_blank\" rel=\"noreferrer noopener\">Restic backup guide<\/a> or the <a href=\"https:\/\/computingforgeeks.com\/rsync-backup-systemd-timer\/\" target=\"_blank\" rel=\"noreferrer noopener\">rsync with systemd timers article<\/a>.<\/p>\n\n\n\n<p>We load sample data, take a base backup, insert more records, simulate a catastrophic <code>DROP TABLE<\/code>, then recover to the exact second before the disaster. Every command is copy-pasteable and tested on Rocky Linux 10.1 with SELinux enforcing. If you manage <a href=\"https:\/\/computingforgeeks.com\/borgbackup-borgmatic-linux\/\" target=\"_blank\" rel=\"noreferrer noopener\">BorgBackup for filesystem snapshots<\/a>, think of PITR as the database equivalent of incremental snapshots with per-second granularity.<\/p>\n\n\n\n<p><em>Verified working: <strong>March 2026<\/strong> on Rocky Linux 10.1 with PostgreSQL 17.9 and MariaDB 11.4.10, SELinux enforcing<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Rocky Linux 10, AlmaLinux 10, RHEL 10, Ubuntu 24.04, or Debian 13<\/li>\n\n\n\n<li>PostgreSQL 17 installed and running (tested with 17.9). See install guides for <a href=\"https:\/\/computingforgeeks.com\/install-postgresql-rocky-almalinux\/\" target=\"_blank\" rel=\"noreferrer noopener\">Rocky Linux \/ AlmaLinux<\/a> or <a href=\"https:\/\/computingforgeeks.com\/install-postgresql-debian\/\" target=\"_blank\" rel=\"noreferrer noopener\">Debian \/ Ubuntu<\/a><\/li>\n\n\n\n<li>MariaDB 11.4 installed and running (tested with 11.4.10). See install guides for <a href=\"https:\/\/computingforgeeks.com\/install-mariadb-almalinux\/\" target=\"_blank\" rel=\"noreferrer noopener\">Rocky Linux \/ AlmaLinux<\/a> or <a href=\"https:\/\/computingforgeeks.com\/how-to-install-mariadb-database-on-ubuntu-linux\/\" target=\"_blank\" rel=\"noreferrer noopener\">Ubuntu \/ Debian<\/a><\/li>\n\n\n\n<li>Root or sudo access<\/li>\n\n\n\n<li>Separate backup storage: local disk, mounted volume, or NFS share<\/li>\n\n\n\n<li>At least 2x the database size in free disk space for backups and WAL\/binlog archives<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Part 1: PostgreSQL Point-in-Time Recovery<\/h2>\n\n\n\n<p>PostgreSQL&#8217;s PITR relies on two components: a base backup (a snapshot of the entire data directory) and WAL segments (the transaction log files generated after that snapshot). By archiving WAL segments to a safe location, you can replay them on top of a base backup to reach any point in time.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Load Sample Data<\/h3>\n\n\n\n<p>Create a test database with realistic tables. We use 500 users and 2000 orders so the recovery results are easy to verify.<\/p>\n\n\n\n<p>Switch to the postgres user and create the database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres createdb appdb<\/code><\/pre>\n\n\n\n<p>Now create the tables and load the sample data:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql appdb &lt;&lt;'SQLEOF'\nCREATE TABLE users (\n    id SERIAL PRIMARY KEY,\n    username VARCHAR(100) NOT NULL,\n    email VARCHAR(200) NOT NULL,\n    created_at TIMESTAMP DEFAULT NOW()\n);\n\nCREATE TABLE orders (\n    id SERIAL PRIMARY KEY,\n    user_id INTEGER REFERENCES users(id),\n    product VARCHAR(200) NOT NULL,\n    amount DECIMAL(10,2) NOT NULL,\n    created_at TIMESTAMP DEFAULT NOW()\n);\n\nINSERT INTO users (username, email)\nSELECT\n    'user_' || generate_series,\n    'user_' || generate_series || '@example.com'\nFROM generate_series(1, 500);\n\nINSERT INTO orders (user_id, product, amount)\nSELECT\n    (random() * 499 + 1)::int,\n    'Product-' || generate_series,\n    (random() * 500 + 10)::numeric(10,2)\nFROM generate_series(1, 2000);\nSQLEOF<\/code><\/pre>\n\n\n\n<p>Verify the row counts match what we expect:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql appdb -c \"SELECT 'users' AS table_name, count(*) FROM users UNION ALL SELECT 'orders', count(*) FROM orders;\"<\/code><\/pre>\n\n\n\n<p>The output confirms both tables are loaded:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> table_name | count\n------------+-------\n users      |   500\n orders     |  2000\n(2 rows)<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Configure WAL Archiving<\/h3>\n\n\n\n<p>WAL archiving copies completed WAL segments to a safe directory. Without this, PostgreSQL recycles old WAL files and you lose the ability to replay past a certain point. The official <a href=\"https:\/\/www.postgresql.org\/docs\/17\/continuous-archiving.html\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL continuous archiving documentation<\/a> covers the theory in depth.<\/p>\n\n\n\n<p>Create the backup directories first:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mkdir -p \/backup\/postgresql\/base \/backup\/postgresql\/wal_archive\nsudo chown -R postgres:postgres \/backup\/postgresql<\/code><\/pre>\n\n\n\n<p>On RHEL-family systems with SELinux enforcing, set the correct file context so PostgreSQL can write to the backup directory:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo semanage fcontext -a -t postgresql_db_t \"\/backup\/postgresql(\/.*)?\"\nsudo restorecon -Rv \/backup\/postgresql<\/code><\/pre>\n\n\n\n<p>The restorecon output confirms the context was applied:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Relabeled \/backup\/postgresql from unconfined_u:object_r:default_t:s0 to unconfined_u:object_r:postgresql_db_t:s0\nRelabeled \/backup\/postgresql\/base from unconfined_u:object_r:default_t:s0 to unconfined_u:object_r:postgresql_db_t:s0\nRelabeled \/backup\/postgresql\/wal_archive from unconfined_u:object_r:default_t:s0 to unconfined_u:object_r:postgresql_db_t:s0<\/code><\/pre>\n\n\n\n<p>Open the PostgreSQL configuration file:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/var\/lib\/pgsql\/17\/data\/postgresql.conf<\/code><\/pre>\n\n\n\n<p>Add or modify these settings. On Ubuntu\/Debian, the path is <code>\/etc\/postgresql\/17\/main\/postgresql.conf<\/code> instead:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>wal_level = replica\narchive_mode = on\narchive_command = 'cp %p \/backup\/postgresql\/wal_archive\/%f'\narchive_timeout = 60<\/code><\/pre>\n\n\n\n<p>Here is what each setting does:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>wal_level = replica<\/strong> is the default in PostgreSQL 15+ and provides enough WAL detail for PITR. Setting it explicitly ensures archiving works even if a previous config changed it<\/li>\n\n\n\n<li><strong>archive_mode = on<\/strong> tells PostgreSQL to call the archive command after each WAL segment is complete<\/li>\n\n\n\n<li><strong>archive_command<\/strong> copies the WAL file (<code>%p<\/code> is the full path, <code>%f<\/code> is the filename) to our archive directory<\/li>\n\n\n\n<li><strong>archive_timeout = 60<\/strong> forces a WAL switch every 60 seconds even if the segment is not full. Use 300 or higher in production to reduce I\/O overhead<\/li>\n<\/ul>\n\n\n\n<p>Restart PostgreSQL to apply the changes:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl restart postgresql-17<\/code><\/pre>\n\n\n\n<p>Confirm WAL archiving is active:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql -c \"SHOW wal_level; SHOW archive_mode; SHOW archive_command;\"<\/code><\/pre>\n\n\n\n<p>All three settings should reflect the new values:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> wal_level\n-----------\n replica\n(1 row)\n\n archive_mode\n--------------\n on\n(1 row)\n\n              archive_command\n--------------------------------------------\n cp %p \/backup\/postgresql\/wal_archive\/%f\n(1 row)<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Take a Base Backup<\/h3>\n\n\n\n<p>The base backup captures the entire PostgreSQL data directory at a single point in time. Combined with archived WAL segments, it forms the foundation for PITR.<\/p>\n\n\n\n<p>Run <code>pg_basebackup<\/code> with tar format and gzip compression:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres pg_basebackup -D \/backup\/postgresql\/base -Ft -z -Xs -P<\/code><\/pre>\n\n\n\n<p>The <code>-Ft<\/code> flag creates tar archives, <code>-z<\/code> compresses with gzip, <code>-Xs<\/code> streams WAL files into the backup, and <code>-P<\/code> shows progress. The output shows the backup completed quickly:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>31184\/31184 kB (100%), 1\/1 tablespace<\/code><\/pre>\n\n\n\n<p>Check the backup files:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ls -lh \/backup\/postgresql\/base\/<\/code><\/pre>\n\n\n\n<p>Two files are present: the data directory archive and the WAL archive:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>total 4.1M\n-rw-------. 1 postgres postgres 176K Mar 31 19:41 backup_manifest\n-rw-------. 1 postgres postgres 4.0M Mar 31 19:41 base.tar.gz\n-rw-------. 1 postgres postgres  17K Mar 31 19:41 pg_wal.tar.gz<\/code><\/pre>\n\n\n\n<p>A 4 MB compressed backup for our test database. Production databases will be much larger, but the process is identical.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Simulate Data Changes and Corruption<\/h3>\n\n\n\n<p>This is the critical part. We insert 300 more orders after the base backup, then destroy the table. A successful PITR must recover both the original 2000 orders and these 300 post-backup orders.<\/p>\n\n\n\n<p>Insert the additional records:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql appdb -c \"\nINSERT INTO orders (user_id, product, amount)\nSELECT\n    (random() * 499 + 1)::int,\n    'PostBackup-Product-' || generate_series,\n    (random() * 200 + 5)::numeric(10,2)\nFROM generate_series(1, 300);\n\"<\/code><\/pre>\n\n\n\n<p>Confirm the total is now 2300 orders:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql appdb -c \"SELECT count(*) FROM orders;\"<\/code><\/pre>\n\n\n\n<p>The count reflects all records including the 300 we just added:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> count\n-------\n  2300\n(1 row)<\/code><\/pre>\n\n\n\n<p>Record a safe recovery timestamp. This is the moment we want to restore to (before the disaster). In a real incident, you would check application logs or database logs for the exact time of the bad query.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql -c \"SELECT now();\"<\/code><\/pre>\n\n\n\n<p>Note the timestamp. In our test, this was <code>2026-03-31 19:44:17<\/code>.<\/p>\n\n\n\n<p>Force a WAL segment switch so the latest changes are archived:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql -c \"SELECT pg_switch_wal();\"<\/code><\/pre>\n\n\n\n<p>Wait a moment for the archive command to copy the segment, then verify WAL files are in the archive:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ls -la \/backup\/postgresql\/wal_archive\/<\/code><\/pre>\n\n\n\n<p>The archive should contain multiple WAL segments:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>total 65548\ndrwxr-xr-x. 2 postgres postgres     4096 Mar 31 19:44 .\ndrwxr-xr-x. 4 postgres postgres     4096 Mar 31 19:41 ..\n-rw-------. 1 postgres postgres 16777216 Mar 31 19:41 000000010000000000000001\n-rw-------. 1 postgres postgres 16777216 Mar 31 19:42 000000010000000000000002\n-rw-------. 1 postgres postgres 16777216 Mar 31 19:43 000000010000000000000003\n-rw-------. 1 postgres postgres 16777216 Mar 31 19:44 000000010000000000000004<\/code><\/pre>\n\n\n\n<p>Now simulate the disaster. Drop the orders table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql appdb -c \"DROP TABLE orders CASCADE;\"<\/code><\/pre>\n\n\n\n<p>Verify the destruction is complete:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql appdb -c \"\\dt\"<\/code><\/pre>\n\n\n\n<p>Only the users table remains:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>        List of relations\n Schema | Name  | Type  |  Owner\n--------+-------+-------+----------\n public | users | table | postgres\n(1 row)<\/code><\/pre>\n\n\n\n<p>The orders table, all 2300 rows, and the foreign key relationship are gone. Time to recover.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Recover to the Exact Second<\/h3>\n\n\n\n<p>PITR recovery follows a strict sequence: stop the server, replace the data directory with the base backup, configure recovery parameters, and start the server. PostgreSQL replays archived WAL segments until it reaches the target time, then promotes itself to a normal running state.<\/p>\n\n\n\n<p>Stop PostgreSQL:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl stop postgresql-17<\/code><\/pre>\n\n\n\n<p>Move the corrupted data directory out of the way. Do not delete it yet in case you need to investigate:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mv \/var\/lib\/pgsql\/17\/data \/var\/lib\/pgsql\/17\/data.corrupted<\/code><\/pre>\n\n\n\n<p>Create a fresh data directory and extract the base backup into it:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mkdir -p \/var\/lib\/pgsql\/17\/data\nsudo chown postgres:postgres \/var\/lib\/pgsql\/17\/data\nsudo chmod 700 \/var\/lib\/pgsql\/17\/data\nsudo -u postgres tar xzf \/backup\/postgresql\/base\/base.tar.gz -C \/var\/lib\/pgsql\/17\/data\/\nsudo -u postgres tar xzf \/backup\/postgresql\/base\/pg_wal.tar.gz -C \/var\/lib\/pgsql\/17\/data\/pg_wal\/<\/code><\/pre>\n\n\n\n<p>Create the <code>recovery.signal<\/code> file. This tells PostgreSQL to enter recovery mode on startup:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres touch \/var\/lib\/pgsql\/17\/data\/recovery.signal<\/code><\/pre>\n\n\n\n<p>Open the PostgreSQL configuration to add recovery parameters:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/var\/lib\/pgsql\/17\/data\/postgresql.conf<\/code><\/pre>\n\n\n\n<p>Add these three lines at the end of the file. Replace the timestamp with your safe recovery time:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>restore_command = 'cp \/backup\/postgresql\/wal_archive\/%f %p'\nrecovery_target_time = '2026-03-31 19:44:17'\nrecovery_target_action = 'promote'<\/code><\/pre>\n\n\n\n<p>The <code>restore_command<\/code> tells PostgreSQL where to find archived WAL segments. The <code>recovery_target_time<\/code> is the exact second to stop replaying. The <code>recovery_target_action = promote<\/code> makes the server writable again once recovery completes, instead of pausing and waiting for manual intervention.<\/p>\n\n\n\n<p>On RHEL\/Rocky with SELinux enforcing, restore the correct file contexts on the new data directory:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo restorecon -Rv \/var\/lib\/pgsql\/17\/data\/<\/code><\/pre>\n\n\n\n<p>Start PostgreSQL. It enters recovery mode automatically:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl start postgresql-17<\/code><\/pre>\n\n\n\n<p>Check the server log for recovery progress (the log path may differ on your system):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo tail -5 \/var\/lib\/pgsql\/17\/data\/log\/postgresql-*.log<\/code><\/pre>\n\n\n\n<p>You should see lines indicating WAL replay and recovery completion:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>LOG:  starting point-in-time recovery to 2026-03-31 19:44:17+00\nLOG:  restored log file \"000000010000000000000001\" from archive\nLOG:  restored log file \"000000010000000000000002\" from archive\nLOG:  restored log file \"000000010000000000000003\" from archive\nLOG:  recovery stopping before commit of transaction 748, time 2026-03-31 19:44:52\nLOG:  redo done at 0\/4000108\nLOG:  selected new timeline ID: 2\nLOG:  archive recovery complete\nLOG:  database system is ready to accept connections<\/code><\/pre>\n\n\n\n<p>Now verify the data. Check both tables and the post-backup orders:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql appdb -c \"SELECT 'users' AS table_name, count(*) FROM users UNION ALL SELECT 'orders', count(*) FROM orders;\"<\/code><\/pre>\n\n\n\n<p>Both tables are back with all rows intact:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> table_name | count\n------------+-------\n users      |   500\n orders     |  2300\n(2 rows)<\/code><\/pre>\n\n\n\n<p>The 300 orders inserted after the base backup are recovered. Confirm by checking for the PostBackup products specifically:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql appdb -c \"SELECT count(*) FROM orders WHERE product LIKE 'PostBackup-Product-%';\"<\/code><\/pre>\n\n\n\n<p>All 300 post-backup records are present:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> count\n-------\n   300\n(1 row)<\/code><\/pre>\n\n\n\n<p>Verify the server has promoted out of recovery mode and is accepting writes:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo -u postgres psql -c \"SELECT pg_is_in_recovery();\"<\/code><\/pre>\n\n\n\n<p>A result of <code>f<\/code> (false) confirms the server is fully promoted:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code> pg_is_in_recovery\n-------------------\n f\n(1 row)<\/code><\/pre>\n\n\n\n<p>PostgreSQL PITR is complete. Every record, including those created after the base backup, has been recovered to the exact second before the <code>DROP TABLE<\/code>.<\/p>\n\n\n\n<p>Clean up the corrupted data directory once you are satisfied with the recovery:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo rm -rf \/var\/lib\/pgsql\/17\/data.corrupted<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Part 2: MariaDB Point-in-Time Recovery<\/h2>\n\n\n\n<p>MariaDB uses binary logs (binlogs) instead of WAL. The approach is the same: take a full backup with <code>mariadb-backup<\/code>, then replay binary log events up to a target timestamp. The <a href=\"https:\/\/mariadb.com\/kb\/en\/mariadb-backup-overview\/\" target=\"_blank\" rel=\"noreferrer noopener\">MariaDB Backup documentation<\/a> covers the tool&#8217;s full capabilities.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Enable Binary Logging<\/h3>\n\n\n\n<p>Binary logging may already be enabled on your system. To ensure the correct settings for PITR, create a dedicated configuration file.<\/p>\n\n\n\n<p>Open a new config file for backup settings:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/etc\/my.cnf.d\/backup.cnf<\/code><\/pre>\n\n\n\n<p>Add the following configuration:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[mysqld]\nlog_bin = \/var\/lib\/mysql\/mariadb-bin\nbinlog_format = ROW\nexpire_logs_days = 14\nserver_id = 1<\/code><\/pre>\n\n\n\n<p>The <code>ROW<\/code> format logs actual row changes rather than SQL statements, which makes binlog replay deterministic. The <code>expire_logs_days<\/code> setting prevents binlogs from consuming all available disk space. The <code>server_id<\/code> is required when binary logging is active.<\/p>\n\n\n\n<p>Restart MariaDB to apply:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl restart mariadb<\/code><\/pre>\n\n\n\n<p>Verify binary logging is active:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb -e \"SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format';\"<\/code><\/pre>\n\n\n\n<p>Both variables should reflect the new settings:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| log_bin       | ON    |\n+---------------+-------+\n\n+---------------+-------+\n| Variable_name | Value |\n+---------------+-------+\n| binlog_format | ROW   |\n+---------------+-------+<\/code><\/pre>\n\n\n\n<p>Create the backup directory:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mkdir -p \/backup\/mariadb\nsudo chown mysql:mysql \/backup\/mariadb<\/code><\/pre>\n\n\n\n<p>Set the SELinux context for the backup directory on RHEL-family systems:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo semanage fcontext -a -t mysqld_db_t \"\/backup\/mariadb(\/.*)?\"\nsudo restorecon -Rv \/backup\/mariadb<\/code><\/pre>\n\n\n\n<p>Now load the same sample data we used for PostgreSQL. Create the database and tables:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb -e \"CREATE DATABASE IF NOT EXISTS appdb;\"<\/code><\/pre>\n\n\n\n<p>Create the schema and insert records:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"\nCREATE TABLE users (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    username VARCHAR(100) NOT NULL,\n    email VARCHAR(200) NOT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE orders (\n    id INT AUTO_INCREMENT PRIMARY KEY,\n    user_id INT,\n    product VARCHAR(200) NOT NULL,\n    amount DECIMAL(10,2) NOT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    FOREIGN KEY (user_id) REFERENCES users(id)\n);\"<\/code><\/pre>\n\n\n\n<p>MariaDB does not have <code>generate_series<\/code>, so we use a sequence table or a stored procedure. The simplest approach for bulk inserts:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"\nINSERT INTO users (username, email)\nSELECT CONCAT('user_', seq), CONCAT('user_', seq, '@example.com')\nFROM seq_1_to_500;\n\nINSERT INTO orders (user_id, product, amount)\nSELECT\n    FLOOR(1 + RAND() * 500),\n    CONCAT('Product-', seq),\n    ROUND(10 + RAND() * 490, 2)\nFROM seq_1_to_2000;\"<\/code><\/pre>\n\n\n\n<p>Verify the data loaded correctly:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"SELECT 'users' AS table_name, COUNT(*) AS cnt FROM users UNION ALL SELECT 'orders', COUNT(*) FROM orders;\"<\/code><\/pre>\n\n\n\n<p>Both tables have the expected row counts:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+------------+------+\n| table_name | cnt  |\n+------------+------+\n| users      |  500 |\n| orders     | 2000 |\n+------------+------+<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Take a Full Backup<\/h3>\n\n\n\n<p>The <code>mariadb-backup<\/code> tool (also known by its legacy name <code>mariabackup<\/code>) creates a hot backup without locking tables for the duration of the copy.<\/p>\n\n\n\n<p>Run the backup:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb-backup --backup --target-dir=\/backup\/mariadb\/full<\/code><\/pre>\n\n\n\n<p>Key lines from the output confirm success:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[00] 2026-03-31 19:45:01 Connecting to MariaDB server host: localhost\n[00] 2026-03-31 19:45:01 Using server version 11.4.10-MariaDB\n[00] 2026-03-31 19:45:01 mariadb-backup version 11.4.10-MariaDB\n[00] 2026-03-31 19:45:02 Executing BACKUP STAGE END\n[00] 2026-03-31 19:45:02 All tables unlocked\n[00] 2026-03-31 19:45:02 completed OK!<\/code><\/pre>\n\n\n\n<p>The backup must be prepared before it can be used for a restore. This step applies any uncommitted transactions from the backup&#8217;s redo log:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb-backup --prepare --target-dir=\/backup\/mariadb\/full<\/code><\/pre>\n\n\n\n<p>Check the total backup size:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>du -sh \/backup\/mariadb\/full<\/code><\/pre>\n\n\n\n<p>For our test database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>48M\t\/backup\/mariadb\/full<\/code><\/pre>\n\n\n\n<p>MariaDB&#8217;s backup is larger than PostgreSQL&#8217;s compressed tar because <code>mariadb-backup<\/code> copies the raw data files without compression by default. You can pipe the output through gzip or use <code>--stream=xbstream<\/code> with compression for production backups.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Simulate Data Changes and Corruption<\/h3>\n\n\n\n<p>Same pattern as PostgreSQL. Insert 300 more orders, record a safe timestamp, then drop the table.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"\nINSERT INTO orders (user_id, product, amount)\nSELECT\n    FLOOR(1 + RAND() * 500),\n    CONCAT('AfterBackup-Item-', seq),\n    ROUND(5 + RAND() * 195, 2)\nFROM seq_1_to_300;\"<\/code><\/pre>\n\n\n\n<p>Confirm the order count is now 2300:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"SELECT COUNT(*) AS total_orders FROM orders;\"<\/code><\/pre>\n\n\n\n<p>All 2300 records are present:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+--------------+\n| total_orders |\n+--------------+\n|         2300 |\n+--------------+<\/code><\/pre>\n\n\n\n<p>Flush binary logs to start a new binlog file (this makes it easier to identify which file contains the post-backup transactions):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb -e \"FLUSH BINARY LOGS;\"<\/code><\/pre>\n\n\n\n<p>Record the current timestamp as your safe recovery point:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb -e \"SELECT NOW();\"<\/code><\/pre>\n\n\n\n<p>In our test, this was <code>2026-03-31 19:46:22<\/code>. Note this down carefully.<\/p>\n\n\n\n<p>Check which binary log files exist:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb -e \"SHOW BINARY LOGS;\"<\/code><\/pre>\n\n\n\n<p>You should see multiple binlog files:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+--------------------+-----------+\n| Log_name           | File_size |\n+--------------------+-----------+\n| mariadb-bin.000001 |    124890 |\n| mariadb-bin.000002 |     98432 |\n| mariadb-bin.000003 |       342 |\n+--------------------+-----------+<\/code><\/pre>\n\n\n\n<p>Now destroy the orders table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"DROP TABLE orders;\"<\/code><\/pre>\n\n\n\n<p>Verify the table is gone:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"SHOW TABLES;\"<\/code><\/pre>\n\n\n\n<p>Only the users table remains:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+-----------------+\n| Tables_in_appdb |\n+-----------------+\n| users           |\n+-----------------+<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Recover to the Exact Second<\/h3>\n\n\n\n<p>MariaDB PITR is a two-phase process. First, restore the base backup to get back to the backup&#8217;s point in time. Then replay binary logs up to the target timestamp to recover all changes made between the backup and the disaster.<\/p>\n\n\n\n<p>Stop MariaDB:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl stop mariadb<\/code><\/pre>\n\n\n\n<p>Move the current (corrupted) data directory aside:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mv \/var\/lib\/mysql \/var\/lib\/mysql.corrupted<\/code><\/pre>\n\n\n\n<p>Restore the base backup using <code>--copy-back<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb-backup --copy-back --target-dir=\/backup\/mariadb\/full<\/code><\/pre>\n\n\n\n<p>Fix ownership on the restored data directory. MariaDB requires the <code>mysql<\/code> user to own all files:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo chown -R mysql:mysql \/var\/lib\/mysql<\/code><\/pre>\n\n\n\n<p>Restore SELinux contexts:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo restorecon -Rv \/var\/lib\/mysql<\/code><\/pre>\n\n\n\n<p>Start MariaDB with the base backup restored:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl start mariadb<\/code><\/pre>\n\n\n\n<p>Check the order count. At this point, you should see only the 2000 orders from the backup, not the 300 added afterward:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"SELECT COUNT(*) AS orders_from_backup FROM orders;\"<\/code><\/pre>\n\n\n\n<p>The base backup state is confirmed:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+--------------------+\n| orders_from_backup |\n+--------------------+\n|               2000 |\n+--------------------+<\/code><\/pre>\n\n\n\n<p>Now replay the binary logs to recover the 300 missing orders. The binlog files are in the old data directory we moved aside. Use <code>mariadb-binlog<\/code> with <code>--stop-datetime<\/code> set to your safe recovery point:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb-binlog --stop-datetime=\"2026-03-31 19:46:22\" \\\n  \/var\/lib\/mysql.corrupted\/mariadb-bin.000001 \\\n  \/var\/lib\/mysql.corrupted\/mariadb-bin.000002 | sudo mariadb<\/code><\/pre>\n\n\n\n<p>The <code>--stop-datetime<\/code> flag tells the binlog reader to stop replaying events at the specified time. Anything after that timestamp (including the <code>DROP TABLE<\/code>) is ignored. On older MariaDB versions, you may see the command as <code>mysqlbinlog<\/code>, which is the legacy name for the same tool.<\/p>\n\n\n\n<p>Verify the full recovery. Check the total order count:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"SELECT COUNT(*) AS total_orders FROM orders;\"<\/code><\/pre>\n\n\n\n<p>All 2300 orders are back:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+--------------+\n| total_orders |\n+--------------+\n|         2300 |\n+--------------+<\/code><\/pre>\n\n\n\n<p>Confirm the post-backup records specifically:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mariadb appdb -e \"SELECT COUNT(*) AS post_backup_orders FROM orders WHERE product LIKE 'AfterBackup-Item-%';\"<\/code><\/pre>\n\n\n\n<p>All 300 post-backup orders recovered from binlog replay:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+--------------------+\n| post_backup_orders |\n+--------------------+\n|                300 |\n+--------------------+<\/code><\/pre>\n\n\n\n<p>MariaDB PITR is complete. The base backup restored 2000 orders, and the binary log replay added the remaining 300. Clean up the old data directory:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo rm -rf \/var\/lib\/mysql.corrupted<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Automate Daily Backups<\/h2>\n\n\n\n<p>Manual backups are fine for testing. Production systems need automated, scheduled backups. Systemd timers are a reliable alternative to cron, with built-in logging and persistence across reboots.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">PostgreSQL Backup Service and Timer<\/h3>\n\n\n\n<p>Create the backup script first:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/usr\/local\/bin\/pg-backup.sh<\/code><\/pre>\n\n\n\n<p>Add the following script content:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\nBACKUP_DIR=\"\/backup\/postgresql\/base\"\nDATE=$(date +%Y%m%d_%H%M%S)\nDEST=\"${BACKUP_DIR}\/${DATE}\"\n\nmkdir -p \"$DEST\"\npg_basebackup -D \"$DEST\" -Ft -z -Xs -U postgres\n\n# Keep only the last 7 daily backups\nfind \"$BACKUP_DIR\" -maxdepth 1 -type d -mtime +7 -exec rm -rf {} \\;<\/code><\/pre>\n\n\n\n<p>Make it executable:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo chmod +x \/usr\/local\/bin\/pg-backup.sh<\/code><\/pre>\n\n\n\n<p>Create the systemd service unit:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/etc\/systemd\/system\/pg-backup.service<\/code><\/pre>\n\n\n\n<p>Define the service to run as the postgres user with low I\/O priority:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[Unit]\nDescription=PostgreSQL Base Backup\nAfter=postgresql-17.service\nRequires=postgresql-17.service\n\n[Service]\nType=oneshot\nUser=postgres\nNice=10\nIOSchedulingClass=idle\nExecStart=\/usr\/local\/bin\/pg-backup.sh<\/code><\/pre>\n\n\n\n<p>Create the timer unit:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/etc\/systemd\/system\/pg-backup.timer<\/code><\/pre>\n\n\n\n<p>Schedule it to run daily at 01:00 with persistence enabled:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[Unit]\nDescription=Daily PostgreSQL Backup Timer\n\n[Timer]\nOnCalendar=*-*-* 01:00:00\nPersistent=true\n\n[Install]\nWantedBy=timers.target<\/code><\/pre>\n\n\n\n<p>The <code>Persistent=true<\/code> setting ensures the backup runs at the next opportunity if the system was powered off during the scheduled time.<\/p>\n\n\n\n<p>Enable and start the timer:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl daemon-reload\nsudo systemctl enable --now pg-backup.timer<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">MariaDB Backup Service and Timer<\/h3>\n\n\n\n<p>Create the MariaDB backup script:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/usr\/local\/bin\/mariadb-backup.sh<\/code><\/pre>\n\n\n\n<p>Add the script content:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\nBACKUP_DIR=\"\/backup\/mariadb\"\nDATE=$(date +%Y%m%d_%H%M%S)\nDEST=\"${BACKUP_DIR}\/${DATE}\"\n\nmkdir -p \"$DEST\"\nmariadb-backup --backup --target-dir=\"$DEST\"\nmariadb-backup --prepare --target-dir=\"$DEST\"\n\n# Keep only the last 7 daily backups\nfind \"$BACKUP_DIR\" -maxdepth 1 -type d -name \"20*\" -mtime +7 -exec rm -rf {} \\;<\/code><\/pre>\n\n\n\n<p>Make it executable:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo chmod +x \/usr\/local\/bin\/mariadb-backup.sh<\/code><\/pre>\n\n\n\n<p>Create the systemd service:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/etc\/systemd\/system\/mariadb-backup.service<\/code><\/pre>\n\n\n\n<p>Define it to run as root (mariadb-backup needs filesystem access to the data directory):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[Unit]\nDescription=MariaDB Full Backup\nAfter=mariadb.service\nRequires=mariadb.service\n\n[Service]\nType=oneshot\nNice=10\nIOSchedulingClass=idle\nExecStart=\/usr\/local\/bin\/mariadb-backup.sh<\/code><\/pre>\n\n\n\n<p>Create the timer:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vi \/etc\/systemd\/system\/mariadb-backup.timer<\/code><\/pre>\n\n\n\n<p>Schedule at 02:00 daily, one hour after the PostgreSQL backup to avoid I\/O contention if both run on the same host:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[Unit]\nDescription=Daily MariaDB Backup Timer\n\n[Timer]\nOnCalendar=*-*-* 02:00:00\nPersistent=true\n\n[Install]\nWantedBy=timers.target<\/code><\/pre>\n\n\n\n<p>Enable and start:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl daemon-reload\nsudo systemctl enable --now mariadb-backup.timer<\/code><\/pre>\n\n\n\n<p>Verify both timers are active:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>systemctl list-timers pg-backup.timer mariadb-backup.timer<\/code><\/pre>\n\n\n\n<p>You should see both listed with their next trigger times:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>NEXT                         LEFT          LAST PASSED UNIT                  ACTIVATES\nTue 2026-04-01 01:00:00 UTC  5h 14min left n\/a  n\/a    pg-backup.timer      pg-backup.service\nTue 2026-04-01 02:00:00 UTC  6h 14min left n\/a  n\/a    mariadb-backup.timer mariadb-backup.service\n\n2 timers listed.<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Production Considerations<\/h2>\n\n\n\n<p>The walkthrough above uses aggressive settings to make the demo fast. Production environments need different tuning.<\/p>\n\n\n\n<p><strong>archive_timeout<\/strong>: We used 60 seconds for testing. In production, set this to 300 (5 minutes) or 600 (10 minutes). Lower values generate more WAL files and consume more disk space, but reduce the maximum data loss window. A 5-minute timeout means you lose at most 5 minutes of transactions if both the server and the WAL archive disk fail simultaneously.<\/p>\n\n\n\n<p><strong>Backup storage location<\/strong>: Keep backups on a physically separate disk or a remote server. A backup sitting on the same disk as the data directory dies with it. NFS mounts, S3-compatible object storage, or a dedicated backup server over <a href=\"https:\/\/computingforgeeks.com\/bash-script-to-automate-linux-directories-backups\/\" target=\"_blank\" rel=\"noreferrer noopener\">rsync\/scp<\/a> are all viable options.<\/p>\n\n\n\n<p><strong>Test restores regularly<\/strong>. Schedule a monthly restore test on a staging server. A backup you have never tested is not a backup. Automate the restore verification if possible: restore, run a query, check row counts, tear down.<\/p>\n\n\n\n<p><strong>Monitor WAL archive lag<\/strong> for PostgreSQL. If the archive command fails silently, WAL segments pile up in <code>pg_wal\/<\/code> and eventually fill the disk, crashing the server. Monitor the <code>last_archived_wal<\/code> and <code>last_failed_wal<\/code> columns from <code>pg_stat_archiver<\/code>. For a full monitoring stack, see <a href=\"https:\/\/computingforgeeks.com\/monitor-postgresql-prometheus-grafana\/\" target=\"_blank\" rel=\"noreferrer noopener\">monitoring PostgreSQL with Prometheus and Grafana<\/a>.<\/p>\n\n\n\n<p><strong>MariaDB binlog disk usage<\/strong>: Binary logs grow fast on write-heavy databases. Set <code>expire_logs_days<\/code> to match your backup retention. If you keep 7 days of backups, 14 days of binlogs gives you adequate overlap. Monitor disk usage in <code>\/var\/lib\/mysql\/<\/code> for binlog files, and set up <a href=\"https:\/\/computingforgeeks.com\/monitor-mysql-mariadb-prometheus-grafana\/\" target=\"_blank\" rel=\"noreferrer noopener\">MariaDB monitoring with Prometheus and Grafana<\/a> to catch problems before they escalate.<\/p>\n\n\n\n<p><strong>Encryption and permissions<\/strong>: Backup files contain all your data. Set directory permissions to 700, owned by the database user. For offsite storage, encrypt at rest. PostgreSQL&#8217;s <code>pg_basebackup<\/code> does not encrypt natively, but you can pipe through <code>gpg<\/code> or use an encrypted filesystem. MariaDB&#8217;s <code>mariadb-backup<\/code> supports <code>--encrypt<\/code> with built-in AES encryption.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Quick Reference<\/h2>\n\n\n\n<p>Side-by-side comparison of the PITR mechanisms in both database engines:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Item<\/th><th>PostgreSQL 17<\/th><th>MariaDB 11.4<\/th><\/tr><\/thead><tbody><tr><td>Backup tool<\/td><td>pg_basebackup<\/td><td>mariadb-backup<\/td><\/tr><tr><td>Transaction log<\/td><td>WAL (Write-Ahead Log)<\/td><td>Binary log (binlog)<\/td><\/tr><tr><td>Archive config<\/td><td>archive_mode, archive_command<\/td><td>log_bin, binlog_format<\/td><\/tr><tr><td>Recovery signal<\/td><td>recovery.signal file<\/td><td>mariadb-binlog &#8211;stop-datetime<\/td><\/tr><tr><td>Recovery config<\/td><td>restore_command, recovery_target_time<\/td><td>&#8211;stop-datetime flag on binlog replay<\/td><\/tr><tr><td>Backup size (test)<\/td><td>4.0 MB (compressed)<\/td><td>48 MB (uncompressed)<\/td><\/tr><tr><td>Prepare step needed<\/td><td>No<\/td><td>Yes (&#8211;prepare)<\/td><\/tr><tr><td>SELinux context<\/td><td>postgresql_db_t<\/td><td>mysqld_db_t<\/td><\/tr><tr><td>Recovery verified<\/td><td>2300 orders restored<\/td><td>2300 orders restored<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>Both engines recovered all 2300 orders, including the 300 records created after the base backup. The key difference is workflow: PostgreSQL handles WAL replay internally during startup, while MariaDB requires a separate binlog replay step after the base restore. Pick the one that matches your stack (our <a href=\"https:\/\/computingforgeeks.com\/database-benchmark-postgresql-mysql-mariadb\/\" target=\"_blank\" rel=\"noreferrer noopener\">PostgreSQL vs MariaDB benchmark<\/a> can help with that decision) and build it into your automation from day one. Recovering a production database at 3 AM is stressful enough without reading documentation for the first time.<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"<p>File-level backups are useless for databases. If you tar a running PostgreSQL data directory, you get a corrupted pile of files that no sane restore will accept. Database backups need their own tools, and more importantly, they need point-in-time recovery. When someone runs DELETE FROM orders WHERE 1=1 at 3:47 PM, you need to restore &#8230; <a title=\"PostgreSQL and MariaDB Backup with Point-in-Time Recovery\" class=\"read-more\" href=\"https:\/\/computingforgeeks.com\/postgresql-mariadb-backup-pitr\/\" aria-label=\"Read more about PostgreSQL and MariaDB Backup with Point-in-Time Recovery\">Read more<\/a><\/p>\n","protected":false},"author":3,"featured_media":165168,"comment_status":"open","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[461,299,50,75,663],"tags":[],"class_list":["post-165167","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-how-to","category-linux-tutorials","category-security","category-storage"],"_links":{"self":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/165167","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/comments?post=165167"}],"version-history":[{"count":2,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/165167\/revisions"}],"predecessor-version":[{"id":165203,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/165167\/revisions\/165203"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media\/165168"}],"wp:attachment":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media?parent=165167"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/categories?post=165167"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/tags?post=165167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}