Thread: Moving a database

Can someone please tell me the easiest method for moving a database from one server to another?

Pleaes be explicit, I am in the Novice group for a reason.

Kent

-----Original Message-----
From: Kent Rigby [mailto:kent.rigby@att.net]
Sent: Tuesday, December 24, 2002 12:54 PM
To: PGSQL Novice List
Subject: [NOVICE] Moving a database

Can someone please tell me the easiest method for moving a database from one server to another?

Pleaes be explicit, I am in the Novice group for a reason.

Kent

I’m having a problem restoring the DB on the remote server. The best
luck I had was with
psql dbName < filename
&
psql myDB
\i filename

However, in both cases I'm getting the following error for all data
entering statements (all of which appear to be based on COPY ...):
psql:trial:LineNumberHere: invalid command \N

There are indeed some \N denoting NULL; however, even when there are no
NULL values in a table the statements always fail too.

So I ended up with tables, indexes, and constraints, but w/o any data,
and I don't know what to do about it. I could put together manually a
long list of SQL statements, but that's not a real solution when there
is a tool available.

P.S. pg_restore command failed with errors like:
pg_restore: [archiver] input file does not appear to be a valid archive
pg_restore: [tar archiver] could not find header for file toc.dat in tar
archive

P.S. Notes:
Source DB is 7.3 and unicode, while destination is 7.2.3 and latin1.
Could affect anything? If it could, I'd have to dig for changes in the
destination.
P.P.S. Example of a COPY statement:
COPY region (id, name) FROM stdin;
1       Mid-West
2       East Coast (N)
3       East Coast (S)
4       Southwest
5       Northwest
6       South
\.

Thank you in advance,
r.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Tuesday, December 24, 2002 5:10 PM
To: 'Eft, Aaron'; 'Kent Rigby'; 'PGSQL Novice List'
Subject: Re: [NOVICE] Moving a database

I’m also about to do that and also asked that a while ago (thanks to
everybody who replied then).
From help, I see suggested syntax of
1) pg_dump –f filename
I guess it does the same as
2) pg_dump > filename

On the destination server, from help I’m seeing syntax like
1) pg_restore –d myDB filename
(to my guess).
That is different from
2) psql dbName < filename
but the result will probably be the same.
Then there is also:
3) psql myDB
\i filename

Is there any difference b/n the 2 dump and the 3 restore ways? If yes.
which of the 2 and 3 ways are preferable?
Thanks,
r.
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Eft, Aaron
Sent: Tuesday, December 24, 2002 3:03 PM
To: 'Kent Rigby'; PGSQL Novice List
Subject: Re: [NOVICE] Moving a database

I would suggest doing a
 
pg_dump > filename
 
then save that to disk, email it, ftp, whatever to the destination
server, and
 
createdb databaseName
psql databaseName < filename
 
-Aaron
 
-----Original Message-----
From: Kent Rigby [mailto:kent.rigby@att.net]
Sent: Tuesday, December 24, 2002 12:54 PM
To: PGSQL Novice List
Subject: [NOVICE] Moving a database
Can someone please tell me the easiest method for moving a database from
one server to another?

Pleaes be explicit, I am in the Novice group for a reason.

Kent

After putting it into an archive with pg_dump, I got:
pg_restore: [archiver] unsupported version (1.7) in file header
on destination machine. It's not clear what 1.7 is though, cause all
pg... version involved are 7.x.y, whereas tar versions are both 1.13.25.

P.S. Going to try 2 more things on my mind.

Sincerely,
r.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Wednesday, December 25, 2002 1:52 AM
To: 'PGSQL Novice List'
Subject: Re: [NOVICE] Moving a database-sos

I’m having a problem restoring the DB on the remote server. The best
luck I had was with
psql dbName < filename
&
psql myDB
\i filename

However, in both cases I'm getting the following error for all data
entering statements (all of which appear to be based on COPY ...):
psql:trial:LineNumberHere: invalid command \N

There are indeed some \N denoting NULL; however, even when there are no
NULL values in a table the statements always fail too.

