{"id":6193,"date":"2026-08-18T07:00:00","date_gmt":"2026-08-18T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6193"},"modified":"2026-08-28T15:12:42","modified_gmt":"2026-08-28T12:12:42","slug":"pos-kanoyme-backup-vasi-dedomenon-postgresql-choris-t-2","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-backup-vasi-dedomenon-postgresql-choris-t-2\/","title":{"rendered":"How to Backup a PostgreSQL Database Without Using 3rd Party Tools"},"content":{"rendered":"

In this article we will see step by step how to do a backup without using 3rd party tools full backup and log backup so that we can do PITR <\/strong>(Point in Time Recovery) if needed in the future. Finally, we will see how we can automate the process and their maintenance through cronjobs.<\/p>\n\n\n\n

We should know that the corresponding transaction \/ archive logs in PostgreSQL are called WAL (Write Ahead Logs).<\/p>\n\n\n\n

The footsteps<\/h2>\n\n\n\n

First we will create the folders that will contain the backups and give them the permissions they need:<\/p>\n\n\n\n

sudo mkdir -p \/var\/lib\/postgresql\/backup\/full\nsudo mkdir -p \/var\/lib\/postgresql\/backup\/archived_wal\n\nsudo chown -R postgres:postgres \/var\/lib\/postgresql\/backup\nsudo chmod -R 700 \/var\/lib\/postgresql\/backup<\/pre>\n\n\n\n
Then if we want to have the Point in Time Recovery feature we will have to do it with the user postgres <\/strong>to enable archiving and declare their location:<\/p>\n\n\n\n
su postgres<\/pre>\n\n\n\n
vi \/var\/lib\/pgsql\/11\/data\/postgresql.conf<\/pre>\n\n\n\n
# - Archiving -\n\narchive_mode = on               # enables archiving; off, on, or always\n\narchive_command = 'cp %p \/var\/lib\/postgresql\/backup\/archived_wal\/%f'<\/code><\/pre>\n\n\n\n
As well as the wal_level<\/code>:<\/p>\n\n\n\n
wal_level = replica<\/code><\/pre>\n\n\n\n
At this point, you will need to restart the service:<\/p>\n\n\n\n
sudo systemctl restart postgresql-11<\/pre>\n\n\n\n
We create an executable file which will contain the script:<\/p>\n\n\n\n
vi \/usr\/local\/bin\/pg_full_backup.sh<\/pre>\n\n\n\n
In the script we declare the folder we created before for the full backups and the command pg_basebackup <\/strong>runs the process by extracting the files into a compressed archive. As a second step, the maintenance process runs which keeps the files for only 30 days:<\/p>\n\n\n\n
#!\/bin\/bash\nBACKUP_DIR=\"\/var\/lib\/postgresql\/backup\/full\"\nTIMESTAMP=$(date +%Y%m%d_%H%M%S)\nDEST=\"$BACKUP_DIR\/full_$TIMESTAMP\"\n\n#full backup\npg_basebackup -D \"$DEST\" -F t -z -P -X fetch\n\n#30 days retention\nfind \"$BACKUP_DIR\" -mindepth 1 -maxdepth 1 -type d -mtime +30 -exec rm -rf {} +<\/code><\/pre>\n\n\n\n
After creating it, we give it the correct permissions:<\/p>\n\n\n\n
sudo chown postgres:postgres \/usr\/local\/bin\/pg_full_backup.sh\nsudo chmod +x \/usr\/local\/bin\/pg_full_backup.sh<\/pre>\n\n\n\n
Correspondingly, we can create executables for WAL maintenance:<\/p>\n\n\n\n
vi \/usr\/local\/bin\/clean_wal.sh<\/pre>\n\n\n\n
#!\/bin\/bash\n\nWAL_DIR=\"\/var\/lib\/postgresql\/archived_wal\"\n\n#retention 30 days\nfind \"$WAL_DIR\" -type f -mtime +30 -exec rm -f {} \\;<\/code><\/pre>\n\n\n\n
sudo chown postgres:postgres \/usr\/local\/bin\/clean_wal.sh\nsudo chmod +x \/usr\/local\/bin\/clean_wal.sh<\/pre>\n\n\n\n
To add them to the cronjob scheduler, run the following:<\/p>\n\n\n\n
sudo -u postgres crontab -e<\/pre>\n\n\n\n
The following lines will essentially be executed every day at 22:00 and 23:00 respectively:<\/p>\n\n\n\n
0 23 * * * \/usr\/local\/bin\/pg_full_backup.sh >\/dev\/null 2>&1\n0 22 * * * \/usr\/local\/bin\/clean_wal.sh >\/dev\/null 2>&1<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
Differential \/ Incremental backup?<\/h2>\n\n\n\n
Until PostgreSQL 17 there was no way to do block level backup, so the only native solution was full backup and then the use of WAL, in older versions to be able to do this type of backup you had to use 3rd party tools such as pgBackRest<\/strong> and Bartender<\/strong>.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n