pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedoverwrote THE 'postgres' database - how to recover
4+ messages / 4 participants
[nested] [flat]
* overwrote THE 'postgres' database - how to recover
@ 2019-09-04 16:51 James B. Byrne <byrnejb@harte-lyne.ca>
2019-09-04 19:52 ` Re: overwrote THE 'postgres' database - how to recover Guillaume Lelarge <guillaume@lelarge.info>
2019-09-06 13:55 ` Re: overwrote THE 'postgres' database - how to recover Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: James B. Byrne @ 2019-09-04 16:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: overwrote THE 'postgres' database - how to recover
2019-09-04 16:51 overwrote THE 'postgres' database - how to recover James B. Byrne <byrnejb@harte-lyne.ca>
@ 2019-09-04 19:52 ` Guillaume Lelarge <guillaume@lelarge.info>
2019-09-05 07:01 ` Re: overwrote THE 'postgres' database - how to recover Luca Ferrari <fluca1978@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Guillaume Lelarge @ 2019-09-04 19:52 UTC (permalink / raw)
To: byrnejb@harte-lyne.ca; +Cc: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: overwrote THE 'postgres' database - how to recover
2019-09-04 16:51 overwrote THE 'postgres' database - how to recover James B. Byrne <byrnejb@harte-lyne.ca>
2019-09-04 19:52 ` Re: overwrote THE 'postgres' database - how to recover Guillaume Lelarge <guillaume@lelarge.info>
@ 2019-09-05 07:01 ` Luca Ferrari <fluca1978@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Luca Ferrari @ 2019-09-05 07:01 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: byrnejb@harte-lyne.ca; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: overwrote THE 'postgres' database - how to recover
2019-09-04 16:51 overwrote THE 'postgres' database - how to recover James B. Byrne <byrnejb@harte-lyne.ca>
@ 2019-09-06 13:55 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2019-09-06 13:55 UTC (permalink / raw)
To: byrnejb@harte-lyne.ca; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-09-06 13:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-09-04 16:51 overwrote THE 'postgres' database - how to recover James B. Byrne <byrnejb@harte-lyne.ca>
2019-09-04 19:52 ` Guillaume Lelarge <guillaume@lelarge.info>
2019-09-05 07:01 ` Luca Ferrari <fluca1978@gmail.com>
2019-09-06 13:55 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox