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

2017-05-11 Thread Tom Lane 
Pierre Couderc  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

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

2017-05-11 Thread Pierre Couderc 

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

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

2017-05-11 Thread John R Pierce 

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

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

2017-05-11 Thread Pierre Couderc 

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


On Thu, May 11, 2017 at 11:53 AM, Pierre Couderc > 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 ?

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

2017-05-11 Thread John R Pierce 

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

I have broken my postgres database by typing :

psql 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

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

2017-05-11 Thread Hunley, Douglas 
On Thu, May 11, 2017 at 11:53 AM, Pierre Couderc  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.hun...@openscg.com ",
  "mobile" : "+1 614 316 5079"
}

[GENERAL] How to recover my postgres database ?

2017-05-11 Thread Pierre Couderc 

I have broken my postgres database by typing :

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


Thanks in advance




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