Обсуждение: Problem of pg_wal filling up

Поиск
Список
Период
Сортировка

Problem of pg_wal filling up

От
Дата:

Dear Experts,

 

Post the setup of streaming replication –MASTER and one SLAVE in PostgreSQL 12 , the pg_wal in both MASTER/SLAVE are - filling up.

 

My MASTER is in archive log mode and SLAVE in no archive log mode.

 

MASTER

 

postgres=# show archive_command;

                                  archive_command

------------------------------------------------------------------------------------

test ! -f /data/tablespaces/archivedir/%f && cp %p /data/tablespaces/archivedir/%f

(1 row)

 

SLAVE

 

postgres=# show archive_command;

archive_command

-----------------

(disabled)

(1 row)

 

 

Is it advisable to change  default wal location (pg_wal)? Or any other options apart from using pg_archivecleanup?

 

Thanks..

 

Re: Problem of pg_wal filling up

От
Jeff Janes
Дата:
On Fri, Jan 31, 2020 at 11:20 AM <soumik.bhattacharjee@kpn.com> wrote:

Dear Experts,

 

Post the setup of streaming replication –MASTER and one SLAVE in PostgreSQL 12 , the pg_wal in both MASTER/SLAVE are - filling up.


Do you have any replication slots on the replica?  When you created the replica, you were supposed to exclude the directory pg_replslot.  If you didn't the replica might have "inherited" slots it should not have.
 

 

My MASTER is in archive log mode and SLAVE in no archive log mode.

 

MASTER

 

postgres=# show archive_command;

                                  archive_command

------------------------------------------------------------------------------------

test ! -f /data/tablespaces/archivedir/%f && cp %p /data/tablespaces/archivedir/%f


Is this command succeeding?  Do you see messages in the log file about it failing?  Are new files showing up in '/data/tablespaces/archivedir/'?

Does the master have any old replication slots which have been abandoned or are falling behind?  Check the view pg_replication_slots.  

 

Is it advisable to change  default wal location (pg_wal)? Or any other options apart from using pg_archivecleanup?


pg_archivecleanup is for cleaning up the archive (which is apparently '/data/tablespaces/archivedir/').  It is *not* for cleaning up pg_wal.

Cheers,

Jeff

RE: Problem of pg_wal filling up

От
Дата:

 

 

From: Jeff Janes <jeff.janes@gmail.com>

Dear Experts,

 

Post the setup of streaming replication –MASTER and one SLAVE in PostgreSQL 12 , the pg_wal in both MASTER/SLAVE are - filling up.

 

Do you have any replication slots on the replica?  When you created the replica, you were supposed to exclude the directory pg_replslot.  If you didn't the replica might have "inherited" slots it should not have.

 

  • No I haven’t created any replication slots on the replica, but its keep filling the logs in pg_wal? So does it mean it can go anywhere without a slot?

 

My MASTER is in archive log mode and SLAVE in no archive log mode.

 

MASTER

 

postgres=# show archive_command;

                                  archive_command

------------------------------------------------------------------------------------

test ! -f /data/tablespaces/archivedir/%f && cp %p /data/tablespaces/archivedir/%f

 

Is this command succeeding?  Do you see messages in the log file about it failing?  Are new files showing up in '/data/tablespaces/archivedir/'?

 

Does the master have any old replication slots which have been abandoned or are falling behind?  Check the view pg_replication_slots.  

 

 

Is it advisable to change  default wal location (pg_wal)? Or any other options apart from using pg_archivecleanup?

 

pg_archivecleanup is for cleaning up the archive (which is apparently '/data/tablespaces/archivedir/').  It is *not* for cleaning up pg_wal.

 

Cheers,

 

Jeff