Thread: createdb Question

createdb Question

From
Date:
can i create a new local db by copying an existing
remote db or do i have to manually create the db
locally and then pg_dump from the remote db and
pg_restore into the local db?

if i can create a db as a copy of an existing db, can
someone point me to howto or give a quick, fictional
example how to get this done?

tia...



__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs

Re: createdb Question

From
Michael Glaesemann
Date:
On Nov 16, 2005, at 8:45 , <operationsengineer1@yahoo.com> wrote:

> if i can create a db as a copy of an existing db, can
> someone point me to howto or give a quick, fictional
> example how to get this done?

existing db cluster with db foo:
pg_dump foo > foo-dump.sql

new db cluster:
createdb foo2
psql -d foo2 -f foo-dump.sql

If you have users and groups that you need to transfer as well,
you'll want to look at pg_dumpall with the -g flag. You may want to
edit the output of pg_dumpall by hand to only get the things you
want. And you'll load the pg_dumpall output into the new db cluster
(with psql) before loading foo-dump.sql.

Take a look at the pg_dump and pg_dumpall docs for more details.

Hope this helps.

Michael Glaesemann
grzm myrealbox com




Re: createdb Question

From
Date:
--- Michael Glaesemann <grzm@myrealbox.com> wrote:

>
> On Nov 16, 2005, at 8:45 ,
> <operationsengineer1@yahoo.com> wrote:
>
> > if i can create a db as a copy of an existing db,
> can
> > someone point me to howto or give a quick,
> fictional
> > example how to get this done?
>
> existing db cluster with db foo:
> pg_dump foo > foo-dump.sql
>
> new db cluster:
> createdb foo2
> psql -d foo2 -f foo-dump.sql
>
> If you have users and groups that you need to
> transfer as well,
> you'll want to look at pg_dumpall with the -g flag.
> You may want to
> edit the output of pg_dumpall by hand to only get
> the things you
> want. And you'll load the pg_dumpall output into the
> new db cluster
> (with psql) before loading foo-dump.sql.
>
> Take a look at the pg_dump and pg_dumpall docs for
> more details.
>
> Hope this helps.
>
> Michael Glaesemann
> grzm myrealbox com

Michael, it sure did.  here is how i got it done on my
remote server with winxp as my local machine.

1. i signed on to my VPN (required to connect in this
case).
2. Start -> Run -> type "cmd" -> OK.
3. type "cd c:\program files/postgresql/8.1/bin" Note:
if this doesn't work type the same except enclose
program files in quotes so it looks like this "program
files".
4. type "pg-dump <your db name> -h <external host
running your db> -u > <your sql file name.sql>

the -u forces a username and password request.  w/o
it, your window's user account is assumed and only a
password is requested.  if you are logged into an
account that isn't also a database user on the remote
db, you can't log in.

5. User name: <type remote host username - postgres in
my case>
6. Password: <type remote host's username password>

7. after some processing - go look in c:\program
files/postgresql/8.1/bin for "your_sql_file_name.sql"
- it should be there.

i created the db and restored it exactly as Michael
suggested.  i didn't do a dump_all and i will manually
create a few roles rather than edit the my .sql file.

Michael, i noticed that you nomenclature:

psql -d foo2 -f foo-dump.sql

is different from the manual's:

psql -e foo < foo-dump.sql

can you please briefly explain the nuances at work
here?  i didn't find any pg-dump reference to your use
of the -f flag or the manual's use of the -e flag.

this is the link to the pg_dump page:

http://www.postgresql.org/files/documentation/books/aw_pgsql/node264.html

tia...  your guidance was very helpful to me and,
hopefully, others.



__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

Re: createdb Question

From
Michael Glaesemann
Date:
On Nov 17, 2005, at 2:25 , <operationsengineer1@yahoo.com> wrote:

> psql -d foo2 -f foo-dump.sql
>
> is different from the manual's:
>
> psql -e foo < foo-dump.sql
>
> can you please briefly explain the nuances at work
> here?  i didn't find any pg-dump reference to your use
> of the -f flag or the manual's use of the -e flag.

That's because they're psql flags:

http://www.postgresql.org/docs/8.1/interactive/app-psql.html

Using the -f flag will include line numbers in its output which I
find helpful.


Michael Glaesemann
grzm myrealbox com