Thread: Something wrong after file system level backup

Something wrong after file system level backup

From
alex@yauza.ru
Date:
The situation is like that:
[19:24] <Press_Enter> Who can help with file system level backup?
[19:24] <KL> Press_Enter: shut down postgres, tar the data dir
[19:25] <KL> Press_Enter: apparently filesystem snapshots might work as well
[19:25] <KL> Press_Enter: you're supposed to use pg_dumpall
[19:25] <Press_Enter> KL, my question is some deeper =) I`ve backup
/var/lib/pgsql/data, then reinstall Postres (the same version) and then
copy all data back. But psql dosn`t see any tables
[19:26] <KL> Press_Enter: exact same version?
[19:26] <Press_Enter> KL, yes
[19:26] <KL> Press_Enter: did you stop postgres while you backed it up?
[19:26] <Press_Enter> KL, yes
[19:27] <KL> Press_Enter: then there shouldn't be any problem...
[19:27] <KL> Press_Enter: try select * from information_schema.tables;
[19:29] <Press_Enter> KL, ERROR:  Namespace "information_schema" does not
exist
[19:29] <KL> Press_Enter: what pgsql version?
[19:30] <Press_Enter> KL, 7.3.4
[19:30] <KL> Press_Enter: ah sorry
[19:30] <KL> Press_Enter: try select * from pg_tables;   (or pg_table)
[19:28] <Press_Enter> ERROR:  Namespace "information_schema" does not exist
[19:28] <Press_Enter> ERROR:  Namespace "information_schema" does not exist
[19:33] <Press_Enter> only rows from schemaname "pg_catalog" (29 rows)
[19:33] <KL> hmm
[19:33] <KL> are you sure you're in the right db?
[19:34] <KL> because if something was wrong with your backup i wouldn't
expect ANYTHING to work
[19:34] <KL> not end up with a fresh clean database
[19:34] <KL> you didn't re-run initdb did you?
[19:34] <Press_Enter> After installation i only copy all the
/var/lib/pgsql/data
[19:34] <Press_Enter> And didn`t did initdb
[19:35] <KL> it's a mystery to me
[19:35] <KL> did you install from packages?
[19:35] <Press_Enter> yes
[19:36] <KL> mayeb the packages did their own initdb somewhere
[19:36] <Press_Enter> in /var/lib/pgsql/data i see manually all the data,
but psql doesn`t
[19:36] <KL> and then when you ran pgsql it used a fresh database INSTEAD
of looking at your restored dir?
[19:36] <KL> ie. pgsql isn't even using /var/lib/pgsql/data
[19:36] <KL> you can test that by stopping postgresql
[19:36] <KL> then running "postmaster -D /var/lib/pgsql/data"
[19:37] <KL> then in another window see if you can connect
[19:37] <KL> then just ctrl-c the postmaster when you're done
[19:39] <Press_Enter> [19:37] <KL> then in another window see if you can
connect   - yes, i can connect, but it`s the same situation
[19:39] <KL> i have no idea then
[19:39] <KL> you'll have to email the developers list
[19:39] <KL> pgsql-hackers@postgresql.org
[19:40] <KL> if you subscribe first from the pgsql website then you won't
have to wait for your post to be approved
[19:40] <Press_Enter> ok, thx


If in two words - i have file system level backup and i can see manually
all the data in it, but PostreSQL doesn`t. What can i do?


Re: Something wrong after file system level backup

From
Tom Lane
Date:
alex@yauza.ru writes:
> If in two words - i have file system level backup and i can see manually
> all the data in it, but PostreSQL doesn`t. What can i do?

You messed up the backup/restore somehow: the system is running with a
transaction counter much less than what it was when you took the backup
(in fact, less than what it was when you created the "missing" tables).
So all the catalog rows appear to be "in the future".

You sure you backed up *all* of $PGDATA, including pg_xlog, pg_global,
and so on?
        regards, tom lane