pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
restore database from bare files
12+ messages / 7 participants
[nested] [flat]

* restore database from bare files
@ 2005-06-29 18:20  jehan procaccia <jehan.procaccia@free.fr>
  0 siblings, 1 reply; 12+ messages in thread

From: jehan procaccia @ 2005-06-29 18:20 UTC (permalink / raw)
  To: pgsql-admin

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%7c28296...;! 
any incompatibility or migration from 7.3.9-2 ?
thanks.






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-06-29 18:48  Peter Eisentraut <peter_e@gmx.net>
  parent: jehan procaccia <jehan.procaccia@free.fr>
  0 siblings, 1 reply; 12+ messages in thread

From: Peter Eisentraut @ 2005-06-29 18:48 UTC (permalink / raw)
  To: jehan procaccia <jehan.procaccia@free.fr>; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-06-29 20:10  Dario <dario_d_s@unitech.com.ar>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 3 replies; 12+ messages in thread

From: Dario @ 2005-06-29 20:10 UTC (permalink / raw)
  To: pgsql-admin

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)




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-06-30 05:34  jehan <jehan.procaccia@free.fr>
  parent: Dario <dario_d_s@unitech.com.ar>
  2 siblings, 0 replies; 12+ messages in thread

From: jehan @ 2005-06-30 05:34 UTC (permalink / raw)
  To: Dario <dario_d_s@unitech.com.ar>; +Cc: pgsql-admin

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
>  
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-06-30 06:32  Martin Fandel <martin.fandel@alphyra-evs.de>
  parent: Dario <dario_d_s@unitech.com.ar>
  2 siblings, 1 reply; 12+ messages in thread

From: Martin Fandel @ 2005-06-30 06:32 UTC (permalink / raw)
  To: jehan <jehan.procaccia@free.fr>; +Cc: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-06-30 10:41  Martin Fandel <martin.fandel@alphyra-evs.de>
  parent: Dario <dario_d_s@unitech.com.ar>
  2 siblings, 1 reply; 12+ messages in thread

From: Martin Fandel @ 2005-06-30 10:41 UTC (permalink / raw)
  To: jehan-free <jehan.procaccia@free.fr>; +Cc: pgsql-admin

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
> >  
> >
> 




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-06-30 10:57  jehan-free <jehan.procaccia@free.fr>
  parent: Martin Fandel <martin.fandel@alphyra-evs.de>
  0 siblings, 0 replies; 12+ messages in thread

From: jehan-free @ 2005-06-30 10:57 UTC (permalink / raw)
  To: Martin Fandel <martin.fandel@alphyra-evs.de>; +Cc: pgsql-admin

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
>  
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-06-30 17:57  jehan-free <jehan.procaccia@free.fr>
  parent: Martin Fandel <martin.fandel@alphyra-evs.de>
  0 siblings, 1 reply; 12+ messages in thread

From: jehan-free @ 2005-06-30 17:57 UTC (permalink / raw)
  To: Martin Fandel <martin.fandel@alphyra-evs.de>; +Cc: pgsql-admin; Lydie.Soler@inapg.inra.fr

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
>  
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-06-30 18:49  Ben Kim <bkim@coe.tamu.edu>
  parent: jehan-free <jehan.procaccia@free.fr>
  0 siblings, 1 reply; 12+ messages in thread

From: Ben Kim @ 2005-06-30 18:49 UTC (permalink / raw)
  To: jehan-free <jehan.procaccia@free.fr>; +Cc: Martin Fandel <martin.fandel@alphyra-evs.de>; pgsql-admin; Lydie.Soler@inapg.inra.fr


>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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-07-01 09:07  jehan procaccia <jehan.procaccia@free.fr>
  parent: Ben Kim <bkim@coe.tamu.edu>
  0 siblings, 1 reply; 12+ messages in thread

From: jehan procaccia @ 2005-07-01 09:07 UTC (permalink / raw)
  To: Ben Kim <bkim@coe.tamu.edu>; +Cc: Martin Fandel <martin.fandel@alphyra-evs.de>; pgsql-admin@postgresql.org, Lydie.Soler@inapg.inra.fr, Yves Marteau <marteau@inapg.fr>

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
>  
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-07-01 13:31  Ben Kim <bkim@coe.tamu.edu>
  parent: jehan procaccia <jehan.procaccia@free.fr>
  0 siblings, 1 reply; 12+ messages in thread

From: Ben Kim @ 2005-07-01 13:31 UTC (permalink / raw)
  To: jehan procaccia <jehan.procaccia@free.fr>; +Cc: Martin Fandel <martin.fandel@alphyra-evs.de>; pgsql-admin@postgresql.org, Lydie.Soler@inapg.inra.fr, Yves Marteau <marteau@inapg.fr>

>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






^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: restore database from bare files
@ 2005-07-04 15:42  jehan procaccia <jehan.procaccia@free.fr>
  parent: Ben Kim <bkim@coe.tamu.edu>
  0 siblings, 0 replies; 12+ messages in thread

From: jehan procaccia @ 2005-07-04 15:42 UTC (permalink / raw)
  To: Ben Kim <bkim@coe.tamu.edu>; +Cc: Martin Fandel <martin.fandel@alphyra-evs.de>; pgsql-admin@postgresql.org, Lydie.Soler@inapg.inra.fr, Yves Marteau <marteau@inapg.fr>

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
>  
>




^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2005-07-04 15:42 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-29 18:20 restore database from bare files jehan procaccia <jehan.procaccia@free.fr>
2005-06-29 18:48 ` Peter Eisentraut <peter_e@gmx.net>
2005-06-29 20:10   ` Dario <dario_d_s@unitech.com.ar>
2005-06-30 05:34     ` jehan <jehan.procaccia@free.fr>
2005-06-30 06:32     ` Martin Fandel <martin.fandel@alphyra-evs.de>
2005-06-30 10:57       ` jehan-free <jehan.procaccia@free.fr>
2005-06-30 10:41     ` Martin Fandel <martin.fandel@alphyra-evs.de>
2005-06-30 17:57       ` jehan-free <jehan.procaccia@free.fr>
2005-06-30 18:49         ` Ben Kim <bkim@coe.tamu.edu>
2005-07-01 09:07           ` jehan procaccia <jehan.procaccia@free.fr>
2005-07-01 13:31             ` Ben Kim <bkim@coe.tamu.edu>
2005-07-04 15:42               ` jehan procaccia <jehan.procaccia@free.fr>

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