How's this for a quick and dirty replication plan?
We need to replicate a PG on one server to another server repeatedly.
The issues coming up relate to:
* Using 'clean' to be able to clean out the data has a problem cos
roles can not be dropped due to dependencies.
* We'd like to use pg_dumpall cos it recreates DB ownership and all
functions etc.
We could loop through the database names and drop/recreate and then
reload the DB using pg_dump - but this won't create any new users which
may have been created on the DB. Also, we have to be careful not to
recreate the postgres, template0 and template1 databases.
So - is there any reason we can't?
Reinitialise the Secondary DB as if it is a new DB. By this I mean drop
all data and all databases.
Reload the entire DB from the output of pg_dumpall.
Comments gratefully received.
Kev