[ADMIN] PG 9.0.1 database errors
Hello Gurus, One of our Pg ver 9.0.1 on RHES 5-64 bits server crashed this weekend, we restarted the db and start seeing the following error, we can't see any disks error in OS error log related to this crashed, no software changes to the system so far I would like to get more info about the error and what's the workaround to fix the issue: ERROR: invalid byte sequence for encoding "UTF8": 0xff 2011-07-25 07:23:45 PDT[23250]:[2-1]STATEMENT: COPY irod.r_data_main (data_id, coll_id, data_name, data_repl_num, data_version, data_type_name, data_size, resc_group_name, resc_name, data_path, data_owner_name, data_owner_zone, data_is_dirty, data_status, data_checksum, data_expiry_ts, data_map_id, data_mode, r_comment, create_ts, modify_ts) TO stdout; 2011-07-25 07:26:05 PDT[31080]:[1-1]LOG: incomplete startup packet 2011-07-25 07:27:24 PDT[935]:[1-1]ERROR: invalid page header in block 5665176 of relation pg_tblspc/16390/PG_9.0_201008051/16393/16453 2011-07-25 07:27:24 PDT[935]:[2-1]CONTEXT: automatic vacuum of table "ICAT.irod.r_data_main" Thank you Isabella -- --- Isabella A. Ghiurea isabella.ghiu...@nrc-cnrc.gc.ca Canadian Astronomy Data Centre |http://www.nrc-cnrc.gc.ca/eng/services/hia/data-centre.html National Research Council of Canada, Herzberg Institute of Astrophysics 5071 West Saanich Road, Victoria BC V9E 2E7, Canada Phone: 250 363-3446 fax: 250 363-0045 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Restore database after drop command
* Adarsh Sharma: > I restore globedatabase from a .sql file on yesterday morning.I insert > some new data in that database. > In the evening, by mistake I issued a *drop database globedatabase* command. > > Today morning, I restore again the same database from backup (.sql) file. > My .sql file have data till yesterday morning but I want newly insert > data now. Is it possible. > > Is it possible to get the data back till the state before drop > database command. It might have been possible if you had performed a hard shutdown directly after discovering the mistake, by undeleting the database files at the operating system level. This has been made more difficult (perhaps even impossible) by your subsequent write activity. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Restore database after drop command
I go through the link, so it is impossible to get the data back. I have following files in my pg_xlog directory : 000100070091 000100070092 000100070093 000100070094 000100070095 000100070096 000100070097 000100070098 I think I issued the drop database command 1 month ago. From the manual, I understand that my segment files are recycled to newer ones : /The segment files are given numeric names that reflect their position in the abstract WAL sequence. When not using WAL archiving, the system normally creates just a few segment files and then "recycles" them by renaming no-longer-needed segment files to higher segment numbers. It's assumed that a segment file whose contents precede the checkpoint-before-last is no longer of interest and can be recycled. /My archive_status folder is empty. How would we know that which data these segment files corresponds too. I followed below steps 1 month ago : 1. Load globdatabase through backup.sql (21 GB)file 2. Insert some data near about 3-4 tables ( KB) data. 3. Drop database globdatabase. 4. Load globdatabase through backup.sql (21GB)file May be there is chance because we work very rarely on that system. Now i have the backup file bt I want that 3-4 tables. Thanks Vibhor Kumar wrote: On Jul 25, 2011, at 12:08 PM, Adarsh Sharma wrote: I restore globedatabase from a .sql file on yesterday morning.I insert some new data in that database. In the evening, by mistake I issued a drop database globedatabase command. Today morning, I restore again the same database from backup (.sql) file. My .sql file have data till yesterday morning but I want newly insert data now. Is it possible. Is it possible to get the data back till the state before drop database command. No you won't be able to recover. If you have Online Backup, then PITR would help you. Thanks & Regards, Vibhor Kumar Blogs: http://vibhork.blogspot.com http://vibhorkumar.wordpress.com
