Thread: Restoring database from backup
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
On 12/13/24 10:21 AM, Rich Shepard wrote: > 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? 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? > > TIA, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, 13 Dec 2024, Ron Johnson wrote: > It's a text file. Open it up, and see if there's a DROP DATABASE in there. Ron, No `drop database' but it drops constraints and tables. I could add a drop database to the backup script after restoring yesterday's status. Thanks, Rich
On 12/13/24 11:15 AM, Rich Shepard wrote: > On Fri, 13 Dec 2024, Ron Johnson wrote: > >> It's a text file. Open it up, and see if there's a DROP DATABASE in >> there. > > Ron, > > No `drop database' but it drops constraints and tables. I could add a drop > database to the backup script after restoring yesterday's status. Please answer the questions I asked earlier. > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, 13 Dec 2024, Adrian Klaver wrote: > This needs more information: > > 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
On Fri, 13 Dec 2024, Rich Shepard wrote: > #!/usr/bin/bash The line was separated. Rich
On 12/13/24 11:18 AM, Rich Shepard wrote: > On Fri, 13 Dec 2024, Adrian Klaver wrote: > >> This needs more information: >> >> 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 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: psql -d bustrac -f bustrac-2024-12-12.sql it will clean out the current corrupted objects and replace them with those in the backup file. > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, 13 Dec 2024, Adrian Klaver wrote: > 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: > > psql -d bustrac -f bustrac-2024-12-12.sql > > it will clean out the current corrupted objects and replace them with those > in the backup file. Adrian, That's what I thought since I wrote the script based on reading pgdump a long time ago. Thanks for confirming! I'll fix my carelessness now. Carpe weekend, Rich