Re: How to move a database from HP server to Linux Server that had already one database.

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