Обсуждение: How to move a database from HP server to Linux Server that had already one database.

"ENGEMANN, DAYSE" <dayse.engemann@hp.com> 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

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

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
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" <dayse.engemann@hp.com> 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

"ENGEMANN, DAYSE" <dayse.engemann@hp.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov> 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

I am not sure if I can do it in this way.. As the are in diferent servers and 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...

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

Thanks for your help.

-----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" <dayse.engemann@hp.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov> 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
todump 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

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=/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 and 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...  

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

Thanks for your help.

-----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" <dayse.engemann@hp.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov> 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
todump 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

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

"ENGEMANN, DAYSE" <dayse.engemann@hp.com> 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

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]
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" <dayse.engemann@hp.com> 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
formatfor your dump, though -- I've had better luck with custom if I want a "structured" dump from which I can
selectivelyrestore.  If you want the whole thing, and don't need an intermediate copy (two big "ifs", I know), you
coulddo something like this on the target machine: 

pg_dump -h sourcemachine -U sourceuser dbname | psql dbname

-Kevin

"ENGEMANN, DAYSE" <dayse.engemann@hp.com> 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

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

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
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" <dayse.engemann@hp.com> 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'trequire 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
createthe database, but I always feel safer creating it myself and pointing to an existing database. 

-Kevin

"ENGEMANN, DAYSE" <dayse.engemann@hp.com> 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

On Mon, Jul 19, 2010 at 10:49 AM, ENGEMANN, DAYSE <dayse.engemann@hp.com> 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

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  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

Bruce Momjian <bruce@momjian.us> 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

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
databaselives and use compression (--format=c) and restore using pg_restore.  It will cut WAY down on the amount of
datathat has to move across the net.  If you have a fast network, then it doesn't matter very much -- you'll be limited
bydisk speed. 

Craig

Craig James <craig_james@emolecules.com> 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