Backup Postgresql Database

Here are the basic steps to backup and restore Postgresql database. At this point of writing, I am using Postgresql 10 and 11 on Centos Linux 8. 

In this article, backup database is inventory_system and restore of the backup is to another database that I will call cupboard.

Singles Database

Step 1: Login as postgres user

su - postgres

or 

sudo su postgres -

Step 2: Dump the database content to a file using the tool pg_dump

pg_dump inventory_system >  inventory_system-20210312.bak

or when database is located at IP 10.1.1.123 with port 5432

pg_dump -h 10.1.1.123 -p 5432 inventory_system >  inventory_system-20210312.bak

Step 3: Create the new database

createdb cupboard

Step 4: Restore the database file with psql

psql cupboard <  inventory_system-20210312.bak

On MS Windows 10 prompt that is provided through Laragon, the postgres user login is required

pg_dump -U postgres cm2020_1 > test.bak

Schedule backup

Cron job is suitable to automate the backup process mentioned above. To schedule a cron job, use the command

crontab -e

Then save the following instruction to run at midnight of every Saturday

0 0 * * 6 pg_dump -U postgres inventory_system > ~/postgres/backups/inventory_system-20210312.bak

Save and exit from the text editor.

Refer to Cron activities in the file /var/log/cron

For automated backup, there is a good note with the scripts at Postgresql wiki.