Postgresql – How to stop/disable PITR and safely clean WAL segments

postgresql

Our production server is running PostgreSQL v8.2.3 on CentOS release 5.2 (Final).

We've setup PITR in our production server. For some reasons, after setting up PITR, we're not able to manage and maintain it. Eventually, our WAL archive drive (secondary drive) has become full (100% use) and additional WAL archive segments that are to be archived got accumulated in pg_xlog/ directory itself (which is available in primary drive)

PITR SETUP DETAILS

There are 2 drives:

Primary drive (pgsql/data/ directory resides) is 400 GB.
Secondary drive (WAL archives) is 300 GB. All WAL archives are written to this secondary drive.

Now, we've decided to stop/disable PITR.

My question is, in this situation, how do I stop/disable PITR and safely clean up all WAL segments from both the drives?

What are the recommended/suggested ways? Experts advice/idea/suggestion on this are highly appreciated.

Best Answer

To stop the archiving in 8.2, you set archive_command to empty ('') and restart the server. The data directory should clean itself up. The archives on your secondary drive you can just throw away.

Related Solutions

Postgresql – How to upgrade PostgreSQL minor releases without a dump/restore

If you are doing a minor version upgrade, nothing will or should touch your data directory. If you are using binary packages, just run whatever upgrade procedure they provide (apt/yum upgrade, etc.). If building from source, just do make install over the old files and then restart the server (pg_ctl restart or whatever you're using).

The documentation could perhaps be amended to say that you could even install the new binaries before restarting the server.

PostgreSQL Backup – Missing WAL File on Standby Server Following a Backup

This part generating recovery.conf is suspicious:

echo "restore_command = 'cp -f $PGDATA/pg_xlog/%f $PGDATA/pg_xlogRestored'" >>$PGDATA/recovery.conf

It seems that the source and destination of this restore_command are reversed.

Compare yours to the example from the doc in Continuous archiving:

restore_command = 'cp /mnt/server/archivedir/%f %p'

The restore must copy from the archive directory (which typically is outside $PGDATA and entirely under the DBA's control) into $PGDATA/pg_xlog (as expanded by %p).

I'm assuming that $PGDATA/pg_xlogRestored is your archive directory since I don't see what else it could be. Personally I'd place it outside of $PGDATA by principle but that's not mandatory.

Best Answer

Related Solutions

Postgresql – How to upgrade PostgreSQL minor releases without a dump/restore

PostgreSQL Backup – Missing WAL File on Standby Server Following a Backup

Related Question