Thread: BUG #5735: pg_upgrade thinks that it did not start the old server
The following bug has been logged online:
Bug reference: 5735
Logged by: Arturas Mazeika
Email address: mazeika@gmail.com
PostgreSQL version: 9.0
Operating system: Windows Server 2003
Description: pg_upgrade thinks that it did not start the old server
Details:
1. I am trying to migrate from Postgres 8.3 to 9.0.
2. I have installed both systems on Windows Server 2003.
3. I have stopped services of both 8.3 and 9.0.
4. I became postgres user with runas
5. I have started the pg_upgrade command and got an error:
c:\windows\system32>pg_upgrade.exe --old-datadir "I:\PostgreSQL\8.3\data"
--new-
datadir "I:\PostgreSQL\9.0" --old-bindir "C:\Program Files
(x86)\PostgreSQL\8.3\
bin" --new-bindir "C:\Program Files\PostgreSQL\9.0\bin" -l "c:\temp\log"
Performing Consistency Checks
-----------------------------
Checking old data directory (I:\PostgreSQL\8.3\data) ok
Checking old bin directory (C:\Program Files (x86)\PostgreSQL\8.3\bin)ok
Checking new data directory (I:\PostgreSQL\9.0) ok
Checking new bin directory (C:\Program Files\PostgreSQL\9.0\bin)ok
mapped win32 error code 2 to 2Trying to start old server
.................ok
Unable to start old postmaster with the command: ""C:\Program Files
(x86)\Postg
reSQL\8.3\bin/pg_ctl" -l "nul" -D "I:\PostgreSQL\8.3\data" -o "-p 5432 -c
autova
cuum=off -c autovacuum_freeze_max_age=2000000000" start >> "nul" 2>&1"
Perhaps pg_hba.conf was not set to "trust".
c:\windows\system32>
The command starts the server (I can see that through process explorer, I
can connect to the DB too after I get the error)
6. pg_hba.conf has a line:
local all all trust
This seems to be a bug, doesn't it? Did I misconfigure anything?
On Sat, Oct 30, 2010 at 3:29 PM, Arturas Mazeika <mazeika@gmail.com> wrote: > > ----------------------------- > Checking old data directory (I:\PostgreSQL\8.3\data) =A0 =A0 =A0 =A0ok > Checking old bin directory (C:\Program Files (x86)\PostgreSQL\8.3\bin)ok > Checking new data directory (I:\PostgreSQL\9.0) =A0 =A0 =A0 =A0 =A0 =A0 ok > Checking new bin directory (C:\Program Files\PostgreSQL\9.0\bin)ok I can't comment on the problem reported as I'm not that familiar with pg_upgrade, but from the paths above, it looks like you're trying to upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't going to work without a dump/restore. With pg_upgrade, the two builds need to be from the same platform, same word size, and have the same configuration for certain settings like integer_datetimes. --=20 Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Hi Dave, Thanks for the info, this explains a lot. Yes, I am upgrading from the 32bit version to the 64bit one. We have pretty large databases (some over 1 trillion of rows, and some containing large documents in blobs.) Giving a bit more memory than 4GB limit to Postgres was what we were long longing for. Postgres was able to handle large datasets (I suppose it uses something like long long (64bit) data type in C++) and I hoped naively that Postgres would be able to migrate from one version to the other without too much trouble. I tried to pg_dump one of the DBs with large documents. I failed with out of memory error. I suppose it is rather hard to migrate in my case :-( Any suggestions? Thanks, arturas On 10/30/2010 7:33 PM, Dave Page wrote: > upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't > going to work without a dump/restore. With pg_upgrade, the two builds > need to be from the same platform, same word size, and have the same > configuration for certain settings like integer_datetimes. >
On Sat, Oct 30, 2010 at 9:30 PM, Arturas Mazeika <mazeika@gmail.com> wrote: > Thanks for the info, this explains a lot. > > Yes, I am upgrading from the 32bit version to the 64bit one. > > We have pretty large databases =A0(some over 1 trillion of rows, and some > containing large documents in blobs.) Giving a bit more memory than 4GB > limit to Postgres was what we were long longing for. Postgres was able to > handle large datasets (I suppose it uses something like long long (64bit) > data type in C++) and I hoped naively that Postgres would be able to migr= ate > from one version to the other without too much trouble. > > I tried to pg_dump one of the DBs with large documents. I failed with out= of > memory error. I suppose it is rather hard to migrate in my case :-( Any > suggestions? Yikes, that's not good. How many tables do you have in your database? How many large objects? Any chance you can coax a stack trace out of pg_dump? --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: BUG #5735: pg_upgrade thinks that it did not start the old server
From
Stefan Kaltenbrunner
Date:
Robert Haas wrote: > On Sat, Oct 30, 2010 at 9:30 PM, Arturas Mazeika <mazeika@gmail.com> wrote: >> Thanks for the info, this explains a lot. >> >> Yes, I am upgrading from the 32bit version to the 64bit one. >> >> We have pretty large databases (some over 1 trillion of rows, and some >> containing large documents in blobs.) Giving a bit more memory than 4GB >> limit to Postgres was what we were long longing for. Postgres was able to >> handle large datasets (I suppose it uses something like long long (64bit) >> data type in C++) and I hoped naively that Postgres would be able to migrate >> from one version to the other without too much trouble. >> >> I tried to pg_dump one of the DBs with large documents. I failed with out of >> memory error. I suppose it is rather hard to migrate in my case :-( Any >> suggestions? > > Yikes, that's not good. How many tables do you have in your database? > How many large objects? Any chance you can coax a stack trace out of > pg_dump? well the usually problem is that it is fairly easy to get large (several hundred megabytes) large bytea objects into the database but upon retrieval we tend to take up to 3x the size of the object as actual memory consumption which causes us to hit all kind of limits(especially on 32bit boxes). We really need to look into reducing that or putting a more prominent "don't use bytea for anything larger than say 50MByte) Stefan
Hi Robert, On 11/08/2010 10:23 PM, Robert Haas wrote: > On Sat, Oct 30, 2010 at 9:30 PM, Arturas Mazeika<mazeika@gmail.com> wrote: > >> Thanks for the info, this explains a lot. >> >> Yes, I am upgrading from the 32bit version to the 64bit one. >> >> We have pretty large databases (some over 1 trillion of rows, and some >> containing large documents in blobs.) Giving a bit more memory than 4GB >> limit to Postgres was what we were long longing for. Postgres was able to >> handle large datasets (I suppose it uses something like long long (64bit) >> data type in C++) and I hoped naively that Postgres would be able to migrate >> from one version to the other without too much trouble. >> >> I tried to pg_dump one of the DBs with large documents. I failed with out of >> memory error. I suppose it is rather hard to migrate in my case :-( Any >> suggestions? >> > Yikes, that's not good. How many tables do you have in your database? > How many large objects? Any chance you can coax a stack trace out of > pg_dump? > We are storing the UKGOV Web archive [1] in a Postgres DB. There are two large tables and a dozen of small tables. The large tables are pages and links. Pages describe information about the Web page, including its compressed content (this is usually tens KBs, sometimes MBs). Links describe link structure between the Web pages. The pages table occupies some 176GB (50M rows), while links occupy 32GB (500M rows). All databases that need to be migrated occupy 1.07TB. BTW, in Postgres, the shared buffers is set to 128M, and the working mem is set to 1GB. We've got 16GB memory in total (the machine is rather an older one). I'll try to dump the DB again, and get the stack trace. This may take quite a while. [1] http://www.europarchive.org/ukgov.php
Arturas Mazeika <mazeika@gmail.com> wrote: > the shared buffers is set to 128M, and the working mem > is set to 1GB. We've got 16GB memory in total Each connection can allocate work_mem memory, potentially multiple times -- for multiple nodes in a query plan. -Kevin
Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
> well the usually problem is that it is fairly easy to get large (several
> hundred megabytes) large bytea objects into the database but upon
> retrieval we tend to take up to 3x the size of the object as actual
> memory consumption which causes us to hit all kind of limits(especially
> on 32bit boxes).
It occurs to me that one place that might be unnecessarily eating
backend memory during pg_dump is encoding conversion during COPY OUT.
Make sure that pg_dump isn't asking for a conversion to some other
encoding than what the database uses. I think the default is to avoid
conversion, so this might be a dead end --- but if for instance you
had PGCLIENTENCODING set in the client environment, it could bite you.
regards, tom lane
Arturas Mazeika wrote: > Hi Dave, > > Thanks for the info, this explains a lot. > > Yes, I am upgrading from the 32bit version to the 64bit one. > > We have pretty large databases (some over 1 trillion of rows, and some > containing large documents in blobs.) Giving a bit more memory than 4GB > limit to Postgres was what we were long longing for. Postgres was able > to handle large datasets (I suppose it uses something like long long > (64bit) data type in C++) and I hoped naively that Postgres would be > able to migrate from one version to the other without too much trouble. > > I tried to pg_dump one of the DBs with large documents. I failed with > out of memory error. I suppose it is rather hard to migrate in my case > :-( Any suggestions? > > Thanks, > arturas > > On 10/30/2010 7:33 PM, Dave Page wrote: > > upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't > > going to work without a dump/restore. With pg_upgrade, the two builds > > need to be from the same platform, same word size, and have the same > > configuration for certain settings like integer_datetimes. Can anyone suggest a way pg_upgrade could detect an upgrade from a 32-bit to 64-bit cpu and throw an error? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
>> On 10/30/2010 7:33 PM, Dave Page wrote:
>>> upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't
>>> going to work without a dump/restore. With pg_upgrade, the two builds
>>> need to be from the same platform, same word size, and have the same
>>> configuration for certain settings like integer_datetimes.
> Can anyone suggest a way pg_upgrade could detect an upgrade from a
> 32-bit to 64-bit cpu and throw an error?
Surely it does that already, as a result of comparing pg_control
contents.
regards, tom lane
On 11/10/2010 05:32 AM, Tom Lane wrote: > Bruce Momjian<bruce@momjian.us> writes: > >>> On 10/30/2010 7:33 PM, Dave Page wrote: >>> >>>> upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't >>>> going to work without a dump/restore. With pg_upgrade, the two builds >>>> need to be from the same platform, same word size, and have the same >>>> configuration for certain settings like integer_datetimes. >>>> >> Can anyone suggest a way pg_upgrade could detect an upgrade from a >> 32-bit to 64-bit cpu and throw an error? >> > Surely it does that already, as a result of comparing pg_control > contents. > The HTML manual might need an update or a small clarification too. Currently, it does not seem that the manual explicitly states that ``pg_upgrade is not applicable in upgrading 32bit systems to 64bit ones''. A good place to write such a sentence would be at the beginning of [1], at the intro of F.32. pg_upgrade. Maybe the documentation already implicitly states that in F.32.4. Limitations in Migrating from PostgreSQL 8.3 section of [1] by this description: ``For Windows users, note that due to different integer datetimes settings used by the one-click installer and the MSI installer, it is only possible to upgrade from version 8.3 of the one-click distribution to version 8.4 or later of the one-click distribution. It is not possible to upgrade from the MSI installer to the one-click installer.'' Unfortunately, I could not understand in full detail the above. Thanks, arturas [1] http://www.postgresql.org/docs/9.0/static/pgupgrade.html
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> >> On 10/30/2010 7:33 PM, Dave Page wrote:
> >>> upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't
> >>> going to work without a dump/restore. With pg_upgrade, the two builds
> >>> need to be from the same platform, same word size, and have the same
> >>> configuration for certain settings like integer_datetimes.
>
> > Can anyone suggest a way pg_upgrade could detect an upgrade from a
> > 32-bit to 64-bit cpu and throw an error?
>
> Surely it does that already, as a result of comparing pg_control
> contents.
Surely it does, but I didn't understand how the user able to run
pg_upgrade? I see now that he failed before we completed our checks so
he would have gotten an error later if he could have started his server:
http://archives.postgresql.org/pgsql-bugs/2010-10/msg00282.php
Thanks.
Not sure why he was unable to start the old server, but we decided he
couldn't use pg_upgrade anyway in his setup.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Arturas Mazeika wrote: > On 11/10/2010 05:32 AM, Tom Lane wrote: > > Bruce Momjian<bruce@momjian.us> writes: > > > >>> On 10/30/2010 7:33 PM, Dave Page wrote: > >>> > >>>> upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't > >>>> going to work without a dump/restore. With pg_upgrade, the two builds > >>>> need to be from the same platform, same word size, and have the same > >>>> configuration for certain settings like integer_datetimes. > >>>> > >> Can anyone suggest a way pg_upgrade could detect an upgrade from a > >> 32-bit to 64-bit cpu and throw an error? > >> > > Surely it does that already, as a result of comparing pg_control > > contents. > > > > The HTML manual might need an update or a small clarification too. > Currently, it does not seem that the manual explicitly states that > ``pg_upgrade is not applicable in upgrading 32bit systems to 64bit > ones''. A good place to write such a sentence would be at the beginning > of [1], at the intro of F.32. pg_upgrade. Maybe the documentation > already implicitly states that in F.32.4. Limitations in Migrating from > PostgreSQL 8.3 section of [1] by this description: > > ``For Windows users, note that due to different integer datetimes > settings used by the one-click installer and the MSI installer, it is > only possible to upgrade from version 8.3 of the one-click distribution > to version 8.4 or later of the one-click distribution. It is not > possible to upgrade from the MSI installer to the one-click installer.'' > > Unfortunately, I could not understand in full detail the above. > > Thanks, > arturas > > [1] http://www.postgresql.org/docs/9.0/static/pgupgrade.html I have added a mention about 32/64-bit isssues to the pg_upgrade manual page, attached. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + commit 8f742d1cdab987ba4624ad0c6aa008ced15cd87c Author: Bruce Momjian <bruce@momjian.us> Date: Wed Nov 10 14:08:30 2010 +0000 Mention that pg_upgrade requires compatible 32/64-bit binaries. diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml index 6d2cdaa..9081cc9 100644 --- a/doc/src/sgml/pgupgrade.sgml +++ b/doc/src/sgml/pgupgrade.sgml @@ -21,7 +21,8 @@ regularly added to PostgreSQL major releases, the internal data storage format rarely changes. <application>pg_upgrade</> does its best to make sure the old and new clusters are binary-compatible, e.g. by - checking for compatible compile-time settings. It is important that + checking for compatible compile-time settings, including 32/64-bit + binaries. It is important that any external modules are also binary compatible, though this cannot be checked by <application>pg_upgrade</>. </para>