Обсуждение: restore database from bare files
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.
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/
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)
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 > >
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
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 > >
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 > > > > >
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 > >
>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
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 > >
>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
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 > >