[postgis-users] Update data in PostGIS database

[thomas lechauve]

Hi,

I read a lot about warm-standby/log-shipping and it could be the solution,
but i have some issues.
I followed this instructions "
http://www.themagicnumber.es/replication-in-postgresql-i?lang=en" to
configure my postgresql server.
Then i did what you recommend :

cd $POSTGRESDATADIRECTORY
> psql -c "SELECT pg_start_backup('label', true)"
> rsync -a ${PGDATA}/ $USBDRIVE --exclude postmaster.pid
> psql -c "SELECT pg_stop_backup()"
>

(instead rsync i used cp)

But pg_stop_backup did not close properly

> WARNING:  pg_stop_backup still waiting for all required WAL segments to
be archived (60 seconds elapsed)
> 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.

archive_command = 'cp -r /var/lib/postgresql/9.1/main/%p ~/wal/%f'

I checked "wal" directory and files are there. Do not know why i have this
error.
I did give it a try, slave server successfully started but i cannot execute
psql :

> psql: FATAL:  the database system is starting up

Any ideas ?


On Mon, Jun 3, 2013 at 1:48 PM, thomas lechauve
<thomas.lechauve at gmail.com>wrote:

> Thank you, i will try you solution and come back to you later.
>
> Cheers
>
>
>
> On Mon, Jun 3, 2013 at 1:41 PM, Morten Sickel <morten at sickel.net> wrote:
>
>> Ahh, I see. You may still use log-shipping, transfering the xlog-files on
>> a flash-disk, but it is probably just as easy to do an entire dump/restore
>> cycle.
>>
>> (on my servers (running ubunto 1204, I have pg_basebackup, pg_dump,
>> pg_dumpall and pg_restore which may be usesd - or you may do on the
>> "master":
>>
>> cd $POSTGRESDATADIRECTORY
>> psql -c "SELECT pg_start_backup('label', true)"
>> rsync -a ${PGDATA}/ $USBDRIVE --exclude postmaster.pid
>> psql -c "SELECT pg_stop_backup()"
>>
>>
>> then on the slaves
>> stop the postgres server
>> copy the files from the usb into the data directory
>> start the postgres server
>>
>> )
>>
>> Morten
>>
>>
>> thomas lechauve skrev:
>> > Unfortunately, servers cannot communicate. "client" servers are not
>> > connected to a network, they work in standalone, so i have to use an usb
>> > drive to transport backup file, then restore it.
>> >
>> >
>> > On Mon, Jun 3, 2013 at 1:12 PM, Morten Sickel <morten at sickel.net>
>> wrote:
>> >
>> >> It depends a bit...
>> >>
>> >> If those servers are able to communicate with each other on port 5432,
>> >> you
>> >> can use streaming replication to make one of the servers being a (read
>> >> only) mirror of the other. - or you may set up log-shipping streaming
>> >> replication, in which case te master server only needs to be able to
>> >> send
>> >> files (i.e. using rsync) to the slave server.
>> >>
>> >> In both cases, the servers must be identical - same versions and the
>> >> same
>> >> databases - which all are read only on the slave... (You may of course
>> >> run
>> >> more postgres servers on the slave, where one is dedicated to the
>> >> OSM-data.)
>> >>
>> >> Just ask me if you need some assistance, I have set up a few
>> >> streaming-replication and log-shipping replication servers.
>> >>
>> >> Morten
>> >>
>> >> thomas lechauve skrev:
>> >> > Hello,
>> >> >
>> >> > I am trying to synchronize two PostGIS databases.
>> >> >
>> >> > I have a reference server which is filled and updated every day with
>> >> OSM
>> >> > data. For this purpose I recreate the whole database. But now i want
>> >> to
>> >> > update another database which is not on the same server based on the
>> >> first
>> >> > database without recreate it.
>> >> >
>> >> > pg_dump/pg_restore tools are probably what i am looking for, but i
>> >> cannot
>> >> > find a way to achieve my purposes.
>> >> >
>> >> > Do i have to keep trying with those tools or is there another way ?
>> >> >
>> >> > Perhaps a SQL script could do it but i want to avoid it because i am
>> >> no
>> >> > expert in PostGIS and databases in general.
>> >> >
>> >> > Any recommendations ?
>> >> >
>> >> > Cheers
>> >> >
>> >> > --
>> >> > Thomas LECHAUVE
>> >> > _______________________________________________
>> >> > postgis-users mailing list
>> >> > postgis-users at lists.osgeo.org
>> >> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >> >
>> >>
>> >>
>> >> _______________________________________________
>> >> postgis-users mailing list
>> >> postgis-users at lists.osgeo.org
>> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >>
>> >
>> >
>> >
>> > --
>> > Thomas LECHAUVE
>> > portable : +33 6 51 21 07 40
>> > fixe : +33 9 82 37 41 94
>> > _______________________________________________
>> > postgis-users mailing list
>> > postgis-users at lists.osgeo.org
>> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> >
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
>
> --
> Thomas LECHAUVE
> portable : +33 6 51 21 07 40
> fixe : +33 9 82 37 41 94
>



-- 
Thomas LECHAUVE
portable : +33 6 51 21 07 40
fixe : +33 9 82 37 41 94
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130605/5b026560/attachment.html>