[GENERAL] Re: pg_restore fails on Windows
Magnus Hagander wrote: > >>> 2.Our production PG version is 8.1.3. For some reasons it is not possible > to > >> upgrade to the LATEST; > >>> I tested the libpq also on this version and it worked. Is it OK? I mean, > did > >> it worked by chance or the library > >>> API & contracts didn't change between this version and latest? > >> Note that libpq is only the *client* side. There is no patch necessary > >> on the server. It might be easier to upgrade than the server? > > > > This I didn't know/realize. It's good enough for us to use only the *client* > side from the HEAD. > > I tried the pg_restore from HEAD + patched libpq (on 8.1 installation) and > > it > complained about missing zlib1 library. When > > supplied, next was libintl3 dll. Further I didn't check. Obviously the > > library > dependencies have changed since the 8.1. > > How can I tell, which libraries/executables/resources of the installation > > are > part of the *client* side (namely pg_restore), > > so that I can use it independently from the server version? > > That is correct, there have been a lot of changes there - mainly in that > 8.3 and up are compiled with MSVC which changes some dependencies. I > would suggest you install the latest 8.3 from the MSI installer. You can > then select the client side only. It is no problem to have both 8.1 and > 8.3 installed on the same machine at the same time (or you can install > it on a different machine and just copy the necessary files over) > OK, that is fine. Thank you for your support, we are happy with the provided solution. You are doing a great job. Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: pg_restore fails on Windows
Magnus Hagander wrote > Tom Tom wrote: > > Magnus Hagander wrote > >> Tom Lane wrote: > >>> =?us-ascii?Q?Tom=20Tom?= <[EMAIL PROTECTED]> writes: > Magnus Hagander wrote: > > Attached is a pg_restore.exe off CVS tip today, which should include the > > patch. Please try this one. > I tested the restore using the provided pg_restore.exe. The output is: > pg_restore: [archiver (db)] could not execute query: could not send data > to > >> server: No buffer space available (0x2747/10055) > >>> According to > >>> http://support.microsoft.com/kb/201213 > >>> this is an acknowledged bug that's been broken since Windows 95, so > >>> I suppose we should conclude that M$ is unwilling or incompetent to > >>> fix it. > >> Yup, I was just reading that one when I saw your email. I finally got > >> around to building a libpq with this change in it - attached here. Tom > >> (not Lane), can you test this please? > >> > >> It shouldn't be this one really, since it doesn't list any modern > >> Windows versions as having this issue, but it's worth a try. > > > > Tested. The restore comes through successfuly with the patched libpq. > > So I take it that it's caused by the MS issue. Again, we are using WinXP > Professional SP2. Perhaps the > > system buffer space was _increased_ in XP (10MB comes through easily), > > still if the block is too large, it occurs (speculation). > > Yes, that sounds quite likely. They fixed the symptoms, but not the > underlying problem. > > > > Since I don't know the implementation details of the patch I'd like to ask: > > 1.This is not official patch, didn't pass the review/test cycle; do you > > think > that it can be used in the > > production environment (any side effects or so..)? If not, is the patch due > for a next version? > > I plan to apply it to HEAD and supported back-branches (8.3 and 8.2) now > that you have verified that it works, so it will be in the next > versions. The only potential side-effect is that it will be slightly > slower on packets >64kb, but I doubt that's even measurable in most cases. > > So yes, it should be safe to use in production. > > > > 2.Our production PG version is 8.1.3. For some reasons it is not possible to > upgrade to the LATEST; > > I tested the libpq also on this version and it worked. Is it OK? I mean, did > it worked by chance or the library > > API & contracts didn't change between this version and latest? > > Note that libpq is only the *client* side. There is no patch necessary > on the server. It might be easier to upgrade than the server? This I didn't know/realize. It's good enough for us to use only the *client* side from the HEAD. I tried the pg_restore from HEAD + patched libpq (on 8.1 installation) and it complained about missing zlib1 library. When supplied, next was libintl3 dll. Further I didn't check. Obviously the library dependencies have changed since the 8.1. How can I tell, which libraries/executables/resources of the installation are part of the *client* side (namely pg_restore), so that I can use it independently from the server version? > > Did you test it with the pg_restore that I sent, or with the one from > 8.1? The pg_restore I sent was for HEAD, as well as the libpq I sent, so > you shouldn't use those in production long-term. > > For binaries, we don't provide backpatches for 8.1 any more (it's not a > supported platform on Windows!), but you might be able to use the latest > 8.2 libpq with the 8.1 pg_restore - you'll have to try that once the > release is eventually out. > > Or you can just apply the patch to the latest 8.1 libpq and build it > yourself, of course. I think it should apply just fine. > 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] Re: pg_restore fails on Windows
=?us-ascii?Q?Tom=20Tom?= <[EMAIL PROTECTED]> writes: > Magnus Hagander wrote: >> Attached is a pg_restore.exe off CVS tip today, which should include the >> patch. Please try this one. > I tested the restore using the provided pg_restore.exe. The output is: > pg_restore: [archiver (db)] could not execute query: could not send data to > server: No buffer space available (0x2747/10055) According to http://support.microsoft.com/kb/201213 this is an acknowledged bug that's been broken since Windows 95, so I suppose we should conclude that M$ is unwilling or incompetent to fix it. Possibly the best workaround is something like + #ifndef WIN32 sent = pqsecure_write(conn, ptr, len); + #else + /* Windows tends to fail on large sends, see KB 20213 */ + sent = pqsecure_write(conn, ptr, Min(len, 65536)); + #endif in pqSendSome(). The backend seems to not be subject to a similar problem because it's already filtering its output through a limited-size buffer. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: pg_restore fails on Windows
Magnus Hagander wrote:
> Tom Lane wrote:
> > I wrote:
> >> Of course the larger issue is why it's failing --- 150MB doesn't seem
> >> like that much for a modern machine. I suspect that PQerrorMessage()
> >> would tell us something useful, but pg_restore isn't letting us see it.
> >
> > I've applied a patch for the latter issue. But the only way we can find
> > out what's happening is if someone will build a Windows version from CVS
> > tip for the OP...
>
> Attached is a pg_restore.exe off CVS tip today, which should include the
> patch. Please try this one.
>
> //Magnus
>
I tested the restore using the provided pg_restore.exe. The output is:
for --inserts mode:
---
pg_restore: connecting to database for restore
Password:
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating TABLE hibtableattachmentxmldata
pg_restore: restoring data for table "hibtableattachmentxmldata"
pg_restore: [archiver (db)] Error from TOC entry 1731; 0 16429 TABLE DATA
hibtableattachmentxmldata postgres
pg_restore: [archiver (db)] could not execute query: could not send data to
server: No buffer space available (0x274
7/10055)
Command was: INSERT INTO hibtableattachmentxmldata VALUES ('',
'
F...
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for TABLE hibtableattachmentxmldata
WARNING: errors ignored on restore: 1
for COPY mode
pg_restore: connecting to database for restore
Password:
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating TABLE hibtableattachmentxmldata
pg_restore: restoring data for table "hibtableattachmentxmldata"
pg_restore: [archiver (db)] error returned by PQputCopyData: could not send
data to server: No buffer space available (0
x2747/10055)
pg_restore: *** aborted because of error
The restore in the COPY mode was obviously aborted, whereas the --inserts
finished with warnings.
Note: the provided pg_restore.exe is not compiled with the support for
compressed dumps.
Regards,
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] Re: pg_restore fails on Windows
I wrote: > Of course the larger issue is why it's failing --- 150MB doesn't seem > like that much for a modern machine. I suspect that PQerrorMessage() > would tell us something useful, but pg_restore isn't letting us see it. I've applied a patch for the latter issue. But the only way we can find out what's happening is if someone will build a Windows version from CVS tip for the OP... regards, tom lane -- 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] Re: pg_restore fails on Windows
=?us-ascii?Q?Tom=20Tom?= <[EMAIL PROTECTED]> writes: > Magnus Hagander wrote: >> Can you set up a reproducible test-case that doesn't involve your data, > - test row was inserted using the Java client code > INSERT INTO hibtableattachmentxmldata VALUES (?,?,?) > where value 1 is "" > value 2 is 25MB (i.e. 1024*1024*25) long text of char 'F' (0x46) > value 3 is 25MB (i.e. 1024*1024*25) long byte array filled with values of 5 > (0x5) Hmm. So allowing for escaping of the bytea values, this line is going to be somewhere around 150MB in text form --- and because you used --inserts rather than COPY mode, it will have to be sent in a single message. I think it's pretty obvious that what's happening is we're failing to do that and then not recovering nicely at all. A look at the pg_dump code says the error message is coming from here res = PQexec(conn, qry->data); if (!res) die_horribly(AH, modulename, "%s: no result from server\n", desc); and a look at the libpq code suggests that PQexec will return a NULL on any send failure, which isn't part of its contract either. So we've got robustness issues on both sides of that API :-( Of course the larger issue is why it's failing --- 150MB doesn't seem like that much for a modern machine. I suspect that PQerrorMessage() would tell us something useful, but pg_restore isn't letting us see it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: pg_restore fails on Windows
Magnus Hagander wrote: > Tom Tom wrote: > > Magnus Hagander wrote: > >> Tom Tom wrote: > Tom Tom wrote: > > Hello, > > > > We have a very strange problem when restoring a database on Windows XP. > > The PG version is 8.1.10 > > The backup was made with the pg_dump on the same machine. > > > > pg_restore -F c -h localhost -p 5432 -U postgres -d "configV3" -v > "c:\Share\POSTGRES.backup" > > pg_restore: connecting to database for restore > > Password: > > pg_restore: creating SCHEMA public > > pg_restore: creating COMMENT SCHEMA public > > pg_restore: creating PROCEDURAL LANGUAGE plpgsql > > pg_restore: creating SEQUENCE hi_value > > pg_restore: executing SEQUENCE SET hi_value > > pg_restore: creating TABLE hibconfigelement > > pg_restore: creating TABLE hibrefconfigbase > > pg_restore: creating TABLE hibrefconfigreference > > pg_restore: creating TABLE hibtableattachment > > pg_restore: creating TABLE hibtableattachmentxmldata > > pg_restore: creating TABLE hibtableelementversion > > pg_restore: creating TABLE hibtableelementversionxmldata > > pg_restore: creating TABLE hibtablerootelement > > pg_restore: creating TABLE hibtablerootelementxmldata > > pg_restore: creating TABLE hibtableunversionedelement > > pg_restore: creating TABLE hibtableunversionedelementxmldata > > pg_restore: creating TABLE hibtableversionedelement > > pg_restore: creating TABLE hibtableversionedelementxmldata > > pg_restore: creating TABLE versionedelement_history > > pg_restore: creating TABLE versionedelement_refs > > pg_restore: restoring data for table "hibconfigelement" > > pg_restore: restoring data for table "hibrefconfigbase" > > pg_restore: restoring data for table "hibrefconfigreference" > > pg_restore: restoring data for table "hibtableattachment" > > pg_restore: restoring data for table "hibtableattachmentxmldata" > > pg_restore: [archiver (db)] could not execute query: no result from > server > > pg_restore: *** aborted because of error > > > > The restore unexpectedly fails on hibtableattachmentxmldata table, which > is > >> as > follows: > > CREATE TABLE hibtablerootelementxmldata > > ( > > xmldata_id varchar(255) NOT NULL, > > xmldata text > > ) > > WITHOUT OIDS; > > > > and contains thousands of rows with text field having even 40MB, encoded > in > UTF8. > > The database is created as follows: > > > > CREATE DATABASE "configV3" > > WITH OWNER = postgres > >ENCODING = 'UTF8' > >TABLESPACE = pg_default; > > > > > > The really strange is that the db restore runs OK on linux (tested on > >> RHEL4, > PG version 8.1.9). > > The pg_restore output is _not_ very descriptive but I suspect some > >> dependency > on OS system libraries (encoding), or maybe it is also related to the > size > >> of > the CLOB field. Anyway we are now effectively without any possibility to > >> backup > our database, which is VERY serious. > > Have you ever came across something similar to this? > Check what you have in your server logs (pg_log directory) and the > eventlog around this time. There is probably a better error message > available there. > > //Magnus > > >>> Thank you for your hint. > >>> The server logs does not display any errors, except for > >>> > >>> 2008-08-08 11:14:16 CEST LOG: checkpoints are occurring too frequently > >>> (14 > >> seconds apart) > >>> 2008-08-08 11:14:16 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:14:38 CEST LOG: checkpoints are occurring too frequently > >>> (22 > >> seconds apart) > >>> 2008-08-08 11:14:38 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:14:57 CEST LOG: checkpoints are occurring too frequently > >>> (19 > >> seconds apart) > >>> 2008-08-08 11:14:57 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:15:14 CEST LOG: checkpoints are occurring too frequently > >>> (17 > >> seconds apart) > >>> 2008-08-08 11:15:14 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:15:36 CEST LOG: checkpoints are occurring too frequently > >>> (22 > >> seconds apart) > >>> 2008-08-08 11:15:36 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:15:56 CEST LOG: checkpoints are occurring too frequently > >>> (20 > >> seconds apart) > >>> 2008-08-08 11:15:56 CEST HINT: Consider increasing the configuration > >> parameter "checkpoint_segments". > >>> 2008-08-08 11:16:16 CEST LOG: checkpoints are occurring too frequently > >>> (20 > >> seconds apart) > >>> 2008-08-08 11:16:16 CEST HINT: Consid
[GENERAL] Re: pg_restore fails on Windows
Magnus Hagander wrote: > Tom Tom wrote: > >> Tom Tom wrote: > >>> Hello, > >>> > >>> We have a very strange problem when restoring a database on Windows XP. > >>> The PG version is 8.1.10 > >>> The backup was made with the pg_dump on the same machine. > >>> > >>> pg_restore -F c -h localhost -p 5432 -U postgres -d "configV3" -v > >> "c:\Share\POSTGRES.backup" > >>> pg_restore: connecting to database for restore > >>> Password: > >>> pg_restore: creating SCHEMA public > >>> pg_restore: creating COMMENT SCHEMA public > >>> pg_restore: creating PROCEDURAL LANGUAGE plpgsql > >>> pg_restore: creating SEQUENCE hi_value > >>> pg_restore: executing SEQUENCE SET hi_value > >>> pg_restore: creating TABLE hibconfigelement > >>> pg_restore: creating TABLE hibrefconfigbase > >>> pg_restore: creating TABLE hibrefconfigreference > >>> pg_restore: creating TABLE hibtableattachment > >>> pg_restore: creating TABLE hibtableattachmentxmldata > >>> pg_restore: creating TABLE hibtableelementversion > >>> pg_restore: creating TABLE hibtableelementversionxmldata > >>> pg_restore: creating TABLE hibtablerootelement > >>> pg_restore: creating TABLE hibtablerootelementxmldata > >>> pg_restore: creating TABLE hibtableunversionedelement > >>> pg_restore: creating TABLE hibtableunversionedelementxmldata > >>> pg_restore: creating TABLE hibtableversionedelement > >>> pg_restore: creating TABLE hibtableversionedelementxmldata > >>> pg_restore: creating TABLE versionedelement_history > >>> pg_restore: creating TABLE versionedelement_refs > >>> pg_restore: restoring data for table "hibconfigelement" > >>> pg_restore: restoring data for table "hibrefconfigbase" > >>> pg_restore: restoring data for table "hibrefconfigreference" > >>> pg_restore: restoring data for table "hibtableattachment" > >>> pg_restore: restoring data for table "hibtableattachmentxmldata" > >>> pg_restore: [archiver (db)] could not execute query: no result from server > >>> pg_restore: *** aborted because of error > >>> > >>> The restore unexpectedly fails on hibtableattachmentxmldata table, which > >>> is > as > >> follows: > >>> CREATE TABLE hibtablerootelementxmldata > >>> ( > >>> xmldata_id varchar(255) NOT NULL, > >>> xmldata text > >>> ) > >>> WITHOUT OIDS; > >>> > >>> and contains thousands of rows with text field having even 40MB, encoded > >>> in > >> UTF8. > >>> The database is created as follows: > >>> > >>> CREATE DATABASE "configV3" > >>> WITH OWNER = postgres > >>>ENCODING = 'UTF8' > >>>TABLESPACE = pg_default; > >>> > >>> > >>> The really strange is that the db restore runs OK on linux (tested on > RHEL4, > >> PG version 8.1.9). > >>> The pg_restore output is _not_ very descriptive but I suspect some > dependency > >> on OS system libraries (encoding), or maybe it is also related to the size > of > >> the CLOB field. Anyway we are now effectively without any possibility to > backup > >> our database, which is VERY serious. > >>> Have you ever came across something similar to this? > >> Check what you have in your server logs (pg_log directory) and the > >> eventlog around this time. There is probably a better error message > >> available there. > >> > >> //Magnus > >> > > > > Thank you for your hint. > > The server logs does not display any errors, except for > > > > 2008-08-08 11:14:16 CEST LOG: checkpoints are occurring too frequently (14 > seconds apart) > > 2008-08-08 11:14:16 CEST HINT: Consider increasing the configuration > parameter "checkpoint_segments". > > 2008-08-08 11:14:38 CEST LOG: checkpoints are occurring too frequently (22 > seconds apart) > > 2008-08-08 11:14:38 CEST HINT: Consider increasing the configuration > parameter "checkpoint_segments". > > 2008-08-08 11:14:57 CEST LOG: checkpoints are occurring too frequently (19 > seconds apart) > > 2008-08-08 11:14:57 CEST HINT: Consider increasing the configuration > parameter "checkpoint_segments". > > 2008-08-08 11:15:14 CEST LOG: checkpoints are occurring too frequently (17 > seconds apart) > > 2008-08-08 11:15:14 CEST HINT: Consider increasing the configuration > parameter "checkpoint_segments". > > 2008-08-08 11:15:36 CEST LOG: checkpoints are occurring too frequently (22 > seconds apart) > > 2008-08-08 11:15:36 CEST HINT: Consider increasing the configuration > parameter "checkpoint_segments". > > 2008-08-08 11:15:56 CEST LOG: checkpoints are occurring too frequently (20 > seconds apart) > > 2008-08-08 11:15:56 CEST HINT: Consider increasing the configuration > parameter "checkpoint_segments". > > 2008-08-08 11:16:16 CEST LOG: checkpoints are occurring too frequently (20 > seconds apart) > > 2008-08-08 11:16:16 CEST HINT: Consider increasing the configuration > parameter "checkpoint_segments". > > > > The warnings disappeared when the "checkpoint_segments" value was increased > > to > 10. The restore still failed however :( > > The Windows eventlogs show no errors, just informational messages about > starting/stopping the pg service. > >
