Обсуждение: [GENERAL] How to recover my postgres database ?

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

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

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

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

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

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 !

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