Thread: restore database from bare files

restore database from bare files

From
jehan procaccia
Date:
hello,

I crashed my server, and I didn't took the precaution to pg_dump one of
my database :-(
However I do have a tar file of the filesystem , it was on a RHEL 3 with
rh-postgresql-server-7.3.9-2 and I have a tar of /var/lib/pgslq
will just restoring the whole directory /var/lib/pgsql will suffices ?
or should I do some tricky operations ?
Here's what I have for example ..., how do I match a database name to
the number -> here 16975 is which database ?:
drwx------ /                 0 2005-03-08 17:38:24 pgsql/data/base/16975/
-rw------- /            475136 2005-03-08 17:38:24
pgsql/data/base/16975/1255
-rw------- /             32768 2005-03-08 17:38:24
pgsql/data/base/16975/1247
and
-rw------- /                 0 2005-03-08 17:38:22 pgsql/data/global/16675
and
drwx------ /                 0 2005-05-31 18:14:51 pgsql/data/pg_xlog/
-rw------- /          16777216 2005-05-31 18:08:53
pgsql/data/pg_xlog/0000000000000011
-rw------- /          16777216 2005-06-28 15:33:10
pgsql/data/pg_xlog/0000000000000010
drwx------ /                 0 2005-06-20 14:54:12 pgsql/data/pg_clog/
-rw------- /            262144 2005-06-01 18:15:07 pgsql/data/pg_clog/0000
-rw------- /            262144 2005-06-20 14:49:10 pgsql/data/pg_clog/0001
-rw------- /             32768 2005-06-28 15:10:19 pgsql/data/pg_clog/0002
and config files
-rw------- /              3080 2005-04-22 18:33:17 pgsql/data/pg_hba.conf
-rw------- /              1441 2005-03-08 17:38:22 pgsql/data/pg_ident.conf
-rw------- /              5331 2005-03-25 15:19:47
pgsql/data/postgresql.conf
-rw------- /                59 2005-06-28 15:27:14
pgsql/data/postmaster.opts

The crashed happened during an upgrade to RHEL 4 , so now on RHEL
postgresql version will be  postgresql-server-7.4.7-2.RHEL4.1
<https://rhn.redhat.com/network/software/packages/details.pxt?cid=2866&id_combo=500000648%7c282969>!
any incompatibility or migration from 7.3.9-2 ?
thanks.




Re: restore database from bare files

From
Peter Eisentraut
Date:
jehan procaccia wrote:
> However I do have a tar file of the filesystem , it was on a RHEL 3
> with rh-postgresql-server-7.3.9-2 and I have a tar of /var/lib/pgslq
> will just restoring the whole directory /var/lib/pgsql will suffices

Assuming that you made the tarball when the server was shut down, just
restore it and you should be all set.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: restore database from bare files

From
"Dario"
Date:
Hello. Sorry for mi inglish! :-)

Postgres 7.4 will not access 7.3 PGDATA repository. You must downgrade your
binary files to any 7.3.X (latest is better)
Note: 7.3 won't access 7.4 files neither. Yo can only do sub-sub-version
updates without having to dump and restore (example 7.3.1. to 7.3.9 doesn't
need to dump and restore of databases)

greeting.

-----Mensaje original-----
De: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]En nombre de Peter Eisentraut
Enviado el: miércoles, 29 de junio de 2005 15:48
Para: jehan procaccia
CC: pgsql-admin@postgresql.org
Asunto: Re: [ADMIN] restore database from bare files


jehan procaccia wrote:
> However I do have a tar file of the filesystem , it was on a RHEL 3
> with rh-postgresql-server-7.3.9-2 and I have a tar of /var/lib/pgslq
> will just restoring the whole directory /var/lib/pgsql will suffices

Assuming that you made the tarball when the server was shut down, just
restore it and you should be all set.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


Re: restore database from bare files

From
jehan
Date:
So I must one way or another run a 7.3, restore the file from the
tarball as is (just put them back to /var/lib/pgsql), the databases
should be running correctly then (?), then pg_dump it , upgrade to 7.4
and restore from the pg_dump .
before running in all this (and I still don't know how I will be able to
get a 7.3 on RHEL4 ... ?) is that the correct procedure ?

thanks

Dario a écrit :

>Hello. Sorry for mi inglish! :-)
>
>Postgres 7.4 will not access 7.3 PGDATA repository. You must downgrade your
>binary files to any 7.3.X (latest is better)
>Note: 7.3 won't access 7.4 files neither. Yo can only do sub-sub-version
>updates without having to dump and restore (example 7.3.1. to 7.3.9 doesn't
>need to dump and restore of databases)
>
>greeting.
>
>-----Mensaje original-----
>De: pgsql-admin-owner@postgresql.org
>[mailto:pgsql-admin-owner@postgresql.org]En nombre de Peter Eisentraut
>Enviado el: miércoles, 29 de junio de 2005 15:48
>Para: jehan procaccia
>CC: pgsql-admin@postgresql.org
>Asunto: Re: [ADMIN] restore database from bare files
>
>
>jehan procaccia wrote:
>
>
>>However I do have a tar file of the filesystem , it was on a RHEL 3
>>with rh-postgresql-server-7.3.9-2 and I have a tar of /var/lib/pgslq
>>will just restoring the whole directory /var/lib/pgsql will suffices
>>
>>
>
>Assuming that you made the tarball when the server was shut down, just
>restore it and you should be all set.
>
>--
>Peter Eisentraut
>http://developer.postgresql.org/~petere/
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>


