Move database from Solaris to Windows

Please forgive me if this question is being asked in the wrong area (and
please suggest the proper one so I can ask there :-) ), but I'm in search of
assistance in moving a database from a Solaris system over to a Windows
system.

Solaris 5.8, postgreSQL 8.01, to Windows Server 2003, postgreSQL 8.0.13.

Installing on the Windows Server has gone simple enough, and I've actually
experimented a little with pg_dump to dump the contents of the database that
I get by default when installing the application that I'm working with which
requires the db.

I tried using pg_dump --format=t to dump the original database, and then
tried to restore into a database with the same name on the Windows side but
that restore has not been successful.

I've been trying to follow information found here:
http://www.postgresql.org/docs/8.0/static/app-pgrestore.html but have not
been successful yet.

Any assistance that can be lent my way is greatly appreciated.

Bcd

On Aug 1, 2007, at 11:44 AM, Barry C Dowell wrote:

> Please forgive me if this question is being asked in the wrong area
> (and
> please suggest the proper one so I can ask there :-) ), but I'm in
> search of
> assistance in moving a database from a Solaris system over to a
> Windows
> system.
>
> Solaris 5.8, postgreSQL 8.01, to Windows Server 2003, postgreSQL
> 8.0.13.
>
> Installing on the Windows Server has gone simple enough, and I've
> actually
> experimented a little with pg_dump to dump the contents of the
> database that
> I get by default when installing the application that I'm working
> with which
> requires the db.
>
> I tried using pg_dump --format=t to dump the original database, and
> then
> tried to restore into a database with the same name on the Windows
> side but
> that restore has not been successful.

I find it a lot easier to diagnose what's going on with plain format,
generally.

>
> I've been trying to follow information found here:
> http://www.postgresql.org/docs/8.0/static/app-pgrestore.html but
> have not
> been successful yet.
>
> Any assistance that can be lent my way is greatly appreciated.

You'll need to post a few more details (what commands you ran,
what errors you got) before people will be able to help you much.

Cheers,
Steve

You asked for details, here's a few:

The database that I'm working with on the Solaris side is approximately
1.5GB in size. One file in the directory that I believe is that database,
is over 1GB in size on it's own.

When I tried using the plain format, I got human readable information which
is fine, but when I attempted to restore the database on the Windows side
using that file the psql database < output.fil command choked on several
lines from the output.fil that included the sequence \^N (or similar) at the
end of the lines (apparently lines that were supposed to load data into a
table).

That was but one of the issues that was noted, as there several. I can
replicate the problem again but have to remove the application first, then
remove postgreSQL, reinstall postgreSQL and then reinstall the application
to create the original database that will then be replaced with the copy
brought over from Solaris.

During the earlier attempts, I did not CLEAN the database before attempting
to restore it, so I was also getting lots of messages about duplicate keys,
or existing keys. I would guess that the recommendation is to clean the
database before restoring it, which might work just fine, but it doesn't
resolve the issue noted with the characters that aren't understood (psql
seemed to think they were commands, but of course gives a syntax type error
message saying they aren't understood).

When I dumped the original database, I used simple commands like this:

pg_dump --file=output.fil --format=p Databasename

When I tried to restore the database, I followed the examples in the link in
my original post and did the following:

psql -d databasename -f output.fil

I also tried the same basic processes when working with the tar format (I
assumed that I was going to need that because of large objects). Again
though, I never cleaned the database during the restore processes.

Thanks again for any assistance!

bcd

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Steve Atkins
Sent: Wednesday, August 01, 2007 3:35 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Move database from Solaris to Windows

On Aug 1, 2007, at 11:44 AM, Barry C Dowell wrote:

> Please forgive me if this question is being asked in the wrong area
> (and please suggest the proper one so I can ask there :-) ), but I'm
> in search of assistance in moving a database from a Solaris system
> over to a Windows system.
>
> Solaris 5.8, postgreSQL 8.01, to Windows Server 2003, postgreSQL
> 8.0.13.
>
> Installing on the Windows Server has gone simple enough, and I've
> actually experimented a little with pg_dump to dump the contents of
> the database that I get by default when installing the application
> that I'm working with which requires the db.
>
> I tried using pg_dump --format=t to dump the original database, and
> then tried to restore into a database with the same name on the
> Windows side but that restore has not been successful.

I find it a lot easier to diagnose what's going on with plain format,
generally.

>
> I've been trying to follow information found here:
> http://www.postgresql.org/docs/8.0/static/app-pgrestore.html but
> have not
> been successful yet.
>
> Any assistance that can be lent my way is greatly appreciated.

