How to recover my postgres database ?

On Thu, May 11, 2017 at 11:53 AM, Pierre Couderc <pierre@couderc.eu> wrote:

How can I safely repair, knowing that I have the pg_dumpall of last
night, but many dbs have changed today... ?

If pg_dumpall is your only backup mechanism then you've lost all changes
after the dump was taken. You'll need to restore from that backup and then
get pgbackrest (or another equivalent tool) up and running to protect
yourself going forward

--
{
"name" : "douglas j hunley",
"title" : "database engineer",
"email" : "douglas.hunley@openscg.com <doug.hunley@gmail.com>",
"mobile" : "+1 614 316 5079"
}

On 5/11/2017 9:53 AM, Pierre Couderc wrote:

I have broken my postgres database by typing :

psql <yyyy

instead of :

psql dbdb <yyyy

How can I safely repair, knowing that I have the pg_dumpall of last
night, but many dbs have changed today... ?

Thanks in advance

was there anything in the postgres database other than the default?

psql template1 -c "drop database postgres; create database postgres with
template template0"

should restore it to a virgin stock empty 'postgres'

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 05/11/2017 07:07 PM, Hunley, Douglas wrote:

On Thu, May 11, 2017 at 11:53 AM, Pierre Couderc <pierre@couderc.eu
<mailto:pierre@couderc.eu>> wrote:

How can I safely repair, knowing that I have the pg_dumpall of
last night, but many dbs have changed today... ?

If pg_dumpall is your only backup mechanism then you've lost all
changes after the dump was taken. You'll need to restore from that
backup and then get pgbackrest (or another equivalent tool) up and
running to protect yourself going forward

Mmm, there is some misunderstanding. I have lost nothing.
All my DBs are ok.
I am speaking of the specific "postgres" database that you connect to with
\c postgres
What is its use ? how to restore it ?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 05/11/2017 07:10 PM, John R Pierce wrote:

On 5/11/2017 9:53 AM, Pierre Couderc wrote:

I have broken my postgres database by typing :

psql <yyyy

instead of :

psql dbdb <yyyy

How can I safely repair, knowing that I have the pg_dumpall of last
night, but many dbs have changed today... ?

Thanks in advance

was there anything in the postgres database other than the default?

Not at my knowledge...

psql template1 -c "drop database postgres; create database postgres
with template template0"

should restore it to a virgin stock empty 'postgres'

Thank you, I do that. This is what I looked for !

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Pierre Couderc <pierre@couderc.eu> writes:

Mmm, there is some misunderstanding. I have lost nothing.
All my DBs are ok.
I am speaking of the specific "postgres" database that you connect to with
\c postgres
What is its use ? how to restore it ?

If you know that there should be nothing non-default in the postgres
database, it should be fine to just drop it and recreate it. It's
not magic, it's just a default landing point for connections.

If there is stuff you keep there, probably restoring that stuff
from your last backup is the best you're going to be able to do.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general