pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump: Error message from server: ERROR: compressed data is corrupt
16+ messages / 7 participants
[nested] [flat]
* pg_dump: Error message from server: ERROR: compressed data is corrupt
@ 2007-05-29 22:09 Roman Chervotkin <roman.chervotkin@gmail.com>
0 siblings, 3 replies; 16+ messages in thread
From: Roman Chervotkin @ 2007-05-29 22:09 UTC (permalink / raw)
To: pgsql-admin
Do usual pg_dump today and have got an error.
---------------------
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: compressed data is corrupt
pg_dump: The command was: COPY public.candidates (id, name, surname,
mid_name, compensation, created, birthday, updated, creator_id, updater_id,
home_region_id, home_city, home_street, other_languages, home_region_other,
edu_conformity, sex, deleted, resume_text, resume_text_index, mark, kids,
driving_license, marital_status, software_knowledge, business_contacts,
type_writing_rus, type_writing_lat, desired_duties, desired_other,
updater_name, citizenship, compensation_currency, folder_id, plus,
random_cat, temp_reg, "temp") TO stdout;
----------------------
I had 8.2.0 on Solaris 10 (SunOS server3 5.10 Generic_125101-05 ),
pg_data_dir on zfs mirrored pool. I've checked status of pool and got:
----------------------
-bash-3.00# zpool status tank
pool: tank
state: ONLINE
status: One or more devices has experienced an unrecoverable error. An
attempt was made to correct the error. Applications are unaffected.
action: Determine if the device needs to be replaced, and clear the errors
using 'zpool clear' or replace the device with 'zpool replace'.
see: http://www.sun.com/msg/ZFS-8000-9P
scrub: scrub completed with 0 errors on Tue May 29 19:46:12 2007
config:
NAME STATE READ WRITE CKSUM
tank ONLINE 0 0 0
mirror ONLINE 0 0 0
c1t0d0 ONLINE 1 0 0
c1t4d0 ONLINE 0 0 0
errors: No known data errors
--------------------------------------------------------
So it seems a drive should be replaced but pool is still functioning. I
still can copy pg_data_dir to other filesystem without any errors. And
database seems working as usual. So I guess pg_dump should work but It does
not. I've upgraded to 8.2.4 but still have the same error. Copy pg_data_dir
to another file system, start postgres with that data_dir, tried pg_dump
again but still have the error.
So the question is what should I do in order to pg_dump start working as
usual?
Thanks
Roman.
^ permalink raw reply [nested|flat] 16+ messages in thread
* pg_dump: Error message from server: ERROR: compressed data is corrupt
@ 2007-05-30 08:00 Roman Chervotkin <roman.chervotkin@gmail.com>
parent: Roman Chervotkin <roman.chervotkin@gmail.com>
2 siblings, 1 reply; 16+ messages in thread
From: Roman Chervotkin @ 2007-05-30 08:00 UTC (permalink / raw)
To: pgsql-admin
Hi list.
Do usual pg_dump today and have got an error.
---------------------
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: compressed data is corrupt
pg_dump: The command was: COPY public.candidates (id, name, surname,
mid_name, compensation, created, birthday, updated, creator_id, updater_id,
home_region_id, home_city, home_street, other_languages, home_region_other,
edu_conformity, sex, deleted, resume_text, resume_text_index, mark, kids,
driving_license, marital_status, software_knowledge, business_contacts,
type_writing_rus, type_writing_lat, desired_duties, desired_other,
updater_name, citizenship, compensation_currency, folder_id, plus,
random_cat, temp_reg, "temp") TO stdout;
----------------------
I had 8.2.0 on Solaris 10 (SunOS server3 5.10 Generic_125101-05 ),
pg_data_dir on zfs mirrored pool. I've checked status of pool and got:
----------------------
-bash-3.00# zpool status tank
pool: tank
state: ONLINE
status: One or more devices has experienced an unrecoverable error. An
attempt was made to correct the error. Applications are unaffected.
action: Determine if the device needs to be replaced, and clear the errors
using 'zpool clear' or replace the device with 'zpool replace'.
see: http://www.sun.com/msg/ZFS-8000-9P
scrub: scrub completed with 0 errors on Tue May 29 19:46:12 2007
config:
NAME STATE READ WRITE CKSUM
tank ONLINE 0 0 0
mirror ONLINE 0 0 0
c1t0d0 ONLINE 1 0 0
c1t4d0 ONLINE 0 0 0
errors: No known data errors
--------------------------------------------------------
So it seems a drive should be replaced but pool is still functioning. I
still can copy pg_data_dir to other filesystem without any errors. And
database seems working as usual. So I guess pg_dump should work but It does
not. I've upgraded to 8.2.4 but still have the same error. Copy pg_data_dir
to another file system, start postgres with that data_dir, tried pg_dump
again but still have the error.
So the question is what should I do in order to pg_dump start working as
usual?
Thanks
Roman.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: compressed data is corrupt
@ 2007-05-30 19:49 Roman Chervotkin <roman.chervotkin@gmail.com>
parent: Roman Chervotkin <roman.chervotkin@gmail.com>
2 siblings, 0 replies; 16+ messages in thread
From: Roman Chervotkin @ 2007-05-30 19:49 UTC (permalink / raw)
To: pgsql-admin
Please help,
I cannot understand what should be done in order to fix the issue.
Any hint, I tried search and irc but found nothing useful yet.
On 5/30/07, Roman Chervotkin <roman.chervotkin@gmail.com> wrote:
>
> Do usual pg_dump today and have got an error.
>
> ---------------------
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: compressed data is corrupt
> pg_dump: The command was: COPY public.candidates (id, name, surname,
> mid_name, compensation, created, birthday, updated, creator_id, updater_id,
> home_region_id, home_city, home_street, other_languages, home_region_other,
> edu_conformity, sex, deleted, resume_text, resume_text_index, mark, kids,
> driving_license, marital_status, software_knowledge, business_contacts,
> type_writing_rus, type_writing_lat, desired_duties, desired_other,
> updater_name, citizenship, compensation_currency, folder_id, plus,
> random_cat, temp_reg, "temp") TO stdout;
> ----------------------
>
> I had 8.2.0 on Solaris 10 (SunOS server3 5.10 Generic_125101-05 ),
> pg_data_dir on zfs mirrored pool. I've checked status of pool and got:
>
> ----------------------
> -bash-3.00# zpool status tank
> pool: tank
> state: ONLINE
> status: One or more devices has experienced an unrecoverable error. An
> attempt was made to correct the error. Applications are
> unaffected.
> action: Determine if the device needs to be replaced, and clear the errors
> using 'zpool clear' or replace the device with 'zpool replace'.
> see: http://www.sun.com/msg/ZFS-8000-9P
> scrub: scrub completed with 0 errors on Tue May 29 19:46:12 2007
> config:
>
> NAME STATE READ WRITE CKSUM
> tank ONLINE 0 0 0
> mirror ONLINE 0 0 0
> c1t0d0 ONLINE 1 0 0
> c1t4d0 ONLINE 0 0 0
>
> errors: No known data errors
> --------------------------------------------------------
>
> So it seems a drive should be replaced but pool is still functioning. I
> still can copy pg_data_dir to other filesystem without any errors. And
> database seems working as usual. So I guess pg_dump should work but It does
> not. I've upgraded to 8.2.4 but still have the same error. Copy
> pg_data_dir to another file system, start postgres with that data_dir, tried
> pg_dump again but still have the error.
>
> So the question is what should I do in order to pg_dump start working as
> usual?
>
>
> Thanks
> Roman.
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: compressed data is corrupt
@ 2007-05-30 21:23 Alvaro Herrera <alvherre@commandprompt.com>
parent: Roman Chervotkin <roman.chervotkin@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Alvaro Herrera @ 2007-05-30 21:23 UTC (permalink / raw)
To: Roman Chervotkin <roman.chervotkin@gmail.com>; +Cc: pgsql-admin
Roman Chervotkin escribió:
> Hi list.
>
> Do usual pg_dump today and have got an error.
>
> ---------------------
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: compressed data is corrupt
> pg_dump: The command was: COPY public.candidates (id, name, surname,
> mid_name, compensation, created, birthday, updated, creator_id, updater_id,
> home_region_id, home_city, home_street, other_languages, home_region_other,
> edu_conformity, sex, deleted, resume_text, resume_text_index, mark, kids,
> driving_license, marital_status, software_knowledge, business_contacts,
> type_writing_rus, type_writing_lat, desired_duties, desired_other,
> updater_name, citizenship, compensation_currency, folder_id, plus,
> random_cat, temp_reg, "temp") TO stdout;
> ----------------------
>
> I had 8.2.0 on Solaris 10 (SunOS server3 5.10 Generic_125101-05 ),
> pg_data_dir on zfs mirrored pool. I've checked status of pool and got:
Apparently ZFS betrayed you, and the data it recovered is not as good as
it seems to think. If it were recovered correctly you wouldn't be
getting this error.
--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"No es bueno caminar con un hombre muerto"
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: compressed data is corrupt
@ 2007-06-04 21:58 Roman Chervotkin <roman.chervotkin@gmail.com>
parent: Roman Chervotkin <roman.chervotkin@gmail.com>
2 siblings, 0 replies; 16+ messages in thread
From: Roman Chervotkin @ 2007-06-04 21:58 UTC (permalink / raw)
To: pgsql-admin
Guys PLEASE help. I still with the problem.
I've asked about my problem in opensolaris zfs list and what that they say
...
Most likely this is an application corruption problem. With ZFS
checksums, it's not possible to have undetected corruption of this type.
Of course, it's possible there is a ZFS bug somewhere, but none of the
information provided so far indicates that. Did you have an unexpected
panic shortly before this happened? It shouldn't matter, but if there
was a bug then it would indicate it might be present in the ZIL replay
code, or that the database isn't using sync/O_DYNC correctly.
> -bash-3.00# fmdump -e
> TIME CLASS
> May 29 19:43:40.8328 ereport.fs.zfs.io
This suggests that there was a single I/O error, and did not result in
any user-visible errors.
As far as I can tell, you had a random unassociated error, and that the
problem is some higher-level piece of software (i.e. postgres). It
would be worth dumping the contents of that file and determining if it's
something completely crazy (like the contents of another file) or
somehow self-inconsistent.
...
So, what do you think, about that? And what is the strategy to clean the
error? Find and drop faulty record?
On 5/30/07, Roman Chervotkin <roman.chervotkin@gmail.com> wrote:
>
> Do usual pg_dump today and have got an error.
>
> ---------------------
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: compressed data is corrupt
> pg_dump: The command was: COPY public.candidates (id, name, surname,
> mid_name, compensation, created, birthday, updated, creator_id, updater_id,
> home_region_id, home_city, home_street, other_languages, home_region_other,
> edu_conformity, sex, deleted, resume_text, resume_text_index, mark, kids,
> driving_license, marital_status, software_knowledge, business_contacts,
> type_writing_rus, type_writing_lat, desired_duties, desired_other,
> updater_name, citizenship, compensation_currency, folder_id, plus,
> random_cat, temp_reg, "temp") TO stdout;
> ----------------------
>
> I had 8.2.0 on Solaris 10 (SunOS server3 5.10 Generic_125101-05 ),
> pg_data_dir on zfs mirrored pool. I've checked status of pool and got:
>
> ----------------------
> -bash-3.00# zpool status tank
> pool: tank
> state: ONLINE
> status: One or more devices has experienced an unrecoverable error. An
> attempt was made to correct the error. Applications are
> unaffected.
> action: Determine if the device needs to be replaced, and clear the errors
> using 'zpool clear' or replace the device with 'zpool replace'.
> see: http://www.sun.com/msg/ZFS-8000-9P
> scrub: scrub completed with 0 errors on Tue May 29 19:46:12 2007
> config:
>
> NAME STATE READ WRITE CKSUM
> tank ONLINE 0 0 0
> mirror ONLINE 0 0 0
> c1t0d0 ONLINE 1 0 0
> c1t4d0 ONLINE 0 0 0
>
> errors: No known data errors
> --------------------------------------------------------
>
> So it seems a drive should be replaced but pool is still functioning. I
> still can copy pg_data_dir to other filesystem without any errors. And
> database seems working as usual. So I guess pg_dump should work but It does
> not. I've upgraded to 8.2.4 but still have the same error. Copy
> pg_data_dir to another file system, start postgres with that data_dir, tried
> pg_dump again but still have the error.
>
> So the question is what should I do in order to pg_dump start working as
> usual?
>
>
> Thanks
> Roman.
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* pg_dump: Error message from server: ERROR: compressed data is corrupt
@ 2010-04-23 11:54 Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleas Mantzios @ 2010-04-23 11:54 UTC (permalink / raw)
To: pgsql-admin
Hello,
just coming back from a rescue marathon on this remote server i was telling you.
As i said, the last problem was while doing a
pg_dump dynacom
(dynacom is my db'd name)
i kept getting
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: compressed data is corrupt
pg_dump: The command was: COPY public.mail_entity (msgno, entno, entparentno....
so i tried dumping the individual mail_entity table, which resulted in the following errors:
pg_dump -t mail_entity > /dev/null
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: missing chunk number 2 for toast value 133502683
pg_dump: The command was: COPY public.mail_entity (msgno, entno, entparentno, entdate, entrecvdate, entsendername, entsenderaddr, entmess
ageid, en;
srv:~>
From the 76000 rows in that table i found the damaged columns by using the following technique
(which requires O(log(n)) selects btw lol!!!)
SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/10000 = 0;
SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/10000 = 1;
.... until i found the correct subset of 10000 rows
SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/10000 = 7;
then
SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/1000 = 70;
SELECT msgno,entno,entdate from mail_entity where coalesce(entdata::text,'')||coalesce(enttext,'') like '%foo%' and msgno/1000 = 71;
...
and so on untill i found the two (it was two of them) bad rows.
i did entdata='' for those two rows, and the selects could be done without issues.
however, after that, the pg_dump -t reverted back to the aforementioned error.
Now doing this gives:
srv:~> pg_dump -t mail_entity > /dev/null
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: compressed data is corrupt
pg_dump: The command was: COPY public.mail_entity (msgno, entno, entparentno, entdate, entrecvdate, entsendername, entsenderaddr, entmessageid, en;
srv:~>
Then i did
# CREATE TABLE mail_entity2 AS SELECT * FROM mail_entity;
which went fine
but, for some crazy reason, pg_dump on mail_entity2 also results to an error:
srv:~> pg_dump -t mail_entity2 > /dev/null
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: compressed data is corrupt
pg_dump: The command was: COPY public.mail_entity2 (msgno, entno, entparentno, entdate, entrecvdate, entsendername, entsenderaddr, entmessageid, e;
srv:~>
Is there anything i could do to take this very much wanted dump?
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: compressed data is corrupt
@ 2010-04-23 14:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2010-04-23 14:05 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> Then i did
> # CREATE TABLE mail_entity2 AS SELECT * FROM mail_entity;
> which went fine
> but, for some crazy reason, pg_dump on mail_entity2 also results to an error:
> srv:~> pg_dump -t mail_entity2 > /dev/null
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: compressed data is corrupt
The create/select would not have bothered to decompress any
inline-compressed datums, so the corrupted data went right over to the
new table. I think you'll have to engage in another round of
divide-and-conquer to locate the specific row or rows with a corrupted
value.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: compressed data is corrupt
@ 2010-04-27 12:52 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Achilleas Mantzios @ 2010-04-27 12:52 UTC (permalink / raw)
To: pgsql-admin
Στις Friday 23 April 2010 17:05:46 ο/η Tom Lane έγραψε:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> > Then i did
>
> > # CREATE TABLE mail_entity2 AS SELECT * FROM mail_entity;
>
> > which went fine
>
> > but, for some crazy reason, pg_dump on mail_entity2 also results to an error:
> > srv:~> pg_dump -t mail_entity2 > /dev/null
> > pg_dump: SQL command failed
> > pg_dump: Error message from server: ERROR: compressed data is corrupt
>
> The create/select would not have bothered to decompress any
> inline-compressed datums, so the corrupted data went right over to the
> new table. I think you'll have to engage in another round of
> divide-and-conquer to locate the specific row or rows with a corrupted
> value.
We just removed one memory SIM, and the problem went away.
It must have been a problem caused by on the fly memory hardware error rather than
actual corrupted data on the disk.
I have a decent dump now,
thank you a lot.
>
> regards, tom lane
>
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 16+ messages in thread
* pg_dump: Error message from server: ERROR: missing chunk number
@ 2010-06-15 13:38 Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 2 replies; 16+ messages in thread
From: Silvio Brandani @ 2010-06-15 13:38 UTC (permalink / raw)
To: pgsql-admin
We have a standby database version postgres 8.3.1 on linux .
During pg_dump we get the error:
-- pg_dump: SQL command failed
-- pg_dump: Error message from server: ERROR: missing chunk number 0
for toast value 254723406
-- pg_dump: The command was: COPY helpdesk.attachments_data (id,
filedata, attachment_id) TO stdout;
I have already try to reindex and vacuum full the table. but problem
still stand.
How can we fix this error and get a good dump??--
Any help higly appreciated.
--Silvio Brandani
---
Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: missing chunk number
@ 2010-06-15 14:30 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Silvio Brandani <silvio.brandani@tech.sdb.it>
1 sibling, 0 replies; 16+ messages in thread
From: Kevin Grittner @ 2010-06-15 14:30 UTC (permalink / raw)
To: pgsql-admin; Silvio Brandani <silvio.brandani@tech.sdb.it>
Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
> We have a standby database version postgres 8.3.1 on linux .
You should seriously consider upgrading to a more recent 8.3 bug fix
release. The most current is now 8.3.11. Please read this:
http://www.postgresql.org/support/versioning
There was a bug fix related to TOAST values in 8.3.6, although I'm
not sure whether that could be related to the corruption you
currently have in your database.
> During pg_dump we get the error:
>
> -- pg_dump: SQL command failed
> -- pg_dump: Error message from server: ERROR: missing chunk
> number 0 for toast value 254723406
> -- pg_dump: The command was: COPY helpdesk.attachments_data (id,
> filedata, attachment_id) TO stdout;
> How can we fix this error and get a good dump??--
You need to read sets of rows to narrow down what row or rows are
damaged, capture any usable information from those rows, and then
delete them.
Do you have any idea how the damage occurred? In particular, what
are your settings for fsync and full_page_writes? Have you had any
power outages or OS freezes? Any indication of hardware problems?
Any unusual issues like accidentally starting two PostgreSQL servers
against the same data directory?
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: missing chunk number
@ 2010-06-15 14:34 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Silvio Brandani <silvio.brandani@tech.sdb.it>
1 sibling, 2 replies; 16+ messages in thread
From: Kevin Grittner @ 2010-06-15 14:34 UTC (permalink / raw)
To: pgsql-admin; Silvio Brandani <silvio.brandani@tech.sdb.it>
Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
> We have a standby database
> During pg_dump
Hmm... I just noticed that word "standby" in there. Can you
elaborate on what you mean by that?
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: missing chunk number
@ 2010-06-15 14:50 Silvio Brandani <silvio.brandani@tech.sdb.it>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 16+ messages in thread
From: Silvio Brandani @ 2010-06-15 14:50 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
Kevin Grittner ha scritto:
> Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
>
>
>> We have a standby database
>>
>
>
>> During pg_dump
>>
>
> Hmm... I just noticed that word "standby" in there. Can you
> elaborate on what you mean by that?
>
> -Kevin
>
>
It means it is an istance refreshed (via rsync) from another istance
which is in recovery mode with log shipping and PITR.
So to summarize we have :
- a production istance
- a pitr istance ( log shipping from production)
- a quality istance (the one with error in the backup) which is
refreshed with rsync from the pitr istance.
- a dev istance that we would like to refresh from the quality with
pg_dump / pg_restore
--
Silvio Brandani
---
Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: missing chunk number
@ 2010-06-15 14:56 Silvio Brandani <silvio.brandani@tech.sdb.it>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 16+ messages in thread
From: Silvio Brandani @ 2010-06-15 14:56 UTC (permalink / raw)
To: pgsql-admin; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Kevin Grittner ha scritto:
> Silvio Brandani <silvio.brandani@tech.sdb.it> wrote:
>
>
>> We have a standby database
>>
>
>
>> During pg_dump
>>
>
> Hmm... I just noticed that word "standby" in there. Can you
> elaborate on what you mean by that?
>
> -Kevin
>
>
It means it is an istance refreshed (via rsync) from another istance
which is in recovery mode with log shipping and PITR.
So to summarize we have :
- a production istance
- a pitr istance ( log shipping from production)
- a quality istance (the one with error in the backup) which is
refreshed with rsync from the pitr istance.
- a dev istance that we would like to refresh from the quality with
pg_dump / pg_restore
--
Silvio Brandani
--
Silvio Brandani
Infrastructure Administrator
SDB Information Technology
Phone: +39.055.3811222
Fax: +39.055.5201119
---
Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--
^ permalink raw reply [nested|flat] 16+ messages in thread
* pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another
@ 2011-08-07 16:23 jtkells@verizon.net
0 siblings, 1 reply; 16+ messages in thread
From: jtkells@verizon.net @ 2011-08-07 16:23 UTC (permalink / raw)
To: pgsql-admin
I am having a problem running "pg_dump -s database "on one system
while it runs fine on another system.
Both databases are nearly identical (minor changes to schemas and
tables)
On the older system it is a redhat x.x (32 bit) 12GiB memory running
postgresql 8.4.3 (32 bit)
On the newer system it is aUbuntu 10.04 (64 bit) 8GiB memory running
postgresql 8.4.8 64 bit
Postgresql.conf are identical .
On the redhat the kernel paramters are :
cat /proc/sys/kernel/sh*
/proc/sys/kernel/shmall /proc/sys/kernel/shmmax
/proc/sys/kernel/shmmni
4294967296
68719476736
4096
On Ubuntu I have cranked up the shmmax parameter several time to be
now twice what is configured on the redhat system and reduced
max_connections to less than half of the redhat system
Both systems have the same parameter settings for (except
max_connections which is much higher on the redhat system, I have been
downgrading this parameter on Ubuntu) :
max_connections | 14 | | 1 | 536870911 |
100 << Ubuntu
max_connections | 100 | | 1 | 536870911 |
100 << Redhat
max_files_per_process | 1000 | | 25 | 2147483647 |
1000
max_function_args | 100 | | 100 | 100 |
100
max_identifier_length | 63 | | 63 | 63 |
63
max_index_keys | 32 | | 32 | 32 |
32
max_locks_per_transaction | 64 | | 10 | 2147483647 |
64
max_prepared_transactions | 0 | | 0 | 536870911 | 0
max_stack_depth | 2048 | kB | 100 | 2147483647 |
100
On the Ubuntu the last test that I did I set the sharred_buffer to:
name | setting | unit | min_val | max_val |
boot_val
--------------------------+---------+------+---------+------------+----------
shared_buffers | 32768 | 8kB | 16 | 1073741823 |
1024
shared_preload_libraries | | |
when I run the following dump on the Ubuntu system I get :
pg_dump -s DB >/tmp/DB_schema_only.dmp
pg_dump: WARNING: out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.
pg_dump: The command was: LOCK TABLE schema_x.x_table IN ACCESS SHARE
MODE
This is a very large table with a lot of inheritances.
I don't understand what I am doing wrong since I have given a larger
amount of resources on the Ubuntu system and continue to fail. Am I
missing anything else?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another
@ 2011-08-07 16:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: jtkells@verizon.net
0 siblings, 1 reply; 16+ messages in thread
From: Tom Lane @ 2011-08-07 16:37 UTC (permalink / raw)
To: jtkells@verizon.net; +Cc: pgsql-admin
jtkells@verizon.net writes:
> I am having a problem running "pg_dump -s database "on one system
> while it runs fine on another system.
> when I run the following dump on the Ubuntu system I get :
> pg_dump -s DB >/tmp/DB_schema_only.dmp
> pg_dump: WARNING: out of shared memory
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: out of shared memory
> HINT: You might need to increase max_locks_per_transaction.
> pg_dump: The command was: LOCK TABLE schema_x.x_table IN ACCESS SHARE
> MODE
> I don't understand what I am doing wrong since I have given a larger
> amount of resources on the Ubuntu system and continue to fail. Am I
> missing anything else?
The HINT told you what you need to do: increase max_locks_per_transaction.
The exact point at which you run out of shared memory after exceeding
max_locks_per_transaction will vary depending on a number of
hard-to-predict factors (in this case I'll bet 32-bitness vs 64-bitness
has a lot to do with it), so the fact that it fails on one machine and
not another is not that surprising. You can be sure though that if the
databases are identical, the "working" machine has not got a lot of
headroom; so you'd be well advised to apply the max_locks_per_transaction
adjustment to both.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another
@ 2011-08-08 21:06 jtkells@verizon.net
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: jtkells@verizon.net @ 2011-08-08 21:06 UTC (permalink / raw)
To: pgsql-admin
Tom,
That did the trick. I made a bad assumption that the shared_memory
was causing the problem and not the other way around. I set it up to
256, last attempt was 128 and it still failed, not sure what value
would have given me success (128 - 256) but it needed quite a bit
more.
Thanks for your help
On Sun, 07 Aug 2011 12:37:22 -0400, tgl@sss.pgh.pa.us (Tom Lane)
wrote:
>jtkells@verizon.net writes:
>> I am having a problem running "pg_dump -s database "on one system
>> while it runs fine on another system.
>
>> when I run the following dump on the Ubuntu system I get :
>> pg_dump -s DB >/tmp/DB_schema_only.dmp
>> pg_dump: WARNING: out of shared memory
>> pg_dump: SQL command failed
>> pg_dump: Error message from server: ERROR: out of shared memory
>> HINT: You might need to increase max_locks_per_transaction.
>> pg_dump: The command was: LOCK TABLE schema_x.x_table IN ACCESS SHARE
>> MODE
>
>> I don't understand what I am doing wrong since I have given a larger
>> amount of resources on the Ubuntu system and continue to fail. Am I
>> missing anything else?
>
>The HINT told you what you need to do: increase max_locks_per_transaction.
>
>The exact point at which you run out of shared memory after exceeding
>max_locks_per_transaction will vary depending on a number of
>hard-to-predict factors (in this case I'll bet 32-bitness vs 64-bitness
>has a lot to do with it), so the fact that it fails on one machine and
>not another is not that surprising. You can be sure though that if the
>databases are identical, the "working" machine has not got a lot of
>headroom; so you'd be well advised to apply the max_locks_per_transaction
>adjustment to both.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2011-08-08 21:06 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-29 22:09 pg_dump: Error message from server: ERROR: compressed data is corrupt Roman Chervotkin <roman.chervotkin@gmail.com>
2007-05-30 08:00 ` Roman Chervotkin <roman.chervotkin@gmail.com>
2007-05-30 21:23 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-05-30 19:49 ` Roman Chervotkin <roman.chervotkin@gmail.com>
2007-06-04 21:58 ` Roman Chervotkin <roman.chervotkin@gmail.com>
2010-04-23 11:54 pg_dump: Error message from server: ERROR: compressed data is corrupt Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-04-23 14:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-27 12:52 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2010-06-15 13:38 pg_dump: Error message from server: ERROR: missing chunk number Silvio Brandani <silvio.brandani@tech.sdb.it>
2010-06-15 14:30 ` Re: pg_dump: Error message from server: ERROR: missing chunk number Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-15 14:34 ` Re: pg_dump: Error message from server: ERROR: missing chunk number Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-06-15 14:50 ` Re: pg_dump: Error message from server: ERROR: missing chunk number Silvio Brandani <silvio.brandani@tech.sdb.it>
2010-06-15 14:56 ` Re: pg_dump: Error message from server: ERROR: missing chunk number Silvio Brandani <silvio.brandani@tech.sdb.it>
2011-08-07 16:23 pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another jtkells@verizon.net
2011-08-07 16:37 ` Re: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another Tom Lane <tgl@sss.pgh.pa.us>
2011-08-08 21:06 ` Re: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another jtkells@verizon.net
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox