Re: How to recover my postgres database ?

From: John R Pierce <pierce(at)hogranch(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: How to recover my postgres database ?
Date: 2017-05-11 17:18:30
Message-ID: 0ecb0068-8ef4-8974-e66e-8ebbf9823bac@hogranch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Pierre Couderc 2017-05-11 17:19:51 Re: How to recover my postgres database ?
Previous Message John McKown 2017-05-11 17:17:24 Re: Top posting....