Re: Duplicating a database - Mailing list pgsql-general

OK. I am lost now.

Introduction:
* postgres 7.4.5, Gentoo

Background:

* Server and laptop have identical configurations in every regard
  (excepting that all software is built for their respective processor
  types).
* the database is named 'orfs'
* I want a copy of it on my laptop.
* This thread led to the pg_dump option

What I Tried w/ Problems:

* pg_dumpall -co
  1) only dumps schema, but just for grins tried
     'psql orfs < pg_dumpall-output.sql' anyway
  2) Tables get created before functions, and tables have functions
     as the default column. No go.

* pg_dump -o orfs > test-dump.sql
  1) The forum users may not exist in the copy db
  2) Manually created users, then same as #2 above

Allins I Can See:

* pg_dump(all) is the only way to create a copy of db without shutting
  down

* pg_dump(all) can't order the operations properly in my version of
  postgres

Conclusion:

To create a copy of my db I must:
A) Shutdown the db and copy (which I can only do in the middle of the
   night, and this will scale unacceptably within a year)
 --OR--
B) Manually edit a fairly large and complex dump *every time I want a
   copy* (ugh.)

Prediction:

* Tom Lane says "upgrade to 8.0" ;-)

Post-Mortem:

Am I missing something? Please bless me with some postgres mojo.

TIA,
\<.

> On Sat, 2004-10-23 at 23:02, Scott Marlowe wrote:
> > On Sat, 2004-10-23 at 22:22, Karim Nassar wrote:
> > > If you just need a working copy, not necessarily right up to date at any
> > > > time, you can just dump and restore it:
> > > >
> > > > pg_dumpall -h source_server |psql -h dest_server
> > > >
> > > > add switches as necessary.
> > >
> > > That would be great for the first time. But what I want to do is copy
> > > ~postgresql/data, stomping/deleting as necessary. Roughly, my thinking
> > > is a daily cron job on the server:
> > >
> > >   rm -rf /safe/dir/data
> > >   /etc/init.d/postgresql stop
> > >   tar czf - -C ~postgres data | tar xzf - -C /safe/dir/
> > >   /etc/init.d/postgresql start
> > >
> > >
> > > And a client script:
> > >
> > >   /etc/init.d/postgresql stop
> > >   rm -rf ~postgres/data
> > >   ssh user@server tar czf - -C /safe/dir data|tar xvzf - -C ~postgres
> > >   /etc/init.d/postgresql start
> > >
> > > Or something similar with rsync instead of tar.
> >
> > Assuming there's only one or two databases in the cluster, it would be
> > pretty easy to just do a
> >
> > dropdb -h dest dbname1
> > dropdb -h dest dbname2
> > createdb dbname1
> > createdb dbname2
> > pg_dump -h source dbname1|psql -h dest
> > pg_dump -h source dbname2|psql -h dest
> >
> > That way there's no need to take down the source server or do anything
> > special to it.
> >