PostgreSQL Error – WARNING: canceling wait for synchronous replication due to user request
I needed to create index on my PostgreSQL cluster today. It was 1 master performing synchronous streaming replication to 1 slave. I did the following:
1. Created a new tablespace on master to store all the indexes.
#pgsql create tablespace tablespace_index location ‘/var/lib/pgsql/9.2/data/tablespace’;
2. Create the actual index itself.
CREATE INDEX CONCURRENTLY certificatedata_idx2 ON CertificateData (username) tablespace tablespace_index;
The index creation hangs. I checked the pg_stat_activity and saw the the query was still active. It took me about 2 hours before I decide to stop the index creation. When I did a Ctrl-C, I saw the following:
WARNING: canceling wait for synchronous replication due to user request DETAIL: The transaction has already committed locally, but might not have been replicated to the standby. ^CCancel request sent ERROR: canceling statement due to user request
The error speaks for itself. It means you have an issue with the replication to the slave database. Now, this is weird. I decided to look at the log files. And I found out that the replication has stopped because I did not create the tablespace directory (/var/lib/pgsql/9.2/data/tablespace) on the slave! Thus, the replication process could not create an tablespace and the slave node crashes. After creating the directory, the index creation took only less than 20 minutes on a 7GB table!
Thankfully, all these was done in the staging environment.