PostgreSQL ERROR: cannot execute COPY FROM in a read-only transaction

ERROR: cannot execute COPY FROM in a read-only transaction

This is usually happening in the read-only replica instance while executing the COPY queries.

We can ensure this by running the following query in psql shell.

>SELECT pg_is_in_recovery();

This will give the result as true if the instance is a read-only replica in hot_standby mode. We can't change this as it will break the replication process. So run the required queries in master server itself.

If the master server itself facing this issue, check whether the file 'recovery.conf' existing in the data directory. If its existing, rename the file and restart the PostgreSQL server.

Now its possible to run the queries without any issues. Please note that to follow this process only if the server is master.

That's all…