pg_upgrade issues
PostgreSQL 9.0 beta 2
Windows XP Professional SP2
While migrating the database from 8.4 to 9.0 using pg_upgrade (now part of the Postgres project), the following issues came up:
1. When using the --logfile option, pg_upgrade quits with an error like this:
The process cannot access the file because it is being used by another process.
There were problems executing ""C:\PostgreSQL\8.4\bin/pg_ctl" -l "pg_upgrade.log
" -D "D:\PostgreSQL84_matlab1b" -o "-p 5432 -c autovacuum=off -c autovacuum_free
ze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1"
2. Although pg_upgrade has a username option, this option is not used when pg_dump is called, which tries to use the current logon account name as the user name. As a result, pg_upgrade can only be used when the command shell is launched under the postgres account. (I know that this is mentioned on the doc page, but this doesn't seem right).
3. The old database had the pgadmin debugger installed. The module is part of 8.4 and 9.0 distributions for Windows. However, pg_upgrade reported the following error:
ERROR: could not load library "C:/PostgreSQL/9.0/lib/pldbgapi.dll": The specified module could not be found.
I had to uninstall the debugger from the old database before I could proceed.
Another issue:
4. The --link option doesn't seem to work on Windows: pg_upgrade still copies data from the old cluster to the new. There doesn't appear to be a way to upgrade a database on Windows without copying the entire uncompressed database, which can be a problem where disk space is limited.
Import Notes
Resolved by subject fallback
depstein@alliedtesting.com wrote:
BBBBBB> PostgreSQL 9.0 beta 2
Windows XP Professional SP2
While migrating the database from 8.4 to 9.0 using pg_upgrade (now part of the Postgres project), the following issues came up:
1. When using the --logfile option, pg_upgrade quits with an error like this:
The process cannot access the file because it is being used by another process.
There were problems executing ""C:\PostgreSQL\8.4\bin/pg_ctl" -l "pg_upgrade.log
" -D "D:\PostgreSQL84_matlab1b" -o "-p 5432 -c autovacuum=off -c autovacuum_free
ze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1"
Hiroshi, can you comment on the above bug report? I was able to
reproduce this on XP. I think we added -l for Win32 because the code
says:
/* use -l for Win32 */
snprintf(cmd, sizeof(cmd),
SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" "
but I don't remember the details, and cvs.pgfoundry.org is down right
now. Thanks.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
depstein@alliedtesting.com wrote:
PostgreSQL 9.0 beta 2 Windows XP Professional SP2
While migrating the database from 8.4 to 9.0 using pg_upgrade (now part
of the Postgres project), the following issues came up:1. When using the --logfile option, pg_upgrade quits with an error like
this:The process cannot access the file because it is being used by another
process.There were problems executing ""C:\PostgreSQL\8.4\bin/pg_ctl" -l
"pg_upgrade.log " -D "D:\PostgreSQL84_matlab1b" -o "-p 5432 -c
autovacuum=off -c autovacuum_free ze_max_age=2000000000" start >>
"pg_upgrade.log" 2>&1"
[ Email moved to hackers list.]
Ah, interesting. I did some research and it turns out there is no way to
send server output and pg_ctl output to the same file on Win32. I have
updated the pg_ctl docs to reflect this (first attached patch). I have
also developed a patch (second attached patch) that sends pg_ctl output
to 'nul' on win32 so we can store the server output in the log file.
2. Although pg_upgrade has a username option, this option is not used
when pg_dump is called, which tries to use the current logon account
name as the user name. As a result, pg_upgrade can only be used when
the command shell is launched under the postgres account. (I know that
this is mentioned on the doc page, but this doesn't seem right).
Thanks, fixed in second attached patch.
3. The old database had the pgadmin debugger installed. The module is
part of 8.4 and 9.0 distributions for Windows. However, pg_upgrade
reported the following error:ERROR: could not load library "C:/PostgreSQL/9.0/lib/pldbgapi.dll":
The specified module could not be found.I had to uninstall the debugger from the old database before I could
proceed.
Uh, pg_upgrade has to have the same dll's, so I suggest you install that
into the new server the same way you did in the old server. If it was
installed by the old installer, the new installer should have done the
same.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Attachments:
/rtmp/doc.difftext/x-diffDownload
Index: ref/pg_ctl-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_ctl-ref.sgml,v
retrieving revision 1.50
diff -c -r1.50 pg_ctl-ref.sgml
*** ref/pg_ctl-ref.sgml 8 Apr 2010 01:39:37 -0000 1.50
--- ref/pg_ctl-ref.sgml 22 Jun 2010 16:18:31 -0000
***************
*** 134,149 ****
<para>
In <option>start</option> mode, a new server is launched. The
! server is started in the background, and standard input is attached to
! <filename>/dev/null</filename>. The standard output and standard
! error are either appended to a log file (if the <option>-l</option>
! option is used), or redirected to <application>pg_ctl</application>'s
! standard output (not standard error). If no log file is chosen, the
! standard output of <application>pg_ctl</application> should be redirected
! to a file or piped to another process such as a log rotating program
! like <application>rotatelogs</>; otherwise <command>postgres</command>
! will write its output to the controlling terminal (from the background)
! and will not leave the shell's process group.
</para>
<para>
--- 134,152 ----
<para>
In <option>start</option> mode, a new server is launched. The
! server is started in the background, and standard input is attached
! to <filename>/dev/null</filename> (or <literal>nul</> on Windows).
! On Unix-like systems, by default, the server's standard output and
! standard error are send to <application>pg_ctl</application>'s
! standard output (not standard error). The standard output of
! <application>pg_ctl</application> should then be redirected to a
! file or piped to another process such as a log rotating program
! like <application>rotatelogs</>; otherwise <command>postgres</command>
! will write its output to the controlling terminal (from the
! background) and will not leave the shell's process group. On
! Windows, by default the server's standard output and standard error
! are sent to the terminal. These default behaviors can be changed
! by using <option>-l</option> to append server output to a log file.
</para>
<para>
depstein@alliedtesting.com wrote:
Another issue:
4. The --link option doesn't seem to work on Windows: pg_upgrade still
copies data from the old cluster to the new. There doesn't appear to
be a way to upgrade a database on Windows without copying the entire
uncompressed database, which can be a problem where disk space is
limited.
[ Email moved to hackers list.]
I am confused why you are seeing this behavior. I know my Win32 tester
had it working. We have this code:
#ifdef WIN32
static int
win32_pghardlink(const char *src, const char *dst)
{
/*
* CreateHardLinkA returns zero for failure
* http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx
*/
if (CreateHardLinkA(dst, src, NULL) == 0)
return -1;
else
return 0;
}
#endif
and we test for failures. We even have code that tests to make sure
hard links work before we start the migration. In fact, pg_upgrade
--check will perform the hard link test without performing the upgrade.
I assume you don't have the ability to try a patch that would exit just
before the hard link test removes its test file.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Hi.
Ooops, I can't follow your quick thread....
sorry, It will be a weekend if allowed.
Regards,
Hiroshi Saito
----- Original Message -----
From: "Bruce Momjian" <bruce@momjian.us>
Show quoted text
depstein@alliedtesting.com wrote:
BBBBBB> PostgreSQL 9.0 beta 2Windows XP Professional SP2
While migrating the database from 8.4 to 9.0 using pg_upgrade (now part of the Postgres
project), the following issues came up:1. When using the --logfile option, pg_upgrade quits with an error like this:
The process cannot access the file because it is being used by another process.
There were problems executing ""C:\PostgreSQL\8.4\bin/pg_ctl" -l "pg_upgrade.log
" -D "D:\PostgreSQL84_matlab1b" -o "-p 5432 -c autovacuum=off -c autovacuum_free
ze_max_age=2000000000" start >> "pg_upgrade.log" 2>&1"Hiroshi, can you comment on the above bug report? I was able to
reproduce this on XP. I think we added -l for Win32 because the code
says:/* use -l for Win32 */
snprintf(cmd, sizeof(cmd),
SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" "but I don't remember the details, and cvs.pgfoundry.org is down right
now. Thanks.--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ None of us is going to be here forever. +
Hiroshi Saito wrote:
Hi.
Ooops, I can't follow your quick thread....
sorry, It will be a weekend if allowed.
I have replied and I think I have it fixed.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +