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 ma*******@postg resql.org)

[Bruno Wolff III]

On Thu, Oct 21, 2004 at 01:39:26 -0700,
Karim Nassar <Ka**********@N AU.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.

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

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

[Scott Marlowe]

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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

[Scott Marlowe]

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.

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

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

[Karim Nassar]

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.

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