Re: [ADMIN] Recover postgres database
Hope there is bad sector. Did u checked restoring the backup DB to ur local server? On Wed, Sep 23, 2009 at 9:50 AM, nalini nal...@nic.in wrote: Backup is with pg_dump -Ft command ie tar backup - Original Message - From: S Arvind arvindw...@gmail.com Date: Tuesday, September 22, 2009 6:13 am Subject: Re: [ADMIN] Recover postgres database To: nalini nal...@nic.in Cc: pgsql-admin pgsql-admin@postgresql.org Is the backup is made by pg_dump or copying the entire data folder? -Arvind S Many of lifes failure are people who did not realize how close they were to success when they gave up. -Thomas Edison On Tue, Sep 22, 2009 at 5:01 PM, nalini nal...@nic.in wrote: I am unable to recover my data due hard disk errors. Although I am able to start the postgres service. even pgfsck fails after some. Any hope of recovery? Please let me know any thing that can be done. The server was located at user site and even the backups are not getting restored. I have more than one backup and none of them are working. They fail with the message somenumber.dat header missing or file missing. please help Nalini S. Nautiyal Nalini S. Nautiyal System Software Division NIC HQ New Delhi Ph 24360324,24305121,24305131
Re: [ADMIN] Recover postgres database
Yes there are bad sectors . I tried to restore backup on a local server but the dump fails with the error somenumber.dat missing - Original Message - From: S Arvind arvindw...@gmail.com Date: Tuesday, September 22, 2009 11:25 pm Subject: Re: [ADMIN] Recover postgres database To: nalini nal...@nic.in Cc: pgsql-admin pgsql-admin@postgresql.org Hope there is bad sector. Did u checked restoring the backup DB to ur local server? On Wed, Sep 23, 2009 at 9:50 AM, nalini nal...@nic.in wrote: Backup is with pg_dump -Ft command ie tar backup - Original Message - From: S Arvind arvindw...@gmail.com Date: Tuesday, September 22, 2009 6:13 am Subject: Re: [ADMIN] Recover postgres database To: nalini nal...@nic.in Cc: pgsql-admin pgsql-admin@postgresql.org Is the backup is made by pg_dump or copying the entire data folder? -Arvind S Many of lifes failure are people who did not realize how close they were to success when they gave up. -Thomas Edison On Tue, Sep 22, 2009 at 5:01 PM, nalini nal...@nic.in wrote: I am unable to recover my data due hard disk errors. Although I am able to start the postgres service. even pgfsck fails after some. Any hope of recovery? Please let me know any thing that can be done. The server was located at user site and even the backups are not getting restored. I have more than one backup and none of them are working. They fail with the message somenumber.dat header missing or file missing. please help Nalini S. Nautiyal Nalini S. Nautiyal System Software Division NIC HQ New Delhi Ph 24360324,24305121,24305131 Nalini S. Nautiyal System Software Division NIC HQ New Delhi Ph 24360324,24305121,24305131
[ADMIN] recovery is stuck when children are not processing SIGQUIT from previous crash
I have observed the following situation a few times now (weeks or months apart), most recently with 8.3.7. Some postgres child process crashes. The postmaster notices and sends SIGQUIT to all other children. Once all other children have exited, it would enter recovery. But for some reason, some children are not processing the SIGQUIT signal and are basically just stuck. That means the whole database system is then stuck and won't continue without manual intervention. If I go in manually and SIGKILL the offending processes, everything proceeds normally, recovery finishes, and the system is up again. I haven't had the chance yet to analyze why the SIGQUIT signals are getting stuck. Be that as it may, it appears there are no provisions for this case. I couldn't find any documentation or previous reports on this sort of thing. One might imagine a feature where the postmaster resorts to throwing SIGKILLs around after a while, similar to how init scripts are sometimes set up. But perhaps manual intervention is the way to go. Comments? -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Authentication Postgres user via LDAP
Hello guys, I'm configuring a new server, and I would to authenticate the users via LDAP. We already have systems doing this, like Intranet (Joomla! Framework) and Imap Mail. So, for tests, I added the following line in the pg_hba.conf (according the Postgres Documentation): hostall r.domiciano 172.16.5.20 255.255.255.255 ldap ldap://172.16.5.1/ou=usuarios,dc=senffnet,dc=intranet;SENFFNET\; The user r.domiciano is already created in the Postgres. When Trying to connect in the databse via psql, I'm prompted for password, and then for the error message: psql postgres -h 172.16.5.20 -U r.domiciano psql: FATAL: autenticação do tipo LDAP falhou para usuário r.domiciano psql: FATAL: LDAP authentication failed for user: r.domiciano In the log I get a message saying error code: 34... googling a while I get that this is a invalid DN syntax. But i thing the pg_hba.conf is ok. Has anyone get the same trouble? Helps? Best Regards, Rafael Domiciano
Re: [ADMIN] recovery is stuck when children are not processing SIGQUIT from previous crash
Peter Eisentraut pete...@gmx.net writes: I have observed the following situation a few times now (weeks or months apart), most recently with 8.3.7. Some postgres child process crashes. The postmaster notices and sends SIGQUIT to all other children. Once all other children have exited, it would enter recovery. But for some reason, some children are not processing the SIGQUIT signal and are basically just stuck. That means the whole database system is then stuck and won't continue without manual intervention. If I go in manually and SIGKILL the offending processes, everything proceeds normally, recovery finishes, and the system is up again. We need some investigation into why that is happening. I haven't had the chance yet to analyze why the SIGQUIT signals are getting stuck. Be that as it may, it appears there are no provisions for this case. I couldn't find any documentation or previous reports on this sort of thing. One might imagine a feature where the postmaster resorts to throwing SIGKILLs around after a while, similar to how init scripts are sometimes set up. I'd prefer not to go there, at least not without a demonstration that this will solve a bug that's unsolvable otherwise. If a child is really stuck in a state that doesn't accept SIGQUIT, it probably won't accept SIGKILL either (eg, uninterruptable disk wait). Or maybe we just have some errant code that is blocking SIGQUIT; but that's a garden variety bug IMO, not something that needs major new postmaster logic to work around. regards, tom lane -- 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] db size and tables size difference
Isabella Ghiurea isabella.ghiu...@nrc-cnrc.gc.ca writes: SELECT nspname || '.' || relname AS relation,pg_size_pretty(pg_total_relation_size(nspname || '.' || relname)) AS s ize FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) ORDER BY pg_relation_size(nspname || '.' || relname) DESC LIMIT 20; I think maybe you'd better ORDER BY pg_total_relation_size instead. Also, maybe look further than 20 rows ... maybe the issue is many thousands of little tables? regards, tom lane -- 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] db size and tables size difference
Hi All,
Tom, please see bellow are the results for the modified query with ORDER
BY
select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
12 GB
SELECT 'the table size without table space restrictions';
SELECT nspname || '.' || relname AS
relation,pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
AS s
ize
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
LIMIT 1000;
--+
caom.spatialentity | 3216 MB
caom.artifact| 2150 MB
caom.plane | 677 MB
caom.positionsample | 219 MB
caom.simpleobservation | 202 MB
caom.artifact_i1 | 171 MB
caom.spatialentity_i1| 162 MB
caom.temporalentity | 86 MB
caom.plane_psi2 | 86 MB
caom.spectralentity | 73 MB
caom.metric | 70 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.polarizationentity | 33 MB
caom.simpleobservation_i2| 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB
caom.metric_i1 | 18 MB
caom.plane_i1| 15 MB
caom.plane_position_i3 | 15 MB
caom.plane_polar_i1 | 15 MB
caom.plane_time_i2 | 15 MB
caom.plane_energy_i2 | 15 MB
caom.plane_i2| 15 MB
caom.simpleobservation_i1| 12 MB
caom.temporalentity_i1 | 9496 kB
caom.spectralentity_i1 | 4384 kB
caom.polarizationentity_i1 | 4368 kB
caom.harvestskip | 2056 kB
pg_catalog.pg_depend | 1008 kB
pg_catalog.pg_proc | 880 kB
caom.harveststate| 856 kB
pg_catalog.pg_attribute | 648 kB
caom.positionhole| 584 kB
pg_catalog.pg_statistic | 576 kB
caom.plane_phi2 | 496 kB
caom.harvestskip_i1 | 480 kB
pg_catalog.pg_proc_proname_args_nsp_index| 328 kB
pg_catalog.pg_operator | 296 kB
pg_catalog.pg_description| 280 kB
pg_catalog.pg_depend_depender_index | 264 kB
pg_catalog.pg_depend_reference_index | 264 kB
pg_catalog.pg_rewrite| 256 kB
pg_catalog.pg_attribute_relid_attnam_index | 240 kB
pg_toast.pg_toast_2618 | 160 kB
pg_catalog.pg_type | 144 kB
pg_catalog.pg_class | 136 kB
pg_catalog.pg_amop | 128 kB
pg_catalog.pg_operator_oprname_l_r_n_index | 112 kB
pg_catalog.pg_description_o_c_o_index| 96 kB
pg_toast.pg_toast_2619 | 96 kB
pg_catalog.pg_constraint | 80 kB
pg_catalog.pg_conversion | 80 kB
pg_catalog.pg_amproc | 72 kB
pg_catalog.pg_attribute_relid_attnum_index | 72 kB
pg_catalog.pg_proc_oid_index | 72 kB
pg_catalog.pg_opclass| 72 kB
pg_catalog.pg_trigger| 56 kB
pg_catalog.pg_type_typname_nsp_index | 56 kB
pg_catalog.pg_index | 56 kB
information_schema.sql_features | 48 kB
pg_catalog.pg_cast | 48 kB
pg_catalog.pg_class_relname_nsp_index| 48 kB
pg_catalog.pg_database | 48 kB
pg_catalog.pg_authid | 48 kB
pg_catalog.pg_ts_config_map | 48 kB
pg_catalog.pg_opfamily | 48 kB
pg_catalog.pg_language | 40 kB
pg_catalog.pg_shdepend | 40 kB
pg_catalog.pg_ts_parser
Re: [ADMIN] db size and tables size difference
Isabella Ghiurea isabella.ghiu...@nrc-cnrc.gc.ca writes: SELECT nspname || '.' || relname AS relation,pg_size_pretty(pg_total_relation_size(nspname || '.' || relname)) AS s ize FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC LIMIT 1000; Hmph ... I can't see anything wrong with that query, so it seems that we're left with the conclusion that there are files in the database directory that don't match any entry in the catalogs. AFAIK this'd only be possible if you'd had a crash while deleting tables or some similar problem. What you'll need to do next is poke around in the data directory and see if you can identify any large files that do not correspond to any entry in pg_class.relfilenode. You should read the internals docs first, if you're not familiar with this chapter: http://www.postgresql.org/docs/8.3/static/storage.html regards, tom lane -- 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] db size and tables size difference
Hi All,
looking in more details on OS partitions sizwe and each table space
corresponding to OS partitions will add up to close to 6,5GB for db size
same result as SQL table size.
The issue may be with pg_size_pretty() results, I don't have details
knowledge of this function.
select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
12 GB
Isabella
Isabella Ghiurea wrote:
Hi All,
Tom, please see bellow are the results for the modified query with
ORDER BY
select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
12 GB
SELECT 'the table size without table space restrictions';
SELECT nspname || '.' || relname AS
relation,pg_size_pretty(pg_total_relation_size(nspname || '.' ||
relname)) AS s
ize
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
LIMIT 1000;
--+
caom.spatialentity | 3216 MB
caom.artifact| 2150 MB
caom.plane | 677 MB
caom.positionsample | 219 MB
caom.simpleobservation | 202 MB
caom.artifact_i1 | 171 MB
caom.spatialentity_i1| 162 MB
caom.temporalentity | 86 MB
caom.plane_psi2 | 86 MB
caom.spectralentity | 73 MB
caom.metric | 70 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.polarizationentity | 33 MB
caom.simpleobservation_i2| 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB
caom.metric_i1 | 18 MB
caom.plane_i1| 15 MB
caom.plane_position_i3 | 15 MB
caom.plane_polar_i1 | 15 MB
caom.plane_time_i2 | 15 MB
caom.plane_energy_i2 | 15 MB
caom.plane_i2| 15 MB
caom.simpleobservation_i1| 12 MB
caom.temporalentity_i1 | 9496 kB
caom.spectralentity_i1 | 4384 kB
caom.polarizationentity_i1 | 4368 kB
caom.harvestskip | 2056 kB
pg_catalog.pg_depend | 1008 kB
pg_catalog.pg_proc | 880 kB
caom.harveststate| 856 kB
pg_catalog.pg_attribute | 648 kB
caom.positionhole| 584 kB
pg_catalog.pg_statistic | 576 kB
caom.plane_phi2 | 496 kB
caom.harvestskip_i1 | 480 kB
pg_catalog.pg_proc_proname_args_nsp_index| 328 kB
pg_catalog.pg_operator | 296 kB
pg_catalog.pg_description| 280 kB
pg_catalog.pg_depend_depender_index | 264 kB
pg_catalog.pg_depend_reference_index | 264 kB
pg_catalog.pg_rewrite| 256 kB
pg_catalog.pg_attribute_relid_attnam_index | 240 kB
pg_toast.pg_toast_2618 | 160 kB
pg_catalog.pg_type | 144 kB
pg_catalog.pg_class | 136 kB
pg_catalog.pg_amop | 128 kB
pg_catalog.pg_operator_oprname_l_r_n_index | 112 kB
pg_catalog.pg_description_o_c_o_index| 96 kB
pg_toast.pg_toast_2619 | 96 kB
pg_catalog.pg_constraint | 80 kB
pg_catalog.pg_conversion | 80 kB
pg_catalog.pg_amproc | 72 kB
pg_catalog.pg_attribute_relid_attnum_index | 72 kB
pg_catalog.pg_proc_oid_index | 72 kB
pg_catalog.pg_opclass| 72 kB
pg_catalog.pg_trigger| 56 kB
pg_catalog.pg_type_typname_nsp_index | 56 kB
pg_catalog.pg_index | 56 kB
information_schema.sql_features | 48 kB
pg_catalog.pg_cast
Re: [ADMIN] db size and tables size difference
Isabella Ghiurea isabella.ghiu...@nrc-cnrc.gc.ca writes:
The issue may be with pg_size_pretty() results, I don't have details
knowledge of this function.
I doubt it, that's a pretty simple function ... but if you don't trust
it, just remove the pg_size_pretty call and look directly at the output
of the size functions.
select pg_size_pretty(pg_database_size('db1'));
Just to double check here ... you're sure you're naming the correct
database in this call?
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] pg_toast record in table pg_class
Hi. I accidentally deleted pg_toast record from pg_class table that belongs to regular table (reltoastrelid). Now [select * from table_name] not work: ERROR: could not open relation with OID [oid_of_pg_toast_table] Is it possible to restore corruption? -- --- Regards, M.Nasedkin
