Thread: restoring pgsql database v7.0.3?

restoring pgsql database v7.0.3?

From
dionysus
Date:
My system was broken into, and following the 'if you're paranoid,
you're safe' mentatilty, I reinstalled my OS (Redhat linux 7.1)...I am
now trying to work out how to restore one of my user's databases to
its previous state from an old copy of my /var/lib/pgsql directory
(databases are in /var/lib/pgsql/data/base).....I tried simply
overwriting the fresh install with the files from my backup, but that
somehow corrupted my installation - I had to reinstall the package
(not merely wipe /var/lib/pgsql and re-init the database - which
suggests that part of postgresql containing data about my tables is
contained outside /var/lib/pgsql......wish I'd read up on
postgresql-dump before this....anyhow, if anyone knows how to insert
my old postgresql table files into a fresh postgresql database (I was
thinking of creating the tables with no data in them, the inserting
just the table files, but I didn't write down the field definitions -
i.e. how long the bpchar fields were :-< ), I would much appreciate
any advice....

thanks heaps in advance

-d

Re: restoring pgsql database v7.0.3?

From
Tom Lane
Date:
dionysus <dionysus39@hotmail.com> writes:
> ... I reinstalled my OS (Redhat linux 7.1)...I am
> now trying to work out how to restore one of my user's databases to
> its previous state from an old copy of my /var/lib/pgsql directory
> (databases are in /var/lib/pgsql/data/base).....I tried simply
> overwriting the fresh install with the files from my backup, but that
> somehow corrupted my installation - I had to reinstall the package

A complete restore of /var/lib/pgsql/data/ should have worked, if the
dump was taken while the database was idle.  If you try to restore
selectively it will likely not work --- pg_log has to be in sync with
all of the data files.  A filesystem dump of an active database is
unlikely to yield consistent results either.

            regards, tom lane