Duplicating a database

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.

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.

\<.

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.

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 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.

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,
\<.