From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "ENGEMANN, DAYSE" <dayse(dot)engemann(at)hp(dot)com> |
Cc: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: How to move a database from HP server to Linux Server that had already one database. |
Date: | 2010-07-19 17:58:00 |
Message-ID: | AANLkTinPQHv2oEyULUUC9HGp5QnTs178uQBlqJNG2sAI@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Mon, Jul 19, 2010 at 10:49 AM, ENGEMANN, DAYSE <dayse(dot)engemann(at)hp(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2010-07-19 21:38:50 | Re: replication recommendation |
Previous Message | Kevin Grittner | 2010-07-19 17:12:21 | Re: How to move a database from HP server to Linux Server that had already one database. |