You'll need to post a few more details (what commands you ran,
what errors you got) before people will be able to help you much.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

On Aug 1, 2007, at 2:34 PM, Steve Atkins wrote:

>
> On Aug 1, 2007, at 11:44 AM, Barry C Dowell wrote:
>
>> Please forgive me if this question is being asked in the wrong
>> area (and
>> please suggest the proper one so I can ask there :-) ), but I'm in
>> search of
>> assistance in moving a database from a Solaris system over to a
>> Windows
>> system.
>>
>> Solaris 5.8, postgreSQL 8.01, to Windows Server 2003, postgreSQL
>> 8.0.13.
>>
>> Installing on the Windows Server has gone simple enough, and I've
>> actually
>> experimented a little with pg_dump to dump the contents of the
>> database that
>> I get by default when installing the application that I'm working
>> with which
>> requires the db.
>>
>> I tried using pg_dump --format=t to dump the original database,
>> and then
>> tried to restore into a database with the same name on the Windows
>> side but
>> that restore has not been successful.
>
> I find it a lot easier to diagnose what's going on with plain
> format, generally.
>
>>
>> I've been trying to follow information found here:
>> http://www.postgresql.org/docs/8.0/static/app-pgrestore.html but
>> have not
>> been successful yet.
>>
>> Any assistance that can be lent my way is greatly appreciated.
>
> You'll need to post a few more details (what commands you ran,
> what errors you got) before people will be able to help you much.

Yes, dump/restore is pretty much the standard to move dbs across
architectures so we'll need more to work with. One thing to check,
did you make sure that your dump was in the same encoding as the
database you created on your Windows server?

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Ok, if you can forgive the possible stupid answer and help pull me a long a
bit more, in answer to this:

>> Yes, dump/restore is pretty much the standard to move dbs across
architectures so we'll need more to work with. One thing to check, did you
make sure that your dump was in the same encoding as the database you
created on your Windows server?

How do I know what encoding was used on one database (on one OS) versus the
other?

I have been assuming that the databases would be the same since they are
created by the same application in both environments. Perhaps that is a
false assumption, but I would expect that the app developers wouldn't want
to work with different types of postgreSQL databases on different platforms.

I know that both platforms connect to the database and do their work through
Java (JDBC connections), but that's about the best of what I can tell you at
this point (though I'm willing to dig for more information to help get more
assistance of course).

Bcd

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Erik Jones
Sent: Wednesday, August 01, 2007 4:22 PM
To: Steve Atkins
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Move database from Solaris to Windows

On Aug 1, 2007, at 2:34 PM, Steve Atkins wrote:

>
> On Aug 1, 2007, at 11:44 AM, Barry C Dowell wrote:
>
>> Please forgive me if this question is being asked in the wrong area
>> (and please suggest the proper one so I can ask there :-) ), but I'm
>> in search of assistance in moving a database from a Solaris system
>> over to a Windows system.
>>
>> Solaris 5.8, postgreSQL 8.01, to Windows Server 2003, postgreSQL
>> 8.0.13.
>>
>> Installing on the Windows Server has gone simple enough, and I've
>> actually experimented a little with pg_dump to dump the contents of
>> the database that I get by default when installing the application
>> that I'm working with which requires the db.
>>
>> I tried using pg_dump --format=t to dump the original database, and
>> then tried to restore into a database with the same name on the
>> Windows side but that restore has not been successful.
>
> I find it a lot easier to diagnose what's going on with plain format,
> generally.
>
>>
>> I've been trying to follow information found here:
>> http://www.postgresql.org/docs/8.0/static/app-pgrestore.html but have
>> not been successful yet.
>>
>> Any assistance that can be lent my way is greatly appreciated.
>
> You'll need to post a few more details (what commands you ran, what
> errors you got) before people will be able to help you much.

Yes, dump/restore is pretty much the standard to move dbs across
architectures so we'll need more to work with. One thing to check, did you
make sure that your dump was in the same encoding as the database you
created on your Windows server?

Erik Jones

Software Developer | EmmaR
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 8/2/07, Barry C Dowell <barry(dot)c(dot)dowell(dot)1(at)gsfc(dot)nasa(dot)gov> wrote:
> Ok, if you can forgive the possible stupid answer and help pull me a long a
> bit more, in answer to this:
>
> >> Yes, dump/restore is pretty much the standard to move dbs across
> architectures so we'll need more to work with. One thing to check, did you
> make sure that your dump was in the same encoding as the database you
> created on your Windows server?
>
> How do I know what encoding was used on one database (on one OS) versus the
> other?

