Обсуждение: overwrote THE 'postgres' database - how to recover
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
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.
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
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