So I ended up with tables, indexes, and constraints, but w/o any data,
and I don't know what to do about it. I could put together manually a
long list of SQL statements, but that's not a real solution when there
is a tool available.

P.S. pg_restore command failed with errors like:
pg_restore: [archiver] input file does not appear to be a valid archive
pg_restore: [tar archiver] could not find header for file toc.dat in tar
archive

P.S. Notes:
Source DB is 7.3 and unicode, while destination is 7.2.3 and latin1.
Could affect anything? If it could, I'd have to dig for changes in the
destination.
P.P.S. Example of a COPY statement:
COPY region (id, name) FROM stdin;
1       Mid-West
2       East Coast (N)
3       East Coast (S)
4       Southwest
5       Northwest
6       South
\.

Thank you in advance,
r.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Tuesday, December 24, 2002 5:10 PM
To: 'Eft, Aaron'; 'Kent Rigby'; 'PGSQL Novice List'
Subject: Re: [NOVICE] Moving a database

I’m also about to do that and also asked that a while ago (thanks to
everybody who replied then).
From help, I see suggested syntax of
1) pg_dump –f filename
I guess it does the same as
2) pg_dump > filename

On the destination server, from help I’m seeing syntax like
1) pg_restore –d myDB filename
(to my guess).
That is different from
2) psql dbName < filename
but the result will probably be the same.
Then there is also:
3) psql myDB
\i filename

Is there any difference b/n the 2 dump and the 3 restore ways? If yes.
which of the 2 and 3 ways are preferable?
Thanks,
r.
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Eft, Aaron
Sent: Tuesday, December 24, 2002 3:03 PM
To: 'Kent Rigby'; PGSQL Novice List
Subject: Re: [NOVICE] Moving a database

I would suggest doing a
 
pg_dump > filename
 
then save that to disk, email it, ftp, whatever to the destination
server, and
 
createdb databaseName
psql databaseName < filename
 
-Aaron
 
-----Original Message-----
From: Kent Rigby [mailto:kent.rigby@att.net]
Sent: Tuesday, December 24, 2002 12:54 PM
To: PGSQL Novice List
Subject: [NOVICE] Moving a database
Can someone please tell me the easiest method for moving a database from
one server to another?

Pleaes be explicit, I am in the Novice group for a reason.

Kent



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

http://archives.postgresql.org

Actually, 1.7 is the dump version which means that the destination
server does not understand the dump version on the source (which is
understood for restoring on the source as I checked). Apparently, that
is because source is 7.3.1, whereas destination is 7.2.3.
I am still planning to try smt. else, maybe a different way around the
destination's restoration will get the idea.

Sincerely,
r.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Wednesday, December 25, 2002 3:58 AM
To: 'PGSQL Novice List'
Subject: Re: [NOVICE] Moving a database-sos

After putting it into an archive with pg_dump, I got:
pg_restore: [archiver] unsupported version (1.7) in file header
on destination machine. It's not clear what 1.7 is though, cause all
pg... version involved are 7.x.y, whereas tar versions are both 1.13.25.

P.S. Going to try 2 more things on my mind.

Sincerely,
r.

Never mind... It was a version problem, but there is a workaround.

Sincerely,
r.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Wednesday, December 25, 2002 4:06 AM
To: 'PGSQL Novice List'
Subject: Re: [NOVICE] Moving a database-sos

Actually, 1.7 is the dump version which means that the destination
server does not understand the dump version on the source (which is
understood for restoring on the source as I checked). Apparently, that
is because source is 7.3.1, whereas destination is 7.2.3.
I am still planning to try smt. else, maybe a different way around the
destination's restoration will get the idea.

Sincerely,
r.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Wednesday, December 25, 2002 3:58 AM
To: 'PGSQL Novice List'
Subject: Re: [NOVICE] Moving a database-sos

After putting it into an archive with pg_dump, I got:
pg_restore: [archiver] unsupported version (1.7) in file header
on destination machine. It's not clear what 1.7 is though, cause all
pg... version involved are 7.x.y, whereas tar versions are both 1.13.25.

P.S. Going to try 2 more things on my mind.

Sincerely,
r.




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html