[postgis-users] Update data in PostGIS database

[Morten Sickel]

Been there, done it ...

If I remember correctly, I got the same error message when I had a mistake
in my archive_command - the directory to copy to did not exist or was not
writeable by the postgres user or something like that.

Morten


thomas lechauve skrev:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>