Re: 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(at)gmail(dot)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(at)gmail(dot)com> wrote:
>
> -----------------------------
> 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

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.

--
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(at)gmail(dot)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?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi Robert,

On 11/08/2010 10:23 PM, Robert Haas wrote:
> On Sat, Oct 30, 2010 at 9:30 PM, Arturas Mazeika<mazeika(at)gmail(dot)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

Robert Haas wrote:
> On Sat, Oct 30, 2010 at 9:30 PM, Arturas Mazeika <mazeika(at)gmail(dot)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

Arturas Mazeika <mazeika(at)gmail(dot)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(at)kaltenbrunner(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +