pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to move a database from HP server to Linux Server that had already one database.
16+ messages / 5 participants
[nested] [flat]
* How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-16 17:33 ENGEMANN, DAYSE <dayse.engemann@hp.com>
0 siblings, 1 reply; 16+ messages in thread
From: ENGEMANN, DAYSE @ 2010-07-16 17:33 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have a HP Server with a postgres database, and I need to transfer it to another server, but it is a Linux server and there is a database that needs be preserved.
Both have PGDATA=/postgres
The database is about 150 MB.
What is the easy way to do it?
thanks
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-16 17:47 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: ENGEMANN, DAYSE <dayse.engemann@hp.com>
0 siblings, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2010-07-16 17:47 UTC (permalink / raw)
To: DAYSE ENGEMANN <dayse.engemann@hp.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-16 17:57 ENGEMANN, DAYSE <dayse.engemann@hp.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 16+ messages in thread
From: ENGEMANN, DAYSE @ 2010-07-16 17:57 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-16 18:11 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: ENGEMANN, DAYSE <dayse.engemann@hp.com>
0 siblings, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2010-07-16 18:11 UTC (permalink / raw)
To: DAYSE ENGEMANN <dayse.engemann@hp.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-16 18:21 ENGEMANN, DAYSE <dayse.engemann@hp.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 16+ messages in thread
From: ENGEMANN, DAYSE @ 2010-07-16 18:21 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-19 15:48 ENGEMANN, DAYSE <dayse.engemann@hp.com>
parent: ENGEMANN, DAYSE <dayse.engemann@hp.com>
0 siblings, 1 reply; 16+ messages in thread
From: ENGEMANN, DAYSE @ 2010-07-19 15:48 UTC (permalink / raw)
To: ENGEMANN, DAYSE <dayse.engemann@hp.com>; Kevin Grittner <Kevin.Grittner@wicourts.gov>; 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 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-19 16:07 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: ENGEMANN, DAYSE <dayse.engemann@hp.com>
0 siblings, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2010-07-19 16:07 UTC (permalink / raw)
To: DAYSE ENGEMANN <dayse.engemann@hp.com>; 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-19 16:31 ENGEMANN, DAYSE <dayse.engemann@hp.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 16+ messages in thread
From: ENGEMANN, DAYSE @ 2010-07-19 16:31 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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 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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-19 16:43 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: ENGEMANN, DAYSE <dayse.engemann@hp.com>
1 sibling, 1 reply; 16+ messages in thread
From: Kevin Grittner @ 2010-07-19 16:43 UTC (permalink / raw)
To: DAYSE ENGEMANN <dayse.engemann@hp.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-19 16:49 ENGEMANN, DAYSE <dayse.engemann@hp.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 16+ messages in thread
From: ENGEMANN, DAYSE @ 2010-07-19 16:49 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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'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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-19 17:12 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: ENGEMANN, DAYSE <dayse.engemann@hp.com>
1 sibling, 0 replies; 16+ messages in thread
From: Kevin Grittner @ 2010-07-19 17:12 UTC (permalink / raw)
To: DAYSE ENGEMANN <dayse.engemann@hp.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-07-19 17:58 Scott Marlowe <scott.marlowe@gmail.com>
parent: ENGEMANN, DAYSE <dayse.engemann@hp.com>
1 sibling, 0 replies; 16+ messages in thread
From: Scott Marlowe @ 2010-07-19 17:58 UTC (permalink / raw)
To: ENGEMANN, DAYSE <dayse.engemann@hp.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-08-10 00:14 Bruce Momjian <bruce@momjian.us>
parent: ENGEMANN, DAYSE <dayse.engemann@hp.com>
1 sibling, 2 replies; 16+ messages in thread
From: Bruce Momjian @ 2010-08-10 00:14 UTC (permalink / raw)
To: ENGEMANN, DAYSE <dayse.engemann@hp.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
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. +
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-08-10 13:44 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 16+ messages in thread
From: Kevin Grittner @ 2010-08-10 13:44 UTC (permalink / raw)
To: DAYSE ENGEMANN <dayse.engemann@hp.com>; Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-08-10 16:34 Craig James <craig_james@emolecules.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 16+ messages in thread
From: Craig James @ 2010-08-10 16:34 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: How to move a database from HP server to Linux Server that had already one database.
@ 2010-08-10 17:06 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Craig James <craig_james@emolecules.com>
0 siblings, 0 replies; 16+ messages in thread
From: Kevin Grittner @ 2010-08-10 17:06 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2010-08-10 17:06 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-16 17:33 How to move a database from HP server to Linux Server that had already one database. ENGEMANN, DAYSE <dayse.engemann@hp.com>
2010-07-16 17:47 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-16 17:57 ` Re: How to move a database from HP server to Linux Server that had already one database. ENGEMANN, DAYSE <dayse.engemann@hp.com>
2010-07-16 18:11 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-16 18:21 ` Re: How to move a database from HP server to Linux Server that had already one database. ENGEMANN, DAYSE <dayse.engemann@hp.com>
2010-07-19 15:48 ` Re: How to move a database from HP server to Linux Server that had already one database. ENGEMANN, DAYSE <dayse.engemann@hp.com>
2010-07-19 16:07 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-19 16:31 ` ENGEMANN, DAYSE <dayse.engemann@hp.com>
2010-07-19 16:43 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-19 16:49 ` ENGEMANN, DAYSE <dayse.engemann@hp.com>
2010-07-19 17:12 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-19 17:58 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-08-10 00:14 ` Bruce Momjian <bruce@momjian.us>
2010-08-10 13:44 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-10 16:34 ` Craig James <craig_james@emolecules.com>
2010-08-10 17:06 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox