Subject: Restoring database from backup

From: Rich Shepard <[email-masked]>
Date: Fri, 13 Dec 2024 10:21:03 -0800 (PST)

I made a careless error this morning and want to restore the database from
yesterday's backup, `bustrac-2024-12-12.sql'. If I run

psql -d bustrac -f bustrac-2024-12-12.sql

will this restore the database to yesterday's status without first
deleting/removing the FUBAR'd one?

TIA,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 13 Dec 2024 10:31:49 -0800

> deleting/removing the FUBAR'd one?

This needs more information:

1) Have you backed up your database at the current state?

2) What command did you use to create bustrac-2024-12-12.sql?

-- 
Adrian Klaver
[email-masked]

---

From: Rich Shepard <[email-masked]>
Date: Fri, 13 Dec 2024 11:18:13 -0800 (PST)

> 1) Have you backed up your database at the current state?

Adrian,

No, the current state is FUBAR'd. The cron backup script runs each night at
11:15 p.m.

> 2) What command did you use to create bustrac-2024-12-12.sql?

!/usr/bin/bash
#
# This script pg_dump to save the database w/date stamp

cd /data1/database-backups/
pg_dump -d bustrac -c -f bustrac-$(date +%Y-%m-%d).sql 
cd

Thanks,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 13 Dec 2024 11:23:31 -0800

On 12/13/24 11:18 AM, Rich Shepard wrote:
> pg_dump -d bustrac -c -f bustrac-$(date +%Y-%m-%d).sql cd

Alright, from here:

https://www.postgresql.org/docs/current/app-pgdump.html

"-c
--clean

     Output commands to DROP all the dumped database objects prior to 
outputting the commands for creating them. This option is useful when 
the restore is to overwrite an existing database. If any of the objects 
do not exist in the destination database, ignorable error messages will 
be reported during restore, unless --if-exists is also specified.

     This option is ignored when emitting an archive (non-text) output 
file. For the archive formats, you can specify the option when you call 
pg_restore.
"

This means when you run the script with:

it will clean out the current corrupted objects and replace them with 
those in the backup file.