[GENERAL] Is there any way to measure disk activity for each query?
Hello! I'm wondering, if there any way to measure how much disk-io were generated by a query? -- Best Regards, Oleg
Re: [GENERAL] Postgresql + corrupted disk = data loss. (Need help for database recover)
by value Float8 argument passing: by value *And main question, how to force postgres to read this files?* 3 декабря 2011 г. 18:10 пользователь Tomas Vondra написал: > On 2.12.2011 09:16, Oleg Serov wrote: > > Hello! > > > > i've don't try to do reindex. There was enough space. > > Not sure whether you tried to reindex or not. And what do you mean by > 'there was enough space'? For example with ext2 (and ext3/ext4) it was > rather simple to exhaust inodes long before the device was actually > full. What filesystem are you using, anyway? > > This seems like a I/O issue, you should check the hardware and the > settings (e.g. what caches are enabled etc.). Post more details, if > possible. Have you checked S.M.A.R.T. info from the drives? > > > And i have a full data-directory backup, when i've stop server, before > > start. > > Good. Have you moved it to a different machine? Otherwise you don't have > a backup, just a copy. > > Tomas > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- С уважением Олег
Re: [GENERAL] Postgresql + corrupted disk = data loss. (Need help for database recover)
Hello! i've don't try to do reindex. There was enough space. And i have a full data-directory backup, when i've stop server, before start. 2011/12/2 Venkat Balaji > > 2011/12/2 Oleg Serov > >> And, i'm an idiot. >> >> My DB version: >> PostgreSQL 8.4.9 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) >> 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit >> >> >> >> 2011/12/2 Oleg Serov >> >>> Hello, i have a problem. >>> >>> I've got a production server, working fine. Then i've got strange error: >>> > ERROR: right sibling's left-link doesn't match: block 147 links to >>> 407 instead of expected 146 in index "order_status_key"' >>> And decidet to backup all server. So i shut-down VPS with server and >>> backup all data. >>> Then, after i booted it - and then - i've got Data loss. >>> >> > This seems to be an Index corruption. Did you try re-indexing ? Index > creation might have failed, re-indexing would re-organize the Index tuples. > If you are sure about disk corruption, try and "re-create" or "create > concurrent Index" on a different disk. > > > >> I've lost data, that have been written to DB around 10-100 hours >>> (different tables, have different last updated value). >>> >>> Then i've analyzed log, and found this: >>> 7 days ago appears this errors: >>> db= LOG: could not rename temporary statistics file >>> "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.stat": >>> db= WARNING: pgstat wait timeout >>> ERROR: missing chunk number 0 for toast value 2550017 in pg_toast_17076 >> >> > This should be a free space issue, do you have enough space in > "pg_stat_tmp" disk ? > > > 5 days ago: >>> a lot of: ERROR: xlog flush request F/DC1A22D8 is not satisfied --- >>> flushed only to F/526512E0 >>> 83238 db= WARNING: could not write block 54 of base/16384/2619 >>> 83239 db= CONTEXT: writing block 54 of relation base/16384/2619 >>> >> And today: >>> 18 db= LOG: could not open file "pg_xlog/0001000F0052" >>> (log file 15, segment 82): >>> 19 db= ERROR: xlog flush request F/DC1A22D8 is not satisfied --- >>> flushed only to F/52FDF0E0 >>> >> >>> There is any ability to recover fresh data from database? >>> >> > What kind of backups you have available ? > > > > Thanks > VB > -- С уважением Олег
Re: [GENERAL] Postgresql + corrupted disk = data loss. (Need help for database recover)
And, i'm an idiot. My DB version: PostgreSQL 8.4.9 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit 2011/12/2 Oleg Serov > Hello, i have a problem. > > I've got a production server, working fine. Then i've got strange error: > > ERROR: right sibling's left-link doesn't match: block 147 links to 407 > instead of expected 146 in index "order_status_key"' > And decidet to backup all server. So i shut-down VPS with server and > backup all data. > Then, after i booted it - and then - i've got Data loss. > > I've lost data, that have been written to DB around 10-100 hours > (different tables, have different last updated value). > > Then i've analyzed log, and found this: > 7 days ago appears this errors: > db= LOG: could not rename temporary statistics file > "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.stat": > db= WARNING: pgstat wait timeout > ERROR: missing chunk number 0 for toast value 2550017 in pg_toast_17076 > > 5 days ago: > a lot of: ERROR: xlog flush request F/DC1A22D8 is not satisfied --- > flushed only to F/526512E0 > 83238 db= WARNING: could not write block 54 of base/16384/2619 > 83239 db= CONTEXT: writing block 54 of relation base/16384/2619 > > And today: > 18 db= LOG: could not open file "pg_xlog/0001000F0052" > (log file 15, segment 82): > 19 db= ERROR: xlog flush request F/DC1A22D8 is not satisfied --- > flushed only to F/52FDF0E0 > > There is any ability to recover fresh data from database? > > Thanks! > > > -- С уважением Олег
[GENERAL] Postgresql + corrupted disk = data loss. (Need help for database recover)
Hello, i have a problem. I've got a production server, working fine. Then i've got strange error: > ERROR: right sibling's left-link doesn't match: block 147 links to 407 instead of expected 146 in index "order_status_key"' And decidet to backup all server. So i shut-down VPS with server and backup all data. Then, after i booted it - and then - i've got Data loss. I've lost data, that have been written to DB around 10-100 hours (different tables, have different last updated value). Then i've analyzed log, and found this: 7 days ago appears this errors: db= LOG: could not rename temporary statistics file "pg_stat_tmp/pgstat.tmp" to "pg_stat_tmp/pgstat.stat": db= WARNING: pgstat wait timeout ERROR: missing chunk number 0 for toast value 2550017 in pg_toast_17076 5 days ago: a lot of: ERROR: xlog flush request F/DC1A22D8 is not satisfied --- flushed only to F/526512E0 83238 db= WARNING: could not write block 54 of base/16384/2619 83239 db= CONTEXT: writing block 54 of relation base/16384/2619 And today: 18 db= LOG: could not open file "pg_xlog/0001000F0052" (log file 15, segment 82): 19 db= ERROR: xlog flush request F/DC1A22D8 is not satisfied --- flushed only to F/52FDF0E0 There is any ability to recover fresh data from database? Thanks!
