Thread: Did I wipe off my database -- please help - urgent
Dear Group, Please help me if you can. I am in a the most gravest situation. here I read the bug report : https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=143208 reason: I got error when i tried \dt relation "pg_catalog.pg_user" does not exist. After reading some responses Comment#21 and Comment#22 (I have no clue why I am getting that error and no idea about SELinux). I tried the following: $sudo /etc/rc.d/init.d/postgresql stop $ psql -d mrnatest psql: could not connect to server: S Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? $ sudo rm -rf /var/lib/pgsql/data $ sudo /sbin/restorecon -R /var/lib/pgsql $ sudo /etc/rc.d/init.d/postgresql start Initializing database: [ OK ] Starting postgresql service: [ OK ] $ psql -d mir-test psql: FATAL: database "mrnatest" does not exist $ psql -d arraydb psql: FATAL: database "arraydb" does not exist I am very much worried and I feel blood is not running any more in me. Please folks, tell me if something is really wrong. The reason I donot have a backup also is that, my pg_dump is not working. when pg_dump'ed my database I created 0 bits file. Very eagerly looking forward for some help...please.. thanks --- Srinivas Iyyer <srini_iyyer_bio@yahoo.com> wrote: > *** Apologies for re-posting this e-mail **** > > Dear group, > I have a strange problem. apologies if this is the > wrong place, however as a first option i am posting > here. I have to subscribe to other special mailing > lists. > > > I have two databases in my system. > > In firstdb i am having the following problem: > firstdb=# \d targets > ERROR: could not access status of transaction 362 > DETAIL: could not open file > "/var/lib/pgsql/data/pg_clog/0000": No such file or > directory > > when I vacuumed it: > $ vacuumdb firstdb > vacuumdb: vacuuming of database "firstdb" failed: > ERROR: could not access status of transaction 11 > DETAIL: could not open file > "/var/lib/pgsql/data/pg_clog/0000": No such file or > directory > > > > Second problem: > > Issue with secondb - arraydb > > > > $ vacuumdb arraydb > VACUUM > > Suspecting there is something going on with > postgres, > I wanted to take a backup of the arraydb. > > here is what I am doing: > > $ sudo pg_dump arraydb > > arraydb_bcup_Aug_05_2006.dump > > $ ls -lh > total 8.0K > -rw-r--r-- 1 speri postgres 0 Aug 5 20:17 > arradb_bcup_Aug_05_2006.out > > > There is nothing in this. I created dumps long back > and they are heavy. > > what could be the problem in the cases of first and > second problems. > > looking forward to hear from experts eagerly. > > Thanks > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On 8/7/06 11:19 AM, "Srinivas Iyyer" <srini_iyyer_bio@yahoo.com> wrote: > Dear Group, > > Please help me if you can. I am in a the most gravest > situation. here > > > I read the bug report : > https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=143208 > reason: > > I got error when i tried \dt > relation "pg_catalog.pg_user" does not exist. > > After reading some responses Comment#21 and Comment#22 > (I have no clue why I am getting that error and no > idea about SELinux). > > I tried the following: > $sudo /etc/rc.d/init.d/postgresql stop > $ psql -d mrnatest > psql: could not connect to server: S > Is the server running locally and accepting > connections on Unix domain socket > "/tmp/.s.PGSQL.5432"? > > $ sudo rm -rf /var/lib/pgsql/data Sri, If you actually executed the command above and your data was stored in this location (/var/lib/pgsql/data, a common location on linux for a data directory), you just deleted the data directory and all the data on your server. I could be wrong here, but I seriously hope you have a backup of the machine.... Sean
> I read the bug report : > https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=143208 > reason: I am not too sure about problems with SELINUX but the purpose of this thread was attempting to upgrade this server to a new version of Postgresql. I presume that this person had a complete pg_dump of the postgresql cluster before beginning the upgrade. > I got error when i tried \dt > relation "pg_catalog.pg_user" does not exist. I am not sure what the reason behind this error is. However, I've seen similar threads on the mailing lists, I would guess that it was caused by either OID/XID wrap-around or corruption of one of your data's pages. There may be other causes of course. The two solutions that I've seen were to vacuum the affected database or determine the corrupted page and delete it. > After reading some responses Comment#21 and Comment#22 > (I have no clue why I am getting that error and no > idea about SELinux). > > I tried the following: > $sudo /etc/rc.d/init.d/postgresql stop > $ psql -d mrnatest > psql: could not connect to server: S > Is the server running locally and accepting > connections on Unix domain socket > "/tmp/.s.PGSQL.5432"? After you stopped the Postgresql Server, this would be an expected error to get if you tried to connect to it. > > $ sudo rm -rf /var/lib/pgsql/data > $ sudo /sbin/restorecon -R /var/lib/pgsql > $ sudo /etc/rc.d/init.d/postgresql start In short... you completely deleted all of your data, log, and config files from your hard-drive. I am not to sure what restorecon does. Next you created a brand new postgresql cluster on the directory where your former db cluster use to reside. I guess this would be a good time to ask if you have a recently back-up copy handy so that you can restore your database. > The reason I donot have a backup also is that, my > pg_dump is not working. Ouch... I am pretty sure you are not going to get your data back with out a back-up copy. Maybe someone else on the list knows of any forensic services that can reconstruct deleted data? Sorry. Regards, Richard Broersma Jr.
> $ sudo rm -rf /var/lib/pgsql/data i'm curious as to what you thought you were doing when you executed this command. did you not realize this would delete the data directory and all the directories and files below it? __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On 8/7/06 12:01 PM, "Srinivas Iyyer" <srini_iyyer_bio@yahoo.com> wrote: > Dear Sean, > > There are 3 directories: > > # whereis pgsql > pgsql: /usr/local/pgsql /usr/share/pgsql > > > # cd /usr/local/pgsql/ > # ls > bin data doc include lib man share > > # cd data > # ls > base pg_clog pg_ident.conf pg_subtrans > pg_twophase pg_xlog > global pg_hba.conf pg_multixact pg_tblspc > PG_VERSION postgresql.conf > > The contents in this data directory looks identical to > what I had it last night. > > I did not remove any thing from here. > Things look exactly what they were last night. > # cd base > # ls > 1 10792 10793 > > > What I remomved today is > > /var/lib/pgsql/data > > I have no clue what is the significance of > /var/lib/pgsql/data. For future reference, NOT a good idea to rm stuff for which you do not know the significance. > does this still means all my databases are gone. I'm not sure, given the directories that you showed us above. > I feel like i am already dead. if this is wrong all my > work is gone. Now, what to do.... First, STOP!! Do NOTHING else. You ABSOLUTELY need to make a backup of what you have. If you don't know where your data is, then I would suggest backing up the ENTIRE disk. If you have to buy a hard disk to do this, do it! You ABSOLUTELY should not be working in an environment where you do not have a backup of critical data, either by pg_dump to another machine (ideally) or by routine cloning of the machine (we do both). Next, get some help. And by help, I mean local help, if possible--someone who can sit down with you and work through this systematically. You may even want to pay for said help--a local contractor, etc., but that is up to you. Next, figure out where your data are stored. Figure out what the problem is that keeps you from accessing it. Figure out what to do about that problem. MAKE ANOTHER BACKUP. Then try fixing the problem. Sean
> Now, what to do.... > > First, STOP!! Do NOTHING else. You ABSOLUTELY need > to make a backup of what > you have. If you don't know where your data is, > then I would suggest > backing up the ENTIRE disk. If you have to buy a > hard disk to do this, do > it! You ABSOLUTELY should not be working in an > environment where you do not > have a backup of critical data, either by pg_dump to > another machine > (ideally) or by routine cloning of the machine (we > do both). > > Next, get some help. And by help, I mean local > help, if possible--someone > who can sit down with you and work through this > systematically. You may > even want to pay for said help--a local contractor, > etc., but that is up to > you. > > Next, figure out where your data are stored. Figure > out what the problem is > that keeps you from accessing it. Figure out what > to do about that problem. > MAKE ANOTHER BACKUP. Then try fixing the problem. > > Sean great advice. don't overwrite anything on that disk (apparently, a reboot might do wome disk overwriting)! as mentioned earlier, perhaps the data can be restored using computer forensics if no backup is available. here's a similar thread with some advice... http://archives.postgresql.org/pgsql-admin/2006-05/msg00391.php this might help, too... http://forums.devshed.com/bsd-help-31/oh-dear-big-mistake-have-deleted-var-181892.html __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com