Обсуждение: Restoring database question..
Hi all, I have a problem. My server got hacked and someone did an rm -rf * on the /home and /var folders. The backups to my database were not as fresh as they should be but I have been able to recover all the files that existed before the rm from the old harddrive, including the files located in the data folder for postgres. My question is as follows, is it possible to simply copy the data files to their correct location and getting access to the databases as they looked at the time of the hacking attempt? I should have the exact same setup before the hack as after, no version changes or anything. Or do I simply have to count my losses and restore from an old backup?? I have a Debian Linux (stable) and the Postgres version is 7.4. Thanks in advance, Archie
Hi all, this is a follow question to my other question about moving the data folder (that holds all databases) as a method to restore the databases after my server was hacked and everything removed. After having read the manual, recieved some helpfuls responses I have carefully stopped the database, moved the newly installed data folder (under /var/lib/postgres) and copied over the old (before the hacking attempt) data folder. The database starts and I can select from the tables but I can't do \d <table> or dump the database. The error message is as follows: ERROR: could not open relation "pg_inherits": No such file or directory From what I can read about the pg_inherits (and other pg_* tables) is that it is a table that contains info about the tables in my database. What I can't find is where these should be located. Any help would be grateful. Thanks, Archie
On Tue, Aug 15, 2006 at 05:25:38PM +0200, arsi@aranzo.netg.se wrote: > > Hi all, > > this is a follow question to my other question about moving the data > folder (that holds all databases) as a method to restore the databases > after my server was hacked and everything removed. > > After having read the manual, recieved some helpfuls responses I have > carefully stopped the database, moved the newly installed data folder > (under /var/lib/postgres) and copied over the old (before the hacking > attempt) data folder. > > The database starts and I can select from the tables but I can't do \d > <table> or dump the database. The error message is as follows: > > ERROR: could not open relation "pg_inherits": No such file or directory > > > From what I can read about the pg_inherits (and other pg_* tables) is that > it is a table that contains info about the tables in my database. What I > can't find is where these should be located. It sounds like you didn't get all the tables. If you do a SELECT relfilenode FROM pg_class WHERE relname='pg_inherits' on another 7.4 database (I don't have one handy), you'll see what the filename should be so you can look for it. If you haven't done many DDL changes since the last backup, you could possibly restore an old copy and use that info to reconstruct pg_inherits. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Please cc the list so others can help. Yes, those tables are per-database. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 -----Original Message----- From: arsi@aranzo.netg.se [mailto:arsi@aranzo.netg.se] Sent: Wed 8/16/2006 8:42 AM To: Jim Nasby Subject: Re: [ADMIN] Restoring database question, part 2.. Hi there, one question to your response, are all the tables (especially the pg_* ones) located under the data/base folder for each database?? I will try your suggestion. I am switching between my "not working" database and the one that was installed when I had to reinstall my Linux. Thanks for the answer, Archie > On Tue, Aug 15, 2006 at 05:25:38PM +0200, arsi@aranzo.netg.se wrote: >> >> Hi all, >> >> this is a follow question to my other question about moving the data >> folder (that holds all databases) as a method to restore the databases >> after my server was hacked and everything removed. >> >> After having read the manual, recieved some helpfuls responses I have >> carefully stopped the database, moved the newly installed data folder >> (under /var/lib/postgres) and copied over the old (before the hacking >> attempt) data folder. >> >> The database starts and I can select from the tables but I can't do \d >> <table> or dump the database. The error message is as follows: >> >> ERROR: could not open relation "pg_inherits": No such file or directory >> >> >> From what I can read about the pg_inherits (and other pg_* tables) is that >> it is a table that contains info about the tables in my database. What I >> can't find is where these should be located. > > It sounds like you didn't get all the tables. If you do a SELECT > relfilenode FROM pg_class WHERE relname='pg_inherits' on another 7.4 > database (I don't have one handy), you'll see what the filename should > be so you can look for it. > > If you haven't done many DDL changes since the last backup, you could > possibly restore an old copy and use that info to reconstruct > pg_inherits. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 >
On Sat, Aug 12, 2006 at 19:58:52 +0200, arsi@aranzo.netg.se wrote: > > My question is as follows, is it possible to simply copy the data files > to their correct location and getting access to the databases as they > looked at the time of the hacking attempt? > I should have the exact same setup before the hack as after, no version > changes or anything. If the database was running when the files were backed up, then just copying them over is very unlikely to work. > Or do I simply have to count my losses and restore from an old backup?? This is probably your best option.
Hi there, >> My question is as follows, is it possible to simply copy the data files >> to their correct location and getting access to the databases as they >> looked at the time of the hacking attempt? >> I should have the exact same setup before the hack as after, no version >> changes or anything. > > If the database was running when the files were backed up, then just copying > them over is very unlikely to work. > The thing is, there was no backup. The machine was hacked and someone did an 'rm -rf *' on / and I don't know if it removed the postgres program first or the datafiles first, nor do I know if that matters. Also, the main data files also exist (I copied the data folder to its correct place as the restoring technique), I can select from the actual data, only the pg_* tables seem to be missing since I can't dump the database or do \d on any tables. Archie