How to move a database from HP server to Linux Server that hadalready one database.

--_000_5FCE2C21B63D86478DBA823B3A27B40149CF10495AGVW1341EXAa me_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

I have a HP Server with a postgres database, and I need to transfer it to a=
nother server, but it is a Linux server and there is a database that needs =
be preserved.
Both have PGDATA=3D/postgres
The database is about 150 MB.
What is the easy way to do it?

thanks

--_000_5FCE2C21B63D86478DBA823B3A27B40149CF10495AGVW1341EXAa me_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Re: How to move a database from HP server to Linux

"ENGEMANN, DAYSE" wrote:

> I have a HP Server with a postgres database, and I need to
> transfer it to another server

> What is the easy way to do it?

pg_dump | psql

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux Serverthat had already one database.

And to restore it.. Can I use pg_restore -d new_db_name ??=20

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]=20
Sent: sexta-feira, 16 de julho de 2010 14:47
To: ENGEMANN, DAYSE; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How to move a database from HP server to Linux Server =
that had already one database.

"ENGEMANN, DAYSE" wrote:
=20
> I have a HP Server with a postgres database, and I need to transfer it=20
> to another server
=20
> What is the easy way to do it?
=20
pg_dump | psql
=20
-Kevin

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux

"ENGEMANN, DAYSE" wrote:
> Kevin Grittner wrote:

>> pg_dump | psql

> And to restore it.. Can I use pg_restore -d new_db_name ??

I usually pipe the output of pg_dump to psql, and thereby avoid
making a copy of the dump output entirely. If you want to dump in
custom format, yeah, you would use pg_restore instead.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux Serverthat had already one database.

I am not sure if I can do it in this way.. As the are in diferent servers a=
nd network.

I found a paper that suggest to use pg_dump -Ft -b db_name > file.tar .... =
Transfer it to new server and you ther the pg_restore -d new_db file.tar
But as the servers have different SO.. I am not sure if it can be used...=
=20

And another question is that in the target server there is a database there=
that we can not destroy.

Thanks for your help.=20

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of Kevin Grittner
Sent: sexta-feira, 16 de julho de 2010 15:12
To: ENGEMANN, DAYSE; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How to move a database from HP server to Linux Server =
that had already one database.

"ENGEMANN, DAYSE" wrote:
> Kevin Grittner wrote:
=20
>> pg_dump | psql
=20
> And to restore it.. Can I use pg_restore -d new_db_name ??=20
=20
I usually pipe the output of pg_dump to psql, and thereby avoid making a co=
py of the dump output entirely. If you want to dump in custom format, yeah=
, you would use pg_restore instead.
=20
-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make chan=
ges to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux Serverthat had already one database.

Hi Kevin,

If I use the :
Origim: pg_dump -Ft -b db_name > file.tar
Target: pg_restore -d new_db file.tar

The target server has a db, using PGDATA=3D/postgres, the same in the other=
server... There is any risk to lost any data?



-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of ENGEMANN, DAYSE
Sent: sexta-feira, 16 de julho de 2010 15:22
To: Kevin Grittner; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How to move a database from HP server to Linux Server =
that had already one database.

I am not sure if I can do it in this way.. As the are in diferent servers a=
nd network.

I found a paper that suggest to use pg_dump -Ft -b db_name > file.tar .... =
Transfer it to new server and you ther the pg_restore -d new_db file.tar Bu=
t as the servers have different SO.. I am not sure if it can be used...=20

And another question is that in the target server there is a database there=
that we can not destroy.

Thanks for your help.=20

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of Kevin Grittner
Sent: sexta-feira, 16 de julho de 2010 15:12
To: ENGEMANN, DAYSE; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How to move a database from HP server to Linux Server =
that had already one database.

"ENGEMANN, DAYSE" wrote:
> Kevin Grittner wrote:
=20
>> pg_dump | psql
=20
> And to restore it.. Can I use pg_restore -d new_db_name ??=20
=20
I usually pipe the output of pg_dump to psql, and thereby avoid making a co=
py of the dump output entirely. If you want to dump in custom format, yeah=
, you would use pg_restore instead.
=20
-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make chan=
ges to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make chan=
ges to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux

