Re: broke postgres, how to fix??

On 03/01/2013 02:31 AM, Albe Laurenz wrote:
> JD Wong wrote:
>>>> Hi Adrian, yes I completely copied the config-file and data directories
>>>> over.
>
>>>     That's guaranteed to break everything badly.
>
>> Even if I "read only style" copied the files? Do you mind elaborating on why this happens?  ( or point
>> me to relevant documentation )
>
> You either have to shutdown the database before copying
> (a filesystem level offline backup) or use some instantaneous
> snapshotting technique that your file system might offer.
> In the latter case PostgreSQL should perform crash recovery
> and eventually reach a consistent state.
>
> Yours,
> Laurenz Albe

Of course, Postgres has some built in capabilities in this regard

I have a script to start our replication server that does something like:

su - postgres -c "psql -c \"checkpoint; select pg_switch_xlog();\";"
su - postgres -c "psql -c \"SELECT pg_start_backup('backup', true)\";"
rsync -av /var/lib/pgsql/ remote-system.example.com:/var/lib/pgsql/
su - postgres -c "psql -c \"SELECT pg_stop_backup()\"