Обсуждение: How to move a database from HP server to Linux Server that had already one database.
How to move a database from HP server to Linux Server that had already one database.
От
"ENGEMANN, DAYSE"
Дата:
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.
The database is about 150 MB.
What is the easy way to do it?
thanks
Re: How to move a database from HP server to Linux Server that had already one database.
От
"Kevin Grittner"
Дата:
"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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"ENGEMANN, DAYSE"
Дата:
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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"Kevin Grittner"
Дата:
"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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"ENGEMANN, DAYSE"
Дата:
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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"ENGEMANN, DAYSE"
Дата:
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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"Kevin Grittner"
Дата:
"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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"ENGEMANN, DAYSE"
Дата:
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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"Kevin Grittner"
Дата:
"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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"ENGEMANN, DAYSE"
Дата:
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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"Kevin Grittner"
Дата:
"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
Re: How to move a database from HP server to Linux Server that had already one database.
От
Scott Marlowe
Дата:
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
Re: How to move a database from HP server to Linux Server that had already one database.
От
Bruce Momjian
Дата:
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. +
Re: How to move a database from HP server to Linux Server that had already one database.
От
"Kevin Grittner"
Дата:
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
Re: How to move a database from HP server to Linux Server that had already one database.
От
"Kevin Grittner"
Дата:
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