Обсуждение: overwrote THE 'postgres' database - how to recover

Поиск
Список
Период
Сортировка

overwrote THE 'postgres' database - how to recover

От
"James B. Byrne"
Дата:
I was experimenting with creating a copy of an existing database from
a pg_dump sql backup file.  I made an misake in failing to delimit an
environment variable and in consequence I issued this command as user
postgres:

I should have used:

gunzip < "$APP_DBNAME".pgsql.gz | pg_restore --create --clean
--user=postgres --dbname="$APP_DBNAME"_copy  &&  vacuumdb
--user=postgres --full --analyze "$APP_DBNAME"_copy

But, instead I did this:

gunzip < $APP_DBNAME.pgsql.gz | pg_restore --create --clean
--user=postgres --dbname=$APP_DBNAME_copy  &&  vacuumdb
--user=postgres --full --analyze $APP_DBNAME_copy

which resulted in this:

gunzip < hll_redmine.pgsql.gz  |  pg_restore --create --clean
--user=postgres --dbname=  &&  vacuumdb --user=postgres --full
--analyze

Which caused the postgres database to be overwritten with
hll_redmine.pgsql.gz.  Is there an easy way to get this back or do I
have to reinitialise the whole thing?






-- 
***          e-Mail is NOT a SECURE channel          ***
        Do NOT transmit sensitive data via e-Mail
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3




Re: overwrote THE 'postgres' database - how to recover

От
Guillaume Lelarge
Дата:
Le mer. 4 sept. 2019 à 18:51, James B. Byrne <byrnejb@harte-lyne.ca> a écrit :
I was experimenting with creating a copy of an existing database from
a pg_dump sql backup file.  I made an misake in failing to delimit an
environment variable and in consequence I issued this command as user
postgres:

I should have used:

gunzip < "$APP_DBNAME".pgsql.gz | pg_restore --create --clean
--user=postgres --dbname="$APP_DBNAME"_copy  &&  vacuumdb
--user=postgres --full --analyze "$APP_DBNAME"_copy

But, instead I did this:

gunzip < $APP_DBNAME.pgsql.gz | pg_restore --create --clean
--user=postgres --dbname=$APP_DBNAME_copy  &&  vacuumdb
--user=postgres --full --analyze $APP_DBNAME_copy

which resulted in this:

gunzip < hll_redmine.pgsql.gz  |  pg_restore --create --clean
--user=postgres --dbname=  &&  vacuumdb --user=postgres --full
--analyze

Which caused the postgres database to be overwritten with
hll_redmine.pgsql.gz.  Is there an easy way to get this back or do I
have to reinitialise the whole thing?


Well, you can connect to some database (but not postgres), drop the postgres one, and create it again.


--
Guillaume.

Re: overwrote THE 'postgres' database - how to recover

От
Luca Ferrari
Дата:
On Wed, Sep 4, 2019 at 9:52 PM Guillaume Lelarge <guillaume@lelarge.info> wrote:
>> gunzip < hll_redmine.pgsql.gz  |  pg_restore --create --clean
>> --user=postgres --dbname=  &&  vacuumdb --user=postgres --full
>> --analyze
>>
>> Which caused the postgres database to be overwritten with
>> hll_redmine.pgsql.gz.  Is there an easy way to get this back or do I
>> have to reinitialise the whole thing?
>>
>
> Well, you can connect to some database (but not postgres), drop the postgres one, and create it again.

I would connect to template1, alter database postgres rename to
<whatever> and then create a new postgres database.
It is interesting to see that pg_restore defaults to username database
even if there's a dbname command line option. I didn't know that (and
I'm not sure is something "cool").

Luca



Re: overwrote THE 'postgres' database - how to recover

От
Jeff Janes
Дата:
On Wed, Sep 4, 2019 at 12:51 PM James B. Byrne <byrnejb@harte-lyne.ca> wrote:
I was experimenting with creating a copy of an existing database from
a pg_dump sql backup file.  I made an misake in failing to delimit an
environment variable and in consequence I issued this command as user
postgres:

I should have used:

gunzip < "$APP_DBNAME".pgsql.gz | pg_restore --create --clean
--user=postgres --dbname="$APP_DBNAME"_copy  &&  vacuumdb
--user=postgres --full --analyze "$APP_DBNAME"_copy

But, instead I did this:

gunzip < $APP_DBNAME.pgsql.gz | pg_restore --create --clean
--user=postgres --dbname=$APP_DBNAME_copy  &&  vacuumdb
--user=postgres --full --analyze $APP_DBNAME_copy

which resulted in this:

gunzip < hll_redmine.pgsql.gz  |  pg_restore --create --clean
--user=postgres --dbname=  &&  vacuumdb --user=postgres --full
--analyze

If this was an SQL backup file, this should have failed with:
pg_restore: error: input file appears to be a text format dump. Please use psql.

pg_restore does not accept SQL backup files as input.  It accepts the custom backup format, is that what you used?
 

Which caused the postgres database to be overwritten with
hll_redmine.pgsql.gz.  Is there an easy way to get this back or do I
have to reinitialise the whole thing?


pg_restore with --create and --clean would drop and recreate whatever database was named inside the custom dump file.  The argument to --dbname is only used as the database to connect to in order to do this drop and create of the one named inside the dump file.  So this should not have overwritten the "postgres" database.  (Unless that was the database which was dumped, and since you can't drop the database you are connected to, that shouldn't have worked anyway) 

 The "postgres" database is usually just used for administrative tasks, and has no interesting contents.  If you did somehow manage to overwrite it, you might not have to do anything.

Cheers,

Jeff