"ENGEMANN, DAYSE" wrote:

> If I use the :
> Origim: pg_dump -Ft -b db_name > file.tar
> Target: pg_restore -d new_db file.tar
>
> The target server has a db, using PGDATA=/postgres, the same in
> the other server... There is any risk to lost any data?

Restoring to a new database should not put the data in pre-existing
databases at risk. I wouldn't recommend the tar format for your
dump, though -- I've had better luck with custom if I want a
"structured" dump from which I can selectively restore. If you want
the whole thing, and don't need an intermediate copy (two big "ifs",
I know), you could do something like this on the target machine:

pg_dump -h sourcemachine -U sourceuser dbname | psql dbname

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux

Hi Kevin,
Sorry to disturb you.. But I am really new in it...
Let me see if I understood...

pg_dump -h sourcemachine -U sourceuser source_dbname | psql target_dbname

Is that?

The data will be import in the existing db?

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]=20
Sent: segunda-feira, 19 de julho de 2010 13:07
To: ENGEMANN, DAYSE; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] How to move a database from HP server to Linux Server =
that had already one database.

"ENGEMANN, DAYSE" wrote:
=20
> If I use the :
> Origim: pg_dump -Ft -b db_name > file.tar
> Target: pg_restore -d new_db file.tar
>=20
> The target server has a db, using PGDATA=3D/postgres, the same in the=20
> other server... There is any risk to lost any data?
=20
Restoring to a new database should not put the data in pre-existing databas=
es at risk. I wouldn't recommend the tar format for your dump, though -- I=
've had better luck with custom if I want a "structured" dump from which I =
can selectively restore. If you want the whole thing, and don't need an in=
termediate copy (two big "ifs", I know), you could do something like this o=
n the target machine:
=20
pg_dump -h sourcemachine -U sourceuser dbname | psql dbname
=20
-Kevin

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux

"ENGEMANN, DAYSE" wrote:

> pg_dump -h sourcemachine -U sourceuser source_dbname | psql
> target_dbname
>
> Is that?

Yes. You need to enter passwords unless they are specified in
..pgpass or you have some form of authorization which doesn't require
typing a password (for example, trust or ident).

> The data will be import in the existing db?

Yes, you should create the target database before issuing this --
you can add a parameter to the dump to cause it to create the
database, but I always feel safer creating it myself and pointing to
an existing database.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux Server that had already one database.

How can I create it as the same that I have in the other server?=20

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]=20
Sent: segunda-feira, 19 de julho de 2010 13:43
To: ENGEMANN, DAYSE; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] How to move a database from HP server to Linux Server =
that had already one database.

"ENGEMANN, DAYSE" wrote:
=20
> pg_dump -h sourcemachine -U sourceuser source_dbname | psql=20
> target_dbname
>=20
> Is that?
=20
Yes. You need to enter passwords unless they are specified in .pgpass or y=
ou have some form of authorization which doesn't require typing a password =
(for example, trust or ident).
=20
> The data will be import in the existing db?
=20
Yes, you should create the target database before issuing this -- you can a=
dd a parameter to the dump to cause it to create the database, but I always=
feel safer creating it myself and pointing to an existing database.
=20
-Kevin

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux

"ENGEMANN, DAYSE" wrote:

> How can I create it as the same that I have in the other server?

Well, you could use CREATE DATABASE, and maybe a few REVOKE and/or
GRANT statements. Is there some particular concern you have about
this?

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux Server

On Mon, Jul 19, 2010 at 10:49 AM, ENGEMANN, DAYSE wrote:
> How can I create it as the same that I have in the other server?

What you likely want here is the same database name, with the same
encoding and collation. And then the global stuff, like user
accounts.

psql -h olddbserver postgres
\l

