Thread: HOT Standby - slave does not appear to be removing wal files

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

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

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

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
theslave must store all wal files to be able to undo the big dump transaction. 

---   ---
Eduardo Morras <emorrasg@yahoo.es>

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

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-tp5747774p5784441.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.