pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedrestore 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