Re: [GENERAL] How to recover my postgres database ?

On 5/11/2017 9:53 AM, Pierre Couderc wrote:
> I have the pg_dumpall of last  night, but many dbs have changed
> today... ?

suggestion in the future, instead of simply pg_dumpall, where all your
databases are in one opaque lump, try something like...

#!/bin/bash
#
d=`date +\%a`
dst=/home2/backups/pgsql
/usr/pgsql-9.3/bin/pg_dumpall --globals-only | gzip >
$dst/pgdumpall.globals.$d.sql.gz
for i in $(psql -tc "select datname from pg_database where not
datistemplate"); do pg_dump -Fc -f $dst/pgdump.$i.$d.dump $i
         done


which uses pg_dumpall to dump the globals only into one file, then uses
pg_dump -Fc to create compressed format dumps of each individual
database, these can be selectively restored with pg_restore (for
instance, you could restore just one table, or schema only, or data
only, etcetc).    that script is setup to create a different set of
files for each day of the week, so you have 7 days backup history,
change the parameter of the d=`date...  line if you want a different
backup rotation scheme, and of course, dst is the destination



--
john r pierce, recycling bits in santa cruz