search

BCDevOps / backup-container

A simple container for a simple backup strategy.
Apache License 2.0
39 stars 56 forks source link

Add more documentation on ON_ERROR_STOP #56

Closed garywong-bc closed 11 months ago

garywong-bc commented 4 years ago

In the case of restores failing due to data issues, such as a duplicate value for example:

DETAIL:  Key (id)=(2078689) is duplicated.

The work-around is to simply re-run the restore manually, step by step, but set:

ON_ERROR_STOP=0
garywong-bc commented 4 years ago

I can probably do this.. here's a note of how I did it (with Wade's help):

  1. Set variables for your context:

    sh-4.2$ export _portArg="-p 5432"
sh-4.2$ export _database=eaofider
sh-4.2$ export _username=eaofider
sh-4.2$ export _fileName=/backups/weekly/2020-06-28/eaofider-postgresql-eaofider_2020-06-28_01-00-00.sql.gz

  2. Run steps from the specific DB plugin file (PostgreSQL in this example):

sh-4.2$ psql -h "${_hostname}" ${_portArg} -ac "DROP DATABASE \"${_database}\";"
sh-4.2$ psql -h "${_hostname}" ${_portArg} -ac "CREATE DATABASE \"${_database}\";"
sh-4.2$ psql -h "${_hostname}" ${_portArg} -ac "GRANT ALL ON DATABASE \"${_database}\" TO \"${_username}\";"
sh-4.2$ gunzip -c "${_fileName}" | psql -v ON_ERROR_STOP=0 -x -h "${_hostname}" ${_portArg} -d "${_database}"
garywong-bc commented 4 years ago

@WadeBarnes if the above is correct, and you agree with me adding it as a PR to https://github.com/BCDevOps/backup-container/blob/master/docs/TipsAndTricks.md then lemme know and I'll do it while it's fresh on my mind.

WadeBarnes commented 4 years ago

@garywong-bc, Yes, that looks correct. However, I'm thinking we can do one better for this particular issue. I'm thinking we should add an "ignore errors" option of some sort to the restore process. That way you can still use the script to attempt the restore.

Like this, where -e is the "ignore errors" flag and would set ON_ERROR_STOP=0 (in the postges instance) automatically:

./backup.sh -e -r eaofider-postgresql:5432/eaofider -f /backups/weekly/2020-06-28/eaofider-postgresql-eaofider_2020-06-28_01-00-00.sql.gz

Thoughts?

garywong-bc commented 4 years ago

Like the concept, not loving the -e parameter as I'm used to that for other uses. What about -I? @WadeBarnes

WadeBarnes commented 4 years ago

I was just using -e as an example. I'm happy settling on whatever "feels" right, -I works for me.

garywong-bc commented 4 years ago

'-I' sold! so I won't add this scenario to Tips and Tricks... Let's convert this issue to 'add -I' parameter that sets ON_ERROR_STOP=0. I may even take a crack at it if I have a mo'...

WadeBarnes commented 4 years ago

Already on it. Will PR shortly.

WadeBarnes commented 4 years ago

@garywong-bc, have a look at the PR and let me know what you think. Feel free to recommend or make changes if you like.