<div dir="ltr"><div><div><div>Hi,<br><br></div>I read a lot about warm-standby/log-shipping and it could be the solution, but i have some issues.<br>I followed this instructions "<a href="http://www.themagicnumber.es/replication-in-postgresql-i?lang=en" target="_blank">http://www.themagicnumber.es/replication-in-postgresql-i?lang=en</a>" to configure my postgresql server.<br>
Then i did what you recommend :<br><br><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div id=":h" style="overflow:hidden">
cd $POSTGRESDATADIRECTORY<br>
psql -c "SELECT pg_start_backup('label', true)"<br>rsync -a ${PGDATA}/ $USBDRIVE --exclude postmaster.pid<br>
psql -c "SELECT pg_stop_backup()"</div></blockquote></div><br></div>(instead rsync i used cp)<br><br></div><div>But pg_stop_backup did not close properly<br><br>> WARNING: pg_stop_backup still waiting for all required WAL segments to be archived (60 seconds elapsed)<br>
> HINT: Check that your archive_command is executing properly. pg_stop_backup can be canceled safely, but the database backup > will not be usable without all the WAL segments.<br><br></div><div>archive_command = 'cp -r /var/lib/postgresql/9.1/main/%p ~/wal/%f'<br>
<br></div><div>I checked "wal" directory and files are there. Do not know why i have this error.<br></div><div>I did give it a try, slave server successfully started but i cannot execute psql :<br><br>> psql: FATAL: the database system is starting up<br>
<br></div><div>Any ideas ?<br></div><div><div><div class="gmail_extra"><br><br><div class="gmail_quote">On Mon, Jun 3, 2013 at 1:48 PM, thomas lechauve <span dir="ltr"><<a href="mailto:thomas.lechauve@gmail.com" target="_blank">thomas.lechauve@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Thank you, i will try you solution and come back to you later.<br><br></div>Cheers<div>
<div class="h5"><br><div class="gmail_extra"><br><br><div class="gmail_quote">On Mon, Jun 3, 2013 at 1:41 PM, Morten Sickel <span dir="ltr"><<a href="mailto:morten@sickel.net" target="_blank">morten@sickel.net</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Ahh, I see. You may still use log-shipping, transfering the xlog-files on<br>
a flash-disk, but it is probably just as easy to do an entire dump/restore<br>
cycle.<br>
<br>
(on my servers (running ubunto 1204, I have pg_basebackup, pg_dump,<br>
pg_dumpall and pg_restore which may be usesd - or you may do on the<br>
"master":<br>
<br>
cd $POSTGRESDATADIRECTORY<br>
psql -c "SELECT pg_start_backup('label', true)"<br>
rsync -a ${PGDATA}/ $USBDRIVE --exclude postmaster.pid<br>
psql -c "SELECT pg_stop_backup()"<br>
<br>
<br>
then on the slaves<br>
stop the postgres server<br>
copy the files from the usb into the data directory<br>
start the postgres server<br>
<br>
)<br>
<br>
Morten<br>
<br>
<br>
thomas lechauve skrev:<br>
<div><div>> Unfortunately, servers cannot communicate. "client" servers are not<br>
> connected to a network, they work in standalone, so i have to use an usb<br>
> drive to transport backup file, then restore it.<br>
><br>
><br>
> On Mon, Jun 3, 2013 at 1:12 PM, Morten Sickel <<a href="mailto:morten@sickel.net" target="_blank">morten@sickel.net</a>> wrote:<br>
><br>
>> It depends a bit...<br>
>><br>
>> If those servers are able to communicate with each other on port 5432,<br>
>> you<br>
>> can use streaming replication to make one of the servers being a (read<br>
>> only) mirror of the other. - or you may set up log-shipping streaming<br>
>> replication, in which case te master server only needs to be able to<br>
>> send<br>
>> files (i.e. using rsync) to the slave server.<br>
>><br>
>> In both cases, the servers must be identical - same versions and the<br>
>> same<br>
>> databases - which all are read only on the slave... (You may of course<br>
>> run<br>
>> more postgres servers on the slave, where one is dedicated to the<br>
>> OSM-data.)<br>
>><br>
>> Just ask me if you need some assistance, I have set up a few<br>
>> streaming-replication and log-shipping replication servers.<br>
>><br>
>> Morten<br>
>><br>
>> thomas lechauve skrev:<br>
>> > Hello,<br>
>> ><br>
>> > I am trying to synchronize two PostGIS databases.<br>
>> ><br>
>> > I have a reference server which is filled and updated every day with<br>
>> OSM<br>
>> > data. For this purpose I recreate the whole database. But now i want<br>
>> to<br>
>> > update another database which is not on the same server based on the<br>
>> first<br>
>> > database without recreate it.<br>
>> ><br>
>> > pg_dump/pg_restore tools are probably what i am looking for, but i<br>
>> cannot<br>
>> > find a way to achieve my purposes.<br>
>> ><br>
>> > Do i have to keep trying with those tools or is there another way ?<br>
>> ><br>
>> > Perhaps a SQL script could do it but i want to avoid it because i am<br>
>> no<br>
>> > expert in PostGIS and databases in general.<br>
>> ><br>
>> > Any recommendations ?<br>
>> ><br>
>> > Cheers<br>
>> ><br>
>> > --<br>
>> > Thomas LECHAUVE<br>
>> > _______________________________________________<br>
>> > postgis-users mailing list<br>
>> > <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> > <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
>> ><br>
>><br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
>><br>
><br>
><br>
><br>
> --<br>
> Thomas LECHAUVE<br>
> portable : <a href="tel:%2B33%206%2051%2021%2007%2040" value="+33651210740" target="_blank">+33 6 51 21 07 40</a><br>
> fixe : <a href="tel:%2B33%209%2082%2037%2041%2094" value="+33982374194" target="_blank">+33 9 82 37 41 94</a><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
><br>
<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br><div>Thomas LECHAUVE</div><div>portable : <a href="tel:%2B33%206%2051%2021%2007%2040" value="+33651210740" target="_blank">+33 6 51 21 07 40</a></div><div>fixe : <a href="tel:%2B33%209%2082%2037%2041%2094" value="+33982374194" target="_blank">+33 9 82 37 41 94</a></div>
</div></div></div></div>
</blockquote></div><br><br clear="all"><br>-- <br><div>Thomas LECHAUVE</div><div>portable : +33 6 51 21 07 40</div><div>fixe : +33 9 82 37 41 94</div>
</div></div></div></div>