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

On Wed, Sep 4, 2019 at 12:51 PM James B. Byrne <byrnejb(at)harte-lyne(dot)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