First I run this query to see the running queries:
select * from pg_stat_activity;
then I run this query to stop them:
SELECT pg_cancel_backend(pid);
but, when I run the pg_stat_activity again, it still shows all the queries!
why it didn't kill the queries?
A number of possible explanations:
You're not looking at an active query, the query text is just the last query that ran on a currently-idle backed. In that case pg_cancel_backend will do nothing since there's nothing to cancel. Check the state field in pg_stat_activity.
The active query is running in extension code that does not CHECK_FOR_INTERRUPTS() during whatever it is doing. This is most typically the case when you're running some extension that does lots of CPU, I/O or network activity using its own libraries, sockets, etc. Particularly things like PL/Perl, PL/Python, etc.
The active query is running in PostgreSQL back-end code that doesn't check for interrupts in a long running loop or similar. This is a bug; if you find such a case, report it.
The backend is stuck waiting on a blocking operating system call, commonly disk I/O or a network socket write. It may be unable to respond to a cancel message until that blocking operation ends, but if it receives a SIGTERM its signal handler can usually cause it to bail out, but not always.
In general it's safe to use pg_terminate_backend as a "bigger hammer". SIGTERM as sent by pg_terminate_backend() will often, but not always, cause a backend that can't respond to a cancel to exit.
Do not kill -9 (SIGKILL) a PostgreSQL backend (postgres process). It will cause the whole PostgreSQL server to emergency-restart to protect shared memory safety.