Easiest way to replicate (copy? Export and import?) a large, rarely changing postgreSQL database -


i have imported 200 gb of census data postgresql 9.3 database on windows 7 box. import process involves many files , has been complex , time-consuming. i'm using database convenient container. existing data if ever change, , updating external data @ once quarter (though i'll adding , modifying intermediate result columns on more frequent basis. i'll call data in database on desktop “master.” queries come same machine, not remote terminals.

i put copies of data on 3 other machines: 2 laptops, 1 windows 7 , 1 windows 8, , on ubuntu virtual machine on windows 7 desktop well. have installed copies of postgresql 9.3 on each of these machines, empty of data. need able both reads , writes on copies. ok, , indeed prefer it, if changes in daughter databases not propagate backwards primary database on desktop. i'd want update daughters master 1 4 times year. if wiped out intermediate results on daughter databases not bother me.

most of replication techniques have read seem worried transaction-by-transaction replication of live , changing server, , perfect history of queries & changes. overkill me. there way replicate copying files 1 postgresql instance another? (if replication name of specific form of copying, i'm trying ask more generic question). or maybe restoring each (empty) instance backup file of master? or of asking postgresql create , export (ideally on external hard drive) kind of postgresql binary of data instance of postgresql can import, without having define tables , data types , forth again?

this question motivated desire work around home wifi/lan setup slow – tenth or less of speed of file copies external hard drive. if there straightforward way imported data 1 machine transference of (ideally compressed) binary files, work best situation.

while perhaps copy data directory directly mentioned nick barnes in comments above, recommend using combination of pg_dump , pg_restore, dump self-contained file can dispersed other copies.

you can run pg_dump on master dump of db. recommend using options -fc -j3 use custom binary format (instead of dumping in sql format; should smaller , perhaps faster well) , dump 3 tables @ once (this can adjusted or down depending on disk throughput capabilities of machine , number of cores has).

then run dropdb on copies, createdb recreate empty db of same name, , run pg_restore on new empty db restore dump file db. want use options -d <dbname> -f <dump_file> -j3 (again adjusting number -j according abilities of machine).

when want refresh copies new content master db, repeat above steps


Comments

Post a Comment

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

i have following sample code used pre-decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(--c); cout << c << endl; return; } } this function give output input 4 : dp3 dp2 dp1 dp0 0 1 2 3 but when use post decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(c--); cout << c << endl; return; } } output goes in infinite loop dp4 can explain me in detail why happens ? this happens because function(c--) called same value of c , when finishes c decremented. recursively called, hence called same value no chance of return , hit stack overflow error. assume this, int x = 1, y = 0; y = x++ result y == 1 , x == 2. if y = ++x , bot x , y 2.
Read more

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

i added new project. made mistake in projectname.conf file in sites-available apache2, led me reinstalling apache2. after reinstalling , re-enabling projects on every page of every project .. <body> run(); </body> .. i've run composer update, restarted service, restarted pc, checked config files, tried disabling new project. there nothing in error log, , access.log: 127.0.0.1 - - [15/may/2015:01:40:47 +0200] "get / http/1.1" 304 181 "-" "mozilla/5.0 (x11; ubuntu; linux x86_64; rv:38.0) gecko/20100101 firefox/38.0" maybe i'm forgetting apache2 reinstall? don't think did different last time installed it. looks haven't setup php correctly. take @ this great guide , maybe you're forgetting link php config apache2.
Read more

php - How can I echo out this array? -

i don't know how go grabbing data in variable , put foreach loop. class tree{ private $info = array( array('name'=>'jim','state'=>'nu','company'=>'nu','phone'=>array('cell'=>'5615111111','office'=>'5611111111'),'email'=>array('primary'=>'exs@example.com','ex@there.com')), array('name'=>'joe smith','city'=>'phoenix','phone'=>'4805551111','email'=>'jsmith@some_email.com'), array('name'=>'john doe','city'=>'chandler','company'=>'doe co.','email'=>array('jdoe@gmail.com','personal'=>'email@email.com'),'phone'=>'6025550002') ); } if you're inside of class, can access private variable using $this->variablename . example, i...
Read more