I'm trying to make a periodic (daily) incremental backup using rsync but noticing a message that postgres startup process is waiting on so and so XLOG file indefinitely.I intend to schedule a cron job to periodically execute this rsync backup process by : a) stop postgres server on backup server b) rsync the data directory of original server to backup server c) start postgres on secondary d) ensure that postgres starts up with out any issue e) execute some test cases so as to ensure that there no data integrity issues f) execute more tests to ensure that data is valid from business logic perspective.One may ask why am i using rsync backup instead of the log shipping bases streaming replication; well, my answer would be that our system would be having a lot of updates and fewer additions and since the transactions would be piling up for each update, the WAL files accumulate so much that we would be needing to archive much more data than the actual changes. Further, transferring such huge amount of logs is going to consume lot of time and bandwidth. Instead, I suppose that with rsync based backup we just need to sync the actual data files and need to worry about archiving the older WAL files, thus saving on the time of transfer, bandwidth costs, and WAL archive management.I would like to know if syncing the $PGDATA directory with rsync would be good enough for incremental backups or that would create data integrity issues.Everytime i run the rsync command, I could successfully start the backup server and am able to see the latest data from the original server, but am a bit worried about this message which may be hinting that the backup is not complete and is missing some essential WAL files.Is there any additional steps or rsync configuration options that i need to specify in order to make this sync complete?Please let me know what can be done to make the rsync process complete so that the backup server does not complain about waiting for missing WAL files.Here are the steps i executed on master and slave:On Backup Serverroot@backup ~]#root@backup ~]# service postgresql stopStopping postgresql service: [ OK ]root@backup ~]#On Original Serverroot@original ~]#root@original ~]# su -l postgres -s /bin/bash -c "psql -d test"test=#test=# SELECT pg_start_backup('rsync_backup');test=#\qroot@original ~]#root@original ~]# rsync --archive --checksum --compress --progress --exclude postgresql.conf --exclude pg_hba.conf --exclude server.crt --exclude server.key /var/lib/pgsql/data/* root@backup.server:/var/lib/pgsql/data/root@original ~]# su -l postgres -s /bin/bash -c "psql -d test"test=#test=# SELECT pg_stop_backup();test=#\qroot@original~]#On Backup Serverroot@backup ~]#root@backup ~]# service postgresql startStarting postgresql service: [ OK ]root@backup ~]#root@backup ~]# ps aux | grep postgrespostgres 18210 5.3 1.5 1811408 88540 ? S 20:43 0:00 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/datapostgres 18220 0.0 0.0 115496 1144 ? Ss 20:43 0:00 postgres: logger processpostgres 18221 0.0 0.0 1812252 2328 ? Ss 20:43 0:00 postgres: startup process waiting for 0000000100000001000000D2postgres 18222 0.0 0.0 1812180 2280 ? Ss 20:43 0:00 postgres: writer processpostgres 18223 0.0 0.0 117744 1388 ? Ss 20:43 0:00 postgres: stats collector processpostgres 18337 0.6 0.1 1814308 8092 ? Ss 20:43 0:00 postgres: avaya_system_app_user avmgmt 127.0.0.1(34207) idlepostgres 18406 0.4 0.1 1815792 9272 ? Ss 20:43 0:00 postgres: avaya_system_app_user avmgmt 127.0.0.1(34217) idlepostgres 18477 0.0 0.1 1813684 6680 ? Ss 20:43 0:00 postgres: avaya_sseuser avmgmt 127.0.0.1(34231) idleroot 18479 0.0 0.0 61160 728 pts/1 R+ 20:43 0:00 grep postgresroot@backup ~]#Please advise me if what i'm doing is makes sense and is an accepted mechanism for taking backups or if there is any other procedure that i can emplpoy to avoid unnecessarily archiving gigabytes of WAL logs which may be growing many times the size of the actual data directory.
Samba
