By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

Duplicating a database

P: n/a
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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


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

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

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

Nov 23 '05 #2

P: n/a
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

Nov 23 '05 #3

P: n/a
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

Nov 23 '05 #4

P: n/a
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

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.