Re: restore database from bare files

From
"Martin Fandel"
Date:
Hi

Maybe you must reset the WAL's
( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html )
after restoring from tarball if postgres doesn't start.

Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan:
> So I must one way or another run a 7.3, restore the file from the
> tarball as is (just put them back to /var/lib/pgsql), the databases
> should be running correctly then (?), then pg_dump it , upgrade to 7.4
> and restore from the pg_dump .
> before running in all this (and I still don't know how I will be able to
> get a 7.3 on RHEL4 ... ?) is that the correct procedure ?
> thanks

yes, this is the correct way :).

Martin


Re: restore database from bare files

From
jehan-free
Date:
OK, I'am not yet at restarting postgres .. but if at get pb then I check
that , thanks !
For now , How can I tell from the bare file the mapping between a
database name and the number appearing in /var/lib/pgsql/base directory
I have :
pgsql/data/base/1/
pgsql/data/base/16975/
pgsql/data/base/16980/
so I made the assumption that 1 is a database (probably the test initial
database ?), 16975 is an other one and 16980 a tird one ! How can I find
the map from  these numbers to database name ?
thanks
Martin Fandel wrote:

>Hi
>
>Maybe you must reset the WAL's
>( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html )
>after restoring from tarball if postgres doesn't start.
>
>Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan:
>
>
>>So I must one way or another run a 7.3, restore the file from the
>>tarball as is (just put them back to /var/lib/pgsql), the databases
>>should be running correctly then (?), then pg_dump it , upgrade to 7.4
>>and restore from the pg_dump .
>>before running in all this (and I still don't know how I will be able to
>>get a 7.3 on RHEL4 ... ?) is that the correct procedure ?
>>thanks
>>
>>
>
>yes, this is the correct way :).
>
>Martin
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>


Re: restore database from bare files

From
"Martin Fandel"
Date:
Hi,

try this:
psql -t -d yourdb -c "SELECT datid FROM pg_stat_database WHERE
datname='yourdb';"

http://www.postgresql.org/docs/8.0/static/monitoring-stats.html

Greetings,

Martin

Am Donnerstag, den 30.06.2005, 12:57 +0200 schrieb jehan-free:
> OK, I'am not yet at restarting postgres .. but if at get pb then I check
> that , thanks !
> For now , How can I tell from the bare file the mapping between a
> database name and the number appearing in /var/lib/pgsql/base directory
> I have :
> pgsql/data/base/1/
> pgsql/data/base/16975/
> pgsql/data/base/16980/
> so I made the assumption that 1 is a database (probably the test initial
> database ?), 16975 is an other one and 16980 a tird one ! How can I find
> the map from  these numbers to database name ?
> thanks
> Martin Fandel wrote:
>
> >Hi
> >
> >Maybe you must reset the WAL's
> >( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html )
> >after restoring from tarball if postgres doesn't start.
> >
> >Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan:
> >
> >
> >>So I must one way or another run a 7.3, restore the file from the
> >>tarball as is (just put them back to /var/lib/pgsql), the databases
> >>should be running correctly then (?), then pg_dump it , upgrade to 7.4
> >>and restore from the pg_dump .
> >>before running in all this (and I still don't know how I will be able to
> >>get a 7.3 on RHEL4 ... ?) is that the correct procedure ?
> >>thanks
> >>
> >>
> >
> >yes, this is the correct way :).
> >
> >Martin
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 9: In versions below 8.0, the planner will ignore your desire to
> >       choose an index scan if your joining column's datatypes do not
> >       match
> >
> >
>


Re: restore database from bare files

From
jehan-free
Date:
very good, that worked fine :-)
I restored the files from tar, started a postgresql 7.3 on an old redhat
9 ! pg_dump my database, psql it back to my postgresql 7.4 on my
production RHEL4 server .

still a small pb, I seem to have lost authentification. (although
pg_hba.conf was restore also)
$ psql -h meta1 -U ezpublish -d ezpublish_db
psql: FATAL:  user "ezpublish" does not exist
if I go with:
$ psql -h meta1 -U postgres -d ezpublish_db
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.
that works , then I go for setting a user + password:
ezpublish_db-# ALTER USER  ezpublish SET PASSWORD secret;
ERROR:  syntax error at or near "$" at character 1
what's wrong ?
note that for that ezpublisher database I had initily integrated from
postgresql-contribs those functions:
$psql ezpublish_db < /usr/share/pgsql/contrib/pgcrypto.sql
don't know if my problem is related to that ?

Thanks again.
Martin Fandel wrote:

