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.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