Thread: cannot execute VACUUM during recovery

cannot execute VACUUM during recovery

From

github kran

Date:

27 February 2019, 17:43:14

Hello Team,

We are using a PostgreSQL 9.6 and seeing the below error while trying to run a VACUUM on one of our live tables running in Production. We wanted to clean up some DEAD tuples on the table.

Command: VACUUM (ANALYZE,VERBOSE) table_name.

ERROR: cannot execute VACUUM during recovery

Thanks

Kranthi

Re: cannot execute VACUUM during recovery

From

Stephen Eilert

Date:

27 February 2019, 21:39:10

Are you running Vacuum on the slave node? It has to run on the master.

Thanks,

– Stephen

On Feb 27, 2019, 6:43 AM -0800, github kran <githubkran@gmail.com>, wrote:

Hello Team,

We are using a PostgreSQL 9.6 and seeing the below error while trying to run a VACUUM on one of our live tables running in Production. We wanted to clean up some DEAD tuples on the table.

Command: VACUUM (ANALYZE,VERBOSE) table_name.

ERROR: cannot execute VACUUM during recovery

Thanks
Kranthi

Re: cannot execute VACUUM during recovery

From

Michael Paquier

Date:

28 February 2019, 05:29:37

On Wed, Feb 27, 2019 at 10:39:10AM -0800, Stephen Eilert wrote:
> Are you running Vacuum on the slave node? It has to run on the master.

VACUUM performs an equivalent write activity so it has to be
restricted.  ANALYZE can work though.
--
Michael

Attachment

signature.asc

Re: cannot execute VACUUM during recovery

From

github kran

Date:

02 March 2019, 21:46:33

On Wed, Feb 27, 2019 at 12:39 PM Stephen Eilert <contact@stepheneilert.com> wrote:

Are you running Vacuum on the slave node? It has to run on the master.

Thanks,

– Stephen

Thanks Stephen for your advice Iam running the VACUUM command from 2 of the linux instances using the below command. We were running some scripts from these linux boxes and surprised to see 1 works the other does not so wondering why does it happen.

Command: VACUUM VERBOSE myTableTest;

1) Running from Linux Instance1

Command Iam running -> psql -h clusterName -U myUserName -d myPostgresDB

There is no .pgpass setup on this linux instance so I had to manually enter the password here. VACUUM on the table Iam running does not work and throws an error as ERROR: cannot execute VACUUM during recovery

2) Running from Linux Instance2

Command Iam running -> psql -d myPostgresDB -h clusterName -U myUserName

This option had a .pgpass file at the root in this linux instance and I did not pass any password here, the

VACUUM on the table Iam running works.

pgpass is setup here and contains -> clusterName:5432:myPostgresDB:myUserName:myDBPassword

Thanks

- Kran.

On Feb 27, 2019, 6:43 AM -0800, github kran <githubkran@gmail.com>, wrote:
Hello Team,

We are using a PostgreSQL 9.6 and seeing the below error while trying to run a VACUUM on one of our live tables running in Production. We wanted to clean up some DEAD tuples on the table.

Command: VACUUM (ANALYZE,VERBOSE) table_name.

ERROR: cannot execute VACUUM during recovery

Thanks
Kran