Duplicating a database

[Karim Nassar]

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)?

\<.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Bruno Wolff III]

Re: Duplicating a database

On Thu, Oct 21, 2004 at 01:39:26 -0700,
Karim Nassar <Karim.Nassar@N AU.EDU> wrote:[color=blue]
> 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)?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Scott Marlowe]

Re: Duplicating a database

On Thu, 2004-10-21 at 02:39, Karim Nassar wrote:[color=blue]
> 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)?[/color]

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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Scott Marlowe]

Re: Duplicating a database

On Sat, 2004-10-23 at 22:22, Karim Nassar wrote:[color=blue]
> If you just need a working copy, not necessarily right up to date at any[color=green]
> > time, you can just dump and restore it:
> >
> > pg_dumpall -h source_server |psql -h dest_server
> >
> > add switches as necessary.[/color]
>
> 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.[/color]

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.



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Karim Nassar]

Re: Duplicating a database

If you just need a working copy, not necessarily right up to date at any[color=blue]
> time, you can just dump and restore it:
>
> pg_dumpall -h source_server |psql -h dest_server
>
> add switches as necessary.[/color]

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:[color=blue]
> On Thu, 2004-10-21 at 02:39, Karim Nassar wrote:[color=green]
> > 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)?[/color]
>
> 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.[/color]


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend