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.