psql -l

lists the encoding. postgresql on windows iirc defaults to sql_ascii
which is actually the most forgiving but not a great choice. linux
defaults to utf-8.

can you post the exact text of the error?

merlin

Following up on this issue --

I found resolution to the problems that I was having, with one error
remaining when I work on the database restore (somewhat expected error
really, and one I no longer need any assistance with).

The initial problems I was having were from running pg_dump on the Solaris
system and then transferring the dump file over to the Windows system and
trying to restore using that file.

First, there was the issue of me not running the pg_dump using the --clean
option. That resulted in attempts to make duplicate keys, etc. Again, to
be expected, and just a parameter issue in the usage caused by my own
inexperience with the software.

Second, the bigger problem, and the one that caused me a lot more grief to
begin with is that in moving the file from Solaris to Windows some munging
of the file was happening with extra characters included in the file that
were confusing psql.

The ultimate solution was to make use of the pg_dump command on the Windows
box when pointing to the Solaris host. I had to use the --host option to
tell the Windows pg_dump command where to find the database on the Solaris
host, but once done, pg_dump created a good copy of the dump. Adding
--clean on top gave me a dump with the commands to clean/out the database
first, and that elminated a lot of extra error messages and warnings that
were coming up.

I want to say thanks to the folks that have developed the various utilities,
but a special thanks to the folks that have put so much effort into the
online documentation. It is clean, clear, concise and offers enough
examples that just about anyone should be able to help themselves to a
solution.

I also would like to say thanks to the people that answered my original
posting for their tips and suggestions. It did help to get me info on the
database encoding (confirmed it was the same on both sides) and helped me
trouble shoot things that much more.

Best of luck to everyone in the list as I get ready to sign off for a while
and quiet down my e-mail traffic some. Hopefully later I'll get a chance to
participate more.

Bcd

-----Original Message-----
From: Barry C Dowell [mailto:barry(dot)c(dot)dowell(dot)1(at)gsfc(dot)nasa(dot)gov]
Sent: Wednesday, August 01, 2007 4:40 PM
To: 'pgsql-general(at)postgresql(dot)org'
Subject: RE: [GENERAL] Move database from Solaris to Windows

Ok, if you can forgive the possible stupid answer and help pull me a long a
bit more, in answer to this:

>> Yes, dump/restore is pretty much the standard to move dbs across
architectures so we'll need more to work with. One thing to check, did you
make sure that your dump was in the same encoding as the database you
created on your Windows server?

How do I know what encoding was used on one database (on one OS) versus the
other?

I have been assuming that the databases would be the same since they are
created by the same application in both environments. Perhaps that is a
false assumption, but I would expect that the app developers wouldn't want
to work with different types of postgreSQL databases on different platforms.

I know that both platforms connect to the database and do their work through
Java (JDBC connections), but that's about the best of what I can tell you at
this point (though I'm willing to dig for more information to help get more
assistance of course).

Bcd

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Erik Jones
Sent: Wednesday, August 01, 2007 4:22 PM
To: Steve Atkins
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Move database from Solaris to Windows

On Aug 1, 2007, at 2:34 PM, Steve Atkins wrote:

>
> On Aug 1, 2007, at 11:44 AM, Barry C Dowell wrote:
>
>> Please forgive me if this question is being asked in the wrong area
>> (and please suggest the proper one so I can ask there :-) ), but I'm
>> in search of assistance in moving a database from a Solaris system
>> over to a Windows system.
>>
>> Solaris 5.8, postgreSQL 8.01, to Windows Server 2003, postgreSQL
>> 8.0.13.
>>
>> Installing on the Windows Server has gone simple enough, and I've
>> actually experimented a little with pg_dump to dump the contents of
>> the database that I get by default when installing the application
>> that I'm working with which requires the db.
>>
>> I tried using pg_dump --format=t to dump the original database, and
>> then tried to restore into a database with the same name on the
>> Windows side but that restore has not been successful.
>
> I find it a lot easier to diagnose what's going on with plain format,
> generally.
>
>>
>> I've been trying to follow information found here:
>> http://www.postgresql.org/docs/8.0/static/app-pgrestore.html but have
>> not been successful yet.
>>
>> Any assistance that can be lent my way is greatly appreciated.
>
> You'll need to post a few more details (what commands you ran, what
> errors you got) before people will be able to help you much.

Yes, dump/restore is pretty much the standard to move dbs across
architectures so we'll need more to work with. One thing to check, did you
make sure that your dump was in the same encoding as the database you
created on your Windows server?

Erik Jones

Software Developer | EmmaR
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match