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
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.... |