Postgres Pro
Downloads
Home   >   mailing lists

Thread: Duplicating a database

Duplicating a database

From
Karim Nassar
Date:
I need to have an exact copy of a postgres install on a testing
computer. I don't want to do slony. Is it feasible/reasonable to think
that I could just rsync to the devel boxen from the pg server? Or is
slony "The Way to Do It"(tm)?

\<.

Re: Duplicating a database

From
Bruno Wolff III
Date:
On Thu, Oct 21, 2004 at 01:39:26 -0700,
  Karim Nassar <Karim.Nassar@NAU.EDU> wrote:
> I need to have an exact copy of a postgres install on a testing
> computer. I don't want to do slony. Is it feasible/reasonable to think
> that I could just rsync to the devel boxen from the pg server? Or is
> slony "The Way to Do It"(tm)?

If you shutdown the database cluster before doing the rsync that will work.

Re: Duplicating a database

From
"Scott Marlowe"
Date:
On Thu, 2004-10-21 at 02:39, Karim Nassar wrote:
> I need to have an exact copy of a postgres install on a testing
> computer. I don't want to do slony. Is it feasible/reasonable to think
> that I could just rsync to the devel boxen from the pg server? Or is
> slony "The Way to Do It"(tm)?

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.

Re: Duplicating a database

From
"Scott Marlowe"
Date:
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.

Re: Duplicating a database

From
Karim Nassar
Date:
On Sat, 2004-10-23 at 16:57, Curtis Zinzilieta wrote:
> rsync, or .tar.gz, or scp or use pg_dump.
>
> any of the copies run assuming you've shut down the DB first, and that
> you're using the same binaries on both boxen.

Aha! Shutdown first.
I knew it was something dumb.
Thanks for the help.

\<.

Re: Duplicating a database

From
Karim Nassar
Date:
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.

\<.

On Sat, 2004-10-23 at 18:04, Scott Marlowe wrote:
> On Thu, 2004-10-21 at 02:39, Karim Nassar wrote:
> > I need to have an exact copy of a postgres install on a testing
> > computer. I don't want to do slony. Is it feasible/reasonable to think
> > that I could just rsync to the devel boxen from the pg server? Or is
> > slony "The Way to Do It"(tm)?
>
> 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.

Re: Duplicating a database

From
Karim Nassar
Date:
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.
> >