should show you the databases on the old server. Here's the output
from a test db on my laptop:

Name | Owner | Encoding | Collation | Ctype |
Access privileges
-----------+----------+-----------+-------------+----------- --+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
:
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
:
postgres=CTc/postgres
test | postgres | SQL_ASCII | C | C |

Note that the test database is SQL_ASCII (anything goes) and C (byte
order) collation.

If I'm gonna dump this to another db server, I need to create the db
on the other end to match. To do so, I'd need a statement something
like this on the other db server's psql prompt:

create database test with encoding 'SQL_ASCII' LC_COLLATE= 'C'
LC_CTYPE='C' template template0;

Note that here I've had to define the template as template0 because of
the need to use a different encoding than template1.

After that I'll likely need the globals from the old db:

pg_dumpall --globals olddbserver

will do that. You can edit it and then use psql to to pipe the
output into the new server.

Then you're ready for

pg_dump -h oldserver dbname | psql -h newserver dbname

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to LinuxServer that had already one database.

ENGEMANN, DAYSE wrote:
> Hi Kevin,
> Sorry to disturb you.. But I am really new in it...
> Let me see if I understood...
>
> pg_dump -h sourcemachine -U sourceuser source_dbname | psql target_dbname

Has anyone done any measurement of whether it is faster to do the dump
on the local machine with psql remote or from a remote machine (where
psql would be local)?

--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com

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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux

Bruce Momjian wrote:
> ENGEMANN, DAYSE wrote:

>> pg_dump -h sourcemachine -U sourceuser source_dbname \
>> | psql target_dbname
>
> Has anyone done any measurement of whether it is faster to do the
> dump on the local machine with psql remote or from a remote
> machine (where psql would be local)?

I haven't, because I want to dump with the pg_dump from the target
environment and because I want to restore using a database
superuser, which we only allow through a local ident connection.
It would take a pretty big performance difference to overcome the
operational motivations for running on the target.

Thinking about this a little, though, brought to mind the
performance issues when we were converting from Sybase to PostgreSQL
using a home-grown Java conversion utility. We found best
performance running it on the target for that. We also got a rather
large performance boost by reading on one thread and writing on
another -- even a 50 row queue to decouple the threads yielded a
very large benefit. I've heard that we have a big bottleneck in
parsing the input during a restore; I suspect that this alternates
with disk I/O as a bottleneck. I know multi-threading is always
controversial, but I wonder whether there wouldn't be some way to
decouple the parsing during a COPY FROM from the tuple insert, to
keep two cores busy on the target even during a piped conversion
like this.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux Serverthat had already one database.

On 8/9/10 5:14 PM, Bruce Momjian wrote:
> ENGEMANN, DAYSE wrote:
>> Hi Kevin,
>> Sorry to disturb you.. But I am really new in it...
>> Let me see if I understood...
>>
>> pg_dump -h sourcemachine -U sourceuser source_dbname | psql target_dbname
>
> Has anyone done any measurement of whether it is faster to do the dump
> on the local machine with psql remote or from a remote machine (where
> psql would be local)?

It probably depends more on your network speed. If you have a slow network, then run pg_dump on the machine where the database lives and use compression (--format=c) and restore using pg_restore. It will cut WAY down on the amount of data that has to move across the net. If you have a fast network, then it doesn't matter very much -- you'll be limited by disk speed.

Craig

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: How to move a database from HP server to Linux

Craig James wrote:

> If you have a slow network, then run pg_dump on the machine where
> the database lives and use compression (--format=c) and restore
> using pg_restore. It will cut WAY down on the amount of data that
> has to move across the net.

If you don't mind dancing around a bit you can ssh from the target
to the source, run pg_dump piped to gzip there, gunzip the stream on
the target side, and still pipe it into psql without having to go
through the disk write/read cycle that --format=c requires. Because
of the pain of getting all the quoting right when trying that as a
one-line bash command, I generally create a short script on the
source to do the dump and gzip. The only other pain point is the
remote password. .pgpass solves that.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin