Обсуждение: psql 8 warm standby strong start, weak finish
Hello, psql family. psql (8.4.7) I am having mixed results for a warm standby. My warm standby appears to work for a couple of days and then refuses to processany more WAL files. I will see lovely entries like this in standby.log: removing "/var/lib/pgsql/archives/00000001000010E5000000E9" ... Trigger file : /tmp/pgsql.trigger Waiting for WAL file : 00000001000010E5000000EF WAL file path : /var/lib/pgsql/archives/00000001000010E5000000EF Restoring to : pg_xlog/RECOVERYXLOG Sleep interval : 2 seconds Max wait interval : 0 forever Command for restore : cp "/var/lib/pgsql/archives/00000001000010E5000000EF" "pg_xlog/RECOVERYXLOG" Keep archive history : 00000001000010E5000000EA and later And then /var/lib/pgsql/archives/ will continue to grow because it won't process any more WAL files. Here's the suspicious part. The 00000001000010E5000000EF WAL in question above is the last file to be listed in standby.log,and it is a different size from all the rest. Is there any chance that my end rsync-ed it over from the masterto the standby before the master had fully written it out, so the standby sees it as incomplete and gets stuck? Oris it normal to see a WAL that isn't 16MB? My rsync deletes the sent files from the master, so I have nothing to checkit against. -rw------- 1 postgres postgres 16777216 Apr 29 05:07 00000001000010E5000000EA -rw------- 1 postgres postgres 16777216 Apr 29 05:11 00000001000010E5000000EB -rw------- 1 postgres postgres 16777216 Apr 29 05:15 00000001000010E5000000EC -rw------- 1 postgres postgres 16777216 Apr 29 05:15 00000001000010E5000000ED -rw------- 1 postgres postgres 16777216 Apr 29 05:15 00000001000010E5000000EE -rw------- 1 postgres postgres 12337152 Apr 29 05:20 00000001000010E5000000EF -rw------- 1 postgres postgres 16777216 Apr 29 05:25 00000001000010E5000000F0 -rw------- 1 postgres postgres 16777216 Apr 29 05:30 00000001000010E5000000F1 And on the master, everything looks good in the logs: `pg_xlog/00000001000010E5000000EC' -> `/var/lib/pgsql/archives/00000001000010E5000000EC' `pg_xlog/00000001000010E5000000ED' -> `/var/lib/pgsql/archives/00000001000010E5000000ED' `pg_xlog/00000001000010E5000000EE' -> `/var/lib/pgsql/archives/00000001000010E5000000EE' `pg_xlog/00000001000010E5000000EF' -> `/var/lib/pgsql/archives/00000001000010E5000000EF' `pg_xlog/00000001000010E5000000F0' -> `/var/lib/pgsql/archives/00000001000010E5000000F0' `pg_xlog/00000001000010E5000000F1' -> `/var/lib/pgsql/archives/00000001000010E5000000F1' I have read around and don't see that my nightly vacuum and pg_dump of the master should have any negative affects on mystandby. But if you disagree, please let me know. TIA. Charlton Galvarino 1 (803) 233-6205 : voice 1 (803) 223-9579 : fax charlton@2creek.com P.O. Box 50960 Columbia, SC 29250
> psql (8.4.7) Uhm.... the last update to 8.4 was 8.4.22: besides using an unsupported version, you're missing three and a half years of patches in 8.4.x :| Bye, Chris
Fair enough. I should have thought a bit harder before airing my dirty laundry! >-----Original Message----- >From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- >owner@postgresql.org] On Behalf Of Chris Mair >Sent: Thursday, April 30, 2015 9:41 AM >To: pgsql-general@postgresql.org >Subject: Re: [GENERAL] psql 8 warm standby strong start, weak finish > >> psql (8.4.7) > >Uhm.... the last update to 8.4 was 8.4.22: besides using an unsupported >version, you're missing three and a half years of patches in 8.4.x :| > >Bye, >Chris > > > > > > > > > > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make >changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general
On 04/30/2015 06:31 AM, Charlton Galvarino wrote: > Hello, psql family. > > psql (8.4.7) > > I am having mixed results for a warm standby. My warm standby appears to work for a couple of days and then refuses toprocess any more WAL files. > > I will see lovely entries like this in standby.log: > removing "/var/lib/pgsql/archives/00000001000010E5000000E9" > ... > Trigger file : /tmp/pgsql.trigger > Waiting for WAL file : 00000001000010E5000000EF > WAL file path : /var/lib/pgsql/archives/00000001000010E5000000EF > Restoring to : pg_xlog/RECOVERYXLOG > Sleep interval : 2 seconds > Max wait interval : 0 forever > Command for restore : cp "/var/lib/pgsql/archives/00000001000010E5000000EF" "pg_xlog/RECOVERYXLOG" > Keep archive history : 00000001000010E5000000EA and later > > And then /var/lib/pgsql/archives/ will continue to grow because it won't process any more WAL files. > > Here's the suspicious part. The 00000001000010E5000000EF WAL in question above is the last file to be listed in standby.log,and it is a different size from all the rest. Is there any chance that my end rsync-ed it over from the masterto the standby before the master had fully written it out, so the standby sees it as incomplete and gets stuck? Oris it normal to see a WAL that isn't 16MB? My rsync deletes the sent files from the master, so I have nothing to checkit against. So what is your archive_command? Where is the listing below from?: > > -rw------- 1 postgres postgres 16777216 Apr 29 05:07 00000001000010E5000000EA > -rw------- 1 postgres postgres 16777216 Apr 29 05:11 00000001000010E5000000EB > -rw------- 1 postgres postgres 16777216 Apr 29 05:15 00000001000010E5000000EC > -rw------- 1 postgres postgres 16777216 Apr 29 05:15 00000001000010E5000000ED > -rw------- 1 postgres postgres 16777216 Apr 29 05:15 00000001000010E5000000EE > -rw------- 1 postgres postgres 12337152 Apr 29 05:20 00000001000010E5000000EF > -rw------- 1 postgres postgres 16777216 Apr 29 05:25 00000001000010E5000000F0 > -rw------- 1 postgres postgres 16777216 Apr 29 05:30 00000001000010E5000000F1 > > And on the master, everything looks good in the logs: > > `pg_xlog/00000001000010E5000000EC' -> `/var/lib/pgsql/archives/00000001000010E5000000EC' > `pg_xlog/00000001000010E5000000ED' -> `/var/lib/pgsql/archives/00000001000010E5000000ED' > `pg_xlog/00000001000010E5000000EE' -> `/var/lib/pgsql/archives/00000001000010E5000000EE' > `pg_xlog/00000001000010E5000000EF' -> `/var/lib/pgsql/archives/00000001000010E5000000EF' > `pg_xlog/00000001000010E5000000F0' -> `/var/lib/pgsql/archives/00000001000010E5000000F0' > `pg_xlog/00000001000010E5000000F1' -> `/var/lib/pgsql/archives/00000001000010E5000000F1' > > I have read around and don't see that my nightly vacuum and pg_dump of the master should have any negative affects on mystandby. But if you disagree, please let me know. > > TIA. > > > > > Charlton Galvarino > 1 (803) 233-6205 : voice > 1 (803) 223-9579 : fax > charlton@2creek.com > > P.O. Box 50960 > Columbia, SC 29250 > > > > -- Adrian Klaver adrian.klaver@aklaver.com
>So what is your archive_command? archive_mode = on archive_command = 'cp -v %p /var/lib/pgsql/archives/%f' archive_timeout = 300 >Where is the listing below from?: The below listing was warm_standby: /var/lib/pgsql/archives/ >> -rw------- 1 postgres postgres 16777216 Apr 29 05:07 >00000001000010E5000000EA >> -rw------- 1 postgres postgres 16777216 Apr 29 05:11 >00000001000010E5000000EB >> -rw------- 1 postgres postgres 16777216 Apr 29 05:15 >00000001000010E5000000EC >> -rw------- 1 postgres postgres 16777216 Apr 29 05:15 >00000001000010E5000000ED >> -rw------- 1 postgres postgres 16777216 Apr 29 05:15 >00000001000010E5000000EE >> -rw------- 1 postgres postgres 12337152 Apr 29 05:20 >00000001000010E5000000EF >> -rw------- 1 postgres postgres 16777216 Apr 29 05:25 >00000001000010E5000000F0 >> -rw------- 1 postgres postgres 16777216 Apr 29 05:30 >00000001000010E5000000F1 # warm_standby:/var/lib/pgsql/data/recovery.conf restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger /var/lib/pgsql/archives %f %p %r 2>>standby.log'
On 04/30/2015 07:03 AM, Charlton Galvarino wrote: >> So what is your archive_command? > > archive_mode = on > archive_command = 'cp -v %p /var/lib/pgsql/archives/%f' > archive_timeout = 300 > So where does the rsync you mentioned previously fit into this? >> Where is the listing below from?: > > The below listing was warm_standby: /var/lib/pgsql/archives/ > >>> -rw------- 1 postgres postgres 16777216 Apr 29 05:07 >> 00000001000010E5000000EA >>> -rw------- 1 postgres postgres 16777216 Apr 29 05:11 >> 00000001000010E5000000EB >>> -rw------- 1 postgres postgres 16777216 Apr 29 05:15 >> 00000001000010E5000000EC >>> -rw------- 1 postgres postgres 16777216 Apr 29 05:15 >> 00000001000010E5000000ED >>> -rw------- 1 postgres postgres 16777216 Apr 29 05:15 >> 00000001000010E5000000EE >>> -rw------- 1 postgres postgres 12337152 Apr 29 05:20 >> 00000001000010E5000000EF >>> -rw------- 1 postgres postgres 16777216 Apr 29 05:25 >> 00000001000010E5000000F0 >>> -rw------- 1 postgres postgres 16777216 Apr 29 05:30 >> 00000001000010E5000000F1 > > # warm_standby:/var/lib/pgsql/data/recovery.conf > restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger /var/lib/pgsql/archives %f %p %r 2>>standby.log' I do not see an -l option in the 8.4.x version of pg_standby, I do see it in the 8.3 version. Where are you getting pg_standby from? > > -- Adrian Klaver adrian.klaver@aklaver.com
>So where does the rsync you mentioned previously fit into this? A cron runs on warm_standby to pull (and delete) the WAL's from the master. rsync -avz --progress --remove-sent-files master:/var/lib/pgsql/archives/ /var/lib/pgsql/archives/ >I do not see an -l option in the 8.4.x version of pg_standby, I do see it in the >8.3 version. I agree. pg_standby --help says exactly that. -l does nothing; use of link is now deprecated >Where are you getting pg_standby from? -bash-3.2$ which pg_standby /usr/bin/pg_standby -bash-3.2$ pg_standby --version pg_standby (PostgreSQL) 8.4.17
On 04/30/2015 07:22 AM, Charlton Galvarino wrote: >> So where does the rsync you mentioned previously fit into this? > > A cron runs on warm_standby to pull (and delete) the WAL's from the master. > > rsync -avz --progress --remove-sent-files master:/var/lib/pgsql/archives/ /var/lib/pgsql/archives/ Hmm, in newer versions of rsync --remove-sent-files has been replaced by --remove-source-files, so I cannot test. Some searching found that --remove-sent-files will move/delete unfinished files. I would say that the above cron command is dangerous. To test, comment out the command and let the archiving run. Postgres will recycle WALs on its own when they are no longer needed. Or is there is some compelling reason you want to get rid of WALs? > >> I do not see an -l option in the 8.4.x version of pg_standby, I do see it in the >> 8.3 version. > > I agree. pg_standby --help says exactly that. > > -l does nothing; use of link is now deprecated Aah, so it is just noise. > >> Where are you getting pg_standby from? > > -bash-3.2$ which pg_standby > /usr/bin/pg_standby > > -bash-3.2$ pg_standby --version > pg_standby (PostgreSQL) 8.4.17 > > -- Adrian Klaver adrian.klaver@aklaver.com
>Hmm, in newer versions of rsync --remove-sent-files has been replaced by -- >remove-source-files, so I cannot test. Some searching found that --remove- >sent-files will move/delete unfinished files. I would say that the above cron >command is dangerous. To test, comment out the command and let the Great idea. Will do and let it run for a few days. >archiving run. Postgres will recycle WALs on its own when they are no longer >needed. Or is there is some compelling reason you want to get rid of WALs? Ah. I didn't know that. I thought the cleanup was on me. Bonus! Thanks for the help.
On 04/30/2015 07:42 AM, Charlton Galvarino wrote: >> Hmm, in newer versions of rsync --remove-sent-files has been replaced by -- >> remove-source-files, so I cannot test. Some searching found that --remove- >> sent-files will move/delete unfinished files. I would say that the above cron >> command is dangerous. To test, comment out the command and let the > > Great idea. Will do and let it run for a few days. > >> archiving run. Postgres will recycle WALs on its own when they are no longer >> needed. Or is there is some compelling reason you want to get rid of WALs? > > Ah. I didn't know that. I thought the cleanup was on me. Bonus! For more info take a look here: http://www.postgresql.org/docs/8.4/static/wal-configuration.html > > Thanks for the help. > > -- Adrian Klaver adrian.klaver@aklaver.com
>>> archiving run. Postgres will recycle WALs on its own when they are no >>> longer needed. Or is there is some compelling reason you want to get rid >of WALs? >> >> Ah. I didn't know that. I thought the cleanup was on me. Bonus! > >For more info take a look here: > >http://www.postgresql.org/docs/8.4/static/wal-configuration.html Things are running smoothly so far. The proof will be in the pudding after 48h or so. But I have changed my rsync fromits original pull approach to push. I do need to clean up the WAL's on master once they've made it to the warm_standby,so in this new push approach, I only round up WAL's that are, say 10m old, and then rsync those to warm_standby,deleting them on master when they've been xferred. warm_standby continues to do a good job of cleaning up thearchive dir w/o any fuss from me.
On 04/30/2015 01:49 PM, Charlton Galvarino wrote: >>>> archiving run. Postgres will recycle WALs on its own when they are no >>>> longer needed. Or is there is some compelling reason you want to get rid >> of WALs? >>> >>> Ah. I didn't know that. I thought the cleanup was on me. Bonus! >> >> For more info take a look here: >> >> http://www.postgresql.org/docs/8.4/static/wal-configuration.html > > Things are running smoothly so far. The proof will be in the pudding after 48h or so. But I have changed my rsync fromits original pull approach to push. I do need to clean up the WAL's on master once they've made it to the warm_standby, Why? so in this new push approach, I only round up WAL's that are, say 10m old, and then rsync those to warm_standby, To the standby server pg_xlog or to the archive directory? If it to the archive directory I am not following. The archive_command is pushing the WALs to the archive directory and restore_command is pulling it from that directory and then cleaning up. What is rsync doing that is not already being done? If directly, to the standby pg_xlog I do not see it ending well when two independent processes are writing to the same directory. deleting them on master when they've been xferred. warm_standby continues to do a good job of cleaning up the archive dir w/o any fuss from me. > -- Adrian Klaver adrian.klaver@aklaver.com
MASTER * archive_command = 'cp -v %p /var/lib/pgsql/archives/%f' * rsync files in [MASTER:/var/lib/pgsql/archives that are +10m and delete on them once sent] to [STANDBY:/var/lib/pgsql/archives] STANDBY * restore_command = 'pg_standby -d -s 2 -t /tmp/pgsql.trigger /var/lib/pgsql/archives %f %p %r 2>>standby.log' * no cleanup to do since pg_standby cleans up old WAL's MASTER and STANDBY are completely separate servers that do not have any common disk space. ________________________________________ From: Adrian Klaver <adrian.klaver@aklaver.com> Sent: Thursday, April 30, 2015 7:15 PM To: Charlton Galvarino; pgsql-general@postgresql.org Subject: Re: [GENERAL] psql 8 warm standby strong start, weak finish On 04/30/2015 01:49 PM, Charlton Galvarino wrote: >>>> archiving run. Postgres will recycle WALs on its own when they are no >>>> longer needed. Or is there is some compelling reason you want to get rid >> of WALs? >>> >>> Ah. I didn't know that. I thought the cleanup was on me. Bonus! >> >> For more info take a look here: >> >> http://www.postgresql.org/docs/8.4/static/wal-configuration.html > > Things are running smoothly so far. The proof will be in the pudding after 48h or so. But I have changed my rsync fromits original pull approach to push. I do need to clean up the WAL's on master once they've made it to the warm_standby, Why? so in this new push approach, I only round up WAL's that are, say 10m old, and then rsync those to warm_standby, To the standby server pg_xlog or to the archive directory? If it to the archive directory I am not following. The archive_command is pushing the WALs to the archive directory and restore_command is pulling it from that directory and then cleaning up. What is rsync doing that is not already being done? If directly, to the standby pg_xlog I do not see it ending well when two independent processes are writing to the same directory. deleting them on master when they've been xferred. warm_standby continues to do a good job of cleaning up the archive dir w/o any fuss from me. > -- Adrian Klaver adrian.klaver@aklaver.com
On 04/30/2015 05:22 PM, Charlton Galvarino wrote: > MASTER > * archive_command = 'cp -v %p /var/lib/pgsql/archives/%f' > * rsync files in [MASTER:/var/lib/pgsql/archives that are +10m and delete on them once sent] to [STANDBY:/var/lib/pgsql/archives] > > STANDBY > * restore_command = 'pg_standby -d -s 2 -t /tmp/pgsql.trigger /var/lib/pgsql/archives %f %p %r 2>>standby.log' > * no cleanup to do since pg_standby cleans up old WAL's > > MASTER and STANDBY are completely separate servers that do not have any common disk space. > ________________________________________ From here: https://wiki.postgresql.org/wiki/Warm_Standby "Set archive_command in the master's postgresql.conf. rysnc is a popular choice or you can just use one of the examples from the docs. I use: rsync -a %p postgres@standbyhost:/path/to/wal_archive/%f " Simplifies the process. -- Adrian Klaver adrian.klaver@aklaver.com
>"Set archive_command in the master's postgresql.conf. rysnc is a popular >choice or you can just use one of the examples from the docs. I use: > >rsync -a %p postgres@standbyhost:/path/to/wal_archive/%f >" >Simplifies the process. Oh, well, sure I could take the simpler way out. But where would the fun be in that? Thanks again.