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