>Hi,
>
>try this:
>psql -t -d yourdb -c "SELECT datid FROM pg_stat_database WHERE
>datname='yourdb';"
>
>http://www.postgresql.org/docs/8.0/static/monitoring-stats.html
>
>Greetings,
>
>Martin
>
>Am Donnerstag, den 30.06.2005, 12:57 +0200 schrieb jehan-free:
>
>
>>OK, I'am not yet at restarting postgres .. but if at get pb then I check
>>that , thanks !
>>For now , How can I tell from the bare file the mapping between a
>>database name and the number appearing in /var/lib/pgsql/base directory
>>I have :
>>pgsql/data/base/1/
>>pgsql/data/base/16975/
>>pgsql/data/base/16980/
>>so I made the assumption that 1 is a database (probably the test initial
>>database ?), 16975 is an other one and 16980 a tird one ! How can I find
>>the map from  these numbers to database name ?
>>thanks
>>Martin Fandel wrote:
>>
>>
>>
>>>Hi
>>>
>>>Maybe you must reset the WAL's
>>>( http://www.postgresql.org/docs/7.3/interactive/app-pgresetxlog.html )
>>>after restoring from tarball if postgres doesn't start.
>>>
>>>Am Donnerstag, den 30.06.2005, 07:34 +0200 schrieb jehan:
>>>
>>>
>>>
>>>
>>>>So I must one way or another run a 7.3, restore the file from the
>>>>tarball as is (just put them back to /var/lib/pgsql), the databases
>>>>should be running correctly then (?), then pg_dump it , upgrade to 7.4
>>>>and restore from the pg_dump .
>>>>before running in all this (and I still don't know how I will be able to
>>>>get a 7.3 on RHEL4 ... ?) is that the correct procedure ?
>>>>thanks
>>>>
>>>>
>>>>
>>>>
>>>yes, this is the correct way :).
>>>
>>>Martin
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 9: In versions below 8.0, the planner will ignore your desire to
>>>      choose an index scan if your joining column's datatypes do not
>>>      match
>>>
>>>
>>>
>>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>


Re: restore database from bare files

From
Ben Kim
Date:
>ezpublish_db-# ALTER USER  ezpublish SET PASSWORD secret;
>ERROR:  syntax error at or near "$" at character 1

I wonder why you have "ezpublish_db-#" instead of "ezpublish_db=#"? I just
noticed it, and to me it happens usually when something's been carried
over from the previous line. My 2 pence...


Regards,

Ben Kim
Developer
College of Education
Texas A&M University


Re: restore database from bare files

From
jehan procaccia
Date:
Ben Kim wrote:

>>ezpublish_db-# ALTER USER  ezpublish SET PASSWORD secret;
>>ERROR:  syntax error at or near "$" at character 1
>>
>>
>
>I wonder why you have "ezpublish_db-#" instead of "ezpublish_db=#"? I just
>noticed it, and to me it happens usually when something's been carried
>over from the previous line. My 2 pence...
>
>
indeed I typed a comment before without ending it with ";" that might
explain the "syntax error" !
for my pb, I have part of an explanation -> I forgot to recreate the
user ! so here I go again in one command to create user and set password:
$ createuser ezpublish -P
Enter password for new user: *******
Enter it again: *******
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
then:
$ psql ezpublish_db -U ezpublish -W
Password: *******
psql: FATAL:  IDENT authentication failed for user "ezpublish"
I really don't understand :-(
If I go with postgres user, no pb:
$ psql ezpublish_db -U postgres
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.

if it can help to debug, here's my users list:
ezpublish_db=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
valuntil | useconfig
-----------+----------+-------------+----------+-----------+----------+----------+-----------

postgres  |        1 | t           | t        | t         | ******** |  |
ezpublish |      100 | t           | f        | f         | ******** |  |
(2 rows)

If you have an idea , I'll really apreciate

Thanks !.

>
>Regards,
>
>Ben Kim
>Developer
>College of Education
>Texas A&M University
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>


Re: restore database from bare files

From
Ben Kim
Date:
>psql: FATAL:  IDENT authentication failed for user "ezpublish"

This might help, or you may want to check or post your pg_hba.conf.

http://archives.postgresql.org/pgsql-sql/2004-03/msg00202.php

(or
http://www.postgresql.org/docs/7.4/interactive/auth-methods.html#AUTH-IDENT)

HTH,

Ben Kim / Developer
College of Education
Texas A&M University




Re: restore database from bare files

From
jehan procaccia
Date:
thanks that was in the doc ! indeed I needed to put "local ezpublish_db
ezpublish trust"  before other rules , as the first rule that match  end
the reading of pg_hba.conf .

Thanks again .

Ben Kim wrote:

>>psql: FATAL:  IDENT authentication failed for user "ezpublish"
>>
>>
>
>This might help, or you may want to check or post your pg_hba.conf.
>
>http://archives.postgresql.org/pgsql-sql/2004-03/msg00202.php
>
>(or
>http://www.postgresql.org/docs/7.4/interactive/auth-methods.html#AUTH-IDENT)
>
>HTH,
>
>Ben Kim / Developer
>College of Education
>Texas A&M University
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>