Thread: [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 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",
"name" : "douglas j hunley",
"title" : "database engineer",
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:
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 ?
Mmm, there is some misunderstanding. I have lost nothing.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
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