Skip site navigation (1) Skip section navigation (2)

Re: createdb Question

From: <operationsengineer1(at)yahoo(dot)com>
To: Michael Glaesemann <grzm(at)myrealbox(dot)com>
Cc: PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: createdb Question
Date: 2005-11-16 17:25:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
--- Michael Glaesemann <grzm(at)myrealbox(dot)com> wrote:

> On Nov 16, 2005, at 8:45 ,
> <operationsengineer1(at)yahoo(dot)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
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
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:

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

Yahoo! FareChase: Search multiple travel sites in one click.

In response to


pgsql-novice by date

Next:From: operationsengineer1Date: 2005-11-16 17:42:52
Subject: Re: Problem Connecting to DB After 8.1 Upgrade (SOLVED)
Previous:From: William YuDate: 2005-11-16 16:02:52
Subject: Re: Application using PostgreSQL as a back end (experienced programmers

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group