Re: [ADMIN] restore database from bare files

[jehan procaccia]

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
 




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] restore database from bare files

[Ben Kim]

>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

Re: [ADMIN] restore database from bare files

[jehan procaccia]

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
 




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] restore database from bare files

[Ben Kim]

>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


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] restore database from bare files

[jehan-free]

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
 




---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [ADMIN] restore database from bare files

[Martin Fandel]

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: [ADMIN] restore database from bare files

[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 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] restore database from bare files

[Martin Fandel]

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: [ADMIN] restore database from bare files

[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

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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED])


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq
 




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [ADMIN] restore database from bare files

[Dario]

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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED])


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [ADMIN] restore database from bare files

[Peter Eisentraut]

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 [EMAIL PROTECTED])

[ADMIN] restore database from bare files

[jehan procaccia]

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/0011
-rw--- /  16777216 2005-06-28 15:33:10 
pgsql/data/pg_xlog/0010

drwx-- / 0 2005-06-20 14:54:12 pgsql/data/pg_clog/
-rw--- /262144 2005-06-01 18:15:07 pgsql/data/pg_clog/
-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 
! 
any incompatibility or migration from 7.3.9-2 ?

thanks.




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings