pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHOT Standby - slave does not appear to be removing wal files
6+ messages / 5 participants
[nested] [flat]
* HOT Standby - slave does not appear to be removing wal files
@ 2013-03-07 01:32 CS DBA <cs_dba@consistentstate.com>
2013-03-07 02:02 ` Re: HOT Standby - slave does not appear to be removing wal files Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
2013-12-23 17:35 ` Re: HOT Standby - slave does not appear to be removing wal files olimaz <oliver.mazariegos@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: CS DBA @ 2013-03-07 01:32 UTC (permalink / raw)
To: pgsql-admin
All;
We're doing a migration as follows:
1) we setup a HOT standby pair
2) we did an import of a db that wan backed up via pg_dump
The pg_xlog directory has > 1700 files in it, It seems that the slave is
way behind which I get since we just imported a 55GB database but the
slave is not removing it's local pg_xlog wal files.
We actually stopped the import when the slave hit 97% full for the file
system where we have it's pg_xlog directory mounted
after like 30minutes the file count in the slave's pg_xlog dir has not
changed.
Any thoughts per why the slave is not removing these files?
Thanks in advance
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HOT Standby - slave does not appear to be removing wal files
2013-03-07 01:32 HOT Standby - slave does not appear to be removing wal files CS DBA <cs_dba@consistentstate.com>
@ 2013-03-07 02:02 ` Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
2013-03-07 02:09 ` Re: HOT Standby - slave does not appear to be removing wal files CS DBA <cs_dba@consistentstate.com>
1 sibling, 1 reply; 6+ messages in thread
From: Prashanth Ranjalkar @ 2013-03-07 02:02 UTC (permalink / raw)
To: CS DBA <cs_dba@consistentstate.com>; +Cc: pgsql-admin
While setting up Hot Standby, it's not a method to copy the primary
database using dump and restore it on standby using restore command as
it's a logical backup. I doubt your WAL archiving is enabled and files are
copied to archive destination that set in archive_command therefore keep
accumulating under pg_xlog directory.
For hot standby setup, archiving must be enabled and online backup (file
system level) needs to be taken for setting up Standby server and
recovery.conf need to be prepared on slave server.
Please go through
http://www.postgresql.org/docs/9.2/static/high-availability.html for more
details.
*Thanks & Regards,*
*** *
*Prashanth Ranjalkar*
*Database Consultant & Architect*
*Skype:prashanth.ranjalkar*
*www.postgresdba.net*
On Thu, Mar 7, 2013 at 7:02 AM, CS DBA <cs_dba@consistentstate.com> wrote:
> All;
>
> We're doing a migration as follows:
>
> 1) we setup a HOT standby pair
>
> 2) we did an import of a db that wan backed up via pg_dump
>
> The pg_xlog directory has > 1700 files in it, It seems that the slave is
> way behind which I get since we just imported a 55GB database but the slave
> is not removing it's local pg_xlog wal files.
>
> We actually stopped the import when the slave hit 97% full for the file
> system where we have it's pg_xlog directory mounted
> after like 30minutes the file count in the slave's pg_xlog dir has not
> changed.
>
> Any thoughts per why the slave is not removing these files?
>
> Thanks in advance
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin;
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HOT Standby - slave does not appear to be removing wal files
2013-03-07 01:32 HOT Standby - slave does not appear to be removing wal files CS DBA <cs_dba@consistentstate.com>
2013-03-07 02:02 ` Re: HOT Standby - slave does not appear to be removing wal files Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
@ 2013-03-07 02:09 ` CS DBA <cs_dba@consistentstate.com>
2013-03-07 10:18 ` Re: HOT Standby - slave does not appear to be removing wal files Eduardo Morras <emorrasg@yahoo.es>
0 siblings, 1 reply; 6+ messages in thread
From: CS DBA @ 2013-03-07 02:09 UTC (permalink / raw)
To: Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>; +Cc: pgsql-admin
On 3/6/13 7:02 PM, Prashanth Ranjalkar wrote:
> While setting up Hot Standby, it's not a method to copy the primary
> database using dump and restore it on standby using restore command
> as it's a logical backup. I doubt your WAL archiving is enabled and
> files are copied to archive destination that set in archive_command
> therefore keep accumulating under pg_xlog directory.
>
> For hot standby setup, archiving must be enabled and online backup
> (file system level) needs to be taken for setting up Standby server
> and recovery.conf need to be prepared on slave server.
>
> Please go through
> http://www.postgresql.org/docs/9.2/static/high-availability.htmlfor
> more details.
> /*Thanks & Regards,*/
> //**
> /*Prashanth Ranjalkar*/
> /*Database Consultant & Architect*/
> /*Skype:prashanth.ranjalkar*/
> */www.postgresdba.net <http://www.postgresdba.net/>/*
>
>
>
> On Thu, Mar 7, 2013 at 7:02 AM, CS DBA <cs_dba@consistentstate.com
> <mailto:cs_dba@consistentstate.com>> wrote:
>
> All;
>
> We're doing a migration as follows:
>
> 1) we setup a HOT standby pair
>
> 2) we did an import of a db that wan backed up via pg_dump
>
> The pg_xlog directory has > 1700 files in it, It seems that the
> slave is way behind which I get since we just imported a 55GB
> database but the slave is not removing it's local pg_xlog wal files.
>
> We actually stopped the import when the slave hit 97% full for
> the file system where we have it's pg_xlog directory mounted
> after like 30minutes the file count in the slave's pg_xlog dir has
> not changed.
>
> Any thoughts per why the slave is not removing these files?
>
> Thanks in advance
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org
> <mailto:pgsql-admin@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
We actually are in HOT standby mode, and it is working. However we are
also migrating a second db from an older (8.4) server into this master
so we did a dump (using the 9.2.2 pg_dump binary) from the 8.4 db and
then restored it into our current 9.2 master
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HOT Standby - slave does not appear to be removing wal files
2013-03-07 01:32 HOT Standby - slave does not appear to be removing wal files CS DBA <cs_dba@consistentstate.com>
2013-03-07 02:02 ` Re: HOT Standby - slave does not appear to be removing wal files Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
2013-03-07 02:09 ` Re: HOT Standby - slave does not appear to be removing wal files CS DBA <cs_dba@consistentstate.com>
@ 2013-03-07 10:18 ` Eduardo Morras <emorrasg@yahoo.es>
2013-05-23 13:02 ` Re: HOT Standby - slave does not appear to be removing wal files Maletin von Oertzen <maletin@cpan.org>
0 siblings, 1 reply; 6+ messages in thread
From: Eduardo Morras @ 2013-03-07 10:18 UTC (permalink / raw)
To: pgsql-admin
On Wed, 06 Mar 2013 19:09:43 -0700
CS DBA <cs_dba@consistentstate.com> wrote:
> We actually are in HOT standby mode, and it is working. However we are
> also migrating a second db from an older (8.4) server into this master
> so we did a dump (using the 9.2.2 pg_dump binary) from the 8.4 db and
> then restored it into our current 9.2 master
I don't know, but maybe the dump from 8.4 to your master is one big transaction that must be rolled by the slave. So the slave must store all wal files to be able to undo the big dump transaction.
--- ---
Eduardo Morras <emorrasg@yahoo.es>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HOT Standby - slave does not appear to be removing wal files
2013-03-07 01:32 HOT Standby - slave does not appear to be removing wal files CS DBA <cs_dba@consistentstate.com>
2013-03-07 02:02 ` Re: HOT Standby - slave does not appear to be removing wal files Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
2013-03-07 02:09 ` Re: HOT Standby - slave does not appear to be removing wal files CS DBA <cs_dba@consistentstate.com>
2013-03-07 10:18 ` Re: HOT Standby - slave does not appear to be removing wal files Eduardo Morras <emorrasg@yahoo.es>
@ 2013-05-23 13:02 ` Maletin von Oertzen <maletin@cpan.org>
0 siblings, 0 replies; 6+ messages in thread
From: Maletin von Oertzen @ 2013-05-23 13:02 UTC (permalink / raw)
To: pgsql-admin
Eduardo Morras <emorrasg <at> yahoo.es> writes:
>
> On Wed, 06 Mar 2013 19:09:43 -0700
> CS DBA <cs_dba <at> consistentstate.com> wrote:
> > We actually are in HOT standby mode, and it is working. However we are
> > also migrating a second db from an older (8.4) server into this master
> > so we did a dump (using the 9.2.2 pg_dump binary) from the 8.4 db and
> > then restored it into our current 9.2 master
>
> I don't know, but maybe the dump from 8.4 to your master is one big
transaction that must be rolled by the
> slave. So the slave must store all wal files to be able to undo the big
dump transaction.
>
> --- ---
> Eduardo Morras <emorrasg <at> yahoo.es>
>
We have the same problem, when we drop a big schema
and import it again (with \i and autocommit on).
To use pg_basebackup with the option -x, we have
wal_keep_segments set to 32.
And we use pg_archive_cleanup_command:
archive_cleanup_command='/usr/lib/postgresql/9.2/bin/pg_archivecleanup
/var/lib/postgresql/9.2/main/pg_xlog %r'
But we get at least 118 WAL-Files.
In the past, we did not set ssl_renegotiation_limit=0
and the archive_command copied quickly so many
WAL-Files to pg_xlog, that the postgresql-database
stopped with no space left on device /var.
https://bugs.launchpad.net/ubuntu/+source/postgresql-9.1/+bug/1018307
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: HOT Standby - slave does not appear to be removing wal files
2013-03-07 01:32 HOT Standby - slave does not appear to be removing wal files CS DBA <cs_dba@consistentstate.com>
@ 2013-12-23 17:35 ` olimaz <oliver.mazariegos@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: olimaz @ 2013-12-23 17:35 UTC (permalink / raw)
To: pgsql-admin
I had the very same problem, and after checking postgres log file, found the
following warning every 5 minutes:
archive_cleanup_command "pg_archivecleanup /srv/postgresql/wals/ %r":
return code 32512
wich means the pg_archivecleanup script was not found.
The solution: edit the file /var/lib/postgresql/9.1/main/recovery.conf and
make sure it is set like this:
archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup
/srv/postgresql/wals/ %r'
(be careful: I keep my wal files in a special dir "/srv/postgresql/wals/"
yours will be a different location)
in my case, after 5 minutes, my 140 GB wal dir changed to just 2 files,
being one of them a .backup file. Before that, I did not had a .backup file
at all, so I could not make use of wal cleanup procedures.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/HOT-Standby-slave-does-not-appear-to-be-removing-wal-files-t...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-12-23 17:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-07 01:32 HOT Standby - slave does not appear to be removing wal files CS DBA <cs_dba@consistentstate.com>
2013-03-07 02:02 ` Prashanth Ranjalkar <prashant.ranjalkar@gmail.com>
2013-03-07 02:09 ` CS DBA <cs_dba@consistentstate.com>
2013-03-07 10:18 ` Eduardo Morras <emorrasg@yahoo.es>
2013-05-23 13:02 ` Maletin von Oertzen <maletin@cpan.org>
2013-12-23 17:35 ` olimaz <oliver.mazariegos@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox